What is the easiest way to update a batch of records using only one trip?

Question

Normally you will do one update at a time inside a loop. If you have 100 records, you will have 100 trips to the server which is not desirable. How can I update a group of records with a single round trip to the database.

using System.Data.SqlClient;

for (int ii = 0; ii < ptList.Length; ii++)    {
    sql = @"update [CCVT].[dbo].[_tb_NCVT_Points] set PointDateTime = CONVERT(datetime, '"
    + ptList[ii]._dateDt.ToString("yyyy-MM-dd HH:mm:ss.FFF") + "', 121), PointStatus = '"
    + ptList[ii]._statStr + "', PointValue =" + ptList[ii]._valDoub.ToString() 
    + " WHERE Pointkey = '" + ptList[ii]._pointName + "'; ";
    theActiveConnection.Open();
    SqlCommand cmd = new SqlCommand(sql, theActiveConnection);
    try {
        cmd.ExecuteNonQuery();
        cmd.Dispose();
    }
    catch (Exception eX) {
       //handle exceptions
    } 
}

Please do not down vote this question.
The question How can I update multiple rows in a table with SQL query? did not ask for one trip, and their answer did not yield one trip!! Did you see the ExecuteNonQuery action is inside the loop?? That is not my question and that is not my answer!

foreach (DataGridViewRow row in dataGridView2.Rows)
    {
        cm.Parameters["@Qty"].Value = row.Cells[2].Value;
        cm.Parameters["@Description"].Value = row.Cells[3].Value;
        cm.Parameters["@Price"].Value = row.Cells[4].Value;
        cm.ExecuteNonQuery();
    }
    cn.Close();

Then just create a batched SQL statement and run it in one go. Or create stored procedure and feed it DataTable with your data. — eocron, Feb 02 '17 at 22:10
Possible duplicate of [How can I update multiple rows in a table with SQL query?](http://stackoverflow.com/questions/34341716/how-can-i-update-multiple-rows-in-a-table-with-sql-query) — eocron, Feb 02 '17 at 22:14
You can try to have the speediest procedure in the world but if you don't fix that security hole called Sql Injection you have a bigger problem — Steve, Feb 02 '17 at 22:19
You may want to be careful with large multi-line batch jobs. There seems to be two different consensuses regarding large job performance on [MSSQL](http://stackoverflow.com/questions/8635818/multiple-insert-statements-vs-single-insert-with-multiple-values) VS [MYSQL](http://stackoverflow.com/questions/1793169/which-is-faster-multiple-single-inserts-or-one-multiple-row-insert). — Ethilium, Feb 02 '17 at 22:25
you can try `Bulk Insert` or you can look at doing this using xml or you can do this by creating a UserDefined type and passing that List to the stored procedure / user type.. do some more googling on how to insert multiple records more efficiently — MethodMan, Feb 02 '17 at 22:57
@Steve my answer does fix the SQL Injection hole, but there may not be an issue, it depends where the data comes from. This code doesn't look like user input, I doubt Jenna needs to deal with [Little Bobby Tables](https://xkcd.com/327/). — Andrew, Feb 02 '17 at 23:07

Andrew · Accepted Answer · 2017-04-21T19:07:18.250

3

SqlCommand cmd = new SqlCommand("", theActiveConnection);
StringBuilder sql = new StringBuilder();
for (int ii = 0; ii < ptList.Length; ii++)    {
    sql.AppendLine("UPDATE [CCVT].[dbo].[_tb_NCVT_Points]"); 
    sql.AppendLine($"SET PointDateTime = CONVERT(datetime, @PointDateTime{ii}, 121), PointStatus = @PointStatus{ii}, PointValue = @PointValue{ii}");
    sql.AppendLine($"WHERE Pointkey = '@PointKey{ii};");
    cmd.Parameters.AddWithValue($"@PointDateTime{ii}",ptList[ii]._dateDt.ToString("yyyy-MM-dd HH:mm:ss.FFF"));
    cmd.Parameters.AddWithValue($"@PointStatus{ii}",ptList[ii]._statStr);
    cmd.Parameters.AddWithValue($"@PointValue{ii}",ptList[ii]._valDoub.ToString());
    cmd.Parameters.AddWithValue($"@Pointkey{ii}",ptList[ii]._pointName);

}
try {
    cmd.CommandText = sql.ToString();
    theActiveConnection.Open();
    cmd.ExecuteNonQuery();
}
catch (Exception eX) {
   //handle exceptions
} 
finally {
    cmd.Dispose();
    theActiveConnection.Close();
}

There are many ways to handle this issue, depending on how close and how different the commands are. In your case I think this is best.

edited Apr 21 '17 at 19:07

answered Feb 02 '17 at 22:18

Andrew

1,544
1
18
36

1

Have you ever heard of a class named StringBuilder ? – Steve Feb 02 '17 at 22:20
3

I have, and I do use string builder, I just reorganized the code posted by @JennaLeaf though there are definitely better ways to build the query strings. – Andrew Feb 02 '17 at 22:21
1

To make you happy @Steve, I put in a string builder. – Andrew Feb 02 '17 at 22:37
1

Thank-you Andrew! You are the best!!! I went home last night , almost doing it the same way you did, but your try{}catch{}finally{dispose()} is the best! And I like your way adopting serialized params!! Thanks!! Good Job. – Jenna Leaf Feb 03 '17 at 14:46
1

Yeah always handle your cleanup in a `finally` that way it's still there if you need it in the catch(it will be anyway since any exception would have probably happened before the cleanup) and if there is an exception the cleanup still happens. – Andrew Feb 03 '17 at 14:49

score 2 · Answer 2 · answered Feb 02 '17 at 22:36

2

The best performing way is to use table valued parameters with a stored procedure https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx

The simple way is to just concat SQL Statements with semicolon using a StringBuilder or .... But be aware that this has limitations in length!!!

answered Feb 02 '17 at 22:36

Markus

2,184
2
22
32

The former is more scalable, correct? I also looked up the max of StringBuilder. I don't think I would exceed that. However your former method is great, I like it! I voted it up!!! – Jenna Leaf Feb 03 '17 at 16:30
1

@Jenna: Please note that not StringBuilder introduces the Limit but SQL Server... https://msdn.microsoft.com/de-de/library/ms143432.aspx (the limits are very high here also) – Markus Feb 04 '17 at 15:45

score 2 · Answer 3 · answered Feb 02 '17 at 22:39

2

Why not use Dapper - a simple object mapper for .Net

var list = ptList.select(p => new{
        DateTime = p._dateDt,
        Status = p._statStr,
        Value = p._valDoub,
        Key = p._pointName
    });
using(var connection = new SqlConnection...)
{
  connection.open()
  connection.Execute("update [CCVT].[dbo].[_tb_NCVT_Points] set PointDateTime = @DateTime, PointStatus = @Status, PointValue = @Value
 WHERE Pointkey = @Key", list);
}

answered Feb 02 '17 at 22:39

praveen

86
3

I haven't tried it yet! I use Linq and nuget but this is my co-worker's proj (he is anti-linq, anti-complication), what you put there looks like it should work! I voted it up. Thx. – Jenna Leaf Feb 03 '17 at 16:35

score 2 · Answer 4 · answered Feb 02 '17 at 23:40

2

The straight answer was posted by Andrew, but it depends on where your data comes from.

If the list of points is already in the database, and you are querying it into a list (one SELECT with a loop to fill up the list), then calling the large number of updates, you should do directly an UPDATE FROM which combines the SELECT and UPDATE in a single statement. One single trip to the server, with no unneccessary rows over the network.

answered Feb 02 '17 at 23:40

Bruno Guardia

453
4
14

Yours works too. Like you said, I do not have millions update stmts, so Andrew's solution is what I am looking for especially I don't prefer creating a tmp work-table on server only merging a couple hundreds of records. For bigger batch of updates, I would definitely use yours. I voted up your answer. See also http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server – Jenna Leaf Feb 03 '17 at 16:12
1

If it's already in a database sure there are other ways, but even then there may be a reason that Jenna wants the data to run through her code, validation, adjustments to the data, but we don't know the source of the data, so I posted a solution that will always work once the values are where OP provided them. – Andrew Mar 01 '17 at 23:00
Andrew, the values to update the database fields are NOT IN DATABASE but entered from some historian engine. – Jenna Leaf Mar 02 '17 at 17:54
@JennaLeaf I didn't really think they were, I just said that even if they were, there may be reasons to run the values through the c# before putting them back or into another table. – Andrew Mar 24 '17 at 20:24

What is the easiest way to update a batch of records using only one trip?

4 Answers4