SqlDataAdapter.Update 无提示地失败
在我的应用程序中，我有一个向用户显示数据库表内容的控件。该控件保存要在 System.Data.DataSet 对象中显示的数据。用户能够修改控件中显示的数据，然后当用户完成后该数据被提交回数据库。
当用户在控件中进行编辑时，某些外部进程修改了数据库表中的数据（例如，某些行已更新），就会出现问题。暂时忽略数据正确性问题，我想做的是提交用户在控件中所做的更改并覆盖此外部进程所做的更改。
我正在使用 SqlDataAdapter 来更新数据库。在所描述的用例中，当外部进程未修改基础数据库表时，SqlDataAdapter.Update 按预期工作。但是，在用户编辑表时某些外部进程对表进行了修改的情况下，SqlDataAdapter.Update 不会引发异常，而是返回 0 表示没有更新任何行。我已检查数据集中的行是否具有正确的数据和 RowState （即 DataRowState.Modified），因此我知道我传递给 < code>SqlDataAdapter.Update 方法是正确的。
我想我的问题有两个部分。
- 为什么
SqlDataAdapter.Update不使用指定的数据集更新数据库？ - 为什么它会默默地失败？
我已阅读此博客条目，并且我的代码不会在任何地方调用 AcceptChanges，正如我上面所说，我已经检查了DataSet 的 RowState 因此我知道这些行已正确标记为已修改数据。
In my application, I have a control that displays the contents of a database table to the user. This control holds the data to display in a System.Data.DataSet object. The user is able to modify the data displayed in the control, and this data is then committed back into the database when the user is done.
Problems occur when the data in the database table is modified by some external process (e.g. some rows have been updated) while the user is making edits in the control. Ignoring the issue of data correctness for the moment, what I would like to do is to commit the changes the user has made in the control and overwrite the changes made by this external process.
I'm using a SqlDataAdapter to update the database. In the described use cases, when the underlying database table has not been modified by an external process, SqlDataAdapter.Update works as expected. However, in the scenario where some external process has fiddled with the table while the user was editing it then SqlDataAdapter.Update does not throw an exception but returns 0 indicating that no rows were updated. I've checked that rows in my dataset are have the correct data and RowState (i.e. DataRowState.Modified) so I know that the data I'm passing in to the SqlDataAdapter.Update method is correct.
I suppose there are two parts to my question.
- Why is
SqlDataAdapter.Updatenot updating the database with the specified dataset? - Why is it silently failing?
I have read this blog entry, and my code does not call AcceptChanges anywhere, and as I've stated above I have checked the DataSet's RowState so I know that the rows are correctly marked as having modified data.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您的表的结构是什么？您使用的版本控制机制（时间戳、日期时间等）是什么？有很多因素会影响
SqlDataAdapter最终处理版本控制，但我的猜测是表上要么有时间戳，要么有SqlCommandBuilder正在生成SqlCommand（通过GetUpdateCommand方法），该方法最终根据您更新的行中的先前值（DataRow存储以前版本的用于比较的行）。所有这些都发挥了作用，因为 ADO.NET 将尝试为您维护乐观并发；如果自您上次获取记录以来其他人已修改该记录，则不会发生更新。
这显然不是您想要的行为；你想要一种最后获胜的方法。
为此，请在
SqlDataAdapter上设置UpdateCommand属性显式设置为SqlCommand，它将执行更新而不检查时间戳，它只更新主键所在的记录等于您指定的DataRow中的值（或具有唯一约束的列中的值）。What is the structure of your table and what is the versioning mechanism (timestamp, datetime, etc) that you use? There are a number of things that can affect how the
SqlDataAdapterultimately handles versioning, but my guess is that you either have a timestamp on the table, or there is aSqlCommandBuilderthat is generating theSqlCommand(through theGetUpdateCommandmethod) which ultimately checks all the values in the row in the database against the previous values in the row that you updated (theDataRowstores the previous version of the row for comparison).All of that plays a role because ADO.NET is going to try and maintain optimistic concurrency for you; if someone else has modified the record since the last time you fetched it, the update will not occur.
This is obviously not the behavior that you want; you want a last-in-wins approach.
To do this, on the
SqlDataAdapterset theUpdateCommandproperty explicitly to aSqlCommandwhich will perform an update and not check the timestamp, it only updates the record where the primary key is equal to the value in theDataRowthat you specify (or a value in a column which has a unique constraint on it).