Handling Data Errors

You should always check for errors in any production code so that you can take corrective action if something goes wrong. The following sections show you how to do this.

Handling Database Errors

The System.Data.SqlClient namespace includes two objects to help you handle SQL Server “specific errors. These are the SqlException class, which inherits from System.Exception , and the SqlError class, which represents a single SQL Server error.

The following code segment shows how to trap SQL Server errors:

 try {      // Create a SqlConnection      SqlConnection cnn = new SqlConnection(         @"Data Source=(local)\NetSDK;Initial Catalog=Northwind;" +         "Integrated Security=SSPI");      // Create a SqlCommand      SqlCommand cmd = cnn.CreateCommand();      cmd.CommandType = CommandType.Text;      cmd.CommandText = "INSERT INTO Customers (CompanyName) " +              "VALUES ('New Company')";      // And execute it      cnn.Open();      cmd.ExecuteNonQuery();      cnn.Close(); } catch(SqlException sqlEx) {      // Handle SQL Server specific errors      foreach (SqlError err in sqlEx.Errors)      {        lbErrors.Items.Add("SQL Error " + err.Number + ": " + err.Message);      } } catch(Exception ex) {      // Handle general errors      lbErrors.Items.Add("Non-SQL Exception " + ex.Message); } 

Here, the SqlException object exposes an Errors property. This property is a collection of SqlError objects ”each of which contains a SQL Server error.

Handling Multiuser Errors

Whenever you have more than one user updating the same data, concurrency issues can arise. The basic question is, "Who wins in case of multiple updates?"

"It depends." When you're creating the SqlCommand object that is used for the UpdateCommand property of a SqlDataAdapter object, it's up to you to choose between two strategies for dealing with such conflicts:

• With optimistic concurrency control, an update to a row succeeds only if no one else has changed that row after it was loaded into the DataSet object.

• With last-one-wins concurrency control, an update to a row always succeeds, whether another user has edited the row (as long as the row still exists).

Consider the following SQL statement that is used to update the database:

 UPDATE Customers SET ContactName = @ContactName WHERE CustomerID = @CustomerID 

The key thing to look at here is the WHERE clause. The only column it looks at is the CustomerID column. CustomerID is the primary key of this table, a value that should never change. As long as that one column has not been changed, the UPDATE statement succeeds, no matter what might have changed about other columns in the same table.

You can modify the previous SQL statement project to implement optimistic concurrency control, like so:

 UPDATE Customers SET ContactName = @ContactName WHERE CustomerID = @CustomerID AND ContactName = @ContactNameOrig 

The new WHERE clause finds a row to update only if both the CustomerID and ContactName fields are unchanged from what they were when the row was originally loaded.

In adition, you need to include a try-catch block to handle exceptions during the update operation, as shown in the following code segment:

 try {     // Save the changes     da.Update(ds, "Customers");     // And make a note on the UI     lblResults.Text = "Row has been updated"; } catch(SqlException sqlEx) {    // Handle SQL Server specific errors    foreach (SqlError err in sqlEx.Errors)    {       lblResults.Text += "SQL Error " + err.Number + ": " + err.Message;    } } catch(Exception ex) {    // Handle general errors   lblResults.Text += "Non-SQL Exception " + ex.Message; } 

Strictly speaking, you can enforce optimistic concurrency control only if you check every column of the table in the WHERE clause. If you retrieve only a few columns, you can miss a change in a column you didn't retrieve.