How to perform batch update in Sql through C# code

Question

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 should happen in batches. Is there any way to batch the update statements and execute the batch through single execute statements? I know in JAVA we can do this through JDBC. Is there similar way in C#?

Thanks in advance

Answer 1

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

StringBuilder sql = new StringBuilder();
int batchSize = 10;
int currentBatchCount = 0;
SqlCommand cmd = null; // The SqlCommand object to use for executing the sql.
for(int i = 0; i < numberOfUpdatesToMake; i++)
{
  int sid = 0; // Set the s_id here
  int id = 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 = new StringBuilder();
    currentBatchCount = 0;
  }
}