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:
Lock the records. When one user works with a record, other users can read the records but cannot update them.
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.
Preview whether the database has changed before you make your updates. If so, notify the user.
Attempt the change and handle the error, if any.
The following sections explore each of these possible strategies.
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.
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.
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 andProduct = @OldProductIDand [Description] = @OldDescriptionand Reporter = @OldReporterif @@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 andstatus = @oldStatus and severity = @OldSeverityand response = @oldResponse and owner = @OldOwnerend 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.
Tip
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.
Tip
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 ClassThe 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:
Fill the DataSet from the database and display it in a grid.
When the user clicks Update DataSet, modify the DataSet and display the changes.
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.
Catch the failure by noting that for one record, RecordsAffected is zero, and handle the error.
Report on the remaining updates, deletes, and inserts. (They should all work well.)
Note
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";