C# batch update to database using Stored Procedure

Question

I want to run my update stored procedure to update just two columns in a table(this table is having many other columns as well).

Currently I have implemented as follows -

foreach (Object obj in customCollection)
{
    string[] updatedValues = GetUpdatedValues(obj.Property1);

    using (SqlConnection sqlConnection = new SqlConnection(connString))
    {
         sqlConnection.Open();

         SqlParameter[] sqlParams = new SqlParameter[2];
         sqlParams[0] = new SqlParameter("@column1", SqlDbType.Float) { Value = updatedValues[0]};
         sqlParams[1] = new SqlParameter("@column2", SqlDbType.Float) { Value = updatedValues[1] };

         using (SqlCommand command = new SqlCommand("upUpdateProcedure", sqlConnection))
         {
              command.CommandType = CommandType.StoredProcedure;
              command.Parameters.AddRange(sqlParams);
              DatabaseHelper.ExecuteNonQuery(command);
         }
    }
}

So, as you see above code is calling database for each object in the collection.

I went through few links for 'batch update from C# to SQL Server', but most of these links have suggested to load table inside C# code, update table rows and then call DataAdapter.Update().

However, just wondering if there is any other known way to handle this scenario differently - I mean I don't want to load whole table in memory as it has got many other column data as well.

Please guide.

Answer 1

One thing I do a lot is send XML to SQL in one chunk. Performs much better than calling the DB several times in a loop.

Jon Galloway has and an oldie but a goodie here that you can reference.

All you need to do is convert your data or columns to XML. If you have this data in a class it's really simple. Check out this extension method - I don't remember where I found this code so I was not able to attribute it properly.

Answer 2

If you are using SQLServer 2008 or later, you can populate a DataTable, and pass it to the stored procedure as a table valued parameter.

Info on table valued parameters : http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQL

The stored proc can access the table parameter like a table variable, so you can perform the update in one chunk, which is good for performance.

This approach is similar to Billy Coover's XML approach, but using a DataTable instead of XML.

Answer 3

Seems to me that what you are doing should work fine. Alternatively you could build a 'batch' of updates (maybe 50, 100 or more updates each), and submit them all in chunks, but not sure that would help with performance at all, and would probably be less readable.

Are you having a performance problem? How many items are in the customCollection?

Answer 4

yes, build your sql query dynamically, yeah and I know it's bad performance compared to stored procedure, but considering all the connection open and close overhead you have in your code, it might not be a bad idea. I also notice that in your code you are trying to open and close the connection asap, it is good practice. However, I find that when I keep 1 connection opened for multiple update, it is ALOT faster compared to having open and close the connection multiple times. Scaleing might not be as good though because you are keeping a connection opened.

Answer 5

Get a DataTable from a TableAdapter (auto generated):

FooTableAdaptor tableAdapter = new FooTableAdapter();
FooDataTable dataTable = FooTableAdaptor.MyQuery(...);

Scroll through rows of DataTable making changes - this is just local.
Call

tableAdapter.Update(dataTable)  

to write the change in one hit.