In database-locking parlance, the term optimistic concurrency means that your application assumes that no one else will be making changes to the same record that it is currently editing. I explored this concept in detail in a previous article, along with how ADO.NET automatically supports this locking method at the Data Adapter level via the Data Adapter Configuration Wizard.
Although the Data Adapter Configuration Wizard generates code to check for optimistic concurrency violations, it does not handle the errors itself. Running into an optimistic concurrency violation simply causes your program to blow up by throwing an exception. You can add a Try…Catchblock to handle the actual exception, but you’ll probably want to be able to give your users the best overall experience by intelligently handling the violation itself. There are two main ways of doing so, which I’ll explore by way of a sample application created using the Data Adapter Configuration Wizard.
Creating a sample application
To create the sample application, start Visual Studio .NET and create a new Visual Basic .NET Windows Application. On the form, add a DataGrid control and three buttons. Set the button properties as shown in Table A.
Next, drag a SqlDataAdapter component onto the form. The Data Adapter Configuration Wizard will be launched. Use it to connect to the Northwind sample database. Choose the Use SQL Statements option, enter SELECT * FROM Customers as your SQL statement, and finish the wizard.
Not using SQL Server?
This sample application assumes that you have access to SQL Server. If you do not, feel free to use an OleDbDataAdapter instead and connect to an Access database.
Choose the Generate Dataset option from the Data menu. If it’s grayed out, make sure the SqlDataAdapter1 object in the component tray below the form is selected. Name the DataSet dsCustomers and make sure you select the option to add it to the designer. Once you have a DataSet, click the DataGrid control and set its DataSource property to DsCustomers1.Customers.
Finally, double-click the Fill button and add the following code:
Private Sub cmdFill_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdFill.Click
The RowUpdated option
One way to catch optimistic concurrency violations is to use the SqlDataAdapter’s RowUpdated event. This event is fired just after an update attempt, and it fires for each row. Handle this event and check to see if the row was updated by finding out how many rows were affected. If zero rows were affected, you know that the update or delete failed and there was an optimistic concurrency violation.
You’ll need to create a procedure in code to handle the event. Add the following procedure to your code:
Private Sub OnRowUpdated(ByVal sender As Object, _
ByVal args As SqlClient.SqlRowUpdatedEventArgs)
If args.RecordsAffected = 0 Then
args.Row.RowError = “Optimistic concurrency violation detected”
args.Status = UpdateStatus.SkipCurrentRow
Here you have a procedure that will accept an argument of type SqlRowUpdatedEventArgs. When checking the args.RecordsAffected property, you’ll know that an error occurred if the property is zero. In that case, set the args.RowError property for that row to any text of your choosing, and then set the args.Status property to SkipCurrentRow so that processing will continue with any other updates.
Next, add a handler for the RowUpdated event. In the code for the form, expand the Windows Form Designer Generated Code region and find the form’s constructor (look for Public Sub New). After the InitializeComponent call, add the following line of code:
AddHandler SqlDataAdapter1.RowUpdated, _
New SqlClient.SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)
When you run this application, you’ll first fill the DataGrid. If there is a violation, the OnRowUpdated procedure will cause the DataGrid to show a small red circle containing an exclamation mark next to the record in question. If the user hovers the mouse over the red circle, the tooltip will show the message you set for the RowError property. If you want to give the user more information, such as the original values and the new values he or she is trying to insert, you can adapt the code I discuss in the section below, “The HasErrors Option.”
Finally, add the following code to the Update With RowUpdate button:
Private Sub cmdRowUpdate_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdRowUpdate.Click
To test this app, compile the application and run two instances of it at once. Click the Fill button on both copies. Change the ContactName on the first record in one instance of the application, but do not click the Update With RowUpdate button. Move to the second instance of the application, edit the same record with a different value, and click the Update With RowUpdate button. Return to the first instance and click the Update With RowUpdate button. The first instance’s update will fail, and the grid will show you that the record did not update successfully.
The HasErrors option
Another approach you can use is to catch errors by examining the HasErrors property of the DataTable you are trying to update, as shown in Listing A. Here, you call the Update method of the DataAdapter. But before you do, you’ll set the ContinueUpdateOnError property of the DataAdapter to True so it will attempt to make all updates, even if an error occurs. When the update is completed, you examine the DataTable’s HasErrors property to determine if any errors occurred. If they did, examine each Row object in the table, looking for one with HasErrors set to True. Note that this example assumes you will be updating only the ContactName field in the table.
In this case, the code retrieves the current and original values for the ContactName field by passing the Original members and Current members of the DataRowVersion enum to the row’s default property. These values are then displayed in a MessageBox.
But what if you also want the value that the other user entered for the field while you were editing? In that case, you’d add code to retrieve the value from the database. In Listing B, I’ve modified the event handler code to read the results of a query with a DataReader.
Test this example as you did before, modifying the ContactName field in two instances of the application, but clicking the Update With HasErrors button instead. This time, you‘ll get a MessageBox displaying the original value retrieved from the database, the value you entered when editing, and the current value in the database.
The disconnected nature of the DataSet makes it quite powerful. However, optimistic concurrency can lead to problems, as in the case of frequent data updates. As you’ve seen here, handling optimistic concurrency violations gracefully can be done with some coding effort. But you’ll still need to design a mechanism that allows a user to overwrite the new record if necessary. It might be better to consider a different approach that can prevent these sorts of violations from occurring in the first place, which is something I’ll cover in my next article.