Friday, October 12, 2007

LINQ to String Formatting (SQL)

I pulled the LINQ bits out of a larger context. I wanted a way to pass in a table name and list of fields and have it format them to parameterized UPDATE and INSERT statements (the full code goes out and figures the field types and adds appropriately typed params to a Command object).

For the UPDATE, I have a Where() to exclude the "id" column from the list of updated columns then only one Select() which concatenates them as "[fieldname] = @fieldname".

public string GetUpdateSQL( string table, string[] fields )
return string.Format( @"update {0} set {1} WHERE ([id] = @id)", table, string.Join( ",",
fields.Where(f => f != "id").Select( f => "["+f+"] = @" + f ).ToArray() ) );

Notice that for the INSERT, I have two separate Select()s, one to build the field list & one for the param list.

public string GetInsertSQL( string table, string[] fields )
return string.Format( "insert into {0} ({1}) values ({2}) ", table,
string.Join( ",", fields.Select( f => "["+f+"]" ).ToArray() ),
string.Join( ",", fields.Select( f => "@"+f ).ToArray() );

If I were to pass in "Changes" as the table and a field array of "id","product_id", "description" the function results would be:

update Changes set [product_id] = @product_id, [description] = @description where [id] = @id


insert into Changes ([id], [product_id], [description]) values (@id, @product_id, @description)

LINQ does not do anything here that was impossible before... it just makes it so you don't have to do your own for loops or have any prep variables that would only exist just to be passed into the Format() call.

No comments: