Update a record in SQL Database from C#.NET

[Guest]

I want to update a record into a table in SQL Server 2000 database from
C#. This table is used concurently for other users too, and I want to be sure
that from the read of record to the update no other user was updated the
record.
Please tell me how can I do this.
Thank you!

 

[Marina]

Couple of ways to do that:

-Store a last update date of the record, and make sure that has stayed the
same
-In your WHERE clause, include all the original values of the fields. If any
of them have changed since, the update should affect 0 records, so you will
know that is because something has changed

 

[Ken Allen]

SQ Server supports a data type called TIMESTAMP that can be used
specifically for this purpose. You do not have to specify a value for
fields that are of this type; the database will automatically assign a
value each time a record is created or updated.

Ensure that you retrieve this column with your query, and ensure that
you include it in the WHERE clause of the update. You can use this
approach whether you are building your own UPDATE statement or if you
are using the Update() method on the DataAdapter object.

Note that you should always check the return value from the execution of
the UPDATE statement or the Update() method call to ensure that the
proper number of records have been modified. If the record was changed,
then the count will be zero; leave this out and a malformed request may
update more than one record. For that reason it is often best to wrap
these updates in a transaction so that you can roll back the attempt in
case of a detected error.

-Ken

 

[Guest]

I thinked to this solution, but I tried an other. When the user begin the
update, I begin a transaction with isolation level read committed and read
the record. Then the user change the record's date and saves the changees.
When the user saves the new values I do the update and the commit transaction.
In this case I can read the record which was not updated and I can update
the record after the first user updated it.
If I use the isolation level serializable, I get the good solution, but
if the user which was read first the record is too slow, he blocks the other
users.
The best solution is with timestamp? In this case I need a timestamp
field in any table.

Thank you for help and I wish you a Happy New Year!
Mihaly