C #, SQL updates multiple rows

Question

I have a question regarding an efficient way to update multiple rows through SQL.

As a rule, I have a request that needs to be run on different RowIDs:

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

If this is a more specific example, 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 would like to know how can I create an update request command in C # (or just give me an example of a given request that I should refer to), so as soon as I use ExecuteQuery, it will run all these commands in one piece and not by executing each command.

edited: I have one more problem, can you also explain that regarding a dynamic situation in which there is not necessarily a row that I want to update, in this case I need to insert instead of updating. to better explain, let's go back to my example, let's 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 point of this is that I need to check if the line exists if I use the update, otherwise I will have to insert it.

Thanks!

Answer 1

You can use a DataTable to store records, insert, delete or modify rows and update all changes in one batch using SqlDataAdapter UpdateBatchSize (0 means without limits):

 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 that you misunderstand how dbms works internally. it

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

coincides with

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

In any case, dbms will update all affected records one by one, even if you write an operator like UPDATE table SET value=1 , which will affect every record in the table. By updating in one batch, you guarantee that all updates (deletions, inserts) will be sent to the database, and not in one round for each operator.

Answer 2

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

Instead of hard coding / dynamic SQL, the MERGE can be encapsulated in a stored process that accepts a table parameter .