Practical ASP.NET

Handling Concurrency with ObjectDataSource

Peter revisits the problem of dealing with multiple updates to the same data, but this time he looks at a solution that involves the ObjectDataSource.

• By Peter Vogel
• 05/26/2009

In earlier columns, I looked at solutions for handling data concurrency (i.e., when two or more users update the same data at the same time). After outlining the problem, I provided two solutions: one to put in your middle-tier business objects and one to put in your SQL statements, either inside your business objects or in the SqlDataSource's update statements.

However, there's also a solution that you can implement in the ObjectDataSource to handle part of the problem, and that's the subject of this column.

ObjectDataSource and the Factory Method Pattern
A quick review: The ObjectDataSource makes it possible for you to bind middle-tier business objects to DataViews, provided that you've implemented the Factory Method pattern (i.e., you have one object with a set of methods, the "factories," that return the objects you want to display in your user interface). In the Factory Method pattern, you also have methods that, when passed a business object, handle making updates to your database.

What the ObjectDataSource allows you to do is check to see which values were changed in the DataView by the user. This allows you to update only those values which were changed. This does not prevent one user from updating a record already updated by another user. However, as I pointed out in an earlier column, if you only update the fields the user has actually changed, you can eliminate most (I would say all) of the problems with concurrency.

The primary issue in concurrency problems is preventing the second user who's doing an update from overwriting changes made by an earlier user's update. In a well-designed database, all values in a record are dependent on the primary key field(s) and otherwise independent of each other. In that scenario, if you only update the fields that your user changes, your concurrency problems go away.

Getting Original Values
By default, the ObjectDataSource passed as single object to your update method that a single object holding the values set in the DataView by the user. To get both the original and update values you need to make two sets of changes: one set to the ObjectDataSource and the second set to your update method.

The fundamental change is to have the ObjectDataSource pass multiple parameters to your update method: one set of parameters that hold the original values that were displayed to the user and a second set of parameters containing the values after the user's updates. In your update method, you'll need to provide a set of parameters with the same names as the properties on the object displayed in the DataView (i.e., if the object returned by your factory method has a property called "LastName," then your update method must have a parameter called "LastName"). You don't need to provide a parameter for every property on your object -- only the properties that are actually displayed in the DataView.

To get the ObjectDataSource to pass a set of parameters with the original values you must set the ObjectDataSource's ConflictDetection property to CompareAllValues. But now that you're getting two sets of parameters, you also need to control the name of the second set of parameters containing the original values. Those names are controlled through a format string that you place in the ObjectDataSource's OldValuesParameterFormatString. If you set the property to "original_{0}" then the parameters in your update method that accept the "original" values must have the string "original_" prefixed to their name. And do pick "original_{0}" as your format string because almost any change to the ObjectDataSource seems to cause the ObjectDataSource to set that value in OldValuseParameterFormatString.

Assuming that my DataView is displaying the EmployeeId, LastName and Region properties from my object, my update method would look like the following code. In this update method, I compare the original and current values and, when the values are different, I create a SQL statement that updates only those fields. The right way to do that is with ADO.NET parameters, but since that's an ADO.NET issue and not an ASP.NET issue, I'll just concatenate strings here to make what I'm doing obvious:

Public Shared Sub UpdateSalesPersonWithConflict( _
     ByVal EmployeeId As String, _
     ByVal LastName As String, _
     ByVal Region As String, _
     ByVal Original_EmployeeId As String, _
     ByVal Original_LastName As String, _
     ByVal Original_Region As String)

Dim sbSQL As New StringBuilder
sbSQL.Append("Update SalesPerson Set ")

If LastName <> Original_LastName Then
     sbSQL.Append("LastName = '" & LastName & "' ,")
End If
If Region <> Original_Region Then
          sbSQL.Append("Region = '" & Region & "' ,")
End If
sbSQL = sbSQL.Remove(sbSQL.Length, 1)
sbSQL.Append(" Where EmployeeId = '" & EmployeeId & "';")

UpdateDatabase(sbSQL.ToString)

End Sub