15.7 Handling Concurrency Issues

It is important to handle concurrency issues that almost inevitably result when multiple users access data simultaneously. Updates from one user should not inadvertently overwrite changes made by another user.

Because ADO.NET uses optimistic concurrency, two users can request the same data and then subsequently update the same data. Without managing concurrency, the changes made by the first user will be overwritten by the second user's changes and, to make matters worse, no one will know that this has happened.

There are several ways that concurrency can be managed in an optimistic locking environment. As discussed earlier, the CommandBuilder object attempts to match all original fields to the row data source to determine if the row has changed since it was retrieved. That approach suffers from several significant drawbacks that limit its usefulness.

The best approach to managing concurrency is to add a timestamp column to the table. The timestamp type is supported by most databases and doesn't actually contain a time and date; it's a binary value that is unique within the database. When updating a record in the data source, the timestamp value is used in addition to the primary key to locate a record in the data source. If a row has been modified by another user since it was last retrieved, it will not be found because the timestamp value will have changed. The second user can be notified of the failure, shown the new data in the row, and presented with options to deal with the concurrency violation that are suitable for the application. Figure 15-1 shows how a timestamp column can be used to handle the concurrency violation that results when a user retrieves a disconnected record, modifies it, and tries to reconcile it to the data source in which the row has been modified since it was retrieved. The timestamp values don't match, the user is notified of the error resulting from the update attempt, and the row in the data source isn't updated.

Figure 15-1. Using a timestamp column to handle data concurrency

Technically, you can match a record in SQL Server just by using the timestamp column because every timestamp value is unique. However, a primary key provides faster lookup because it is indexed. You should never create an index on a timestamp value because it changes frequently.

Example 15-5 uses a timestamp field called rowversion to help manage concurrency issues. Further, each concurrency violation is added to a table in a DataSet of conflicts.

Example 15-5. Managing concurrency

private SqlDataAdapter daConflict; 
private DataSet dsConflict; 

// ... 

// connection and select command strings 
String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;"; 
SqlConnection conn = new SqlConnection(connString); 
SqlCommand cmd = new SqlCommand("SELECT * FROM Orders ", conn); 

daConflict = new SqlDataAdapter(cmd); 

// create the DataSet to store concurrency exceptions and retrieve the 
// schema from the data source 
dsConflict = new DataSet();
daConflict.FillSchema(dsConflict, SchemaType.Source); 

// create command objects using SQL statements 
SqlCommand selectCmd = new SqlCommand("SELECT OrderID, CustomerID, " + 
    "EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, " + 
    "Freight, ShipName, ShipAddress, ShipCity, ShipRegion, " + 
    "ShipPostalCode, ShipCountry, rowversion FROM Orders", conn); 
SqlCommand updateCommand = new SqlCommand("UPDATE Orders SET " + 
    "CustomerID=@CustomerID, EmployeeID=@EmployeeID, " + 
"OrderDate=@OrderDate, RequiredDate=@RequiredDate, " + 
"ShippedDate=@ShippedDate, ShipVia=@ShipVia, Freight=@Freight, " + 
"ShipName=@ShipName, ShipAddress=@ShipAddress, ShipCity=@ShipCity, " +
"ShipRegion=@ShipRegion, ShipPostalCode=@ShipPostalCode, " +
"ShipCountry=@ShipCountry " + "WHERE OrderID=@OrderID AND rowversion=@rowversion;" + 
"SELECT rowversion WHERE OrderID=@OrderID", conn); 

// ... code for delete and insert commands

// set up the parameters 
SqlParameterCollection cparams; 

// update command parameters 
cparams = updateCommand.Parameters;
cparams.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
cparams.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
cparams.Add("@EmployeeID", SqlDbType.Int, 0, "EmployeeID");
cparams.Add("@OrderDate", SqlDbType.DateTime, 0, "OrderDate");
cparams.Add("@RequiredDate", SqlDbType.DateTime, 0, "RequiredDate");
cparams.Add("@ShippedDate", SqlDbType.DateTime, 0, "ShippedDate");
cparams.Add("@ShipVia", SqlDbType.Int, 0, "ShipVia");
cparams.Add("@Freight", SqlDbType.Money, 0, "Freight");
cparams.Add("@ShipName", SqlDbType.NVarChar, 40, "ShipName");
cparams.Add("@ShipAddress", SqlDbType.NVarChar, 60, "ShipAddress");
cparams.Add("@ShipCity", SqlDbType.NVarChar, 15, "ShipCity");
cparams.Add("@ShipRegion", SqlDbType.NVarChar, 15, "ShipRegion");
cparams.Add("@ShipPostalCode", SqlDbType.NVarChar, 10, "ShipPostalCode"); 
cparams.Add("@ShipCountry", SqlDbType.NVarChar, 15, "ShipCountry"); 
cparams.Add("@rowversion", SqlDbType.Timestamp, 0, "rowversion");
cparams["@rowversion"].SourceVersion=DataRowVersion.Original; 

// ... create parameters for delete and insert commands 

// create the data adapter 
SqlDataAdapter da = new SqlDataAdapter(selectCmd.CommandText, conn); 

// set the ContinueUpdateOnError property so that all records are 
// processed regardless of exceptions 
da.ContinueUpdateOnError = true; 

// add the event handler so that the concurrency exceptions can be added 
// to the DataSet containing the conflicts 
da.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated); 

// set the update commands for the data adapter 
da.DeleteCommand = deleteCommand;
da.InsertCommand = insertCommand; 
da.UpdateCommand = updateCommand;

// create a new DataSet to receive the data and load the data 
DataSet ds = new DataSet(); 

// load all of the data for the Orders table into the DataSet 
da.Fill(ds, "Orders"); 

// ... code to modify the DataSet

// update the data in the Orders table in the DataSet to the data source 
da.Update(ds, "Orders"); 

private void da_RowUpdated(object sender, SqlRowUpdatedEventArgs e) 
{ 
    if (e.Status == UpdateStatus.ErrorsOccurred && 
        e.Errors.GetType() == typeof(DBConcurrencyException)) 
    { 
        // retrieve the data for the row with concurrency 
       // exception and store it to the DataSet with an error message 
       daConflict.SelectCommand.Parameters["@ID"].Value 
          = (String) e.Row["ID"]; 
       if(daConflict.Fill(dsConflict, "Orders") == 1)
          e.Row.RowError = "Row has been changed in the database"; 
       else
          e.Row.RowError = "Row has been deleted from database"; 
       e.Status = UpdateStatus.Continue; 
    } 
}

There are really two practical ways to deal with the row updates that have failed as a result of a concurrency exception:

• If partial updates are acceptable, handle the RowUpdated event. When an exception is encountered, set the RowError for the failed row and retrieve new values from the data source. Continue processing the update for remaining rows by setting the UpdateStatus to Continue. Once all rows in the update have been processed, present the user with the new data source values for the failed rows along with the data that failed. Allow the user the opportunity to modify or correct the failed updates and resubmit as appropriate to the application. Example 15-5 demonstrates most of this technique.

• If the update requires that all rows successfully update back to the data source, wrap the entire update in a transaction. If a concurrency error is raised, roll back the transaction. This prevents rows that have been updated prior to the exception from being persisted to the data source, thereby leaving the data source in a state that is known to be valid. This approach works well with small batches where multiple users are unlikely to be accessing the same data.

The RowUpdated event occurs while an update is in progress and, hence, while your application has a live connection to the database. For that reason, you should be extremely careful not to perform any time-consuming logic in your event handler (or even worse, delay the code by requesting user input). A good approach is to log errors and display them in a user interface control once the update is complete.