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

score 22 · Accepted Answer · answered Feb 24 '10 at 15:35

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.

score 14 · Answer 2 · answered Feb 24 '10 at 15:34

14

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();

answered Feb 24 '10 at 15:34

Jeff Sternal

47,787
8
93
120

I like this approach. Much easier to implement/debug than SqlDataAdapter for my purposes. – Dan Bechard Aug 19 '14 at 20:05
@Arvand How do you address this? – Aroueterra Mar 31 '17 at 08:11
@Arvayne Depends on what you are doing, but transactions usually are a good choice. – Saber Jun 07 '17 at 15:54

score 3 · Answer 3 · answered Feb 24 '10 at 15:32

3

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;
  }
}

answered Feb 24 '10 at 15:32

Rob

45,296
24
122
150

3

Probably because SQL Injection attacks are possible with your code – GvS Feb 24 '10 at 16:25
1

Not if its just integer values – Gunnar Steinn Oct 24 '11 at 13:01
1

@GvS SQL Injection is not relevant when there is no untrusted input. Anyone with the ability to change `int id = 0` could just as easily change the whole query. – Dan Bechard Aug 19 '14 at 16:07
Not really a SQL Injection issue in this case, but a bad practice. Use Parameters for security. – Igor Quirino Dec 01 '16 at 15:23

score 0 · Answer 4 · answered Feb 24 '10 at 15:27

0

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().

answered Feb 24 '10 at 15:27

David Morton

16,338
3
63
73

How to perform batch update in Sql through C# code

4 Answers4

Linked