Using SQL Server s NOCOUNT Setting

Handling Failed Update Attempts Elegantly

ADO.NET is designed to work with disconnected data. When a user modifies the contents of a DataSet, he or she does not directly modify the contents of the database. Instead, ADO.NET caches the change in the modified DataRow object or objects. You can submit the changes to the database later using a DataAdapter object.

However, there are no guarantees that the data in the database won't be changed after the user runs the initial query. The updating logic that DataAdapter objects use to submit changes uses optimistic concurrency. As with being an optimist in life, things don't always turn out the way you'd like.

Consider the following scenario. User A retrieves customer information from your database into a DataSet. That user modifies information for a particular customer. Between the time that User A queries the database and attempts to submit the new customer data, User B modifies that same row of data in your database. As a result, User A's update attempt fails.

Many developers see this behavior as a major headache, but consider the alternative. What if User A's update attempt were to succeed? User A would overwrite the changes that User B made without even realizing that the data had been changed.

Planning Ahead for Conflicts

If you're building a multi-user application that works with disconnected data and relies on optimistic concurrency to submit changes, there's a chance that update attempts will fail. You should plan ahead and determine how your application should respond to such situations.

Say you modify the contents of 10 rows and attempt to submit those changes. The DataAdapter successfully submits the changes in the first three rows, but the attempt to submit the change stored in the fourth row fails. How should your application respond? Should the DataAdapter attempt to submit the remaining pending changes?

The DataAdapter Object's ContinueUpdateOnError Property

You can control how the DataAdapter responds to a failed update attempt by using the ContinueUpdateOnError property. By default, this property is set to False,which means that the DataAdapter will throw a DBConcurrencyException when it encounters a failed update attempt. If you want the DataAdapter to attempt to submit the remaining changes, set its ContinueUpdateOnError property to True.

If you set this property to True and one or more of the update attempts fail, the DataAdapter will not throw an exception. When the DataAdapter encounters a failed update attempt, it will set the HasErrors property of the corresponding DataRow object to True and set the RowError property of the DataRow to the concurrency error message. You can check the HasErrors property of your DataSet or DataTable after calling DataAdapter.Update to determine whether any of the update attempts failed. Of course, this will not be a valid test if your DataSet or DataTable has errors before you call DataAdapter.Update.

Some developers will want to submit changes in a transaction and commit the changes only if all update attempts succeed. In such scenarios, you'll probably want to leave the ContinueUpdateOnError property set to its default value of False and roll back the transaction if the Update method throws an exception.

Informing the User of Failures

It's important to inform the user if an update attempt fails. Some components will help you show the user which rows did not update successfully. For example, if you modify a number of rows in a Windows DataGrid control and the attempt to submit those modified rows fails, the rows that did not update successfully will be marked with a warning icon in the row header. If you move the mouse over the icon, the grid will display a tooltip that shows the contents of the error.

If you're not using a bound Windows DataGrid, you can use code like the following to provide information about the rows whose updates failed:

Visual Basic .NET

Try     MyDataAdapter.ContinueUpdateOnError = True     MyDataAdapter.Update(MyDataTable)     If MyDataTable.HasErrors         Dim strMessage As String         strMessage = "The following row(s) were not updated " & _                      "successfully:"         Dim row As DataRow         For Each row In MyDataTable.Rows             If row.HasErrors Then                 strMessage &= vbCrLf & row("ID") & " - " & _                               row.RowError             End If                     Next row         MessageBox.Show(strMessage)     Else         MessageBox.Show("All updates succeeded")     End If Catch ex As Exception     MessageBox.Show("The following exception occurred:" & vbCrLf & _                     ex.Message) End Try

Visual C# .NET

try {     MyDataAdapter.ContinueUpdateOnError = true;     MyDataAdapter.Update(MyDataTable);     if (MyDataTable.HasErrors)     {         string strMessage;         strMessage = "The following row(s) were not updated " +                      "successfully:";         foreach (DataRow row in MyDataTable.Rows)             if (row.HasErrors)                 strMessage += "\n\r" + (string) row["ID"] +                                row.RowError;         MessageBox.Show(strMessage);     }     else         MessageBox.Show("All updates succeeded"); } catch (Exception ex) {     MessageBox.Show("The following exception occurred: \n\r" +                      ex.Message); }

Users can be demanding. If an update attempt fails, they don't just want to know that it failed. They generally want to know why the update attempt failed and how to make the update successful. First, let's focus on determining why an update attempt failed.

What if we could extract the information shown in Figure 11-14 for each failed update attempt?

Figure 11-14

Displaying information about failed update attempts

You already know how to use the DataRow object to access the current and original contents of a row.

Visual Basic .NET

Dim tbl As DataTable = CreateFillAndModifyTable() Dim row As DataRow = tbl.Rows(0) Console.WriteLine("Current  Balance Due: " & row("BalanceDue")) Console.WriteLine("Original Balance Due: " & _                   row("BalanceDue", DataRowVersion.Original))

Visual C# .NET

DataTable tbl = CreateFillAndModifyTable(); DataRow row = tbl.Rows[0]; Console.WriteLine("Current  Balance Due: " + row["BalanceDue"]); Console.WriteLine("Original Balance Due: " +                    row["BalanceDue", DataRowVersion.Original]);

But how can we fetch the current contents of the desired rows in the database?

Fetching the Current Contents of Conflicting Rows

To fetch the current contents of the desired rows, we can use the DataAdapter object's RowUpdated event. The following code snippet determines whether the DataAdapter encountered an error on the update attempt. If the error is a concurrency exception, the code will use a parameterized query to fetch the current contents of the corresponding row in the database.

To make the code snippet concise and readable, I've omitted the definition of the DataAdapter objects and DataSet objects. The ConflictAdapter is a DataAdapter that contains a parameterized query that retrieves the contents of a row in the database. The parameter for this query is the primary key column for the database. That column in the DataTable is ID. The code uses the value of the ID column for the row whose update failed as the value for the parameter, executes the query using that parameter, and stores the results into a separate DataSet.

There's also the chance that the row you tried to update no longer exists in the database. The code determines whether the query retrieved a row and sets the DataRow object's RowUpdate property appropriately.

Visual Basic .NET

Private Sub HandleRowUpdated(ByVal sender As Object, _                              ByVal e As OleDbRowUpdatedEventArgs)     If e.Status = UpdateStatus.ErrorsOccurred AndAlso _        TypeOf(e.Errors) Is DBConcurrencyException Then         ConflictAdapter.SelectCommand.Parameters(0).Value = e.Row("ID")         Dim intRowsReturned As Integer          intRowsReturned = ConflictAdapter.Fill(ConflictDataSet)         If intRowsReturned = 1 Then             e.Row.RowError = "The row has been modified by another user."         Else             e.Row.RowError = "The row no longer exists in the database."         End If         e.Status = UpdateStatus.Continue     End If End Sub

Visual C# .NET

private void HandleRowUpdated(object sender, OleDbRowUpdatedEventArgs e) {     if ((e.Status == UpdateStatus.ErrorsOccurred) &&         (e.Errors.GetType == typeof(DBConcurrencyException))     {         ConflictAdapter.SelectCommand.Parameters[0].Value = e.Row["ID"];         int intRowsReturned = ConflictAdapter.Fill(ConflictDataSet);         if (intRowsReturned == 1)             e.Row.RowError = "The row has been modified by another user.";         else             e.Row.RowError = "The row no longer exists in the database.";         e.Status = UpdateStatus.Continue;     } }

	The DataAdapter will automatically append text to the RowError property on a failed update attempt if you do not change Status to Continue or SkipCurrentRow.

This code snippet fetches the current contents of the corresponding rows in the database into a separate DataSet so that you can examine the data after the update attempt. You now have all the data you need to construct a dialog box similar to Figure 11-14.

If at First You Don't Succeed...

Telling the user why an update attempt failed is helpful, but your users probably don't want to requery your database and reapply the same set of changes to the new data in order to try to resubmit their changes. How can we use the ADO.NET object model to simplify the process?

Let's think back to why the update attempt failed in the first place. The data that the DataAdapter used in its concurrency checks no longer matches the current contents of the row in the database. The DataAdapter uses the original content of the DataRow object in the updating logic's concurrency checks. Until we refresh the original values in the DataRow object, we will not be able to submit the changes stored in that DataRow to the database no matter how many times we call the DataAdapter object's Update method.

If we can change the original content of a DataRow object without losing the current content of the DataRow, we can successfully submit the changes stored in that DataRow, assuming that the contents of the corresponding row in the database don't change again.

Using the DataSet Object's Merge Method to Import "New Original" Values

Earlier in the chapter, you learned how to use the DataSet object's Merge method to combine the contents of two DataSet objects. If the Merge method detects that two rows have the same primary key values, it will combine the contents of the two rows into one. The Merge method also lets you specify that you want to preserve the changes stored in your DataSet.

In the previous code snippet, we trapped for the DataAdapter object's RowUpdated event. If the current row did not update successfully, the code retrieved the current contents of the corresponding row in the database into a new DataSet called ConflictDataSet. Assuming that the main DataSet is called MainDataSet, we can use the following line of code to merge the contents of ConflictDataSet into MainDataSet:

Visual Basic .NET

MainDataSet.Merge(ConflictDataSet, True)

Visual C# .NET

MainDataSet.Merge(ConflictDataSet, true);

The code does not change the current contents of the DataRow objects in the main DataSet. It overwrites only the original values of the DataRow objects with the data in the conflict DataSet.

With this "new original" data in the main DataSet, we can attempt to submit the remaining pending changes to the database. If the contents of the corresponding rows have not changed since we retrieved them in the RowUpdated event, our updates will succeed.

Keep in mind that you can't get "more up-to-date" information about a row that no longer exists in the database. If an update attempt fails because the row no longer exists in the database, you can't use this approach to refresh the original values in the row. If you want to re-add the current contents of the DataRow object to the database, you can remove the DataRow from the DataTable object's Rows collection and then re-add it. This will change the DataRow object's RowState to Added. When you use the DataAdapter to submit the change, it will try to insert the row into the database.

The Conflicts Sample Application

The companion CD includes a sample application called Conflicts (shown in Figure 11-15) that's an example of how you can detect, analyze, and resolve failed update attempts. The sample retrieves data from the database into a DataSet and modifies that data. I've designed the sample to modify some of the rows in the database directly to simulate another user submitting changes. When you click the button to submit the changes cached in the DataSet to your database, some of the update attempts will fail as a result of the "other user's" changes.

The sample application handles these failed update attempts and fetches the current contents of those rows from the database into a second DataSet. As you navigate through the main DataSet, the sample displays the status for the row, its current contents, and its original contents. If the row contains a failed update attempt, you'll also see the current contents of the corresponding row in the database. The sample application forces you to resolve the conflicts before attempting to update the database.

Figure 11-15

The Conflicts sample application