Comparing DataSet Values Against the Database for Optimistic Concurrency

for RuBoard

Comparing DataSet Values Against the Database for Optimistic Concurrency

To do optimistic concurrency correctly (with 100% data integrity), you really should compare all the original data column's data values against what is in the database for your update to be considered valid (not violating optimistic concurrency). Perhaps another user has updated another column's data values for a particular data row (like the ContactName column's data value) and, at the same time, you have updated some other column's data values (like the ContactTitle column's data value). The resulting data row (after both of the updates) will have potentially mismatched data values for this particular data row (the ContactName data value doesn't correspond to the ContactTitle data value any longer). This can potentially cause a difficult data integrity anomaly. It would look like this:

1. Client A reads the ContactName value from the database for CustomerID = 'ABC' at 9:14:42 AM. The data values in the database are now:

   CustomerID	ContactName	ContactTitle
   ABC	Peach	Owner

2. Client B reads the ContactTitle value from the database for the same CustomerID = 'ABC' at 9:14:43 AM. The data values in the database are still:

   CustomerID	ContactName	ContactTitle
   ABC	Peach	Owner

3. Client A updates the ContactName value from "Peach" to the value of "Peter Johnson" at 9:14:55 AM. Client A's application only compared the original ContactName value (that they read into cache) with what is on the database at the time of their update. These matched, so they committed their update. The data values in the database are now:

   CustomerID	ContactName	ContactTitle
   ABC	Peter Johnson	Owner

4. Client B updates the ContactTitle value from "Owner" to the value of "Assistant buyer" at 9:15:05 AM. Client B's application only compared the original ContactTitle value (that they read into cache) with what is on the database at the time of their update. These matched, so they committed their update. The data values in the database are now:

   CustomerID	ContactName	ContactTitle
   ABC	Peter Johnson	Assistant buyer

Unfortunately, the data for CustomerID = 'ABC' is now all messed up. Peter Johnson is the owner, not the assistant buyer of the ABC company. The application just wasn't completely implementing optimistic concurrency correctly.

Listing 14.1 is a short piece of Visual Basic code that you can execute as practice for learning more on this subject. This example uses the ADO.NET DataSet fill and update approach from the Customers table in the Northwind database (that comes with Microsoft SQL Server).

 CustomerAdapter.Fill(CustomerDataSet, "Customers") CustomerAdapter.Update(CustomerDataSet, "Customers") 

It will also save the original data row values as they were originally read from the database.

 OldParms = CustomerAdapter.UpdateCommand.Parameters.Add("@oldCustomerID",          SqlDbType.NChar, 5, "CustomerID")   OldParms.SourceVersion = DataRowVersion.Original 

These will be used in a comparison ( WHERE clause) that will be part of the UPDATE statement.

 CustomerAdapter.UpdateCommand = New SqlCommand( "UPDATE Customers (CustomerID, CompanyName, ContactName) " & "VALUES(@CustomerID, @CompanyName, @ContactName) " & _ "WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName " & " AND ContactName = @oldContactName", nwindConn) 

By doing this comparison of the original data values read from the database with what's in the database at the time the update is issued, you will be guaranteeing yourself that no other user has slipped in and updated something before you. This is optimistic concurrency.

Listing 14.1 Comparing Original Data Values to Current Data Values (File 14OptCon.vb)

 Imports System Imports System.Data Imports System.Data.SqlClient Imports Microsoft.VisualBasic namespace HowTo.ADONET24.Samples Public Class OptConSample   Public Shared Sub Main()     Dim nwindConn As SqlConnection = New SqlConnection         ("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")     Dim CustomerAdapter As SqlDataAdapter = New SqlDataAdapter         ("SELECT CustomerID, CompanyName, ContactName " &         " FROM Customers ORDER BY CustomerID", nwindConn)   CustomerAdapter.UpdateCommand = New SqlCommand         ("UPDATE Customers (CustomerID, CompanyName, ContactName) " &         "VALUES(@CustomerID, @CompanyName, @ContactName) " & _         "WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName " &         " AND ContactName = @oldContactName", nwindConn)   CustomerAdapter.UpdateCommand.Parameters.Add        ("@CustomerID", SqlDbType.NChar, 5, "CustomerID")   CustomerAdapter.UpdateCommand.Parameters.Add        ("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")   CustomerAdapter.UpdateCommand.Parameters.Add        ("@ContactName", SqlDbType.NVarChar, 30, "ContactName")  'Set up OldParms to hold the rows original values  'These are then used in the WHERE clause for the  'optimistic concurrency comparison   Dim OldParms As SqlParameter   OldParms = CustomerAdapter.UpdateCommand.Parameters.Add         ("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID")   OldParms.SourceVersion = DataRowVersion.Original   OldParms = CustomerAdapter.UpdateCommand.Parameters.Add         ("@oldCompanyName", SqlDbType.NVarChar, 40, "CompanyName")   OldParms.SourceVersion = DataRowVersion.Original   OldParms = CustomerAdapter.UpdateCommand.Parameters.Add        ("@oldContactName", SqlDbType.NVarChar, 30, "ContactName")   OldParms.SourceVersion = DataRowVersion.Original   Dim CustomerDataSet As DataSet = New DataSet()   Console.Writeline ("Go get some customer data - Fill")   CustomerAdapter.Fill(CustomerDataSet, "Customers")   Console.Writeline ("Update the rows")   CustomerAdapter.Update(CustomerDataSet, "Customers")   Dim CustRow As DataRow   Console.Writeline ("Look for optimistic concurrency violations")   For Each CustRow In CustomerDataSet.Tables("Customers").Rows     Console.Writeline ("Looking for errors for row with CustomerID of " &         CustRow(0) )     If CustRow.HasErrors Then Console.WriteLine(CustRow(0) &        vbCrLf & CustRow.RowError)     if not CustRow.HasErrors then Console.Writeline        ("No optimistic concurrency error found")   Next   Console.Writeline ("Show contents of DataSet")   For each CustRow in CustomerDataSet.Tables("Customers").Rows       Console.Writeline("Customer Contacts Selected: "       + CustRow("ContactName").ToString())   Next  End Sub End Class End namespace 

To execute this code, you must first quickly compile it from a DOS command prompt. Change directories to the location of the VB source code that contains this example (14optcon.vb). You might need to change the data source statement in the SQLConnection string ( Data Source=localhost ) to point to a specific MS SQL Server instance if it can't resolve "localhost". Then just compile the code as follows :

 C:\ADOSAMPLES>  vbc.exe 14optcon.vb  /r:System.dll  /r:System.Data.dll   /r:System.Xml.dll  

You should see the following .NET VB compiler messages:

 Microsoft (R) Visual Basic .NET Compiler version 7.00.9447 for Microsoft (R) .NET Framework version 1.00.3617 Copyright (C) Microsoft Corporation 1987-2001. All rights reserved. 

This will be followed by the DOS command prompt again (if it is a successful compile). Otherwise, you will be getting compile errors of one kind or another.

After the code has compiled successfully, you will need to make sure that you have Microsoft SQL Server up and running and the Northwind database has been installed (usually by default). To execute this sample, just specify the sample name at the DOS command prompt and press Enter.

 C:\ADOSAMPLES>  14OptCon.exe  <press enter> Go get some customer data - Fill Update the rows Look for optimistic concurrency violations Looking for errors for row with CustomerID of ABC No optimistic concurrency error found Looking for errors for row with CustomerID of ALFKI No optimistic concurrency error found . . . Looking for errors for row with CustomerID of WOLZA No optimistic concurrency error found Show contents of DataSet Customer Contacts Selected: Peach Customer Contacts Selected: Maria Anders Customer Contacts Selected: Ana Trujillo . . . Customer Contacts Selected: Zbyszek Piestrzeniewicz 

Great! To make things a bit more robust, you can add in an OnRowUpdated subroutine as follows:

 Private Shared Sub OnRowUpdated(sender As Object, updevent As        SqlRowUpdatedEventArgs)      If updevent.RecordsAffected = 0         updevent.Row.RowError = "Optimistic Concurrency Violation"         updevent.Status = UpdateStatus.SkipCurrentRow      End If    End Sub 

The test for ZERO records affected will be true if the UPDATE statement comparisons fail (indicating that some original data did not match what is now in the database ”thus an optimistic concurrency violation).

Then you add the SqlRowUpdatedEventHandler handler to the main subroutine as follows:

 AddHandler CustomerAdapter.RowUpdated,                New SqlRowUpdatedEventHandler(AddressOf OnRowUpdated) 

Now each row that violates optimistic concurrency will be marked as having this error. This also makes it much easier to narrow in on the rows that have only this error. Then you can decide what you want to do with this update situation.

Timestamps in ANSI SQL-92 and Higher

In ANSI-SQL 92 and higher, the timestamp column is just a datetime column. In SQL Server 2000, you can also use a rowversion data type that is equivalent to the old SQL Server timestamp column. If you specify the timestamp column in SQL Server 2000, it will behave as it always has. The real trick comes when you are looking at other data providers' interpretations of timestamp. Don't get these mixed up! SQL storage is Varbinary 8 (null), or binary 8 (not null) for the timestamp column.

for RuBoard