Concurrency and the ObjectDataSource Control

Concurrency and the `ObjectDataSource` Control

Imagine that two users open the same page for editing the records in the movies database table at the same time. By default, if the first user submits changes before the second user, then the first user's changes are overwritten. In other words, the last user to submit changes wins.

This default behavior of the ObjectDataSource control can be problematic in an environment in which a lot of users are working with the same set of data. You can modify this default behavior by modifying the ObjectDataSource control's ConflictDetection property. This property accepts the following two values:

CompareAllValues Causes the ObjectDataSource control to track both the original and new values of its parameters
OverwriteChanges Causes the ObjectDataSource to overwrite the original values of its parameters with new values (the default value)

When you set the ConflictDetection property to the value CompareAllValues, you should add an OldValuesParameterFormatString property to the ObjectDataSource control. You use this property to indicate how the original values the database columns should be named.

The page in Listing 15.35 contains a GridView and ObjectDataSource control, which you can use to edit the movies in the Movies database table. The ObjectDataSource control includes a ConflictDetection property with the value CompareAllValues and an OldValuesParameterFormatString property with the value original_{0}.

Listing 15.35. `ShowConflictDetection.aspx`

[View full width]

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server">     Protected Sub srcMovies_Updated(ByVal sender As Object, ByVal e As  ObjectDataSourceStatusEventArgs)         If Not e.Exception Is Nothing Then             e.ExceptionHandled = True             lblError.Text = "Could not update record"         End If     End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <style type="text/css">         .error         {             color:red;             font:bold 16px Arial,Sans-Serif;         }         a         {             color:blue;         }     </style>     <title>Show Conflict Detection</title> </head> <body>     <form  runat="server">     <div>     <asp:Label                  EnableViewState="false"         Css         Runat="server" />     <asp:GridView                  DataSource         DataKeyNames="Id"         AutoGenerateEditButton="true"         Runat="server" />     <asp:ObjectDataSource                  ConflictDetection="CompareAllValues"         OldValuesParameterFormatString="original_{0}"         TypeName="ConflictedMovies"         SelectMethod="GetMovies"         UpdateMethod="UpdateMovie"         OnUpdated="srcMovies_Updated"         Runat="server" />     </div>     </form> </body> </html>

The ObjectDataSource control in Listing 15.35 is bound to the component in Listing 15.36.

Listing 15.36. `ConflictedMovies.vb`

[View full width]

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class ConflictedMovies     Private Shared ReadOnly _conString As String     Public Shared Function GetMovies() As SqlDataReader         ' Initialize connection         Dim con As New SqlConnection(_conString)         ' Initialize command         Dim cmd As New SqlCommand()         cmd.Connection = con         cmd.CommandText = "SELECT Id,Title,Director FROM Movies"         ' Execute command         con.Open()         Return cmd.ExecuteReader(CommandBehavior.CloseConnection)     End Function     Public Shared Sub UpdateMovie(ByVal title As String, ByVal director As String, ByVal  original_title As String, ByVal original_director As String, ByVal original_id As Integer)         ' Initialize connection         Dim con As New SqlConnection(_conString)         ' Initialize command         Dim cmd As New SqlCommand()         cmd.Connection = con         cmd.CommandText = "UPDATE Movies SET Title=@Title,Director=@Director WHERE  Id=@original_Id AND Title=@original_Title AND Director=@original_Director"         ' Create parameters         cmd.Parameters.AddWithValue("@Title", title)         cmd.Parameters.AddWithValue("@Director", director)         cmd.Parameters.AddWithValue("@original_Id", original_id)         cmd.Parameters.AddWithValue("@original_Title", original_title)         cmd.Parameters.AddWithValue("@original_Director", original_director)         Using con             con.Open()             Dim rowsAffected As Integer = cmd.ExecuteNonQuery()             If rowsAffected = 0 Then                 Throw New Exception("Could not update movie record")             End If         End Using     End Sub     Shared Sub New()         _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString     End Sub  End Class

The component in Listing 15.36 includes an UpdateMovie() method. Notice that this method accepts five parameters: the original_title, title, original_director, director, and original_id parameters.

The UpdateMovie() method raises an exception when the original parameter values don't match the current values in the Movies database table. Notice that the command executed by the Command object looks like this:

UPDATE Movies SET Title=@Title, Director=@Director WHERE Id=@original_id AND Title=@original_Title AND Director=@original_Director

This statement updates a row in the database only when the current values from the row match the original values selected from the row. If the original and current values don't match, no records are affected and the UpdateMovie() method raises an exception.

Concurrency and the ObjectDataSource Control