Generating String SQL Statement 1

[ConfusedNewby]

I am very new to OO and C#.

I want to write a method which returns a String. It will accept arguments a
tablename(string) a collection of Structs (hope this is a better option than class) and a parameter indicating whether to generate an insert or an update (char).

The Struct definition is :-
public struct FieldDef{
public String _FieldName;
public Char _FieldType;
public Boolean _IsID;
}
where Fieldname is just a name and FieldType is a character indicating
String/Integer/etc.

The method should take the given collection and output an SQL statement
of the format

"Insert into _Invoice (cInvoiceCode, dInvoiceDate, cInvoiceCust, iInvoiceAmt)
values ('{0}', '{1}', '{2}', {3})"
or
"Update _Invoice set cInvoiceCode='{0}', dInvoiceDate='{1}',
cInvoiceCust='{2}', iInvoiceAmt={3} where iIdInvoice={4}"

I want to use the fieldtype to determine whether to enclose the format
specifiers in quotes or not.

How do I get to the best solution.

Tried an ArrayList, but it doesn't seem to work that well...

Thanks

 

[jose74]

Hi,

my experience is not as well, too - but I had the problem to build a "where"-clause. So I had a class with columnname, fieldtype, operator and value called "filter".
To create the filter I have a generic List<filter>. So I can loop through the items and create the "where"-clause.

Hope, I could help!

 

[ConfusedNewby]

Hi jose74,

Thank you for your reply. I will check into it. Just to make sure: You suggest that I rather use a class than a struct to define the field definitions...?

 

[jose74]

Well, I did it! And it works! But I do not know if this is the best solution, but the handling is good. You have an object of type filter (in your case field) and can loop through the list with "foreach".

 

[simonchristieis]

I came across this type of problem before whne creating dynamic sql statements using xsl.

The way I worked round it was to pass all the values using quotes, sql server never complained.

eg:

update myTable
set intField = '1',
charField = 'bob'
where foo = 'bar'

dont know if this helps

simon

 

[stevexff]

If you use placeholders in a prepared statement, you don't have to worry about quoting. You also get protection from SQL injection attacks as a bonus...

 

[ConfusedNewby]

Hi stevexff,

How does the placeholders work? Can you still generate the SQL on the go with only fields affected, or do you need to update all fields when using placeholders? (sorry if this is a dumb question)

 

[stevexff]

I don't have a .NET environment here to give you a working demo, but basically the steps would be[ol][li]Generate a dynamic SQL string "SELECT blah FROM table WHERE blahdate BETWEEN ? and ?"[/li][li]PREPARE the statement[/li][li]EXECUTE the statement passing in the parameters. SQL slots them in in the positions specified by the placeholder question marks. Retrieve the results as normal.[/li][li]Because the statement is prepared before the parameters are added, there is no way for someone to type in a start date of ";DROP TABLE table;" for example, and have it executed.[/li][/ol]HTH