Dealing with Update Failures

So far, the examples you've seen have assumed that the updates pushed to the database by the Update() have succeeded. In this section, you see what happens when updates fail-and what you can do about it.

Note 

You'll find all the code examples shown in this section in the HandlingUpdateFailures.cs file located in the ch11 directory. This program listing is omitted from this book for brevity.

In the examples in this section, assume that the CommandText property of a SqlDataAdapter object's UpdateCommand is set as follows:

 UPDATE Customers SET   CompanyName = @NewCompanyName,   Address = @NewAddress WHERE CustomerID = @OldCustomerID AND CompanyName = @OldCompanyName AND Address = @OldAddress 

This UPDATE statement uses optimistic concurrency because the updated columns are included in the WHERE clause.

An Update Failure Scenario

Consider the following scenario that shows an update failure:

  1. User 1 retrieves the rows from the Customers table into a DataTable named customersDataTable.

  2. User 2 retrieves the same rows.

  3. User 1 updates the CustomerName DataColumn of the DataRow with the CustomerID DataColumn of J5COM and pushes the change to the database. Let's say User 1 changes the CustomerName from J5 Company to Updated Company.

  4. User 2 updates the same DataRow and changes the CompanyName from J5 Company to Widgets Inc. and attempts to push the change to the database. User 2 then causes a DBConcurrecy-Exception object to be thrown and their update fails. (The same exception occurs if User 2 tries to update or delete a row that has already been deleted by User 1.)

Why does the update fail in step 4? The reason is that with optimistic concurrency, the CompanyName column is used in the WHERE clause of the UPDATE statement. Because of this, the original row loaded by User 2 cannot be found anymore-and therefore the UPDATE statement fails. The row cannot be found because User 1 has already changed the CompanyName column from J5 Company to Updated Company in step 2.

That's the problem with optimistic concurrency, but what can you as a developer do about it? You can report the problem to User 2, refresh their rows using the Fill() method, and they can make their change again-however, if User 2 has already made a large number of changes and they can't save any of them, they'll probably be very annoyed at your program.

Fortunately, you can set the ContinueUpdateOnError property of your DataAdapter to true to continue updating any DataRow objects even if an error occurs. That way, when User 2 saves their changes they can at least save the rows that don't cause any errors. Let's take a look at how to set the ContinueUpdateOnError property.

Setting the ContinueUpdateOnError Property

The following example sets the ContinueUpdateOnError property to true for mySqlDataAdapter:

 mySqlDataAdapter.ContinueUpdateOnError = true; 

When you call mySqlDataAdapter.Update(), it will push all the changes that don't cause errors to the database. You can then check for errors afterward using the HasErrors property of a DataSet or the HasErrors property of individual DataTable objects, which you'll see how to shortly in the section "Checking for Errors."

Programming a Failed Update Example

Let's program an example of a failed update. This example will simulate the updates made by User 1 and User 2 described earlier. I'll use the following method, named ModifyRowsUsingUPDATE(), to simulate the update made by User 1 in step 3 described earlier:

 public static void ModifyRowUsingUPDATE(   SqlConnection mySqlConnection ) {   Console.WriteLine("\nIn ModifyDataRowUsingUPDATE()");   Console.WriteLine("Updating CompanyName to 'Updated Company' for J5COM");   SqlCommand mySqlCommand = mySqlConnection.CreateCommand();   mySqlCommand.CommandText =     "UPDATE Customers " +     "SET CompanyName = 'Updated Company' " +     "WHERE CustomerID = 'J5COM'";   mySqlConnection.Open();   int numberOfRows = mySqlCommand.ExecuteNonQuery();   Console.WriteLine("Number of rows updated = " +     numberOfRows);   mySqlConnection.Close(); } 

Notice that the CompanyName is set to Updated Company for the row with the CustomerID of J5COM.

I'll use the following ModifyDataRow() method to simulate the update made by User 2 in step 4. This is similar to the other ModifyDataRow() methods you've seen earlier in this chapter. Notice that the CompanyName is set to Widgets Inc. for the row with the CustomerID of J5COM:

 public static void ModifyDataRow(   DataTable myDataTable,   SqlDataAdapter mySqlDataAdapter,   SqlConnection mySqlConnection ) {   Console.WriteLine("\nIn ModifyDataRow()");   // step 1: set the PrimaryKey property of the DataTable   myDataTable.PrimaryKey =     new DataColumn[]     {       myDataTable.Columns["CustomerID"]     };   // step 2: use the Find() method to locate the DataRow   // in the DataTable using the primary key value   DataRow myDataRow = myDataTable.Rows.Find("J5COM");   // step 3: change the DataColumn values of the DataRow   myDataRow["CompanyName"] = "Widgets Inc.";   Console.WriteLine("myDataRow.RowState = " +     myDataRow.RowState);   // step 4: use the Update() method to push the modified   // row to the database   Console.WriteLine("Calling mySqlDataAdapter.Update()");   mySqlConnection.Open();   int numOfRows = mySqlDataAdapter.Update(myDataTable);   mySqlConnection.Close();   Console.WriteLine("numOfRows = " + numOfRows);   Console.WriteLine("myDataRow.RowState = " +     myDataRow.RowState);   DisplayDataRow(myDataRow, myDataTable); } 

The next example calls ModifyRowUsingUPDATE() to perform the first update of the row with the CustomerID of J5COM:

 ModifyRowUsingUPDATE(mySqlConnection); 

Next, the ContinueUpdateOnError property of mySqlDataAdapter is set to true to continue updating any DataRow objects even if an error occurs:

 mySqlDataAdapter.ContinueUpdateOnError = true; 

Next, ModifyDataRow() is called to attempt to modify the same row as ModifyRowsUsingUPDATE():

 ModifyDataRow(customersDataTable, mySqlDataAdapter, mySqlConnection); 

Normally, this will throw an exception since the row cannot be found, but since the ContinueUpdateOnError property of mySqlDataAdapter has been set to true, no exception will be thrown. That way, if myDataTable had other updated rows, they would still be pushed to the database by the call to the Update() method.

Checking for Errors

Having set the ContinueUpdateOnError property of mySqlDataAdapter to true, no exception will be thrown when an error occurs. Instead, you can check for errors in a DataSet or individual DataTable or DataRow using the HasErrors property. You can then show the user the details of the error using the RowError property of the DataRow, along with the original and current values for the DataColumn objects in that DataRow. For example:

 if (myDataSet.HasErrors) {   Console.WriteLine("\nDataSet has errors!");   foreach (DataTable myDataTable in myDataSet.Tables)   {     // check the HasErrors property of myDataTable     if (myDataTable.HasErrors)     {       foreach (DataRow myDataRow in myDataTable.Rows)       {         // check the HasErrors property of myDataRow         if (myDataRow.HasErrors)         {           Console.WriteLine("Here is the row error:");           Console.WriteLine(myDataRow.RowError);           Console.WriteLine("Here are the column details in the DataSet:");           foreach (DataColumn myDataColumn in myDataTable.Columns)           {             Console.WriteLine(myDataColumn + "original value = " +               myDataRow[myDataColumn, DataRowVersion.Original]);             Console.WriteLine(myDataColumn + "current value = " +               myDataRow[myDataColumn, DataRowVersion.Current]);           }         }       }     }   } } 

Fixing the Error

Showing the user the error is only half the story. What can you do to fix the problem? One solution is to call the Fill() method again to synchronize the rows in your DataSet with the database. That way, the row updated by ModifyRowsUsingUPDATE() in the Customers table will be pulled from the Customers table and replace the original J5COM row in the DataSet. For example:

 mySqlConnection.Open(); numOfRows =   mySqlDataAdapter.Fill(myDataSet, "Customers"); mySqlConnection.Close(); 

You might expect numOfRows to be 1 because you're replacing only one row, right? Wrong: numOfRows will contain the total number of rows in the Customers table. The reason for this is that the Fill() method actually pulls all of the rows from the Customers table and puts them in the Customers DataTable of myDataSet, throwing away any existing rows with matching primary key column values already in the Customers DataTable.

Warning 

If you didn't add a primary key to your Customers DataTable, then the call to the Fill() method would simply add all the rows from the Customers table to the Customers DataTable again-duplicating the rows already there.

Next, you call ModifyDataRow() again to modify the J5COM row:

 ModifyDataRow(customersDataTable, mySqlDataAdapter, mySqlConnection); 

This time the update succeeds because ModifyDataRow() finds the J5COM row in customersDataTable that was just pulled from the Customers table.

Note 

If a user tries to update a row that has already been deleted from the database table, the only thing you can do is refresh the rows and ask the user to enter their row again.




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net