Database concurrency issue in .NET application

If userA deleted OrderA while userB is modifying OrderA, then userB saves OrderA then there is no order in the database to be updated. My problem is there is no error! The SqlDataAd开发者_运维知识库apter.Update succeeds and returns a "1" indicating a record was modified when this is not true. Does anybody know how this is supposed to work, thanks.

You need to use, at the very least, optimistic locking. See here:

Optimistic Locking

Basically, this says that you check the values of all fields during the update. So you say, for example assuming when you first read record 1, bar was equal to 0:

UPDATE FOO SET BAR=1 WHERE ID=1 AND BAR=0

The idea is, if the record changes, the update will fail. This will solve your problem.

When not working stateless (like webservices work), you could try pessimistic locking; more info here (VB example though): https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-1049842.html

I also find Optimistic Concurrency as the best way.

One should only decide which database field use as a criteria of update fails. It's depend on your situation, but there are one universal way to implement this. I personally use MS SQL Server and so prefer inserting not nullable rowversion fields (alias timestamp) in all tables of database (one field per table). So every row in your table will have a "rowversion" which will be updated automatically is somebody update a field of the row. So you just should use this field as a criteria of update fails. See also my old answer Concurrency handling of Sql transactrion for the close information.

UPDATED: Because you use SqlDataAdapter to access the database, this links can be also interesting for you:

https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-1050108.html

and on the next one just search for DataRowVersion:

http://msdn.microsoft.com/en-us/library/ww3k31w0(VS.71).aspx, http://msdn.microsoft.com/en-us/library/bbw6zyha(VS.80).aspx, http://msdn.microsoft.com/en-us/library/ms971491.aspx, http://msdn.microsoft.com/en-us/magazine/cc163908.aspx

I ran into a situation similar to yours. It involved an SqlDataAdapter, an SqlCommandBuilder connected to it, and a DataTable object. Changes I made consistently failed to save, but giving no error. It turned out that one of the columns in the DataTable object was misnamed. Once I corrected it, it started working perfectly. I still have no idea why this didn't raise an error.