[ACCEPTED]-How to perform batch update in Sql through C# code-sql
Yes, you can use an SqlDataAdapter.
The SqlDataAdapter 18 has InsertCommand and UpdateCommand properties which allow you to 17 specify an SQLCommand to use to insert new 16 rows into the database and an SqlCommand 15 to update rows in the database respectively.
You 14 can then pass a DataTable to the Update method 13 of the dataadapter, and it will batch up 12 the statements to the server - for rows 11 in the DataTable that are new rows, it executes 10 the INSERT command, for modified rows it 9 executes the UPDATE command.
You can define 8 the batch size using the UpdateBatchSize property.
This 7 approach allows you to deal with large volumes 6 of data, and allows you to nicely handle 5 errors in different ways, i.e. if an error 4 is encountered with a particular update, you 3 can tell it to NOT throw an exception but 2 to carry on with the remaining updates by 1 setting the ContinueUpdateOnError property.
Yes, you can build a plain-text SQL command 1 (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();
Use a StringBuilder (System.Text.StringBuilder) to 1 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;
}
}
Create a set of those updates (with the 4 id's filled in), separate them by semicolon 3 in one string, set the resulting string 2 to a SqlCommand's CommandText property, then 1 call ExecuteNonQuery().
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.