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.