ADO.NET Cookbook

2.10. 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 ...