The DataSet object is designed to encourage the use of optimistic concurrency for long-running activities, such as when you are remoting data and when users are interacting with data. When submitting updates from a DataSet to the database server, there are four main approaches to managing optimistic concurrency:
- Including only the primary key columns
- Including all columns in the WHERE clause
- Including unique key columns and the timestamp columns
- Including unique key columns and the modified columns
Note that the last three approaches maintain data integrity; the first does not.
Including Only the Primary Key Columns
This option creates a situation where the last update overrides all previous changes. The CommandBuilder does not support this option, but the Data Adapter Configuration Wizard does. To use it, go to the Advanced Options tab and clear the Use Concurrency check box.
This approach is not a recommended practice because it allows users to unknowingly overwrite other users' changes. It is never advisable to compromise the integrity of another user's update. (This technique is appropriate only for single-user databases.)
Including All Columns in the WHERE Clause
This option prevents you from overwriting changes made by other users between the time your code fetches the row and the time your code submits the pending change in the row. This option is the default behavior of both the Data Adapter Configuration Wizard and the SQL code generated by the SqlCommandBuilder.
This approach is not a recommended practice for the following reasons:
- If an additional column is added to the table, the query will need to be modified.
- In general, databases do not let you compare two BLOB values because their large sizes make these comparisons inefficient. (Tools such as the CommandBuilder and the Data Adapter Configuration Wizard should not include BLOB columns in the WHERE clause.)
- Comparing all columns within a table to all the columns in an updated row can create excessive overhead.
Including Unique Key Columns and the Timestamp Columns
With this option, the database updates the timestamp column to a unique value after each update of a row. (You must provide a timestamp column in your table.) Currently, neither the CommandBuilder nor the Data Adapter Configuration Wizard supports this option.
Including Unique Key Columns and the Modified Columns
In general, this option is not recommended because errors may result if your application logic relies on out-of-date data fields or even fields that it does not update. For example, if user A changes an order quantity and user B changes the unit price, it may be possible for the order total (quantity multiplied by price) to be incorrectly calculated.
Correctly Updating Null Fields
When fields in a database do not contain data values, it is often convenient to think of these empty fields as containing a special null value. However, this mental picture can be the source of programming errors because database standards require special handling for null values.
The core issue with null fields is that the ordinary SQL = operator will always return false if one or both of the operands is a null value. The operator IS NULL is the only correct way to check for the presence of a null field in a SQL query.
If your application uses the technique described above to manage concurrency by specifying a WHERE clause, you must include explicit IS NULL expressions wherever it is possible that a field could be null. For example, the following query will always fail if OldLastName is null:
SET LastName = @NewLastName WHERE StudentID = @StudentID AND LastName = @OldLastName
The query should be rewritten as:
SET LastName = @NewLastName WHERE (StudentID = @StudentID) AND
((LastName = @OldLastName) OR
(OldLastName IS NULL AND LastName IS NULL))
A good way to understand how to write the kind of update logic shown above is to read the output generated by the CommandBuilder tool.