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.
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.
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 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
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.
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.
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.
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.
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)
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 .
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
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
...
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
...
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
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.
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.
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 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()
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)
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"
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.
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).
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
...
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
...
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:
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.
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.
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.
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.
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")
...
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
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