Raising and Handling Stored Procedure Errors

Problem

You want to catch and handle an error raised from a stored procedure.

Solution

Use a try . . . catch block to catch serious errors. Use the SqlConnection.InfoMessage event handler to catch informational and warning messages.

The sample code, as shown in Example 2-11, uses a single stored procedure and two event handlers:

SP0210_Raiserror

Accepts two input parameters specifying the severity and the state of an error and raises an error with the specified severity and state.

Raise Error Button.Click

Creates a connection and attaches a handler for warning and information messages from the SQL Server. A Command is created for the SP0210_Raiserror stored procedure and the input parameters are defined. The user -specified severity and state are assigned to the input parameters and the stored procedure command is executed within a try statement.

SqlConnection.InfoMessage

Called when a warning or informational message is raised by the SQL Server.

Example 2-11. Stored procedure: SP0210_Raiserror

CREATE PROCEDURE SP0210_Raiserror
 @Severity int,
 @State int = 1
AS
 if @Severity>=0 and @Severity <=18
 RAISERROR ('Error of severity %d raised from SP 0210_Raiserror.', @Severity, 
 @State, @Severity)
 
 if @Severity>=19 and @Severity<=25
 RAISERROR ('Fatal error of severity %d raised from SP 0210_Raiserror.',
 @Severity, @State, @Severity) WITH LOG
 
 RETURN

The C# code is shown in Example 2-12.

Example 2-12. File: RaiserrorForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

// . . . 

private void raiseErrorButton_Click(object sender, System.EventArgs e)
{
 resultTextBox.Text =
 "Severity: " + severityTextBox.Text + Environment.NewLine +
 "State: " + stateTextBox.Text + Environment.NewLine +
 Environment.NewLine;

 // Create the connection.
 SqlConnection conn = new SqlConnection(
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);
 // Attach handler for SqlInfoMessage events.
 conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
 
 // Define a stored procedure command and the parameters.
 SqlCommand cmd = new SqlCommand("SP0210_Raiserror", conn);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@Severity", SqlDbType.Int);
 cmd.Parameters.Add("@State", SqlDbType.Int);
 // Set the value for the stored procedure parameters.
 cmd.Parameters["@Severity"].Value = severityTextBox.Text;
 cmd.Parameters["@State"].Value = stateTextBox.Text;

 // Open the connection.
 conn.Open( );
 try
 {
 // Try to execute the stored procedure.
 cmd.ExecuteNonQuery( );
 }
 catch(System.Data.SqlClient.SqlException ex)
 {
 // Catch SqlException errors.
 resultTextBox.Text += "ERROR: " + ex.Message;
 }
 catch(Exception ex)
 {
 // Catch other errors.
 resultTextBox.Text += "OTHER ERROR: " + ex.Message;
 }
 finally
 {
 // Close the connection.
 conn.Close( );
 }
}

private void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
 resultTextBox.Text += "MESSAGE: " + e.Message;
}

Discussion

Errors and messages are returned from a SQL Server stored procedure to a calling application using the RAISERROR (note the spelling) function. The error message severity levels are listed in Table 2-12.

Table 2-12. RAISERROR error message severity levels

Severity level

Description

0-10

Informational

11-16

Error which can be corrected by the user

17-19

Resource or system error

20-25

Fatal error indicating a system problem

Severity levels greater than 20 result in the connection being closed.

Since severity levels 10 or less are considered to be informational, they raise a SqlInfoMessageEvent rather than an error. This is handled by subscribing a SqlInfoMessageEventHandler to the InfoMessage event of the SqlConnection object.

If the error has severity level 11 or greater, a SqlException is thrown by the SQL Server .NET data provider.

For more information about the RAISERROR function, look up RAISERROR in SQL Server Books Online.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax



ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton

Similar book on Amazon

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