使用DataAdapter批量更新
我遇到过这样的情况：我有一堆 SQL 更新命令都需要执行。 我知道数据集可以进行批量更新，但我能够实现它的唯一方法是首先将整个表加载到数据集中。 如果我只想更新表中的记录子集怎么办？
I have a situation where I have a bunch of SQL Update commands that all need to be executed. I know that DataSets can do batch updates, but the only way I've been able to accomplish it is to load the whole table into a dataset first. What if I want to only update a subset of the records in a table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
最简单的方法是加载包含所需行（您想要更新的行）的表。 仔细检查 RowState 是否为“已插入”。
将适配器的 InsertCommand 属性分配给执行“更新”的存储过程（包装在 SqlCommand 中），此调整将确保表中存在的所有行都得到更新。
这里的基本原理是：DataAdapter 对状态为 Updated 的行运行 UpdateCommand，对状态为 Inserted 的行运行 InsertCommand，最后对状态为 Deleted 的行运行 DeleteCommand。
The easiest way out here is load table with the required rows (rows you wan to update). Double check that the RowState is "Inserted".
Assign the InsertCommand property of the adapter with your stored procedure (wrapped in an SqlCommand) that does the "update", this tweak will ensure that all the rows present in the table gets updated.
The fundamental here being: The DataAdapter runs the UpdateCommand on rows for which state is Updated, runs InsertCommand for rows which state is Inserted and finally DeleteCommand for the rows for which state is Deleted.
编辑：根据您的评论，我建议首先使用批量复制方法转到临时表。 然后，您可以根据临时表对真实表进行一次更新。
=========
一种方法是自己构建 SQL 命令； 但是，我建议您阅读有关 SQL 注入可能性的信息以保护自己。 根据您的情况和平台，还有其他选择。
例如，如果您正在处理大量数据，那么您可以批量导入到保存表中，然后从中发出单个更新命令。 我还成功地将记录作为 XML 传递（我发现在我的环境中，我需要至少 50 行来抵消加载 DOM 的成本，并且我知道可伸缩性问题不是我的情况的一个因素）。
我看到的其他一些事情是人们将更新打包到二进制字段中，例如每一列一个二进制字段，然后他们有一个解包二进制字段的函数。 您将再次需要在您的环境中进行测试。
话虽如此，您是否已验证仅针对所需的每个更新调用单个更新命令或存储过程还不够？ 您甚至可能不需要批量处理命令。
小心过早的优化，它可能是致命的！
EDITED: Based on your comment, I'd recommend using the Bulk Copy method to go to a staging table first. Then you can do a single update on your real table based on the staging table.
=========
One way is to build the SQL Command yourself; however, I'd recommend reading about the SQL Injection possibilities to protect yourself. Depending on your situation and your platform there are other options.
For example if you are dealing with a lot of data, then you could do a bulk import into a holding table, which you would then issue a single update command off of. I've also had good success passing records in as XML (I found in my environment that I needed at least 50 rows to offset the cost of loading the DOM, and I knew that the scalability issues were not a factor in my case).
Some other things I've seen was people package the updates into a binary field, for example one binary field for each column, then they have a function which unpacks the binary field. Again you will want to test with your environment.
With that said, have you verified that simply calling a single update command or stored procedure for each update you need is not sufficent? You might not even need to batch the commands up.
Watch out for Premature Optimization it can be killer!