Dynamically create SQL statements through .NET Program

Question

I'm working on a small application that verifies some entries in an Oracle database by comparing some entries on an excel sheet to the tables in an Oracle database so I'm only working on querying the database and nothing too complex.

I'm trying to get this to be completely dynamic so as I wouldn't have to make any code changes regardless of what the queries are however I'm running into a small issue when the table I'm querying has numeric and string type values.

If chkConnState() Then
sSql = "SELECT * FROM " & workSheetName & " WHERE "
For i = 1 To ds.Tables.Item(0).Columns.Count - 1
sSql &= ds.Tables.Item(0).Columns.Item(i).ToString & " = " & ds.Tables.Item(0).Rows.Item(i).Item(i).ToString & " AND "
Next
newSql = sSql.Substring(0, sSql.Length - 4)
End If

Here I am only gathering the SQL statement to query the table against. I grab the columns and rows information from the Excel(the information I know is correct and am using to check against what's in the table) and then remove the ending 'AND' from the query.

Is there any easy way to determine if the column is numeric or string? The issue I'm having now is I have no idea how to determine where I should add the "'" & value & "'" in string values dynamically. I would say just query by the PK but I'm not sure there's an easy way to find this either as it's different for every table and doing it the way above I will only retrieve one record rather than possible multiple. Is there a better way to go about this to make it dynamic?

Try using Integer.TryParse or Decimal.TryParse on the ToString items. — LarsTech
– LarsTech, Commented Oct 3, 2013 at 20:06
Your question is a little hard to follow. Are you trying to say how do I know the use when to use where colname = '1' vs where colname = 1. I know that you can quote the numeric data on some databases without problem -- don't recall if Oracle allows this — Gary Walker
– Gary Walker, Commented Oct 3, 2013 at 20:37

Whitey · Accepted Answer · 2013-10-03 21:24:15Z

2

As LarsTech said you can use TryParse to determine if the values are numeric.

If(Double.TryParse(ds.Tables.Item(0).Rows.Item(i).Item(i).ToString, Nothing), _
ds.Tables.Item(0).Rows.Item(i).Item(i).ToString, _ 
"'" & ds.Tables.Item(0).Rows.Item(i).Item(i).ToString & "'")

This will put single quotations around any value that is not numeric.

edited Oct 3, 2013 at 21:24

answered Oct 3, 2013 at 21:19

Whitey

4313 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aog · Accepted Answer · 2013-10-03 21:58:05Z

0

You can use the following Func to check fields easily

public static Func<string, bool> isNumeric = x => { int i = 0; return Int32.TryParse(x, out i); };

Simple usage

if (isNumeric(yourField)) {

} else {

}

answered Oct 3, 2013 at 21:58

aog

5143 silver badges15 bronze badges

Collectives™ on Stack Overflow

Dynamically create SQL statements through .NET Program

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related