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. |