ADO.NET is Microsoft’s new technology for working with data. One of the most trumpeted features of ADO.NET is the DataSet object, which is an object that stores data in an in-memory schema, complete with DataTable and DataRelation objects. The ability to store an entire schema in memory allows the DataSet to run completely disconnected; the DataSet has no knowledge of any underlying data source. The DataSet cannot connect to a database; instead, records are added programmatically and manipulated in the DataSet. You must then programmatically take the records from the DataSet and update the underlying data source.

Operating in a completely disconnected paradigm raises a particularly troublesome issue: handling concurrency violations. If, for instance, Bob fills a DataSet with records from a database and is making changes to them in a disconnected mode at the same time Kim is updating those records with another application, how do you determine that the records have changed when Bob tries to apply his changes to the database? Let’s examine how ADO.NET handles checking for concurrency conflicts, and take a look at how to handle concurrency violations.

The DataAdapter and optimistic concurrency
When you are in Visual Studio .NET, you can use the Data Adapter Configuration Wizard to generate all the Command objects in the DataAdapter. This makes life much easier, although you are also always free to generate the commands yourself.

What few developers have noticed is that, by default, the Data Adapter Configuration Wizard generates SQL statements that check for optimistic concurrency violations. The term optimistic concurrency means that you assume no one will be making edits to a record while you are making your edits. In other words, you don’t think two people will be editing the same record at the same time. Because you are optimistic that two people will not edit the same record simultaneously, you do not apply a lock to the record as soon as a user starts to edit it. Instead, you apply the lock only when the actual update is attempted. This results in a much shorter lock, and in database terms, the rule is simple: Locks are good, but long locks are bad.

You can see this behavior by starting a new project in VS.NET and dragging a SqlDataAdapter or OleDbDataAdapter onto a designer surface. If you step through the wizard and choose to use SQL statements, the next page of the wizard will allow you to type in the Select statement. This page has a button labeled Advanced Options. Clicking this button opens the Advanced SQL Generation Options dialog box, and by default, the option for using optimistic concurrency is turned on. Figure A shows you the Advanced SQL Generation Options dialog box.

Figure A
Advanced options

Examining the optimistic concurrency code
To check for optimistic concurrency violations, the Data Adapter Configuration Wizard writes SQL statements that verify the record you are about to update or delete has not changed since you originally loaded it into your DataSet. The Wizard does this by adding a large WHERE clause to the SQL statement to verify that it is updating or deleting an exact match of what was downloaded and placed in the DataSet. However, this also relies on another aspect of the DataSet: The DataTable objects can store multiple versions of each row. If you could peer into a DataSet in memory, you would see one or more DataTable objects. Each DataTable would be made up of DataRow objects. Each record in the table, however, can actually exist multiple times, where each record has a different DataRowVersion.

There are four possible values for the DataRowVersion, but the ones that will come into play with optimistic concurrency are Current and Original. When the DataAdapter first populates the DataSet with records, each DataRow is given a DataRowVersion of Original. If you then edit a record, the changes are stored in a new version of the record with a DataRowVersion of Current. Now, you have both the Original version (what was retrieved from the database) and the Current version (the changes you propose to make). It is called the Current version because it is the current version of the data in your DataSet; it is not yet the current version in the underlying database.

As an example, assume you have a table named Person in a database. You retrieve all the records from the Person table and place them in a DataSet. In the Person table is a field named LastName. On the first record, the LastName field is Smith when you retrieve the record. You change the LastName to Jones in the DataSet.

If you call the DataAdapter’s Update method, the query that is run will attempt to update the LastName field for the first record to Jones. However, it will first check to make sure that the current value for the first record in the database is Smith. How does it do this?

If you look at the code generated by the Data Adapter Configuration Wizard, the Update statement contains a WHERE clause that examines the existing value of each field in the database to verify that they match the values that were originally downloaded. In other words, the query takes the values from the Original DataRowVersion of the record and makes sure those values are the ones still in the database. If they are, the values from the Current DataRowVersion are used to update the database. The following code snippet shows the appropriate code for the LastName field:
UPDATE Person SET LastName = @LastName,
WHERE (LastName = @Original_LastName) AND
Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
Me.SqlUpdateCommand1.Parameters.Add(New _
System.Data.SqlClient.SqlParameter(“@LastName”, _
System.Data.SqlDbType.VarChar, 40, “LastName”))
Me.SqlUpdateCommand1.Parameters.Add(New _
System.Data.SqlClient.SqlParameter(“@Original_LastName”, _
System.Data.SqlDbType.VarChar, 40, _
System.Data.ParameterDirection.Input, False, _
CType(0, Byte), CType(0, Byte), “LastName”, _
System.Data.DataRowVersion.Original, Nothing))


The wizard creates parameters to hold the Current and Original versions of each field. You can see the Current version is held in a parameter named @LastName, and the value is pulled from the DataTable simply by calling the field name LastName. However, the parameter @Original_LastName is pulled from the DataTable by specifying that you need the LastName field where the DataRowVersion is Original. In the example above, the Update would attempt to set the LastName field to Jones where the LastName field is Smith.

Note: In this simple example, I left out the primary key field, which you would normally have to ensure you are updating just one record.

Violating optimistic concurrency
In the previous example, you change Smith to Jones for a record. However, before you call the Update method, someone else could change the last name to Anderson. Now, when you call the Update method, the query will attempt to change the LastName field to Jones where the LastName field is Smith. However, no record now has a LastName of Smith, so ADO.NET knows that the record has been updated or deleted, and the update will fail.

While the wizard generates code to check for optimistic concurrency by default, it does nothing to handle a concurrency violation. In fact, if you change a record and call the Update method after someone else has changed that record, ADO.NET raises an unhandled exception. By default, you will receive the error shown in Figure B.

Figure B
ADO.NET error

Obviously, this error is less than useful. Instead, you’ll want to inform the user not just that optimistic concurrency has failed, but what the old and new values for the field happen to be. The next article will cover two ways of handling optimistic concurrency violations and show you how to present that information to the users.

Know how to check for concurrency violations
Catching optimistic concurrency is simple if you use the Data Adapter Configuration Wizard. Even if you create the Update and Delete statements manually, you simply create the WHERE clause that verifies the values in the underlying database match the Original DataRowVersion values in your DataSet. To make sure you can check for optimistic concurrency violations, it’s important that you understand the DataRowVersion property of the DataRow. However, handling these violations is not done for you automatically. How you handle these violations will be covered in an upcoming article.