如何通过C#代码在Sql中执行批量更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2327081/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to perform batch update in Sql through C# code
提问by malay
I want to update multiple rows like below
我想更新多行，如下所示
update mytable set s_id = {0} where id = {1}
(Here s_idis evaluated based on some complex logic).
For performance reason, updates should happen in batches. Is there any way to batch the update statements and execute the batch through single execute statements? I know in JAVA we can do this through JDBC. Is there similar way in C#?
（这里s_id是根据一些复杂的逻辑进行评估的）。
出于性能原因，更新应分批进行。有没有办法批量更新语句并通过单个执行语句执行批处理？我知道在 JAVA 中我们可以通过 JDBC 来做到这一点。C# 中有类似的方法吗？
Thanks in advance
提前致谢
采纳答案by AdaTheDev
Yes, you can use an SqlDataAdapter.
是的，您可以使用SqlDataAdapter。
The SqlDataAdapter has InsertCommandand UpdateCommandproperties which allow you to specify an SQLCommand to use to insert new rows into the database and an SqlCommand to update rows in the database respectively.
SqlDataAdapter 具有InsertCommand和UpdateCommand属性，它们允许您分别指定用于将新行插入数据库的 SQLCommand 和用于更新数据库中的行的 SqlCommand。
You can then pass a DataTable to the Updatemethod of the dataadapter, and it will batch up the statements to the server - for rows in the DataTable that are new rows, it executes the INSERT command, for modified rows it executes the UPDATE command.
然后，您可以将 DataTable 传递给dataadapter的Update方法，它会将语句批量发送到服务器 - 对于 DataTable 中的新行，它执行 INSERT 命令，对于修改过的行，它执行 UPDATE 命令。
You can define the batch size using the UpdateBatchSizeproperty.
您可以使用UpdateBatchSize属性定义批次大小。
This approach allows you to deal with large volumes of data, and allows you to nicely handle errors in different ways, i.e. if an error is encountered with a particular update, you can tell it to NOT throw an exception but to carry on with the remaining updates by setting the ContinueUpdateOnErrorproperty.
这种方法允许您处理大量数据，并允许您以不同的方式很好地处理错误，即如果特定更新遇到错误，您可以告诉它不要抛出异常，而是继续处理剩余的通过设置ContinueUpdateOnError属性来更新。
回答by David Morton
Create a set of those updates (with the id's filled in), separate them by semicolon in one string, set the resulting string to a SqlCommand's CommandText property, then call ExecuteNonQuery().
创建一组这些更新（填充 id），在一个字符串中用分号分隔它们，将结果字符串设置为 SqlCommand 的 CommandText 属性，然后调用 ExecuteNonQuery()。
回答by Rob
Use a StringBuilder (System.Text.StringBuilder) to build your Sql, such as:
使用 StringBuilder (System.Text.StringBuilder) 来构建您的 Sql，例如：
StringBuilder sql = new StringBuilder();
int batchSize = 10;
int currentBatchCount = 0;
SqlCommand cmd = null; // The SqlCommand object to use for executing the sql.
for(int i = 0; i < numberOfUpdatesToMake; i++)
{
int sid = 0; // Set the s_id here
int id = 0; // Set id here
sql.AppendFormat("update mytable set s_id = {0} where id = {1}; ", sid, id);
currentBatchCount++;
if (currentBatchCount >= batchSize)
{
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
sql = new StringBuilder();
currentBatchCount = 0;
}
}
回答by Jeff Sternal
Yes, you can build a plain-text SQL command (parameterized for security), like this:
是的，您可以构建一个纯文本 SQL 命令（为了安全而进行参数化），如下所示：
SqlCommand command = new SqlCommand();
// Set connection, etc.
for(int i=0; i< items.length; i++) {
command.CommandText += string.Format("update mytable set s_id=@s_id{0} where id = @id{0};", i);
command.Parameters.Add("@s_id" + i, items[i].SId);
command.Parameters.Add("@id" + i, items[i].Id);
}
command.ExecuteNonQuery();
