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 { 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')"; 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. 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 or not another user has edited the row (as long as the row still exists). The following UPDATE statement implements "last one wins" concurrency control: 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 UPDATE statement to implement optimistic concurrency control by changing its WHERE clause, like this: 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. | 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. |
|