Data Concurrency in ADO.NET - Part II

If you are reading this article without reading the first part, please check Data Concurrency in ADO.NET - Part I

Version Number Approach

In the version number approach, the record to be updated must have a column that contains a date-time stamp or version number. The date-time stamp or a version number is saved on the client when the record is read. This value is then made part of the update.

One way to handle concurrency is to update only if value in the WHERE clause matches the value on the record. The SQL representation of this approach is:

UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2
WHERE DateTimeStamp = @origDateTimeStamp


Alternatively, the comparison can be made using the version number:

UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2
WHERE RowVersion = @origRowVersionValue


If the date-time stamps or version numbers match, the record in the data store has not changed and can be safely updated with the new values from the dataset. An error is returned if they don't match. You can write code to implement this form of concurrency checking in Visual Studio .NET. You will also have to write code to respond to any update conflicts. To keep the date-time stamp or version number accurate, you need to set up a trigger on the table to update it when a change to a row occurs.

Saving All Values Approach

An alternative to using a date-time stamp or version number is to get copies of all the fields when the record is read. The DataSet object in ADO.NET maintains two versions of each modified record: an original version (that was originally read from the data source) and a modified version, representing the user updates. When attempting to write the record back to the data source, the original values in the data row are compared against the record in the data source. If they match, it means that the database record has not changed since it was read. In that case, the changed values from the dataset are successfully written to the database.

Each data adapter command has a parameters collection for each of its four commands (DELETE, INSERT, SELECT, and UPDATE). Each command has parameters for both the original values, as well as the current (or modified) values.

Note Adding new records (the INSERT command) only requires the current values since no original record exists and removing records (the DELETE command) only requires the original values in order to locate the record to delete.

The following example shows the command text for a dataset command that updates a typical Customers table. The command is specified for dynamic SQL and optimistic concurrency.

UPDATE Customers
SET CustomerID = @currCustomerID,
CompanyName = @currCompanyName,
ContactName = @currContactName,
ContactTitle = currContactTitle,
Address = @currAddress,
City = @currCity,
PostalCode = @currPostalCode,
Phone = @currPhone,
Fax = @currFax
WHERE (CustomerID = @origCustomerID) AND (Address = @origAddress OR @origAddress IS NULL AND Address IS NULL) AND (City = @origCity OR @origCity IS NULL AND City IS NULL)
AND (CompanyName = @origCompanyName OR @origCompanyName IS NULL AND CompanyName IS NULL) AND (ContactName = @origContactName OR @origContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = @origContactTitle OR @origContactTitle IS NULL AND ContactTitle IS NULL)
AND (Fax = @origFax OR @origFax IS NULL AND Fax IS NULL) AND (Phone = @origPhone OR @origPhone IS NULL AND Phone IS NULL) AND (PostalCode = @origPostalCode OR @origPostalCode IS NULL AND PostalCode IS NULL);
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City,
PostalCode, Phone, Fax
FROM Customers WHERE (CustomerID = @currCustomerID)


Note that the nine SET statement parameters represent the current values that will be written to the database, whereas the nine WHERE statement parameters represent the original values that are used to locate the original record.

The first nine parameters in the SET statement correspond to the first nine parameters in the parameters collection. These parameters would have their SourceVersion property set to Current.

The next nine parameters in the WHERE statement are used for optimistic concurrency. These placeholders would correspond to the next nine parameters in the parameters collection, and each of these parameters would have their SourceVersion property set to Original.

The SELECT statement is used to refresh the dataset after the update has occurred. It is generated when you set the Refresh the DataSet option in the Advanced SQL Generations Options dialog box.

By default Visual Studio will create these parameters for you if you select the Optimistic Concurrency option in the DataAdapter Configuration Wizard. It is up to you to add code to handle the errors based upon your own business requirements.

For more information on Tackling Data Concurrency Exceptions Using the DataSet Object check
This article

Cheers and Happy Programming !!!

Print | posted on Thursday, July 7, 2005 1:13 PM

Comments on this post

# re: Data Concurrency in ADO.NET - Part II

Requesting Gravatar...
thanks dean...
Left by mario oyunları on Sep 22, 2009 12:08 AM

Your comment:

 (will show your gravatar)