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 theSP0210_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 atry
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 ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.