Efficient Way to Update a lot of Rows from C#

Question

I have a program where I open a SqlConnection, load up a list of objects, modify a value on each object, then update the rows in the SQL Server database. Because the modification requires string parsing I wasn't able to do with with purely T-SQL.

Right now I am looping through the list of objects, and running a SQL update in each iteration. This seems inefficient and I'm wondering if there is a more efficient way to do it using LINQ

The list is called UsageRecords. The value I'm updating is MthlyConsumption.

Here is my code:

foreach (var item in UsageRecords)
{
    string UpdateQuery = @"UPDATE tbl810CTImport 
                           SET MthlyConsumption = " + item.MthlyConsumption +
                           "WHERE ID = " + item.Id;
    SqlCommand update = new SqlCommand(UpdateQuery, sourceConnection);
    update.ExecuteNonQuery();
}

Unrelated to your question: [use parameterized sql](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements), to avoid injection and also to stop bad characters from breaking the query (like `'` characters if you're inserting a string) — Frank Bryce, Jun 01 '16 at 17:19
Related to your question: [see this post](https://stackoverflow.com/questions/2336362/execute-multiple-sql-commands-in-one-round-trip). I think it's what you're looking for. — Frank Bryce, Jun 01 '16 at 17:22
Thanks that's a good point. Just had the SQL text in the code for now while I'm testing. — SQLSuperHero, Jun 01 '16 at 17:23
Possible duplicate of [How to perform batch update in Sql through C# code](http://stackoverflow.com/questions/2327081/how-to-perform-batch-update-in-sql-through-c-sharp-code) — Michal Ciechan, Jun 01 '16 at 17:35

Nasreddine · Accepted Answer · 2016-06-01T17:36:53.190

3

Try this instead:

string UpdateQuery = @"UPDATE tbl810CTImport SET MthlyConsumption = @consumption WHERE ID = @itemId";
var update = new SqlCommand(UpdateQuery, sourceConnection);
update.Parameters.Add("@consumption", SqlDbType.Int); // Specify the correct types here
update.Parameters.Add("@itemId", SqlDbType.Int); // Specify the correct types here
foreach (var item in UsageRecords)
{
    update.Parameters[0].Value = item.MthlyConsumption; 
    update.Parameters[1].Value = item.Id;
    update.ExecuteNonQuery();
}

It should be faster because:

You don't have to create the command each time.
You don't create a new string each time (concatenation)
The query is not parsed at every iteration (Just changes the parameters values).
And it will cache the execution plan. (Thanks to @JohnCarpenter from the comment)

edited Jun 01 '16 at 17:36

answered Jun 01 '16 at 17:24

Nasreddine

36,610
17
75
94

Also note that [SQL will cache the execution plan](https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx), which speeds things up with this approach – Frank Bryce Jun 01 '16 at 17:24
@JohnCarpenter Thanks. I knew about that but couldn't find the source. I've edited the answer accordingly. – Nasreddine Jun 01 '16 at 17:37
Depending on performance vs amount of dev work you're willing to put in, BulkInserting into a staging table and running a single update statement like in my answer will be fastest. – Michal Ciechan Jun 01 '16 at 17:43

score 2 · Answer 2 · edited May 23 '17 at 11:53

You can either use

SqlDataAdapter - See How to perform batch update in Sql through C# code

or what I have previously done was one of the following:

Tear down the ID's in question, and re-bulkinsert

or

Bulk Insert the ID + new value into a staging table, and update the table on SQL server:

update u set u.MthlyConsumption = s.MthlyConsumption from tbl810CTImport u inner join staging s on u.id = s.id

score 0 · Answer 3 · answered Jun 01 '16 at 17:37

In a situation like this, where you can't write a single update statement to cover all your bases, it's a good idea to batch up your statements and run more than one at a time.

var commandSB = new StringBuilder();
int batchCount = 0;

using (var updateCommand = sourceConnection.CreateCommand())
{
    foreach (var item in UsageRecords)
    {
        commandSB.AppendFormat(@"
            UPDATE tbl810CTImport 
            SET MthlyConsumption = @MthlyConsumption{0}
            WHERE ID = @ID{0}", 
            batchCount
        );

        updateCommand.Parameters.AddWithValue(
            "@MthlyConsumption" + batchCount,
            item.MthlyConsumption
        );

        updateCommand.Parameters.AddWithValue(
            "@ID" + batchCount,
            item.MthlyConsumption
        );

        if (batchCount == 500) {
            updateCommand.CommandText = commandSB.ToString();
            updateCommand.ExecuteNonQuery();

            commandSB.Clear();
            updateCommand.Parameters.Clear();
            batchCount = 0;
        }
        else {
            batchCount++;
        }
    }

    if (batchCount != 0) {
        updateCommand.ExecuteNonQuery();
    }
}

ASH · Answer 4 · 2016-06-20T14:23:27.173

It should be as simple as this . . .

private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Server=YourServerName;Database=YourDataBaseName;Trusted_Connection=True"); 

            try
            {
                //cmd new SqlCommand( "UPDATE Stocks 
                //SET Name = @Name, City = @cit Where FirstName = @fn and LastName = @add";

                cmd = new SqlCommand("Update Stocks set Ask=@Ask, Bid=@Bid, PreviousClose=@PreviousClose, CurrentOpen=@CurrentOpen Where Name=@Name", con);
                cmd.Parameters.AddWithValue("@Name", textBox1.Text);
                cmd.Parameters.AddWithValue("@Ask", textBox2.Text);
                cmd.Parameters.AddWithValue("@Bid", textBox3.Text);
                cmd.Parameters.AddWithValue("@PreviousClose", textBox4.Text);
                cmd.Parameters.AddWithValue("@CurrentOpen", textBox5.Text);
                con.Open();
                int a = cmd.ExecuteNonQuery();
                if (a > 0)
                {
                    MessageBox.Show("Data Updated");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

Change the code to suit your needs.

Efficient Way to Update a lot of Rows from C#

4 Answers4