Managing Concurrent Data Updates | Professional ASP.NET MVC 1.0 (Wrox Programmer to Programmer)

To finish off looking at relational data handling in .NET, we'll examine some of the issues that arise when you have multiple users updating your data “a problem area normally referred to as concurrency . It's easy enough to see how such a problem could arise:

Alice in accounts receives a fax from a customer indicating that their address has changed. She opens the customer record in her browser and starts to change the address column values.
Just at this moment, Dave in dispatch (who received a copy of the fax) decides to update the customer's delivery route code. He also opens the customer record in his browser and changes the routing code column value.
While Dave is doing this, Alice finishes editing the address and saves the record back to the database.
Shortly afterwards, Dave saves his updated record back to the database.

What's happened is that Dave's record, which was opened before Alice saved her changes, contains the old address details. So when he saves it back to the database, the changes made by Alice are lost. And while concurrency issues aren't solely confined to databases (they can be a problem in all kinds of multi- user environments) it is obviously something that you can't just ignore when building data access applications.

Avoiding Concurrency Errors

Various database systems and applications use different approaches to control the concurrent updates problem. One solution is the use of pessimistic record locking . When a user wants to update a record, they open it with pessimistic locking; preventing any other user opening the same record in update mode. Other users can only open the record in 'read' mode until the first user saves their copy and releases their lock on the record.

Note	For maximum runtime efficiency, many database systems actually lock a 'page' containing several contiguous records rather than just a single one “but the principle is the same.

However, in a disconnected environment (particularly one with occasionally unreliable network links such as the Internet) pessimistic locking is not really feasible . If a user opens a record and then goes away, or the network connection fails, it will not be released. It requires some other process to monitor record locks and take decisions about when and if the user will come back to update the record so that the lock can be released.

Instead, within .NET, all data access is through optimistic record locking , which allows multiple users to open the same record for updating “possibly leading to the scenario we described at the start of this section. It means that you have to use some kind of code that can prevent errors occurring when you need to support concurrent updates. There are a few options you can take, such as:

Write stored procedures that do lock records and manage the updates to prevent concurrency errors. For example, you could add a column called " Locked " and set this when a user fetches a row for updating. While it's set, no other user could open the row for updating, only for reading. This is not a favored approach in .NET as it takes away the advantages of the disconnected model.
Arrange for your code to only update the actual columns that it changes the value of, minimizing the risk of (but not guaranteeing to prevent) concurrency errors. For example, in the previous scenario, if Dave in dispatch had only updated the route code column that he changed the value of, Alice's changes to the address columns would not have been lost.
Compare the existing values in the records with the values that were there when you created your disconnected copy of the record. This way you can see if another user has changed any values in the database while you were working on your disconnected copy. This is the preferred solution in .NET, and there are built-in features that help you to implement it.

A Concurrency Error Example

To illustrate how concurrency error can be detected , try the example page Catching Concurrency Errors When Updating the Source Data ( concurrency - error . aspx ) shown in Figure 10-15. This page extracts a row from the source data table and displays the values in it. Then it executes a SQL statement directly against the original table in the database to change the Title column of the row while the disconnected DataSet is holding the original version of the row. You can see this in the screenshot after the first DataGrid control.

Figure 10-15:

Next the code changes a couple of columns in the disconnected DataSet table row, then calls the Update method of the DataAdapter to push this change back into the original source table. A CommandBuilder object is used to create the SQL statement that performs the update, and you can see this statement displayed in the preceding page the SELECT statement that originally fetched the row. Notice that it uses a WHERE clause that tests all the values of the row in the database against the values held in the DataSet . This means, of course, that (because the concurrent process has changed the row) the update fails and an error is returned. What's happened is that the Update process expects a single row to be updated, and when this didn't happen it reports an error. The error message is displayed at the bottom of the page.

At this point, the developer would usually indicate to the user that this error had occurred, and give them the chance to reconcile the changes. Exactly how this is done and what options the user has depends on the application requirements. The usual process is to provide the user with the values that currently exist in the row as well as the values they entered, and allow them to specify which should be persisted into the data store.

The Code for the 'Catching Concurrency Errors' Example

The only section of the code for this example that you haven't seen before is that which performs a concurrent update to the source table in the database while the DataSet is holding a disconnected copy of the rows. It's simply a matter of creating a suitable SQL statement, a new Connection and Command object, and executing the SQL statement. Collect the number of rows affected by the update and display this in the page, along with the SQL statement you executed against the original data in the database.

  'change one of the rows concurrently  i.e. while the   'DataSet is holding a disconnected copy of the data   Dim strUpdate As String   Dim datNow As DateTime = Now()   Dim strNow As String = datNow.ToString("dd-M-yy \a\t hh:mm:ss")   strUpdate = "UPDATE BookList SET Title = 'Book Updated on " _   & strNow & "' WHERE ISBN = '1000000002'"   Dim intRowsAffected As Integer   Dim objNewConnect As New OleDbConnection(strConnect)   Dim objNewCommand As New OleDbCommand(strUpdate, objNewConnect)   objNewConnect.Open()   intRowsAffected = objNewCommand.ExecuteNonQuery()   objNewConnect.Close()   outUpdate.InnerHtml = "Command object concurrently updated " _   & CStr(intRowsAffected) & " record(s)<br />" & strUpdate

Next, the code changes a couple of column values in the disconnected copy of the row within the DataSet table. Then all you have to do is execute the Update method of the DataAdapter object. The error is trapped by the Try..Catch construct (like that we've used in all the examples) and details are displayed in the page.

  'change the same row in the table in the DataSet   objTable.Rows(0)("Title") = "Amateur Theatricals for Windows 2000"   objTable.Rows(0)("PublicationDate") = Now()   Try   'create an auto-generated command builder and set UPDATE command   Dim objCommandBuilder As New OleDbCommandBuilder(objDataAdapter)   objDataAdapter.UpdateCommand = objCommandBuilder.GetUpdateCommand()   'display the auto-generated UPDATE command statement   outUpdate.InnerText = objDataAdapter.UpdateCommand.CommandText

Now do the update (in this case we know it will fail):

  intRowsAffected = objDataAdapter.Update(objDataSet, "Books")   outResult.InnerHtml = "<b>* DataSet.Update</b> affected <b>" _   & CStr(intRowsAffected) & "</b> row."   Catch objError As Exception   'display error details   outError.innerHTML = "* Error updating original data.<br />" _   & objError.Message & "<br />" & objError.Source   End Try

Updating Just the Changed Columns

In general, it is the process of modifying existing rows in a data store that is most likely to create a concurrency error. The process of deleting rows is usually less error-prone , and less likely to cause data inconsistencies in your database. Likewise, providing data entry programs are reasonably clever about how they create the values for unique columns; the process of inserting new rows is generally less of a problem.

One of the ways that you can reduce the likelihood of a concurrency error during row modification, as we suggested right at the start of this section of the chapter, is to push only the modified column values (the ones that have been changed by this user or process) into the original data store, rather than blindly updating all of the columns. Of course, this means that you can't use the Update method “you have to build and execute each SQL statement yourselves.

An Example of Updating Individual Columns

The example page Managing Concurrent Updates to Individual Columns ( concurrency-columns.aspx ) demonstrates the process we've just been discussing. Rather than updating all the columns in every row that has been modified, it only attempts to update the column values that have actually changed. The code extracts a series of rows from the BookList table and displays them, then changes some of the column values and displays the rows again.

In Figure 10-16, you can see the original values of the rows in the database, and the values of the rows in our disconnected DataSet table after the code has changed some of the values. This code changed the title of the first book, the title and publication date of the third book, and (highlighted in the screenshot) just the publication date for the fourth book.

Figure 10-16:

Lower down the the page, as shown in Figure 10-17, you can see that the code then concurrently updates two of the rows in the source database table while the DataSet is holding a disconnected copy of the data. It changes the titles of the third and fourth books. Below this is another DataGrid that shows just the modified rows in the DataSet .

Figure 10-17:

Finally, at the bottom of the page you can see that “after the concurrent updates have taken place “we attempt to push our changes in the DataSet back into the data store. However, in this case, the Update method of the DataAdapter is not used. Instead, each of the modified rows is processed individually by executing a custom SQL statement for each one. As you can see from comparing these, the statements used only update the columns that have been changed within the table in the DataSet , and only check the existing values of these columns (as part of the WHERE clause) in the database.

As seen in Figure 10-17, the first update succeeds because the concurrent process hasn't changed the original row. Following this, the second update fails because you are attempting to change the title while the concurrent process has already changed this column in the database (look at Figure 10-16 to see the original values of the rows).

However, the third update also succeeds “even though the same row in the original table in the database has been concurrently updated. The concurrent process changed only the Title column while our disconnected copy contains an updated value for only the PublicationDate column. Hence, because the update code is clever enough to only update the changed columns, both updates can occur concurrently without the risk of inconsistencies arising.

The Code for the 'Updating Individual Columns' Example

There are several things going on in this example page that we need to look at in more depth. For example, you need to be able to get at just the modified rows in the table within your DataSet so that you can iterate through these rows processing the update for each one. Secondly, you need to look in more detail at how to create the values that you use in the WHERE clause of your SQL statements to compare to a DateTime column in SQL server.

Marshalling the Changed Rows in a DataSet

As mentioned in the previous chapter, every row in a table within a DataSet has a RowState property that indicates whether that row has changed since the table was filled, or since the last time the AcceptChanges or RejectChanges method was called. So, to get a list of the changed rows you could iterate through the table looking at this property in each row, and extract just the ones we want into an array “or into another table.

The general process of collecting together data and transferring it to another location is often referred to as marshalling . In your case, you want to marshal the changed rows from one table into another table, and the .NET data access classes make it easy through the GetChanges method of the DataSet object. It returns a DataSet object containing just the changed rows. You can use the GetChanges method in two ways:

With no parameters, whereupon it returns a DataSet object with the default table (at index zero) filled with all the changed rows “e.g. all the rows that have been modified, deleted, or inserted.
With a DataRowState value as the single parameter, whereupon it returns a DataSet object with the default table (at index zero) filled with just the changed rows having that value for their RowState property “e.g. just the rows that have been modified, or just the rows that have been deleted, or just the rows that have been inserted.

This process would also allow you to take a table that contained updated, deleted, and inserted rows and extract these into separate arrays of rows “one each for changed rows, deleted rows, and updated rows. You could then use the Update method of the DataAdapter with each table or array of rows in turn (as discussed earlier in this section of the chapter) “in the correct order to avoid any errors due to parent/child relationships within the source data tables.

Getting the Modified Rows into a New DataSet

The code in your page creates a variable to hold a DataSet object, and then executes the GetChanges method with the value DataRowState.Modified as the single parameter. The new DataSet object is returned and assigned to your variable objChangeDS , and you can display the contents in the usual way using a DataGrid control defined within the HTML section of the page.

  'declare a variable to hold another DataSet object   Dim objChangeDS As DataSet     'get *changed* records into the new DataSet   'copy only rows with a RowState property of "Modified"   objChangeDS = objDataSet.GetChanges(DataRowState.Modified)     'display the modified records from the table in the new DataSet   dgrResult3.DataSource = objChangeDS.Tables(0).DefaultView   dgrResult3.DataBind() 'and bind (display) the data

As an aside (we don't actually do it in our example here) you can use the same technique to get the inserted, deleted, or unchanged rows as well. To get the inserted rows into a DataSet just specify the value DataRowState.Added in the parameter to the GetChanges method:

  objChangeDS = objDataSet.GetChanges(DataRowState.Added)

The same applies to the deleted rows; specify DataRowState.Deleted in the parameter to the GetChanges method:

  objChangeDS = objDataSet.GetChanges(DataRowState.Deleted)

However, if you then bind this data to a DataGrid object, nothing will be displayed because all of the rows have been deleted! To get round this you can create a DataView object explicitly for the table and then set the RowStateFilter property to DataViewRowState.Deleted as well (this topic was covered in previous chapters). Then it shows the deleted rows:

  Dim objDataView As DataView = objChangeDS.Tables(0).DefaultView   objDataView.RowStateFilter = DataViewRowState.Deleted   dgrResult.DataSource = objDataView   dgrResult.DataBind()

Finally, to get the unchanged rows specify DataRowState.Unchanged in the parameter to the GetChanges method:

  objChangeDS = objDataSet.GetChanges(DataRowState.Unchanged)

Getting Back to Our Example

After that short aside, let's get back to the code for your example page. While there is quite a lot of code in this page, most of it is stuff that you've seen several times before. Basically, you extract the rowset from the database into a DataSet and display it, execute a couple of SQL UPDATE statements to change the original data store contents, then change some values in the same rows in the disconnected copy held within the DataSet object. All these steps can be seen in the page.

What we want to concentrate on here is how to create and execute the SQL statements that you'll use to perform the updates to the original data from the rows in the disconnected DataSet .

Building the SQL Statements

The plan is to create the two 'root' parts of the SQL statement (the SET clause and the WHERE clause) separately as you iterate through each column in the row, then assemble the complete statement afterwards. You've already marshaled the modified rows into a new DataSet named objChangeDS , so you can iterate through the single table in that DataSet processing each modified row using a For Each construct.

As you process each row, create your two sections of SQL statement. For the WHERE clause include the test for the Original value of the ISBN (the primary key). Then you can start the nested For Each construct that will iterate through each column in this row, and collect the column name in a string variable.

The next step is to see if the value of the column has been changed since you loaded your DataSet , by comparing the Original and the Current values. Note that this is nothing to do with checking the original values in the source table in the database. You're disconnected from the database, and so you can't see any concurrent updates going on. What you're checking for here is if the contents of the disconnected row have been changed within the DataSet since it was originally extracted from the source database.

  'iterate through all the modified rows in the table   For Each objRow in objChangeDS.Tables(0).Rows     'create the two root parts of the SQL statement   strSQL = "UPDATE BookList SET "   strWhere = " WHERE ISBN='" & objRow("ISBN", DataRowVersion.Original) & "'"     'iterate through all the columns in this row   For Each objColumn In objChangeDS.Tables(0).Columns     'see if this column has been changed since the DataSet was   'originally created by comparing Original and Current values   strColName = objColumn.ColumnName   If objRow(strColName, DataRowVersion.Current) <> _   objRow(strColName, DataRowVersion.Original) Then   ...

Matching a SQL Server DateTime Column

If the column has been changed, you need to add it to both sections of the SQL statement you're constructing. However, if the value is a DateTime , you have to format the Original value (which will be used in the WHERE clause) to match the column in the source table in the database. To perform a match against a SQL DateTime column, you have to specify the value in your disconnected row in a suitable format so that it can be compared properly.

The next part of the code extracts the original value of the PublicationDate column from the row and formats it if it is a date/time “if not it just extracts the value. Then add the column name and values to the two sections of the SQL statement. You can use the Current value in the SET clause and the Original value in the WHERE clause.

After this, you can go round and process the next column. And once all the changed columns have been processed, tidy up the SQL statement by stripping off the extra comma and space you added and assemble it into one string. Then you can display it in the page.

  ...   'have to get format of DateTime exactly right for a comparison   If objColumn.DataType.ToString() = "System.DateTime" Then   datRowDateValue = objRow(strColName, DataRowVersion.Original)   strRowValue = datRowDateValue.Format("yyyy-MM-dd\ HH:mm:ss", _   Nothing)   Else   strRowValue = objRow(strColName, DataRowVersion.Original)   End If     strSQL &= strColName & "='" _   & objRow(strColName, DataRowVersion.Current) & "', "   strWhere &= " AND " & strColName & "='" & strRowValue & "'"     End If     Next 'go to next column     'strip off extra comma and space from end of string   strSQL = Left(strSQL, Len(strSQL) 2) & strWhere     'display the SQL statement   strResults &= "* " & strSQL & " ... "   objCommand.CommandText = strSQL   ...

Executing the SQL Statement

Now you can execute this SQL statement, check the number of rows affected, and display a suitable message for this row. If the number of rows affected is less than 1 you know there was a concurrency error “the original row in the source table has changed while you were holding the disconnected copy. Then, after processing this row, go back and do the next one in your modified rows table. When all the rows have been processed, display the result in a <div> element located in the HTML of the page.

  ...   Try     intRowsAffected = objCommand.ExecuteNonQuery()   If intRowsAffected > 0 Then   strResults &= "... updated <b>" & intRowsAffected & "</b> row(s)"   Else   strResults &= "<b>Error</b>: Row was changed by another user"   End If     Catch objError As Exception     'display error details   strResults &= "Error: " & objError.Message & " " _   & objError.Source & "<br />"   End Try     Next       'repeat for next row if any     outUpdates.InnerHtml = strResults 'then display the results

Handling Concurrency Errors

Your example simply displays the errors that were encountered due to concurrent updates in the page. It doesn't provide any way to reconcile these errors. In fact, the .NET data access objects don't provide any features for this, as there is no fixed way to do it. It all depends on what your application is doing, how the updates are being carried out, and what business rules you want to apply. The next example demonstrates another approach for managing concurrency errors.

Capturing Errors with the RowUpdated Event

The previous example attempted to reduce the likelihood of concurrency errors occurring by taking over the update process and replacing it with a custom system of SQL statements. This allows updates to be monitored individually. However, this kind of process is going to produce a performance hit when compared to the Update method exposed by the DataAdapter .

You saw in an earlier section of this chapter that the DataAdapter raises two events for each row as the Update method is being executed. These events allow you to examine each row before it is pushed into the original table (the RowUpdating event) and after the update has been processed for that row (the RowUpdated event). By writing handlers for these events, you can deal with many concurrency issues.

Of course, you don't usually detect a concurrency error until you actually perform the update to a row against the original data source. You could use the RowUpdating event to fetch the data again from the database before you attempted to perform your update, and see if it had changed, but this is an inefficient approach unless you really need to actually prevent update attempts that might result in a concurrency error.

Generally, a better solution is to trap any errors that occur during the update process and report these back so that the user (or some other process) can reconcile them. The next example demonstrates how you can do this, and also introduces a couple more features of ADO.NET.

Concurrent Updates and the RowUpdated Event

The example page Managing Concurrent Updates with the RowUpdated Event ( concurrency- rowupdated.aspx ) demonstrates how we can capture information about concurrency errors while updating a data source. It handles the RowUpdated event, and creates a DataSet object containing a single table that details all the errors that occurred. This DataSet could be returned to the user, or passed to another process that will decide what to do next. In your example, you simply display the contents in the page.

So, as shown in Figure 10-18, when you open the example page you see the rowset with its original values when you fetched it from the database, and then the contents after you've made some changes to this disconnected data. We've deleted the first row (ISBN 1100000001 ), changed the title and publication date in the second row (ISBN 1100000002 ), and just the publication date in the third row (ISBN 1100000003 ).

Figure 10-18:

Next, as shown in Figure 10-19, the code in the page executes three SQL UPDATE statements directly against the database (as in the previous example) to change the values in the three rows that you have just edited in the disconnected DataSet .

Figure 10-19:

At the bottom of the page you can see a third DataGrid control. This displays the contents of the new errors table that you've dynamically created in response to errors that occurred during the update process.

In the errors table, you can see that you've got several errors, and for each one the table provides information about the type of operation that was being executed (the Action , equivalent to the statement type), the primary key of the row, the name of the column that was modified in the DataSet , and three values for this column. These are:

OriginalValue : The value when the DataSet was first filled (the value that was in the database at that time).
CurrentValue : The current value in the DataSet after the updates we made.
DatabaseValue : The value of this column at the present moment within the database (the value set by the concurrently executed SQL UPDATE statement).

If you look at the result, the first three error rows indicate that you deleted a row in the DataSet that was concurrently changed within the database. You can see that the Title column was changed while you had the DataSet open. These rows show the Original values, but of course they cannot show the Current values in the DataSet because the row was deleted.

The fourth and fifth rows in the errors table are for the second row that you modified in your DataSet . You changed the Title and PublicationDate columns in your DataSet , while the concurrent process changed the Title column “so the value for this column in the database is different from the Original value.

In the last of the error rows, the update failed because the concurrent process had changed a different column than was changed in the DataSet within that row. The database value and the Original value are the same for this column, so it must have been a different column that caused the concurrency error.

If you intend to use a page like this to extract data that will be presented to a user so that they can manually reconcile the data, you may prefer to include all the columns from rows where a concurrency error occurred in the errors table. We'll discuss this at the appropriate point as we work through the code.

The Code for the 'RowUpdated Event' Example

The majority of the code in this example is the same as in our earlier concurrency examples. One difference is that you declare some of the variables you use as being global to the page, rather than within the Page_Load event handler as you've done before. This is because you want to be able to access these variables within your RowUpdated event handler.

  <script language="vb" runat="server">   Dim gstrResult As String          'to hold the result messages   Dim gstrConnect As String         'to hold connection string   Dim gobjDataSet As DataSet        'to hold rows from database   Dim gobjErrorTable As DataTable   'to hold a list of errors   Dim gobjErrorDS As DataSet        'to hold the Errors table   Sub Page_Load()   page load event handler is here

In the Page_Load event, fill a table in the DataSet with some rows from the BookList table in your example database and display these rows in the first DataGrid control. Then change three of the rows in the DataSet in exactly the same way as you did in the previous examples, and display the rowset in the second DataGrid control.

Next create a new connection to the source database, and through it execute three SQL UPDATE statements that change three of the rows in the database. These statements are displayed in the preceding page the second DataGrid control. At this point, you are ready to push the updates back to the database. But before you do so, add your OnRowUpdated event handler to the DataAdapter so that it will be executed each time a row is updated in the original data source:

  AddHandler objDataAdapter.RowUpdated, _   New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)

Creating and Displaying the 'Errors' DataSet

Before you start the update process, you need to create the new DataSet that will contain details of errors that occur during the process. Create a DataTable object named Errors , and define the columns for this table (you saw how this works in the previous chapter). Then you can create a new DataSet object and add the table you've just defined to it (notice that all these objects are referenced by global variables that you declared outside the Page_Load event handler so that you can access them from other event handlers).

Now you can carry on as in other examples by creating the auto-generated commands for the update and executing them by calling the DataAdapter object's Update method (not shown in this code). Once the update is complete, display the contents of your errors DataSet in the third DataGrid control at the bottom of the page.

  'create a new empty Table object to hold error rows   gobjErrorTable = New DataTable("Errors")     'define the columns for the Errors table   gobjErrorTable.Columns.Add("Action", System.Type.GetType("System.String"))   gobjErrorTable.Columns.Add("RowKey", System.Type.GetType("System.String"))   gobjErrorTable.Columns.Add("ColumnName", System.Type.GetType("System.String"))   gobjErrorTable.Columns.Add("OriginalValue", _   System.Type.GetType("System.String"))   gobjErrorTable.Columns.Add("CurrentValue", _   System.Type.GetType("System.String"))   gobjErrorTable.Columns.Add("DatabaseValue", _   System.Type.GetType("System.String"))     'create a new empty DataSet object to hold Errors table   gobjErrorDS = New DataSet()   gobjErrorDS.Tables.Add(gobjErrorTable)     ... execute Update method here to push changes into database ...     'display the contents of the Errors table   dgrResult3.DataSource = gobjErrorDS   dgrResult3.DataMember = "Errors"   dgrResult3.DataBind() 'and bind (display) the data

In this case, you've bound the DataGrid to the DataSet object itself (using the DataSource property) and then specified that it should display the contents of the Errors table within that DataSet by setting the DataMember property.

Getting the Current Value from the Database Table

Of course, the code shown so far won't actually put any rows into the errors DataSet . These rows are created within the RowUpdated event handler whenever a concurrency error is detected. We know that we want to include in each row the current value of the column in the original database table at the point that the update process was executed “it will be different from the Original value of that column in the DataSet if that column in the row was changed by a concurrent process.

So, we have written a short function within the page that “ given a connection string, primary key (ISBN) value, and a column name “will return the value of that column for that row from the source database. The function is named GetCurrentColumnValue :

  Function GetCurrentColumnValue(strConnect As String, strISBN As String, _   strColumnName As String) As String   'select existing column value from underlying table in the database   Dim strSQL = "SELECT " & strColumnName _   & " FROM BookList WHERE ISBN='" & strISBN & "'"   Dim objConnect As New OleDbConnection(strConnect)   Dim objCommand As New OleDbCommand(strSQL, objConnect)   Try   objConnect.Open()   'use ExecuteScalar for efficiency, it returns only one item   'get the value direct from it and convert to a String   GetCurrentColumnValue = objCommand.ExecuteScalar().ToString()   objConnect.Close()   Catch objError As Exception   GetCurrentColumnValue = "*Error*"   End Try   End Function

One interesting point is that we use the ExecuteScalar method of the Command object to get the value. The ExecuteScalar method returns just a single value from a query (rather than a rowset, for which we'd have to use a DataReader object).

So, it is extremely efficient when compared to a DataReader , where we have to call the Read method to load the first row of results, and then access the column by name or ordinal index.

Note

The ExecuteScalar method is especially appropriate for queries that calculate a value, such as summing values or working out the average value in a column for some or all of the rows. In our case, it's useful because our SQL statement also only returns a single value (sometimes referred to as a singleton).

So, this simple function will return the value of a specified column in a specified row, or the string value " *Error* " if it can't access it (for example if it has been deleted).

The OnRowUpdated Event Handler

Finally, the page contains the OnRowUpdated event handler itself. Remember that this is called after each row has been updated in the source database, whether or not there was an error. So, the first thing we do is check the RecordsAffected field of the RowUpdatedEventArgs object to see if the update for this row failed. If it did, we need to add details of the error to our errors DataSet .

We want to know what type of update this is, so we extract the StatementType as a String using the enumeration's GetName method, and store this in a local variable for use later. We also need to extract the value of the ISBN column from the row, as this is the primary key we'll need for locating the row later. Being the primary key means that it should not be possible for the user to change the value in that row of the DataSet , which would mean that we could just access the Original value.

However, if they were allowed to change the value, we would have to use the Current value of that column to extract the updated value. But if this row has been deleted in the DataSet , there will be no Current value. In our example, to demonstrate the technique, we first check to see if it is a deleted row by examining the RowState property, and then extract the value from the appropriate version of the column.

  'event handler for the RowUpdated event   Sub OnRowUpdated(objSender As Object, objArgs As OleDbRowUpdatedEventArgs)   'see if the update failed   If objArgs.RecordsAffected < 1 Then   'get the text description of the StatementType   Dim strType = System.Enum.GetName(objArgs.StatementType.GetType(), _   objArgs.StatementType)   'get the primary key of the row (the ISBN). Must look at Original   'version in deleted rows because they have no Current version   Dim strRowKey As String   If objArgs.Row.RowState = DataRowState.Deleted Then   strRowKey = objArgs.Row("ISBN", DataRowVersion.Original)   Else   strRowKey = objArgs.Row("ISBN", DataRowVersion.Current)   End If   ...

Handling the Columns for Deleted Rows

Now we can check which column(s) caused the concurrency error to occur, or simply collect all the column values if the concurrency error occurred for a row that was deleted in the DataSet . We start by getting a reference to the table in our original DataSet , the one we filled with rows from the database, and we declare a couple of other variables that we'll need as well.

Then we iterate through the Columns collection of this table, extracting the column name and again checking whether this is a deleted row. If it is, we want to add the values from this column to our error database. We create a new DataRow based on the Errors table, and then we can start filling in the values:

We saved the values for the first three columns of our Errors table (the statement type, row key and column name) as strings earlier on in our event handler. The next value comes from the row referenced by the RowUpdatedEventArgs object that is passed to our event handler. Because it is a deleted row, we can only access the Original value, and we set the CurrentValue column of our Errors table to " [NULL] ".

The final value comes from the custom GetCurrentColumnValue function we described earlier, and contains the current value of this column in this row within the source database. And after we've filled in all the values for the new row we add it to the Errors table.

  ...   'get a reference to the original table in the DataSet   Dim objTable As DataTable = gobjDataSet.Tables(0)     Dim objColumn As DataColumn 'to hold a DataColumn object   Dim strColumnName As String 'to hold the column name     'iterate through the columns in the current row   For Each objColumn In objTable.Columns     'get the column name as a string   strColumnName = objColumn.ColumnName     'if this is a deleted row, insert all the original columns values   If objArgs.Row.RowState = DataRowState.Deleted Then     'create a new DataRow object instance in this table   Dim objDataRow As DataRow = gobjErrorTable.NewRow()     'and fill in the values   objDataRow("Action") = strType   objDataRow("RowKey") = strRowKey   objDataRow("ColumnName") = strColumnName   objDataRow("OriginalValue") _   = objArgs.Row(strColumnName, DataRowVersion.Original)   objDataRow("CurrentValue") = "[NULL]"   objDataRow("DatabaseValue") _   = GetCurrentColumnValue(gstrConnect, strRowKey, strColumnName)     'add new row to the Errors table   gobjErrorTable.Rows.Add(objDataRow)   Else   ...

Handling the Columns for Modified Rows

The preceding code handles the case of a deleted row, but for a modified row the Else section of the If..Then construct we started will be executed. This section of the event handler code is shown in the following code. The first step here is to compare the Current and Original values of the row. If they are different, we know that this column in the current row has been modified within the DataSet since it was filled from the database table.

Notice that this is why we only get the modified columns in our errors table. If the concurrent process changes a different column to the one(s) that are modified in the DataSet , a row will not appear in the Errors table. We could simply remove this If..Then construct, which will cause all the values from all the columns in a row that caused a concurrency error to be included in the table. However, in that case we would probably also want to change the way we extract the current values from the database, as using a separate function call for each column would certainly not be the most efficient technique.

Returning to the code in our example, since we know that this row caused a concurrency error and that this column has been changed since the DataSet was filled, we add details about the values in this column to our table named Errors within the new errors DataSet we created earlier as we did for a deleted row, though this time using the Current value of the column for the CurrentValue column in the Errors table. And, as before, after filling in the row values we add it to the Errors table.

The Next statement at the end means we can go back, look at the next column and repeat the process.

  ...   Else   'see if this column has been modified   If objArgs.Row(strColumnName, DataRowVersion.Current) _   <> objArgs.Row(strColumnName, DataRowVersion.Original) Then   'create a new DataRow object instance in this table   Dim objDataRow As DataRow = gobjErrorTable.NewRow()   'and fill in the values   objDataRow("Action") = strType   objDataRow("RowKey") = strRowKey   objDataRow("ColumnName") = strColumnName   objDataRow("OriginalValue") _   = objArgs.Row(strColumnName, DataRowVersion.Original)   objDataRow("CurrentValue") _   = objArgs.Row(strColumnName, DataRowVersion.Current)   objDataRow("DatabaseValue") _   = GetCurrentColumnValue(gstrConnect, strRowKey, strColumnName)   'add new row to the Errors table   gobjErrorTable.Rows.Add(objDataRow)   End If   End If   Next 'go to next column

So that you can see the result and better understand what the code does, we've repeated the relevant section of the screenshot of this example page again in Figure 10-20:

Figure 10-20:

Handling the Columns for Inserted Rows

Notice that we aren't specifically handling errors for new (inserted) rows in our example. In general, a concurrency error can only occur in this situation if the page allocates an existing primary key to the new row, or perhaps because a parent row was deleted when we are trying to add a related row to a child table. However, it's not hard to add the code to handle insert errors as well. The StatementType property of the RowUpdatedEventArgs that is passed to the event handler will be StatementType.Insert in this case, and the rows will have DataRowState.Added as their RowState property.

The one point to be aware of is that there will be no Original value in the row in the DataSet , in the same way as there is no Current value for a deleted row (hence our example code would fail when trying to access the Current value). And, of course, in this case the GetCurrentColumnValue function will return the value of the column inserted by another user, or an error value if the insert fails for some other reason.

Returning an UpdateStatus Value

The other important point when using the RowUpdating and RowUpdated event handlers that we haven't mentioned so far is how we manage the status value that is exposed by the Status field of the RowUpdatingEventArgs and RowUpdatedEventArgs objects. In our earlier example of using the RowUpdating and RowUpdated events ( rowupdated-event.aspx ) we just ignored these values, but that was really only acceptable because we didn't get any concurrency errors during the update process.

When the DataAdapter object's Update method is executing, each call to the RowUpdating and RowUpdated event handler includes a status flag value. We can set this to a specific value from the UpdateStatus enumeration to tell the Update method what to do next, as shown in the following table:

Continue	Default. The DataAdapter will continue to process rows (including the current one if this is a RowUpdating event) as part of the Update method call.
ErrorsOccurred	The DataAdapter will stop processing rows and treat the RowUpdating or RowUpdated event as raising an error.
SkipAllRemainingRows	The DataAdapter will stop processing rows and end the Update method, but it will not treat the RowUpdating or RowUpdated event as an error.
SkipCurrentRow	The DataAdapter will not process this row (if this is a RowUpdating event), but will continue to process all remaining rows as part of the Update method call.

Because the default is Continue , the Update process will actually stop executing and report a runtime error when the first concurrency error occurs if we just ignore this status flag. So, as we're handling the concurrency errors ourselves , we must set the value to UpdateStatus.SkipCurrentRow so that the concurrency error doesn't cause the Update process to be terminated . This is the last step in our event handler, as shown in the code:

  ...   'set the Status property of the row to skip current row update   'if there is an error. Default is Continue, which means an error   'will halt execution for this failed update and not process the   'remaining updated or deleted rows   objArgs.Status = UpdateStatus.SkipCurrentRow   End If   End Sub

In this example, you've seen how we can capture information on concurrency errors, allowing the user or another process to take a reasoned decision on how to reconcile the values. Because we've placed the information in a disconnected DataSet object, it could easily be remoted to a client via HTTP or a Web Service, or passed directly to another tier of the application.

Locating Errors After an Update Is Complete

There is one final approach to managing concurrent updates that we can take advantage of in ADO.NET when using the Update method of the DataAdapter object. Instead of reacting to each RowUpdated event, we can force the DataAdapter to continue processing the updates for each row even if it encounters an error (rather than terminating the Update process when the first concurrency or other error occurs).

All we need to do is set the ContinueUpdateOnError property of the DataAdapter object that is performing the Update to True . Then, whenever an error is encountered, the DataAdapter will simply insert the error message that it receives into the RowError property of the relevant row within the DataSet , and continue with the next updated row.

The RowError property is a String value. You saw how to use this in the example from the previous chapter where we used the RowUpdated event of the DataTable object (rather than the event of the same name exposed by the DataAdapter object that we've been using in this chapter).

So, if we can wait until after the Update process has finished to review and fix errors, we have an easier option for managing concurrency errors. The process is:

Once the appropriate DataAdapter is created and ready to perform the Update , set the ContinueUpdateOnError property of the DataAdapter object to True .
Call the Update method of the DataAdapter object to push the changes into the data source.
After the Update process completes, check the HasErrors property of the DataSet object to see if any of the rows contain an error (e.g. have a non-empty value for their RowError property).
If there are (one or more) errors, check the HasErrors property of each DataTable object in the DataSet to see which ones contain errors
Iterate through the rows of each DataTable that does contain errors, checking the RowError property of each row “or use the GetErrors method of the DataTable to get an array of the rows with errors in them
Display or feed back to the user the error details and column values so that they can retry the updates as required.

Using the ContinueUpdateOnError Property

We've provided an example that carries out this series of steps while attempting to update a data source. The page Locating Concurrency Errors After Updating the Source Data (concurrency-continue.aspx ) shown in Figure 10-21 displays the rows that it extracts from our sample database, edits two of the rows (the first and second ones), then displays the rowset again to show the changes.

Figure 10-21:

After that, the same process as we used in earlier examples changes the same two rows in the source database using a separate connection, while we are holding a disconnected copy of the data in our DataSet . Then, as you can see at the bottom of the page, it displays the errors found in the DataSet after the update process has completed. It shows the error message (the value of the RowError property for that row), and the original, current, and underlying (database) values for the row.

The Code for the ContinueUpdateOnError Example

Most of the code we use in this example is identical to the previous example. The only real differences are in the preparation for the Update process, and in the way that we extract and display the row values afterwards. We don't set up any event handlers of course, because we're not going to be reacting to the RowUpdated event in this case. However, as shown in the code, at the point where we're ready to call the Update method of the DataAdapter , we set the DataAdapter object's ContinueUpdateOnError property to True .

  ...   'prevent exceptions being thrown due to concurrency errors   objDataAdapter.ContinueUpdateOnError = True     'perform the update on the original data   objDataAdapter.Update(objDataSet, "Books")   ...

Checking for Row Errors

After the Update process has finished, we must check for row errors. The process is the same as we used in the previous chapter when we were looking at the RowError property in general, and as we described in the introduction to the current example. The complete code for this part of the process is shown in the following code:

  'see if there are any update errors anywhere in the DataSet   If objDataSet.HasErrors Then     Dim objThisRow As DataRow   Dim intIndex As Integer     'check each table for errors in that table   Dim objThisTable As DataTable   For Each objThisTable In objDataSet.Tables     If objThisTable.HasErrors Then     strResult &= "One or more errors found in table '<b>" _   & objThisTable.TableName & "</b>:'<p />"   'get collection containing only rows with errors   'using the GetErrors method of the DataTable object   'check each row in this table for errors   For Each objThisRow In objThisTable.GetErrors()     'display the error details and column values   strResult &= "* Row with ISBN=<b>" _   & objThisRow("ISBN") _   & "</b> has error <b>" _   & objThisRow.RowError & "</b><br />" _   & "Original Values: "     'iterate through row collecting original and current values   For intIndex = 0 To objThisTable.Columns.Count - 1   strResult &= objThisRow(intIndex, DataRowVersion.Original) & ", "   Next   strResult = Left(strResult, Len(strResult) - 2)   strResult &= "<br />Current Values: "   For intIndex = 0 To objThisTable.Columns.Count - 1   strResult &= objThisRow(intIndex, DataRowVersion.Current) & ", "   Next   strResult = Left(strResult, Len(strResult) - 2)   'use function declared later in page to get underlying values   strResult &= "<br />Underlying (database) Values: " _   & GetUnderlyingValues(strConnect, objThisRow("ISBN")) _   & "<p />"   Next     End If     Next 'table     End If     'display the results of the Update in <div> elsewhere on page   outResult.InnerHtml = strResult

Extracting the Underlying Row Values

The only other new code in this page is the GetUnderlyingValues function that extracts the underlying database values, so that they can be displayed along with the Original and Current values from the row in the DataSet . As shown in the following code, It works in much the same way as the GetCurrentColumnValue function we used in the previous example, but this time we want all the row values. So we use a DataReader to read the row and then return a String that we build up containing all the column values.

  Function GetUnderlyingValues(strConnect As String, strRowKey As String) _   As String   'select existing column values from underlying table in database   Dim strSQL = "SELECT * FROM BookList WHERE ISBN='" & strRowKey & "'"     'create connection and command to access database   Dim objConnect As New OleDbConnection(strConnect)   Dim objCommand As New OleDbCommand(strSQL, objConnect)     'declare the variables we'll need   Dim objReader As OleDbDataReader   Dim strValues As String = ""   Dim intIndex As Integer     Try     'get a DataReader containing the specified row data   objConnect.Open()   objReader = objCommand.ExecuteReader()     'put values from row into a string to return   If objReader.Read() Then   For intIndex = 0 To objReader.FieldCount - 1   strValues &= objReader.GetValue(intIndex) & ", "   Next   End If     'close connection and return result   objConnect.Close()   GetUnderlyingValues = Left(strValues, Len(strValues) - 2)     Catch objError As Exception     GetUnderlyingValues = "*Error*"     End Try     End Function