Multiuser Updates

In the previous section, you read data from the database into a DataSet, updated the data in the DataSet, and then wrote the changes back to the database. In a real-world application, it would be possible for other people to read the same data into DataSets of their own, edit their data, and write their changes back to the database.

You can easily imagine that this possibility could possibly cause tremendous data corruption. Imagine, for example, that a quality assurance person downloads the current open bugs with an eye toward updating some of the information. Meanwhile, across the office (or across town) a developer has downloaded and is reviewing a few open bugs. Both of them are reading bug 17, which looks like this:

BugID 17
Reporter: John Galt
Severity: High
Status: Assigned
Owner: Jesse Liberty

The QA person decides to change the severity to Medium and reassign the bug to Dan Hurwitz. Meanwhile, the developer is updating the DataSet to change the action taken on the bug. The QA person writes back the changed DataSet, and the database now thinks the Owner is Dan and the Severity is Medium. The record now appears as follows:

BugID 17
Reporter: John Galt
Severity: Medium
Status: Assigned
Owner: Dan Hurwitz

Then the developer writes back his DataSet, in which the Owner was Jesse and the Severity was High. These earlier values are written over the values updated by QA, and the QA edits are lost. The technical term for this is bad.

To prevent this kind of problem, use any of the following strategies:

1. Lock the records. When one user works with a record, other users can read the records but cannot update them.
2. Update only the columns you change. In the previous example, QA would have changed only the owner and the status, while the developer would have changed only the description.
3. Preview whether the database has changed before you make your updates. If so, notify the user.
4. Attempt the change and handle the error, if any.

The following sections explore each of these possible strategies.

20.4.1 Lock the Records

Many databases provide pessimistic record locking. When a user opens a record, it is locked, and no other user may write to that record. For database efficiency, most databases also implement pessimistic page locking; not only is the particular record locked, but many surrounding records are locked as well.

While record and page locking is not uncommon in some database environments, it is generally undesirable. It's possible for a record to be locked, and the user never to return to the database to unlock it (if the user goes to lunch or her computer crashes). In that case, you would need to write monitoring processes that keep track of how long records have been locked, and unlock records after a time-out period.

As you saw in the previous example, a single query may touch many records in many tables. If you were to lock all those records for each user, it wouldn't take long before the entire database was locked. In addition, it often isn't necessary. While each user may look at dozens of records, each user usually updates only a very few. Locking is a very big, blunt weapon; what is needed is a small, delicate surgical tool.

20.4.2 Compare Original Against New

To understand how to compare the DataSet against the database, consider three possible values for each field:

• The value currently in the database
• The value that was in the database when you first filled the DataSet
• The value that is now in the DataSet because you have changed it

The DataSet provides support for this approach even though it is not an efficient way to manage data updates. This approach involves creating an event handler for the RowUpdating event. The event handler examines the original value of each field and queries the database for the value currently in the database. If these values are different, then someone has changed the database since the DataSet was filled, and you can take corrective action.

You will find two significant problems with this approach. First, you must query the database for the current values before each update. Second, there is no guarantee that you have solved the problem. It is certainly possible that someone will update a record after you have queried the database, but before you write back your changes. In any case, this approach is inefficient and won't be demonstrated here.

20.4.3 Handle the Errors

Odd as it may seem at first, the best approach to managing concurrency is to try the update, and then respond to errors as they arise. For this approach to be effective, however, you must craft your update statement so it will be guaranteed to fail if someone else updates the records.

This approach is very efficient. In most cases, your update will succeed, and you will not have bothered with extra reads of the database. If your update succeeds, there is no lag between checking the data and the update, so there is no chance of someone sneaking in another write. Finally, if your update fails, you know why, and you can take corrective action.

For this approach to work, your stored procedure for updates must fail if the data has changed in the database since the time you retrieved the DataSet. Since the DataSet can tell you the original values it received from the database, you can pass those values back into the stored procedure as parameters, and then add them to the Where clause in your update statement, as shown in the spUpdateBugFromDataSetWithConcurrency stored procedure listed in Example 20-14.

Example 20-14. Updating with concurrency

CREATE PROCEDURE spUpdateBugFromDataSetWithConcurrency
@ProductID int,
@OldProductID int,
@Description varChar(8000),
@OldDescription varChar(8000),
@Response varChar(8000),
@OldResponse varChar(8000),
@Reporter int,
@OldReporter int,
@Owner int,
@OldOwner int,
@Status int,
@OldStatus int,
@Severity int,
@OldSeverity int,
@bugID int,
@BugHistoryID int
as
Begin transaction
 Update Bugs 
 set 
 Product = @productID,
 [Description] = @Description,
 Reporter = @Reporter
 where bugID = @BugID and Product = @OldProductID
 and [Description] = @OldDescription 
 and Reporter = @OldReporter
if @@Error <> 0 goto ErrorHandler
if @@RowCount > 0 
begin
 
 Update BugHistory 
 Set
 status = @Status, 
 severity = @Severity, 
 response = @Response, 
 owner = @Owner
 where BugHistoryID = @bugHistoryID and bugID = @bugID 
 and status = @oldStatus and severity = @OldSeverity 
  and response = @oldResponse and owner = @OldOwner
end
if @@Error <> 0 goto ErrorHandler
 commit transaction
 return
ErrorHandler:
 rollBack transaction
 return

When you update the record, the original values will now be checked against the values in the database. If they have changed, no records will match, and you will not update any records. After you attempt to update the BugsTable, check the @@RowCount to see if any rows were successfully added. If so, add these lines to the BugHistory table:

if @@RowCount > 0 
begin
 
Update BugHistory

The result of this test of @@RowCount is that if no records are added to the Bugs table, then no records will be added to the BugHistory table.

The transaction tests for errors. If no rows match, there is no error and the transaction will continue. You must make sure that at least one row was added to Bugs before updating the BugHistory.

You can test for how many rows were added altogether in the RowUpdated event handler. If no row was updated, you can assume that it was because the original row was changed, and you can take appropriate corrective action.

It is possible for the update to Bugs to work, yet the update to BugHistory fails. The program will return one updated record. For simplicity, this example does not handle that permutation. A well-crafted update statement could catch this problem, but at the cost of making the code more difficult to understand.

The complete listing is shown in Example 20-15 for C# and Example 20-16 for VB.NET. A detailed analysis follows the listing.

Example 20-15. Updating the DataSet with concurrency (C#)

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
 
namespace UpdatingDataSetsWithConcurrencyCS
{
 public class Form1 : System.Windows.Forms.Form
 {
 private System.Windows.Forms.DataGrid dgBugs;
 private System.Windows.Forms.Button btnUpdateDS;
 private System.Windows.Forms.Button btnRefreshDS;
 private System.Windows.Forms.Button btnUpdateDB;
 private DataSet bugDS;
 
 private System.ComponentModel.Container components = null;
 
 public Form1( )
 {
 InitializeComponent( );
 RefreshDataSet( );
 }
 
 protected override void Dispose( bool disposing )
 {
 if( disposing )
 {
 if (components != null) 
 {
 components.Dispose( );
 }
 }
 base.Dispose( disposing );
 }
 
 #region Windows Form Designer generated code
 #endregion
 
 [STAThread]
 static void Main( ) 
 {
 Application.Run(new Form1( ));
 }
 
 private DataSet CreateBugDataSet( )
 {
 // connection string to connect to the Bugs Database
 string connectionString = 
 "server=YourServer; uid=sa; pwd=YourPW; "+
 "database=WindForm_Bugs";
 
 // Create connection object, initialize with 
 // connection string. Open it.
 System.Data.SqlClient.SqlConnection connection = 
 new System.Data.SqlClient.SqlConnection(connectionString);
 connection.Open( );
 
 
 // Create a SqlCommand object and assign the connection
 System.Data.SqlClient.SqlCommand command = 
 new System.Data.SqlClient.SqlCommand( );
 command.Connection=connection;
 command.CommandText="spBugsWithIDs";
 command.CommandType=CommandType.StoredProcedure;
 
 // create a data adapter and assign the command object
 // and add the table mapping for bugs
 SqlDataAdapter dataAdapter = new SqlDataAdapter( );
 dataAdapter.SelectCommand=command;
 dataAdapter.TableMappings.Add("Table","BugInfo");
 
 // Create the data set and use the data adapter to fill it
 DataSet dataSet = new DataSet( );
 dataAdapter.Fill(dataSet);
 return dataSet;
 }
 
 private void btnUpdateDS_Click(object sender, System.EventArgs e)
 {
 DataTable bugTable = bugDS.Tables["BugInfo"];
 bugTable.Rows[0]["Response"] = "This is a test";
 bugTable.Rows[1].Delete( );
 DataRow newRow = bugTable.NewRow( );
 newRow["BugHistoryID"] = 1;
 newRow["Description"] = "New bug test";
 newRow["Response"] = "Created new bug";
 newRow["Owner"] = "Jesse Liberty";
 newRow["OwnerID"] = 1;
 newRow["ProductID"] = 2; 
 newRow["ProductDescription"] = "PIM - My Personal Infomation Manager";
 newRow["Version"] = "0.01";
 newRow["ReporterID"] = 3;
 newRow["Reporter"] = "John Galt";
 newRow["StatusID"] = 1;
 newRow["StatusDescription"] = "open";
 newRow["SeverityID"] = 2;
 newRow["SeverityDescription"] = "High";
 newRow["DateStamp"] = "07-27-2005";
 bugTable.Rows.Add(newRow);
 
 }
 
 private void btnRefreshDS_Click(object sender, System.EventArgs e)
 {
 RefreshDataSet( );
 }
 
 private void RefreshDataSet( )
 {
 bugDS = CreateBugDataSet( );
 dgBugs.DataSource = bugDS.Tables[0];
 }
 
 private void btnUpdateDB_Click(object sender, System.EventArgs e)
 {
 SqlDataAdapter dataAdapter = new SqlDataAdapter( );
 
 string connectionString = 
 "server=YourServer; uid=sa; pwd=YourPW; "+
 "database=WindForm_Bugs";
 
 
 // mimic another user writing to your data after
 // you have retrieved the data from the database
 System.Data.SqlClient.SqlConnection connection2 = 
 new System.Data.SqlClient.SqlConnection(connectionString);
 connection2.Open( );
 string cmd = "Update Bugs set Product = 2 where BugID = 1";
 SqlCommand cmd1 = new SqlCommand(cmd,connection2);
 cmd1.ExecuteNonQuery( );
 
 
 // Create connection object, initialize with 
 // connection string. Open it.
 System.Data.SqlClient.SqlConnection connection = 
 new System.Data.SqlClient.SqlConnection(connectionString);
 
 SqlTransaction transaction;
 connection.Open( );
 transaction = connection.BeginTransaction( );
 
 // *** create the update command object
 SqlCommand updateCmd = new SqlCommand(
 "spUpdateBugFromDataSetWithConcurrency",connection);
 updateCmd.CommandType=CommandType.StoredProcedure;
 
 // declare the parameter object
 System.Data.SqlClient.SqlParameter param;
 
 // Add new parameters, get back a reference 
 // set the parameters' direction and value
 param = 
 updateCmd.Parameters.Add("@ProductID",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="ProductID";
 param.SourceVersion=DataRowVersion.Current;
 
 // pass in the original value for the where statement
 param = 
 updateCmd.Parameters.Add("@OldProductID",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="ProductID";
 param.SourceVersion=DataRowVersion.Original;
 
 param = 
 updateCmd.Parameters.Add("@Description",SqlDbType.Text,8000); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="Description";
 param.SourceVersion=DataRowVersion.Current;
 
 param = 
 updateCmd.Parameters.Add("@OldDescription",SqlDbType.Text,8000); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="Description";
 param.SourceVersion=DataRowVersion.Original;
 
 param = 
 updateCmd.Parameters.Add("@Response",SqlDbType.Text,8000); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="Response";
 param.SourceVersion=DataRowVersion.Current;
 
 param = 
 updateCmd.Parameters.Add("@OldResponse",SqlDbType.Text,8000); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="Response";
 param.SourceVersion=DataRowVersion.Original;
 
 param = 
 updateCmd.Parameters.Add("@Reporter",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="ReporterID";
 param.SourceVersion=DataRowVersion.Current;
 
 param = 
 updateCmd.Parameters.Add("@OldReporter",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="ReporterID";
 param.SourceVersion=DataRowVersion.Original;
 
 param = 
 updateCmd.Parameters.Add("@Owner",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="OwnerID";
 param.SourceVersion=DataRowVersion.Current;
 
 param = 
 updateCmd.Parameters.Add("@OldOwner",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="OwnerID";
 param.SourceVersion=DataRowVersion.Original;
 
 param = 
 updateCmd.Parameters.Add("@Status",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="StatusID";
 param.SourceVersion=DataRowVersion.Current;
 
 param = 
 updateCmd.Parameters.Add("@OldStatus",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="StatusID";
 param.SourceVersion=DataRowVersion.Original;
 
 param = 
 updateCmd.Parameters.Add("@Severity",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="SeverityID";
 param.SourceVersion=DataRowVersion.Current;
 
 param = 
 updateCmd.Parameters.Add("@OldSeverity",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="SeverityID";
 param.SourceVersion=DataRowVersion.Original;
 
 param = 
 updateCmd.Parameters.Add("@bugID",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="bugID";
 param.SourceVersion=DataRowVersion.Original; // note Original
 
 param = 
 updateCmd.Parameters.Add("@BugHistoryID",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="BugHistoryID";
 param.SourceVersion=DataRowVersion.Original; // note Original
 
 dataAdapter.UpdateCommand=updateCmd;
 
 // *** the delete command
 SqlCommand deleteCmd = 
 new SqlCommand("spDeleteBugFromDataSet",connection);
 deleteCmd.CommandType=CommandType.StoredProcedure;
 
 param = deleteCmd.Parameters.Add("@bugID",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="bugID";
 param.SourceVersion=DataRowVersion.Original; // note Original
 
 param = deleteCmd.Parameters.Add("@BugHistoryID",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="BugHistoryID";
 param.SourceVersion=DataRowVersion.Original; // note Original
 
 dataAdapter.DeleteCommand=deleteCmd;
 
 // *** insert command
 SqlCommand insertCmd = 
 new SqlCommand("spInsertBugFromDataSet",connection);
 insertCmd.CommandType=CommandType.StoredProcedure;
 
 // Add new parameters, get back a reference 
 // set the parameters' direction and value
 param = insertCmd.Parameters.Add("@ProductID",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="ProductID";
 param.SourceVersion=DataRowVersion.Current;
 
 param = 
 insertCmd.Parameters.Add("@Version",SqlDbType.Text,50); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="Version";
 param.SourceVersion=DataRowVersion.Current;
 
 param = 
 insertCmd.Parameters.Add("@Description",SqlDbType.Text,8000); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="Description";
 param.SourceVersion=DataRowVersion.Current;
 
 param = 
 insertCmd.Parameters.Add("@Response",SqlDbType.Text,8000); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="Response";
 param.SourceVersion=DataRowVersion.Current;
 
 param = insertCmd.Parameters.Add("@Reporter",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="ReporterID";
 param.SourceVersion=DataRowVersion.Current;
 
 param = insertCmd.Parameters.Add("@Owner",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="OwnerID";
 param.SourceVersion=DataRowVersion.Current;
 
 param = insertCmd.Parameters.Add("@Status",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="StatusID";
 param.SourceVersion=DataRowVersion.Current;
 
 param = insertCmd.Parameters.Add("@Severity",SqlDbType.Int); 
 param.Direction = ParameterDirection.Input;
 param.SourceColumn="SeverityID";
 param.SourceVersion=DataRowVersion.Current;
 
 dataAdapter.InsertCommand=insertCmd;
 
 // add transaction support for each command
 dataAdapter.UpdateCommand.Transaction = transaction;
 dataAdapter.DeleteCommand.Transaction = transaction;
 dataAdapter.InsertCommand.Transaction = transaction;
 
 // try to update, if all succeed commit
 // otherwise roll back
 try
 {
 dataAdapter.RowUpdated += 
 new SqlRowUpdatedEventHandler(OnRowUpdate);
 int rowsUpdated = dataAdapter.Update(bugDS,"BugInfo");
 transaction.Commit( );
 MessageBox.Show(rowsUpdated.ToString( ) + " rows Updated.");
 RefreshDataSet( );
 }
 catch
 {
 transaction.Rollback( );
 }
 
 // rebind the grid to show the results
 // grid should be unchanged
 dgBugs.DataSource = bugDS.Tables["BugInfo"]; 
 }
 // handle the Row Updated event
 public void OnRowUpdate(object sender, SqlRowUpdatedEventArgs e)
 {
 // get the type of update (update, insert, delete)
 // as a string
 string s = "Attempted " + 
 System.Enum.GetName(
 e.StatementType.GetType( ),e.StatementType) + ". ";
 
 // if the update failed
 if (e.RecordsAffected < 1)
 {
 MessageBox.Show(s + "Concurrency error! Unable to update BugID: " + 
 e.Row["BugID",DataRowVersion.Original].ToString( ));
 
 // skip over this row, continue with the next
 e.Status = UpdateStatus.SkipCurrentRow;
 }
 else // the update succeeded
 {
 MessageBox.Show(s + " Row updated, BugID: " + 
 e.Row["BugID",DataRowVersion.Original].ToString( ));
 }
 } // close OnRowUpdate
 }
}

Example 20-16. Updating DataSet with concurrency (VB.NET)

Imports System.Data.SqlClient
Public Class Form1
 Inherits System.Windows.Forms.Form
 
 Dim bugDS As DataSet
 
#Region " Windows Form Designer generated code "
 
 Public Sub New( )
 MyBase.New( )
 
 'This call is required by the Windows Form Designer.
 InitializeComponent( )
 RefreshDataSet( )
 
#End Region
 
 Private Function CreateBugDataSet( ) As DataSet
 ' myConnection string to connect to the Bugs Database
 Dim connectionString As String = _
 "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs"
 
 ' Create myConnection object, initialize with 
 ' myConnection string. Open it.
 Dim myConnection As New System.Data.SqlClient.SqlConnection(connectionString)
 myConnection.Open( )
 
 
 ' Create a SqlCommand object and assign the myConnection
 Dim command As New System.Data.SqlClient.SqlCommand( )
 command.Connection = myConnection
 command.CommandText = "spBugsWithIDs"
 command.CommandType = CommandType.StoredProcedure
 
 ' create a data adapter and assign the command object
 ' and add the table mapping for bugs
 Dim myDataAdapter As New SqlDataAdapter( )
 myDataAdapter.SelectCommand = command
 myDataAdapter.TableMappings.Add("Table", "BugInfo")
 
 ' Create the data set and use the data adapter to fill it
 Dim myDataSet As New DataSet( )
 myDataAdapter.Fill(myDataSet)
 Return myDataSet
 End Function ' close CreateBugDataSet
 
 Private Sub RefreshDataSet( )
 bugDS = CreateBugDataSet( )
 dgBugs.DataSource = bugDS.Tables(0)
 End Sub
 
 ' Update the dataset with bogus data
 Private Sub btnUpdateDS_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles btnUpdateDS.Click
 Dim bugTable As DataTable = bugDS.Tables("BugInfo")
 bugTable.Rows(0)("Response") = "This is a test"
 bugTable.Rows(1).Delete( )
 
 Dim newRow As DataRow = bugTable.NewRow( )
 newRow("BugHistoryID") = 1
 newRow("Description") = "New bug test"
 newRow("Response") = "Created new bug"
 newRow("Owner") = "Jesse Liberty"
 newRow("OwnerID") = 1
 newRow("ProductID") = 2
 newRow("ProductDescription") = "PIM - My Personal Infomation Manager"
 newRow("Version") = "0.01"
 newRow("ReporterID") = 3
 newRow("Reporter") = "John Galt"
 newRow("StatusID") = 1
 newRow("StatusDescription") = "open"
 newRow("SeverityID") = 2
 newRow("SeverityDescription") = "High"
 newRow("DateStamp") = "07-27-2005"
 bugTable.Rows.Add(newRow)
 
 End Sub ' close btnUpdateDS_Click
 
 Private Sub btnRefreshDS_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles btnRefreshDS.Click
 RefreshDataSet( )
 End Sub
 
 Private Sub btnUpdateDB_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) _
 Handles btnUpdateDB.Click
 Dim myDataAdapter As New SqlDataAdapter( )
 
 Dim connectionString As String = "server=YourServer; uid=sa; 
pwd=YourPW; database=WindForm_Bugs"
 
 ' Create myConnection object, initialize with 
 ' myConnection string. Open it.
 Dim myConnection As New SqlConnection(connectionString)
 Dim myConnection2 As New SqlConnection(connectionString)
 
 Dim transaction As SqlTransaction
 
 myConnection.Open( )
 myConnection2.Open( )
 transaction = myConnection.BeginTransaction( )
 
 ' mimic concurrent user
 Dim cmd As String = "Update Bugs set Product = 1 where BugID = 1"
 Dim cmd1 As New SqlCommand(cmd, myConnection2)
 cmd1.ExecuteNonQuery( )
 
 
 ' *** create the update command object
 Dim updateCmd As _
 New SqlCommand("spUpdateBugFromDataSetWithConcurrency", _
 myConnection)
 updateCmd.CommandType = CommandType.StoredProcedure
 
 ' declare the parameter object
 Dim param As System.Data.SqlClient.SqlParameter
 
 ' Add new parameters, get back a reference 
 ' set the parameters' direction and value
 param = updateCmd.Parameters.Add("@ProductID", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "ProductID"
 param.SourceVersion = DataRowVersion.Current
 
 ' pass in the original value for the where statement
 param = updateCmd.Parameters.Add("@OldProductID", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "ProductID"
 param.SourceVersion = DataRowVersion.Original
 
 param = updateCmd.Parameters.Add("@Description", _
 SqlDbType.Text, 8000)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "Description"
 param.SourceVersion = DataRowVersion.Current
 
 param = updateCmd.Parameters.Add( _
 "@OldDescription", SqlDbType.Text, 8000)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "Description"
 param.SourceVersion = DataRowVersion.Original
 
 param = updateCmd.Parameters.Add("@Response", SqlDbType.Text, 8000)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "Response"
 param.SourceVersion = DataRowVersion.Current
 
 param = updateCmd.Parameters.Add("@OldResponse", _
 SqlDbType.Text, 8000)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "Response"
 param.SourceVersion = DataRowVersion.Original
 
 param = updateCmd.Parameters.Add("@Reporter", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "ReporterID"
 param.SourceVersion = DataRowVersion.Current
 
 param = updateCmd.Parameters.Add("@OldReporter", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "ReporterID"
 param.SourceVersion = DataRowVersion.Original
 
 param = updateCmd.Parameters.Add("@Owner", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "OwnerID"
 param.SourceVersion = DataRowVersion.Current
 
 param = updateCmd.Parameters.Add("@OldOwner", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "OwnerID"
 param.SourceVersion = DataRowVersion.Original
 
 param = updateCmd.Parameters.Add("@Status", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "StatusID"
 param.SourceVersion = DataRowVersion.Current
 
 param = updateCmd.Parameters.Add("@OldStatus", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "StatusID"
 param.SourceVersion = DataRowVersion.Original
 
 param = updateCmd.Parameters.Add("@Severity", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "SeverityID"
 param.SourceVersion = DataRowVersion.Current
 
 param = updateCmd.Parameters.Add("@OldSeverity", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "SeverityID"
 param.SourceVersion = DataRowVersion.Original
 
 
 param = updateCmd.Parameters.Add("@bugID", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "bugID"
 param.SourceVersion = DataRowVersion.Original ' note Original
 
 param = updateCmd.Parameters.Add("@BugHistoryID", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "BugHistoryID"
 param.SourceVersion = DataRowVersion.Original ' note Original
 
 myDataAdapter.UpdateCommand = updateCmd
 
 
 ' *** the delete command
 Dim deleteCmd As New SqlCommand("spDeleteBugFromDataSet", _
 myConnection)
 deleteCmd.CommandType = CommandType.StoredProcedure
 
 param = deleteCmd.Parameters.Add("@bugID", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "bugID"
 param.SourceVersion = DataRowVersion.Original ' note Original
 
 param = deleteCmd.Parameters.Add("@BugHistoryID", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "BugHistoryID"
 param.SourceVersion = DataRowVersion.Original ' note Original
 
 myDataAdapter.DeleteCommand = deleteCmd
 
 ' *** insert command
 Dim insertCmd As New SqlCommand("spInsertBugFromDataSet", _
 myConnection)
 insertCmd.CommandType = CommandType.StoredProcedure
 
 ' Add new parameters, get back a reference 
 ' set the parameters' direction and value
 param = insertCmd.Parameters.Add("@ProductID", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "ProductID"
 param.SourceVersion = DataRowVersion.Current
 
 param = insertCmd.Parameters.Add("@Version", SqlDbType.Text, 50)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "Version"
 param.SourceVersion = DataRowVersion.Current
 
 param = insertCmd.Parameters.Add("@Description", _
 SqlDbType.Text, 8000)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "Description"
 param.SourceVersion = DataRowVersion.Current
 
 param = insertCmd.Parameters.Add("@Response", SqlDbType.Text, 8000)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "Response"
 param.SourceVersion = DataRowVersion.Current
 
 param = insertCmd.Parameters.Add("@Reporter", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "ReporterID"
 param.SourceVersion = DataRowVersion.Current
 
 param = insertCmd.Parameters.Add("@Owner", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "OwnerID"
 param.SourceVersion = DataRowVersion.Current
 
 param = insertCmd.Parameters.Add("@Status", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "StatusID"
 param.SourceVersion = DataRowVersion.Current
 
 param = insertCmd.Parameters.Add("@Severity", SqlDbType.Int)
 param.Direction = ParameterDirection.Input
 param.SourceColumn = "SeverityID"
 param.SourceVersion = DataRowVersion.Current
 
 myDataAdapter.InsertCommand = insertCmd
 
 ' add transaction support for each command
 myDataAdapter.UpdateCommand.Transaction = transaction
 myDataAdapter.DeleteCommand.Transaction = transaction
 myDataAdapter.InsertCommand.Transaction = transaction
 
 ' try to update, if all succeed commit
 ' otherwise roll back
 Try
 AddHandler myDataAdapter.RowUpdated, AddressOf OnRowUpdate
 Dim rowsUpdated As Int16 = myDataAdapter.Update(bugDS, "BugInfo")
 transaction.Commit( )
 MessageBox.Show(rowsUpdated.ToString( ) + " rows Updated.")
 RefreshDataSet( )
 Catch ex As Exception
 MessageBox.Show("Unable to update db!" + ex.Message)
 transaction.Rollback( )
 End Try
 
 
 ' rebind the grid to show the results
 ' grid should be unchanged
 dgBugs.DataSource = bugDS.Tables("BugInfo")
 End Sub ' close btnUpdateDB_Click
 
 Public Sub OnRowUpdate(ByVal sender As Object, _
 ByVal e As SqlRowUpdatedEventArgs)
 ' get the type of update (update, insert, delete)
 ' as a string
 Dim s As String = _
 "Attempted " & _
 System.Enum.GetName(e.StatementType.GetType( ), e.StatementType) & _
 ". "
 
 ' if the update failed
 If (e.RecordsAffected < 1) Then
 ' write to the trace log
 MessageBox.Show(s & "Concurrency error updating BugID: " & _
 e.Row("BugID", DataRowVersion.Original))
 
 ' skip over this row, continue with the next
 e.Status = UpdateStatus.SkipCurrentRow
 Else ' the update succeeded
 ' write a success message to the trace log
 MessageBox.Show(s & " Row updated, BugID: " & _
 e.Row("BugID", DataRowVersion.Original))
 End If
 End Sub
 
End Class

The key change in this listing is in the btnUpdateDB_Click method, in which you must add additional parameters for the original values, such as the highlighted lines in the code snippet below.

@ProductID int,
@OldProductID int,
@Description varChar(8000),
@OldDescription varChar(8000)

Both the ProductID and the OldProductID are drawn from the same field in the DataSet: ProductID. For ProductID, you will use the Current version of the field; for OldProductID, you'll use the Original version:

param =
 updateCmd.Parameters.Add("@ProductID",SqlDbType.Int); 
param.Direction = ParameterDirection.Input;
param.SourceColumn="ProductID";
param.SourceVersion=DataRowVersion.Current;
 
// pass in the original value for the where statement
param = 
updateCmd.Parameters.Add("@OldProductID",SqlDbType.Int); 
param.Direction = ParameterDirection.Input;
param.SourceColumn="ProductID";
param.SourceVersion=DataRowVersion.Original;
 
param = 
 updateCmd.Parameters.Add("@Description",SqlDbType.Text,8000); 
param.Direction = ParameterDirection.Input;
param.SourceColumn="Description";
param.SourceVersion=DataRowVersion.Current;
 
param = 
 updateCmd.Parameters.Add("@OldDescription",SqlDbType.Text,8000); 
param.Direction = ParameterDirection.Input;
param.SourceColumn="Description";
param.SourceVersion=DataRowVersion.Original;

Other than setting the new parameters for the Update command, the only other change to btnUpdateDB_Click comes just before you call Update on the data adapter. You will add an event handler for the RowUpdated event:

dataAdapter.RowUpdated +=
 new SqlRowUpdatedEventHandler(OnRowUpdate);

AddHandler myDataAdapter.RowUpdated, AddressOf OnRowUpdate

The RowUpdate event is called each time a row is updated and offers you an opportunity to examine the updated row. In the event handler, you will get the statement type, which will be one of the StatementTypeEnumeration values: Delete, Insert, Select, or Update. You can turn the enumerated value into a string by calling the static GetName method on the System.Enum class, passing in the type and the value:

string s =
 System.Enum.GetName(
 e.StatementType.GetType( ),e.StatementType);

Dim s As String = _
 "Attempted " & _
 System.Enum.GetName(e.StatementType.GetType( ), e.StatementType) & ". "

Use the type to inform the user of the success or failure of updating (or inserting or deleting) each row. You can now examine the number of rows affected by the update:

if (e.RecordsAffected < 1)

Each update action affects zero or more rows. However, a single update might affect two or more rows, as you saw in the update stored procedure, which updates a row in Bugs and also a row in BugsHistory. If this procedure succeeds, e.RecordsAffected will be 2 (one record each in Bugs and BugHistory). You have crafted the update procedure so that if the update fails, no rows are affected and you can catch the error:

if (e.RecordsAffected < 1)
{
 MessageBox.Show(s + "Concurrency error! Unable to update BugID: " + 
 e.Row["BugID",DataRowVersion.Original].ToString( ));

If (e.RecordsAffected < 1) Then
 MessageBox.Show(s & "Concurrency error updating BugID: " & _
 e.Row("BugID", DataRowVersion.Original))

In this example, you handle the error by opening a message box with the error message. You could, in a real-world application, determine which row update had the problem and display that row (perhaps along with the current contents of the database) to the user for resolution.

The Status property is a property of the SqlRowUpdatedEventArgs object that was passed into your RowUpdated event handler. This will be one of the UpdateStatus enumerated values: Continue, ErrorsOccurred, SkipAllRemainingRows, or SkipCurrentRow. If an error was found (e.g., the update failed), this value will be set to ErrorsOccurred, and if you do not change it, an exception will be thrown. Since you have now handled the error (by displaying it to the user or in whatever way you've chosen), you will want to change the value to SkipCurrentRow, which will allow the update command to continue, skipping over the row whose update failed:

e.Status = UpdateStatus.SkipCurrentRow;

To test whether the update will be protected against concurrency issues, you will hand-update one field in one record before attempting the automated update. To do so, just before you begin the transaction, create a new connection in btnUpdateDB_Click, open it, and execute a SQL statement to update the Bugs table; you will also set the Product value to 1 where the BugID equals 1:

System.Data.SqlClient.SqlConnection connection2 =
 new System.Data.SqlClient.SqlConnection(connectionString)
connection2.Open( );
string cmd = "Update Bugs set Product = 2 where BugID = 1";
SqlCommand cmd1 = new SqlCommand(cmd,connection2);
cmd1.ExecuteNonQuery( );

Dim myConnection2 As _
 New System.Data.SqlClient.SqlConnection(connectionString)
myConnection2.Open( )
Dim cmd As String = _
 "Update Bugs set Product = 1 where BugID = 1"
Dim cmd1 As New SqlCommand(cmd, myConnection2)
cmd1.ExecuteNonQuery( )

The sequence of events is now:

1. Fill the DataSet from the database and display it in a grid.
2. When the user clicks Update DataSet, modify the DataSet and display the changes.
3. When the user clicks Update Database, hand-modify one record in the database and then tell the DataSet to update the database. The record you modified (for BugID =1) should make the update from the DataSet for that bug fail.
4. Catch the failure by noting that for one record, RecordsAffected is zero, and handle the error.
5. Report on the remaining updates, deletes, and inserts. (They should all work well.)

   

   You must make one change to the btnUpdateDataSet_Click method for this test to be meaningful. The field you update in BugID1 should be a field in Bugs rather than in BugHistory. In previous examples, you wrote:

   bugTable.Rows[0]["Response"] = 
    "This is a test";

   In this example, you will modify it to:

   bugTable.Rows[0]["ReporterID"] = "1";