2

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.

  1. Why is SqlDataAdapter.Update not updating the database with the specified dataset?
  2. 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.

casperOne
  • 73,706
  • 19
  • 184
  • 253
CadentOrange
  • 3,263
  • 1
  • 34
  • 52

1 Answers1

1

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 SqlDataAdapter ultimately handles versioning, but my guess is that you either have a timestamp on the table, or there is a SqlCommandBuilder that is generating the SqlCommand (through the GetUpdateCommand method) which ultimately checks all the values in the row in the database against the previous values in the row that you updated (the DataRow stores 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 SqlDataAdapter set the UpdateCommand property explicitly to a SqlCommand which will perform an update and not check the timestamp, it only updates the record where the primary key is equal to the value in the DataRow that you specify (or a value in a column which has a unique constraint on it).

casperOne
  • 73,706
  • 19
  • 184
  • 253
  • The timestamp was the problem. The SqlDataAdapter was wrapped in a SqlCommandBuilder, and the select command on the SqlDataAdapter retrieved the timestamp which in turn meant that the automatically generated UPDATE command used the timestamp as part of the query. Creating an explicit update command that ignored the timestamp fixed the issue. I only wished I'd seen your answer sooner, as digging around in the SQL profiler is not fun :) – CadentOrange Aug 11 '11 at 13:34
  • @Philip Goh: I don't know that you could have seen my answer sooner, as I just put it up an hour ago. =) – casperOne Aug 11 '11 at 14:07