C# / SQL Best Practices

[kreplech]

Hello All,I have a question regarding SQL industry best practices. Specifically calling stored procedures from server side scripting (in this case C#). My current sproc and C# pseudo-coded below - question to follow.SQL:create putData{@recID@actionFlag@field1@field2@field3...@fieldx}AS-- actionFlag = 0 indicates that this is an insert statementif @actionFlag=0 BEGIN TRANSACTION insert into someTable (allValues) values (allValues)COMMITif @actionFlag=1BEGIN TRANSACTION if @field1 NOT LIKE '' update someTable set field1 = @field1 if @field2 NOT LIKE '' update someTable set field2 = @field2 -- and so on until every param has been testedCOMMITGOC# (well really just pseudo-code)protected void callSproc(int actionFlag, int RecID, string field1, string field2, ... etc){open databasecall putData // sproc nameadd paramsexecute}protected void onSomeAction_UpdateField1(object sender, EventArgs e){callSproc(1,1234,"Field1 Data","","", ... etc)}OK - lot's of nonsense there, but here's the question(s)There's just got to be a better way... whether it's some fancy SQL or OOP that's currently beyond me... specifically because, [sometable] could be (and is) huge. The sp and C# have to be updated everytime the table changes or i decide i want to update/insert a previously unused field in the table.Also, I know I shouldn't have to test for NOT LIKE "" in the sproc. It's just plain dirty. I can't pass NULLS by virtue of C# sending blank params and sql complaining... but once again, there's just got to be a better way.I don't expect anybody to do all this work for me - but if you could point me in the right direction that'd be just great.Also, I'll be posting this identically to the C# forum because I'm not sure if it requires a fix from the SQL side or the C# side.I appreciate any help at all!!!Thanks,M

[aspnetguy]

There is no need to execute aq statement for every field in the table. BTW you have no WHERE clause so those updates will update those fields for EVERY record.There is no clean way to do what you want all in one procedure. I would start by having seperate procedures for inserting and updating. I assume by your code that if @fieldx is "" you don't want to update that field???

[kreplech]

I assume by your code that if @fieldx is "" you don't want to update that field???

nope - @fieldx was just supposed to indicate that the statements will go on for quite a while. there are dozens of fields in that table. i suppose i should send the name of the column to be updated to the sproc / perform update per field. that just seems extremely intensive for large updates.

[aspnetguy]

I was using fieldx to indicate any field as you where evaluating each of them. For instance if @field1 = '' are you going to insert NULL or not update the field??? I think you should be doing your validation for empty fields before you send the Stored Procedure (in your C# or Javascript). That will make your SP more efficient and less complicated.

[kreplech]

I was using fieldx to indicate any field as you where evaluating each of them. For instance if @field1 = '' are you going to insert NULL or not update the field??? I think you should be doing your validation for empty fields before you send the Stored Procedure (in your C# or Javascript). That will make your SP more efficient and less complicated.

I guess that's the problem. The only way I can think of only sending the field I need updated is to send the field name along with the update value. But, if I have 20 fields that all need to be updated, I'm opening connection to DB, calling function, closing connection to DB per field. This seems less efficient. No?

[kreplech]

OK - Maybe this is the best way:SQL:putData sproc accepts params @fieldname @fieldvalue @recordIDC# functions:openDBcloseDBputDataLogic:If I want to update field1, field2, field3 thenopenDBputData(@fieldName, @fieldValue, @recordID) // for field1putData(@fieldName, @fieldValue, @recordID) // for field2putData(@fieldName, @fieldValue, @recordID) // for field3closeDBis that stupid? it saves me from have to call putData(field1,field2,field3, .... , fieldx) and sending mostly NULL values or blanks when I only want to update one field.Also, I realize this post has drifted away from .NET progging - sorry 'bout that... but thanks for the help!

[kreplech]

Based on responses I've received in other forums - this is dynamic sql thereby punishable by death. I'm looking into more socially acceptable means of implementing dSQL; however, in the meantime i'll bite the bullet and stick with my cumbersome sprocs.Thanks again,M

OK - Maybe this is the best way:SQL:putData sproc accepts params @fieldname @fieldvalue @recordIDC# functions:openDBcloseDBputDataLogic:If I want to update field1, field2, field3 thenopenDBputData(@fieldName, @fieldValue, @recordID) // for field1putData(@fieldName, @fieldValue, @recordID) // for field2putData(@fieldName, @fieldValue, @recordID) // for field3closeDBis that stupid? it saves me from have to call putData(field1,field2,field3, .... , fieldx) and sending mostly NULL values or blanks when I only want to update one field.Also, I realize this post has drifted away from .NET progging - sorry 'bout that... but thanks for the help!