C# , SQL update multiple rows

Question

i have a question regarding an efficient way to update multiple rows via SQL.

Basiclly i have a query i need to run on different RowIDs:

UPDATE TableName SET Column = (some number) WHERE RowID = (some number)

if to be more specific this is a better example:

UPDATE TableName SET Column = 5 WHERE RowID = 1000
UPDATE TableName SET Column = 10 WHERE RowID = 1001
UPDATE TableName SET Column = 30 WHERE RowID = 1002
..

I'd like to know how should i build the update query command on C# (or just give me an example of the resulted query i should get to) so once i use ExecuteQuery it will run all of these commands at one piece and not by executing each command.

edited: I have another problem, can you also explain what about dynamic situation in which not necessarly the row i want to update exist already, in that case i need to insert instead of update. to explain better, back to my example lets say i want to do

UPDATE TableName SET Column = 5 WHERE RowID = 1000
INSERT INTO TableName [RowID, Column] VALUES (1001, 20)
UPDATE TableName SET Column = 30 WHERE RowID = 1002
..

The meaning of this is that i need to check if the row exist, if so i'd use update otherwise i'll have to insert it.

Thank you!

Can you confirm which database product and version you're using? For modern SQL Server (>= 2008), you'd be looking at using a table-valued parameter and a MERGE statement — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Feb 23, 2012 at 11:37

Tim Schmelter · Accepted Answer · 2012-02-23 12:26:42Z

9

You could use a DataTable to store your records, insert, delete or change rows and update all changes in one batch by using SqlDataAdapter's UpdateBatchSize(0 means no limit):

public static void BatchUpdate(DataTable dataTable,Int32 batchSize)
{
    // Assumes GetConnectionString() returns a valid connection string.
    string connectionString = GetConnectionString();

    // Connect to the AdventureWorks database.
    using (SqlConnection connection = new 
      SqlConnection(connectionString))
    {

        // Create a SqlDataAdapter.
        SqlDataAdapter adapter = new SqlDataAdapter();

        // Set the UPDATE command and parameters.
        adapter.UpdateCommand = new SqlCommand(
            "UPDATE Production.ProductCategory SET "
            + "Name=@Name WHERE ProductCategoryID=@ProdCatID;", 
            connection);
        adapter.UpdateCommand.Parameters.Add("@Name", 
           SqlDbType.NVarChar, 50, "Name");
        adapter.UpdateCommand.Parameters.Add("@ProdCatID", 
           SqlDbType.Int, 4, "ProductCategoryID");
         adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

        // Set the INSERT command and parameter.
        adapter.InsertCommand = new SqlCommand(
            "INSERT INTO Production.ProductCategory (Name) VALUES (@Name);", 
            connection);
        adapter.InsertCommand.Parameters.Add("@Name", 
          SqlDbType.NVarChar, 50, "Name");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

        // Set the DELETE command and parameter.
        adapter.DeleteCommand = new SqlCommand(
            "DELETE FROM Production.ProductCategory "
            + "WHERE ProductCategoryID=@ProdCatID;", connection);
        adapter.DeleteCommand.Parameters.Add("@ProdCatID", 
          SqlDbType.Int, 4, "ProductCategoryID");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

        // Set the batch size.
        adapter.UpdateBatchSize = batchSize;

        // Execute the update.
        adapter.Update(dataTable);
    }
}

http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx

I assume you're misunderstanding how the dbms works internally. This

UPDATE TableName SET Column = 5 WHERE RowID = 1000;
UPDATE TableName SET Column = 5 WHERE RowID = 1002;

is the same as

UPDATE TableName SET Column = 5 WHERE RowID IN(1000,2002);

The dbms will update all affected records one by one anyway even if you would write a statement like UPDATE table SET value=1 which would affect every record in the table. By updating in one batch you ensure that all updates(deletes,inserts)are submitted to the database instead of one roundtrip for every statement.

edited Feb 23, 2012 at 12:26

answered Feb 23, 2012 at 9:17

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Popokoko Over a year ago

I am viewing the update code (since it's the most relevant to my question) and i didn't quite understand what is the resulted query at the end and why is it efficient? it looks like like your'e building the query im trying to avoid of isn't it ?

Tim Schmelter Over a year ago

The dbms will update all affected records one by one anyway even if you would write a statement like UPDATE table SET value=1 which would affect every record in the table. By updating in one batch you ensure that all updates(deletes,inserts)are commited at once in the same transaction.

Damien_The_Unbeliever Over a year ago

It should be noted that Update(dataTable) is documented not to batch the updates: "It should be noted that these statements are not performed as a batch process; each row is updated individually.", and that batches and transactions are two orthogonal concepts in SQL.

Tim Schmelter Over a year ago

@Damien_The_Unbeliever: Of course, every modified row in the DataTable will create an individual update/insert/delete statement. But by setting DataAdapter's UpdateBatchSize to <>1 you're avoiding roundtrips to the database for every statement by submitting them in one batch.

Tim Schmelter Over a year ago

@Damien_The_Unbeliever: To your second argument, you're dead right! You can easily create a Transaction and assign multiple commands to it. Sorry for lumping together both.

onedaywhen · Accepted Answer · 2012-02-23 14:30:28Z

1

Use MERGE:

MERGE INTO TableName
   USING (
          VALUES (1000, 5), 
                 (1001, 10), 
                 (1002, 30)
         ) AS source (RowID, Column_name)
      ON TableName.RowID = source.RowID
WHEN MATCHED THEN
   UPDATE 
      SET Column_name = source.Column_name
WHEN NOT MATCHED THEN
   INSERT (RowID, Column_name) 
      VALUES (RowID, Column_name);

Rather than hard-coding/dynamic SQL, the MERGE statement could be encapsulated into a stored proc that takes a table-valued parameter.

answered Feb 23, 2012 at 14:30

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

1 Comment

Kunal Over a year ago

Just came across this mssqltips.com/sqlservertip/3074/…

Collectives™ on Stack Overflow

C# , SQL update multiple rows

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related