C＃，SQL更新多行 [英] C# , SQL update multiple rows

问题描述

我有一个关于一个有效的方式来更新通过SQL多行的问题。

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

Basiclly我有一个查询，我需要在不同的ROWID运行：

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
..

我想知道我应该怎么建立在C＃中的更新查询命令（或只是给我造成查询的示例我应该去），所以一旦我用的executeQuery将执行每条命令在一块运行所有这些命令，而不是

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.

的 编辑： 的
我还有一个问题，你能不能也解释了有关动态情况，即不necessarly我想更新该行什么已经存在，在这种情况下，我需要插入，而不是更新。为更好地解释，回到我的例子可以说我想要做的。

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.

感谢您

推荐答案

您可以使用的DataTable 来存储你的记录，插入，删除或更改行和使用更新一批的所有变化的SqlDataAdapter的< A HREF =htt​​p://msdn.microsoft.com/en-us/library/system.data.common.dbdataadapter.updatebatchsize.aspx> UpdateBatchSize （0表示没有限制）：

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

在DBMS将更新所有受影响的记录逐一反正就算你会写像<$声明C $ C>更新表设定值= 1 这会影响表中的每个记录。通过在一个批次更新可以确保所有更新（删除，插入）被提交到数据库，而不是一个来回，每声明。

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.

这篇关于C＃，SQL更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！