Handling Database Errors

Chapter 9 - Data-Driven ASP.NET Applications in the Real World
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

At some point, an error is going to occur in your web application. Maybe it's your code, maybe it's the SQL statement that's being executed, or maybe it's the network that's preventing proper communication between your web server and the database itself - but whatever the situation, you must be able to handle it in such a way that the user feels as if they are still in control. If the user can't recover, or failing that, if they can't exit the error situation gracefully, they're going to feel as though the application has failed them.

Using @@ERROR

SQL Server and MSDE provide a system global variable called @@ERROR that you can use to check for errors from within your stored procedure code (there are many other useful global variables - check out MSDN for details). The variable contains 0 if the most recent SQL statement completed successfully; if it didn't, it contains an error number.

To use @@ERROR effectively, you must capture its value after every SQL statement in your stored procedure (use a local variable of type INT to do this), because if any statement completes successfully after an earlier one has failed, @@ERROR will return 0, the value that signifies success.

To show you how this works, we need to create a situation within the database in which errors are likely to occur. One such situation is when there's an attempt to insert a non-unique record into a table that uses a unique index to prevent such an action. Let's see @@ERROR in action now.

Try It Out - @@ERROR

start example

In the last chapter, we created a number of Visual Basic console applications for the purpose of inserting stored procedures into the Northwind database. You can use that same skeleton code to execute the sample SQL code in this chapter.

  1. Listed below is some SQL code that creates a new unique index in the Employees table of the Northwind database. This unique index prevents multiple employees with the same first and last names from being entered. Use your skeleton VB.NET code to execute it.

     CREATE UNIQUE NONCLUSTERED INDEX IX_Employees ON dbo.Employees (LastName, FirstName) ON [PRIMARY] 

  2. Next, create a stored procedure that inserts a new record into the Employees table.

     CREATE PROCEDURE InsertEmployee(@FirstName nvarchar(10),                                 @LastName nvarchar(20)) AS INSERT INTO Employees (firstname, lastname) VALUES (@FirstName, @LastName) RETURN @@ERROR 

  3. Still using the same skeleton code, execute the following SQL to ensure that no employee with the name Joe Smith currently exists.

     DELETE FROM Employees WHERE firstname = 'joe' and lastname = 'smith' 

  4. At this point, we have to use a tool that ships with SQL Server, but doesn't come with MSDE. In later examples, we'll be using ASP.NET to show our results, but for the time being we're going to use the Query Analyzer. If you don't have access to this tool, don't worry - we'll reproduce and explain all the results of using it here.

    Using the Query Analyzer, execute the following SQL script against the Northwind database:

     DECLARE @errnum int EXECUTE @errnum = insertemployee 'joe', 'smith' PRINT 'Error number: ' + CONVERT(varchar, @errnum) 

    When you run the script for the first time, the result will be:

        (1 row(s) affected)    Error number: 0 

    This means that the employee was inserted without error.

end example

How It Works

The stored procedure that we created in Step 2 will take a first name and last name, and insert a new employee in the Employees table using those values. Its return value will be the last error that occurred. If the action was successful, 0 is returned.

In Step 3, we execute a SQL DELETE statement to ensure that an employee by the name of Joe Smith doesn't yet exist.

Let's run the SQL script in Step 4 again, to see what happens when you try to insert an employee that already exists. Because we created the unique index in Step 1, we get the following error, along with its error number:

     Server: Msg 2601, Level 14, State 3, Procedure InsertEmployee, Line 4     Cannot insert duplicate key row in object 'Employees' with unique index 'IX_Employees'.     The statement has been terminated.     Error number: 2601 

While we're here, we can test something else that we stated above. Keep the Joe Smith record in the database, but change the stored procedure to include a SELECT command immediately after the INSERT command:

    ALTER PROCEDURE InsertEmployee(@FirstName nvarchar(10),                                   @LastName nvarchar(20)) AS    INSERT INTO employees (firstname, lastname) VALUES (@FirstName, @LastName)    SELECT TOP 1 * FROM employees    RETURN @@ERROR 

Now re-run Step 4. Even though the record insertion still fails, as you can see from the result below, the returned message is Error number: 0, as our last statement (SELECT) was successful. (Note that the first employee returned from your Northwind database may be different from the one below.)

    Server: Msg 2601, Level 14, State 3, Procedure InsertEmployee, Line 3    Cannot insert duplicate key row in object 'Employees' with unique index 'IX_Employees'.    The statement has been terminated.    EmployeeID    LastName     FirstName      Title                    TitleOfCourtesy    -----------------------------------------------------------------------------------    1             Davolio      Nancy          Sales Representative     Ms.    (1 row(s) affected)    Error number: 0 

As mentioned before, you must check for an error condition before the next SQL statement is executed. Otherwise, whatever the old error value was is overwritten by the most recently executed statement's error value. In this case, since the SELECT statement executed successfully, the @@ERROR value is 0.

Finally, let's change the InsertEmployee procedure one more time to return the error value correctly:

    ALTER PROCEDURE InsertEmployee(@FirstName nvarchar(10),                                   @LastName nvarchar(20)) AS    DECLARE @errnum int    INSERT INTO employees (firstname, lastname) VALUES (@FirstName, @LastName)    SET @errnum = @@ERROR    SELECT TOP 1 * FROM employees     RETURN @errnum 

What we're now doing is capturing the @@ERROR global variable's value to a local variable of type int named @errnum, so that we can use it later. Execute Step 4 one last time, and you'll see the correct error value returned, as shown below.

     Server: Msg 2601, Level 14, State 3, Procedure InsertEmployee, Line 3     Cannot insert duplicate key row in object 'Employees' with unique index 'IX Employees'.     The statement has been terminated.     EmployeeID   LastName    FirstName     Title                    TitleOfCourtesy     -------------------------------------------------------------------------------     1            Davolio     Nancy         Sales Representative     Ms.     (1 row(s) affected)     Error number: 2601 

Making @@ERROR Useful

Application troubleshooting can be a troublesome thing. Often, one of the most frustrating aspects of troubleshooting is trying to understand what went wrong. Usually, it's your users, not you, who experience errors when they occur - and users are infamous for telling you that there was an error, but not bothering to record specific details about it.

One way to minimize the frustration is to log error information automatically. There are many approaches to this, one of which is to have the database itself record an error when it happens. You can do this in your stored procedures by checking the @@ERROR value after each statement within the procedure, and logging any error that occurs.

Try It Out - Using @@ERROR in a stored procedure

start example

Let's work through an example that does exactly what we just described. We'll execute a command, and then see if an error occurred. If it did, we'll write an error log entry into a new table that we'll create.

  1. Modify the InsertEmployee stored procedure as follows:

     ALTER PROCEDURE InsertEmployee(@FirstName nvarchar(10),                                @LastName nvarchar(20)) AS INSERT INTO Employees (firstname, lastname) VALUES (@FirstName, @LastName) IF @@ERROR <> 0 BEGIN   INSERT INTO tblErrorLog (ErrType, Msg)   VALUES ('Duplicate record', 'Employee duplicate record couldn '' t be created.') END 

  2. Create the tblErrorLog table:

     CREATE TABLE [tblErrorLog] (   [ErrorID] [int] IDENTITY (1, 1) NOT NULL,   [ErrType] [varchar] (20) NOT NULL,   [Msg] [varchar] (100) NULL,   [ErrorDate] [datetime] NULL     CONSTRAINT [DF_tblErrors_ErrorDate] DEFAULT (getdate()),   CONSTRAINT [PK_tblErrors] PRIMARY KEY CLUSTERED   (     [ErrorID]   ) ON [PRIMARY] ) ON [PRIMARY] 

  3. In a new directory called webroot\ch09, create a web page called Insert_Employee.aspx, as shown below:

     <%@ Import namespace="system.data" %> <%@ Import namespace="system.data.sqlclient" %> <script language="vb" runat="server"> Private Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)   Dim sql As String = "InsertEmployee"   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   Dim conn As New SqlConnection(strConnection)   conn.Open()   Dim cmd As New SqlCommand(sql, conn)   cmd.CommandType = CommandType.StoredProcedure   cmd.Parameters.Add("@FirstName", "Mark")   cmd.Parameters.Add("@LastName", "Seeley")   Try     cmd.ExecuteNonQuery()   Catch ex As Exception     Response.Write("Error: " & ex.Message & "<br/>")   End Try   cmd.Connection.Close() End Sub </script> 

  4. Run Insert_Employee.aspx several times. The first time, it will run successfully, with the resulting page being blank. The second and successive times, however, you'll see this:

    click to expand

  5. If you use Visual Studio .NET to take a look at the contents of the tblErrorLog table, your results should be similar to this:

    click to expand

end example

How It Works

Let's step through the stored procedure to understand how it works. First, we execute the INSERT statement.

    INSERT INTO employees (firstname, lastname) VALUES (@FirstName, @LastName) 

If there has been an error,

    IF @errnum <> 0 

we insert a record into tblErrorLog, stating what the problem was:

    BEGIN      INSERT INTO tblErrorLog (ErrType, Msg)      VALUES ('Duplicate record', 'Employee duplicate record couldn''t be created.')    END 

The key to the ASPX page, meanwhile, lies in our Try...Catch block.

      Try        cmd.ExecuteNonQuery()      Catch ex As Exception        Response.Write("Error: " & ex.Message & "<br>")      End Try 

Since the ExecuteNonQuery() method call is within the Try section, the Catch section will be executed if it fails. Logging an error from within the stored procedure does not remove the need to handle the error from within our code, and your users still need to be notified that an error occurred in a way that doesn't mean the application crashing. We do that in the Catch section, where we simply echo the exception message out to the user.

        Response.Write("Error: " & ex.Message & "<br/>") 

This way, the user knows that an error has occurred, and when they call and tell you about it, you can look in the tblErrorLog table and see the specifics!

Raising Your Own Database Errors with RAISERROR

What if you have multiple steps within a stored procedure, and you'd like some way of communicating to your ASP.NET application which step within the stored procedure caused an error? One way of doing this would be to use return values, but capturing those after every statement is a time-consuming business, and we really don't want to write more code than we absolutely have to. We want a way to know that a problem occurred, and at exactly what step, without using a return value.

In this case, the T-SQL RAISERROR statement will fit our requirements. It allows you to raise a user-specific error in place of the actual database error that has occurred. By raising a user-specific error, you, can write your .NET code to handle the error better, or you can give more explanation to your user about the error. Normally, SQL error messages are pretty cryptic about exactly what happened, but RAISERROR gives you complete flexibility in describing the error to your user. The complete syntax of RAISERROR is as follows:

    RAISERROR ( { msg_id I msg_str} {, severity, state} [, argument [,n] ] )    [WITH option [,n] ] 

As you can see from the syntax, RAISERROR has a number of options. For our purposes here, we're just going to look at the most common parameters, which are shown below:

Argument

Description

msg_id

If you want to use messages that are stored in the sysmessages table, specify the value in the 'error' field here. Note that you can only use error messages numbered 13,000 or above.

msg_str

This is the error text. If you specify this instead of msg_id, a default value for msg_id of 50,000 will be used. Any error message with an ID of 50,000 or above is considered to be user-defined.

severity

This value specifies the relative severity of the error that you're raising. You can specify a value between 0 and 18 (where 18 is the most severe); values outside this range are reserved by SQL. You will see this called the "level" of the error in some places.

state

This is an arbitrary number between 1 and 127 that represents the state of the error at the time it occurred.

Try It Out - Using RAISERROR

start example
  1. Let's change our InsertEmployee stored procedure to use the RAISERROR statement.

     ALTER PROCEDURE InsertEmployee(@FirstName nvarchar(10),                                @LastName nvarchar(20)) AS INSERT INTO Employees (firstname, lastname) VALUES (@FirstName, @LastName) IF @@ERROR <> 0 BEGIN   RAISERROR('Employee duplicate record couldn't be created.', 1, 1)   INSERT INTO tblErrorLog (ErrType, Msg)   VALUES ('Duplicate record', 'Employee duplicate record couldn't be created.')   IF @@ERROR <> 0   BEGIN     RAISERROR('Error log couldn '' t be updated.', 1, 1)   END END 

  2. Then, let's run it with the following SQL twice in Query Analyzer, and examine the results.

     EXEC InsertEmployee 'brian', 'Jones' 

The first time, it should execute correctly. However, the second time you should get the following result.

     Server: Msg 2601, Level 14, State 3, Procedure InsertEmployee, Line 4     Cannot insert duplicate key row in object 'Employees' with unique index 'IX_Employees'.     The statement has been terminated.     Msg 50000, Level 1, State 50000     Employee duplicate record couldn't be created.    (1 row(s) affected) 

Note that SQL Server automatically raises the error 2601 again. Next, our custom error is raised. The message (1 row(s) affected) indicates that the record in the tblErrorLog was written.

end example

How It Works

As usual, the first thing we attempt to do in this stored procedure is to insert the new record into the Employees table:

    INSERT INTO employees (firstname, lastname) VALUES (@FirstName, @LastName) 

After that, we check to see whether an error occurred. If it did, we raise an error stating that the duplicate record couldn't be created. We use a severity of 1 and a state of 1 as arbitrary values, since our code doesn't care about them.

    IF @@ERROR <> 0    BEGIN      RAISERROR(Employee duplicate  record couldn't be  created.', 1, 1) 

If an error occurred, we also attempt to insert a new record into tblErrorLog.

      INSERT INTO tblErrorLog (ErrType, Msg)      VALUES ('Duplicate record', 'Employee duplicate record couldn''t be created.') 

Finally, we check to see if yet another error occurred. If it did, we raise another error stating that the error log couldn't be updated.

      IF @@ERROR <> 0      BEGIN        RAISERROR ('Error log couldn" t be updated.', 1, 1 )      END 

Now let's create an artificial situation where an error will also occur while updating tblErrorLog. For that, we need to modify the InsertEmployee stored procedure yet again.

    ALTER PROCEDURE InsertEmployee(@FirstName nvarchar(10),                                   @LastName nvarchar(20)) AS    INSERT INTO Employees (firstname, lastname) VALUES (@FirstName, @LastName)    IF @@ERROR <> 0    BEGIN      RAISERROR('Employee duplicate record couldn't be created.', 1, 1)      INSERT INTO tblErrorLog (ErrType, Msg)      VALUES (NULL, 'Employee duplicate record couldn''t be created.')      IF @@ERROR <> 0      BEGIN        RAISERROR('Employee duplicate record couldn '' t be created.', 1, 1)      END    END 

The only change we've made is that we're now attempting to insert NULL into the ErrType field, which does not allow nulls. Let's look at the results in Query Analyzer after we've run Step 2 again.

 Server: Msg 2601, Level 14, State 3, Procedure InsertEmployee, Line 4 Cannot insert duplicate key row in object 'Employees' with unique index 'IX_Employees'. The statement has been terminated. Msg 50000, Level 1, State 50000 Employee duplicate record couldn't be created. Server: Msg 515, Level 16, State 2, Procedure InsertEmployee, Line 8 Cannot insert the value NULL into column 'ErrType', table 'Northwind.dbo.tblErrorLog'; column does not allow nulls. INSERT fails. The statement has been terminated. Msg 50000, Level 1, State 50000 Error log couldn't be updated. 

Notice that four errors are now raised: the original error 2601, the Employee duplicate record couldn't be created error, error 515, and the Error log couldn't be updated error.

Handling Errors in .NET

When an error occurs, it's important to give your application's users as much information as possible, so that they can handle it more effectively. To do this, you need error messages that are as descriptive as possible within your .NET code. This is where RAISERROR really shines. In the examples above, we took a relatively cryptic error and raised another one that was much more descriptive and specific to our application.

When an error occurs within the database during execution via ADO.NET, a .NET exception of type System.Data.SqlClient.SqlException occurs. Before we get into an example, let's look at some of the members that System.Data.SqlClient.SqlException provides that override or augment those that it inherits from the Exception class.

Member

Description

Class

The severity of the first error that occurred during execution of the SQL statement.

Errors

An instance of the SqlErrorCollection class that contains SqlError objects. As you'll see later in this section, SQL can return more than one error message - when it comes across some errors, it continues with execution, meaning that more than one error condition is possible. You can use this collection to enumerate through all of them.

Message

The text of the errors that occurred. If multiple errors occurred, all of the text is appended, with carriage returns between them.

Number

The message ID for the first error in the Errors collection. If you specified the error via the SQL RAISERROR statement, the Number will default to 50000.

State

This is the value of the state for the first error in the Errors collection.

Try It Out - ASP.NET with RAISERROR

start example
  1. Let's create an ASP.NET example that actually shows the error messages created by the most recent version of our InsertEmployee stored procedure on a web page. Call this new file Use_Raiserror.aspx.

     <%@ Import namespace="system.data" %> <%@ Import namespace="system.data.sqlclient" %> <script language="vb" runat="server"> Private Sub Page_Load(ByVal Source As object, ByVal E As EventArgs)   Dim sql As String = "InsertEmployee"   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   Dim conn As New SqlConnection(strConnection)   conn.Open()   Dim cmd As New SqlCommand(sql, conn)   cmd.CommandType = CommandType.StoredProcedure   cmd.Parameters.Add("@FirstName", "Jane")   cmd.Parameters.Add("@LastName", "Zerdeni")   Try     cmd.ExecuteNonQuery()   Catch ex As System.Data.SqlClient.SqlException     Response.Write("- SQLException -<br/>")     Response.Write("Message: ")     Response.Write(replace(ex.Message, controlchars.lf, "<br/>") & "<br>")     Response.Write("Class: " & ex.class & "<br/>")     Response.Write("State: " & ex.state & "<br/>")     Response.Write("<br/>- Errors collection -<br/>")     Dim sErr As SqlError     For Each sErr In ex.Errors       Response.Write("#: " & sErr.Number & " - Class: ")       Response.Write(sErr.Class & " - State: " & sErr.State)       Response.Write(" - Message: ")       Response.Write(replace(sErr.Message, controlchars.lf, "<br/>"))       Response.Write("<br/><br/>")     Next   End Try   cmd.Connection.Close() End Sub </script> 

  2. Run the example twice. Here are the results you'll see when trying to insert the user during the second execution attempt.

    click to expand

end example

How It Works

Taking all of the familiar code as read, we catch the SqlException through this line:

       Catch ex As System.Data.SqlClient.SqlException 

Now we have the SqlException within the variable ex, and the next lines can display the state, the severity (the member that returns the severity is called class), and the message of the SqlException.

       Response.Write("- SQLException -<br/>")       Response.Write("Message: ")       Response.Write(replace(ex.Message, controlchars.lf, "<br/>") & "<br>")       Response.Write("Class: " & ex.class & "<br/>")       Response.Write("State: " & ex.state & "<br/>") 

While using the SqlException object is enough to know that an error occurred, it's not enough to know the exact details of each error. There are times when knowing the severity and state of each error, and not just the first one, is important. For that, you have to enumerate through the Errors collection:

       Dim sErr As SqlError       For Each sErr In ex.Errors         Response.Write("#: " & sErr.Number & " - Class: ")         Response.Write(sErr.Class & " - State: " & sErr.State)         Response.Write(" - Message: ")         Response.Write(replace(sErr.Message, controlchars.lf, "<br/>"))         Response.Write("<br/><br/>")       Next 

For each error, the error number, severity, state, and message are returned, and you can use this information to display friendly error information to your users. Let's clean up the example above so that it displays only the custom error messages that we generate via RAISERROR in the InsertEmployee stored procedure. The changes to the code are highlighted.

    <%@ Import namespace="system.data" %>    <%@ Import namespace="system.data.sqlclient" %>    <script language="vb" runat="server">    Private Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)      Dim sql As String = "InsertEmployee"      Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")      Dim conn As New SqlConnection(strConnection)      Response.Write("Attempting to add an employee to the database...<br>")      conn.Open()      Dim cmd As New SqlCommand(sql, conn)      cmd.CommandType = CommandType.StoredProcedure      cmd.Parameters.Add("@FirstName", "Jane")      cmd.Parameters.Add("@LastName", "Zerdeni")      Try        cmd.ExecuteNonQuery()      Catch ex As System.Data.SqlClient.SqlException        Dim sErr As SqlError        For Each sErr In ex.Errors          If sErr.Number = 50000 Then            Response.Write(sErr.Message & "<br/>")          End If        Next      End Try      cmd.Connection.Close()    End Sub    </script> 

The results are shown below.

click to expand

This time, when errors occur, we enumerate through each one and display only those with a message ID of 50000. As mentioned previously, this is the message ID that's generated when you specify a custom message string using RAISERROR. This is our way of knowing that these are the friendly error messages.

         For Each sErr In ex.Errors           If sErr.Number = 50000 Then             Response.Write(sErr.Message & "<br/>")           End If         Next 

In this example, we're simply showing the user what went wrong. In your production code, you should try to do more. For instance, you'd probably want to allow them to try the operation again, or make changes to their data. In fact, in a production application, you wouldn't want execution to get to the point where it tries to insert a duplicate record. Instead, you should verify that no duplicate exists prior to attempting the insert.



Beginning ASP. NET 2.0 and Databases
Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)
ISBN: 0471781347
EAN: 2147483647
Year: 2004
Pages: 263

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