How To Perform Batch Update In Sql Through C# Code

I want to update multiple rows like below update mytable set s_id = {0} where id = {1} (Here s_id is evaluated based on some complex logic). For performance reason, updates shou

Solution 1:

Yes, you can use an SqlDataAdapter.

The SqlDataAdapter has InsertCommand and UpdateCommand properties which allow you to specify an SQLCommand to use to insert new rows into the database and an SqlCommand to update rows in the database respectively.

You can then pass a DataTable to the Update method of the dataadapter, and it will batch up the statements to the server - for rows in the DataTable that are new rows, it executes the INSERT command, for modified rows it executes the UPDATE command.

You can define the batch size using the UpdateBatchSize property.

This approach allows you to deal with large volumes of data, and allows you to nicely handle errors in different ways, i.e. if an error is encountered with a particular update, you can tell it to NOT throw an exception but to carry on with the remaining updates by setting the ContinueUpdateOnError property.

Solution 2:

Yes, you can build a plain-text SQL command (parameterized for security), like this:

SqlCommand command = new SqlCommand();
// Set connection, etc.
for(int i=0; i< items.length; i++) {
    command.CommandText += string.Format("update mytable set s_id=@s_id{0} where id = @id{0};", i);
    command.Parameters.Add("@s_id" + i, items[i].SId);
    command.Parameters.Add("@id" + i, items[i].Id);
}
command.ExecuteNonQuery();

Solution 3:

Use a StringBuilder (System.Text.StringBuilder) to build your Sql, such as:

StringBuildersql=newStringBuilder();
intbatchSize=10;
intcurrentBatchCount=0;
SqlCommandcmd=null; // The SqlCommand object to use for executing the sql.for(inti=0; i < numberOfUpdatesToMake; i++)
{
  intsid=0; // Set the s_id hereintid=0; // Set id here
  sql.AppendFormat("update mytable set s_id = {0} where id = {1}; ", sid, id);

  currentBatchCount++;
  if (currentBatchCount >= batchSize)
  {
    cmd.CommandText = sql.ToString();
    cmd.ExecuteNonQuery();
    sql = newStringBuilder();
    currentBatchCount = 0;
  }
}

Solution 4:

Create a set of those updates (with the id's filled in), separate them by semicolon in one string, set the resulting string to a SqlCommand's CommandText property, then call ExecuteNonQuery().