Professional ASP.NET 1.1 [Electronic resources] نسخه متنی

Managing Concurrent Data Updates

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.

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.

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:

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