Update big table (a lot of columns). C# .NET

Question

I have to update a big table with over 270 update fields.

I am relative new to .NET and need advise what is better to use in this case: SqlCommand, some kind of memory-mapped table or DataSet or maybe it exists some kind of auto-generated objects using meta-data from DB? Please help.

Reason: I have an old big Delphi7 application, a part of which is responsible for listening on socket some packets which are marshaled to big structures and, in final step, stored in DB. Now I am porting this part to new C# Service and at least actually i have to preserve the same logic. The problem is that structure is BIG (over 220 fields) and tables where it is stored have near 300 fields. From my structure of 220 fields are deducted/calculated other ~50 fields and all should be update in DB. Actual Delphi code is ugly ant it grew over several years like table itself, something like this:

'UPDATE TABLE_NAME ' +
  '  MSG_TYPE = ' + IntToStr(integer(RecvSruct.MSG_TYPE)) + ' ' + 
  ' ,SomeFLOATfield = ' + FloatToStr(RecvSruct.SomeFLOATfield) + ' ' + 
  ... //and other over 270 fileds here
'WHERE ID = ' + IntToStr(obj.ID)

no any dynamic SQL, etc.. Actually I cannot change DB structure.. so that i have to play in code only and I am not sure if it is pertinently to translate code. Table is used for some reports and statistics. Some of calculated/deducted fields have to deal with some constants in source-code.

used dev-tools: MS SQL Server 2000, C# .net2.0, VS2008

Answer 1

The simplest solution applies here because the way ole db works is with strings. So, to pass 270, 500, 1000 parameters, all I do is pass a single string, a string containing 270 parameters is probably well under 2kb... which in modern computing... carry over the 1... doesn't have a performance penalty. There's an xml solution here, but that's just apples and oranges, you're still passing the string, it would require extra code to handle the xml however. So... your architecture should look like:

1. Stored procedure on SQL server with 270 input parameters:

    Create Procedure sp_Example1 
    (@param1 [type], @param2 [type], @param3 [type], etc...)
    AS 
    BEGIN
    [SQL statements]
    END
   

2. A command object with 270 parameters:

   SqlCommand cmd = new SqlCommand("sp_Example1", [sqlconnectionstring]);
   cmd.Parameters.Add(New SqlParameter("@param1", param1.value));
   cmd.Parameters.Add(New SqlParameter("@param2", param2.value));
   cmd.Parameters.Add(New SqlParameter("@param3", param3.value));
   

Remember, you're still doing a pretty intensive operation, but your benchmark should be the old application. If it's a little bit worse, I wouldn't worry about it, since the framework requires more computing overhead.

I have no idea why it won't format the code...