JavaScript Editor Javascript validator     Javascripts

Main Page

↑
Previous Section Next Section

12.4 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. It is possible, of course, that many other people were simultaneously reading the same data into datasets of their own, editing their data, and writing their changes back to the database.

You can easily imagine that this could cause tremendous problems of data corruption. Imagine, for example, that a QA person downloads the current open bugs and begins to review the bugs with an eye towards updating some of the information. Meanwhile, across the office (or across town) a developer has read a few open bugs into a form of his own. It happens that they both 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 to reassign the bug to Dan Hurwitz. Meanwhile the developer is updating his dataset to change the action taken on the bug. The QA person writes back his changes, 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, you may use any of the following strategies:

  1. Locking the records. When one user is working with a record, other users can read the records but they cannot update them.

  2. Updating 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. Previewing whether the database has changed before you make your updates. If so, notify the user.

  4. Attempting the change and handling the error, if any.

The following sections explore each of these possible strategies.

12.4.1 Locking 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 a number of surrounding records are locked as well.

While record and page locking is not uncommon in some database environments, it is generally undesirable, especially in large web applications. It's possible for a record to be locked, and the user never returns to the database to unlock it. 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.

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, typically each user will update only a very few. Locking is a very big, blunt weapon; what is needed in a web application is a small, delicate surgical tool.

12.4.2 Comparing Original Against New

To understand how to compare the dataset against the database, you must keep in mind three possible values for each of your fields:

  1. The value currently in the database.

  2. The value that was in the database when you first filled the dataset.

  3. The value that is now in the data set 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 method 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.

There are two significant problems with this approach. First, you must query the database for the current values before each update. Second, there is no guarantee you have solved the problem. It is certainly possible that someone will update a record after you've queried the database, but before you write back your changes! In any case, this approach is so inefficient, we won't bother to demonstrate it here.

12.4.3 Handling the Errors

Odd as it may seem at first, it turns out that 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, you must craft your Update statement so that it will fail if someone else has updated the records.

This approach has tremendous efficiency advantages. In the vast majority of 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 that it received from the database, you need pass only those values back into the stored procedure as parameters, and then add them to the Where clause in your Update statement, as shown in Example 12-14.

Example 12-14. Modified update stored procedure
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
       Update Bugs 
       set 
       Product = @productID,
       [Description] = @Description,
       Reporter = @Reporter
       where bugID = @BugID and Product = @OldProductID
              and [Description] = @OldDescription and Reporter = @OldReporter

if @@RowCount > 0 
begin

       Update BugHistory 
       Set
       bugID = @BugID, 
       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
GO

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 Bugs Table, you check the @@RowCount to see if any rows were successfully added. If so, you can add 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. 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 take appropriate corrective action.

The careful reader will note that it is possible that the update to Bugs will work, but the update to BugHistory will fail, and the program will return 1 record updated. 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 somewhat more difficult to understand.

You will, of course, need to modify the btnUpdateDataBase_Click method to create the new parameters you need. Notice that you have pairs of parameters, such as:

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

Both the ProductID and the OldProductID will be drawn from the same field in the dataset: ProductID. In the former case, you will use the Current version of that field; in the latter case, 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;

In VB .NET, the code is virtually identical, without the semicolons.

Other than setting the new parameters for the Update command, the only other change to btnUpdateDataBase_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);

In VB .NET, it is:

AddHandler dataAdapter.RowUpdated, AddressOf OnRowUpdate

The RowUpdate event is called each time a row is updated and offers you an opportunity to examine the row that was updated. 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);

In VB .NET, it is:

Dim s As string  = _
  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. It is, of course, possible that a single update will affect two or more rows. You saw that 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)
{
    Trace.Warn(s + "Error updating BugID: " +
           e.Row["BugID",DataRowVersion.Original].ToString(  ));

In VB.NET, it is:

If e.RecordsAffected < 1 Then
   ' write to the trace log
   Trace.Warn(s & "Error updating BugID: " & _
     e.Row("BugID", DataRowVersion.Original))

In this example, you are handling the error by writing a statement to the trace output. 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.

One of the properties of the SqlRowUpdatedEventArgs object passed into your RowUpdated event handler is the Status property. 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, in btnUpdateDataBase_Click, you will create a new connection, 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 = 1 where BugID = 1";
 SqlCommand cmd1 = new SqlCommand(cmd,connection2);
 cmd1.ExecuteNonQuery(  );

In VB.NET, it would be:

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 and stash it in a session variable.

  2. When the user clicks Update DataSet, retrieve the dataset from the session variable, modify the dataset, and display the changes.

  3. When the user clicks Update Database, hand-modify one record in the database, 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 fine.)

The source code is once again mostly unchanged. The only affected methods are btnUpdateDataBase_Click and the new method, OnRowUpdate. These are annotated and shown in full in Example 12-15 for C# and in Example 12-16 for VB.NET.

One change must be made 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 this to:

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

Example 12-15. Support for concurrency (C#)
private void btnUpdateDataBase_Click(
   object sender, System.EventArgs e)
{

   //retrieve the dataset from session variable
   DataSet ds = (DataSet) Session["BugsDataSet"];

   // create a new data adapter
   SqlDataAdapter dataAdapter = new SqlDataAdapter(  );

   // set up the connection string
   string connectionString = 
      "server=YourServer; uid=sa; 
              pwd=YourPassword; database=ProgASPDotNetBugs";

   // Create connection object, initialize with 
   // connection string. Open it.
   System.Data.SqlClient.SqlConnection connection = 
      new System.Data.SqlClient.SqlConnection(connectionString);

   // 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 = 1 where BugID = 1";
   SqlCommand cmd1 = new SqlCommand(cmd,connection2);
   cmd1.ExecuteNonQuery(  );

   // create the transaction
   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(ds,"BugInfo");
      transaction.Commit(  );
      CountUpdatedRows.Visible=true;
      CountUpdatedRows.Text = rowsUpdated.ToString(  ) + " rows Updated.";
   }
   catch
   {
      transaction.Rollback(  );
   }

   // rebind the grid to show the results
   // grid should be unchanged
   DataGrid2.DataSource = ds.Tables["BugInfo"];
   DataGrid2.DataBind(  );
}

// 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)
   {
      // write to the trace log
      Trace.Warn(
         s + "Error updating BugID: " + 
         e.Row["BugID",DataRowVersion.Original].ToString(  ));

      // skip over this row, continue with the next
      e.Status = UpdateStatus.SkipCurrentRow;
   }
   else // the update succeeded
   {
      // write a success message to the trace log
       Trace.Write(s + " Row updated, BugID: " + 
          e.Row["BugID",DataRowVersion.Original].ToString(  ));
   }
}
Example 12-16. Support for concurrency (VB.NET)
Private Sub btnUpdateDataBase_Click( _
   ByVal sender As Object, ByVal e As System.EventArgs)

   Dim ds As DataSet = CType(Session("BugsDataSet"), DataSet)
   Dim dataAdapter As New SqlDataAdapter(  )

   Dim connectionString As String = _
      "server=YourDB; uid=sa; pwd=YourPassword; database=ProgASPDotNetBugs"

   ' Create connection object, initialize with 
   ' connection string. Open it.
   Dim myConnection As _
    New System.Data.SqlClient.SqlConnection(connectionString)
   Dim myConnection2 As _
    New System.Data.SqlClient.SqlConnection(connectionString)

   Dim transaction As SqlTransaction
   myConnection.Open(  )
   myConnection2.Open(  )

   Dim cmd As String = "Update Bugs set Product = 1 where BugID = 1"
   Dim cmd1 As New SqlCommand(cmd, myConnection2)
   cmd1.ExecuteNonQuery(  )

   transaction = myConnection.BeginTransaction(  )

   ' *** 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

   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
   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

   dataAdapter.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

   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
      AddHandler(myDataAdapter.RowUpdated, AddressOf OnRowUpdate);
      Dim rowsUpdated As Int32
      rowsUpdated = CType(dataAdapter.Update(ds, "BugInfo"), Int32)
      transaction.Commit(  )
      ' transaction.Rollback(  )
      CountUpdatedRows.Visible = True
      CountUpdatedRows.Text = rowsUpdated.ToString(  ) + " rows Updated."

   Catch
      transaction.Rollback(  )

   End Try

   ' rebind the grid to show the results
   ' grid should be unchanged
   DataGrid2.DataSource = ds.Tables("BugInfo")
   DataGrid2.DataBind(  )
End Sub

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
      Trace.Warn(s & "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
      Trace.Write(s & " Row updated, BugID: " & _
           e.Row("BugID", DataRowVersion.Original))
   End If
End Sub
    Previous Section Next Section


    JavaScript Editor Javascript validator     Javascripts 
    →




    ©