RAISERROR―How to distinguish with SqlException?

c# wcf sql-server-2005 sqlexception raiserror
User picture User · Jun 28, 2011 · Viewed 9.6k times · Source

I have some 3-4 stored procedures ― which I can modify if needed ― that use RAISERROR to inform my application of some fatal errors on the database side. Some of these stored procedures are executed from the C# side with ExecuteNonQuery, while other are executed with ExecuteReader. At the moment, I am wrapping these command in a try { ... } catch (SqlException ThisSqlException) { ... } block, but the problem is that this exception will be thrown in at least two scenarios I must deal with separately:

1) Errors with the connection itself, or with faulted or type-mismatched parameters; and

2) Errors that occur whenever I use RAISERROR explicitly.

Since this is a WCF application, I must return to the client application different feedback based on the nature of the exception (whether it was due to a RAISERROR command or not). How can I, then, distinguish between both situations?

Answer

Jeffrey L Whitledge picture Jeffrey L Whitledge · Jun 28, 2011

The RAISERROR command includes a msg_id parameter, which can be used to identify the type of error. This value is supplied to the application through the SqlException.Number property. In this way, you can identify any exception raised by a stored procedure that includes a custom error message that is defined in the system.

If RAISERROR is called with a text string error message, then Number will be 50000.

Related questions

Collection was modified; enumeration operation may not execute

I can't get to the bottom of this error, because when the debugger is attached, it does not seem to occur. Collection was modified; enumeration operation may not execute Below is the code. This is a WCF server in a …

Read More
No connection could be made because the target machine actively refused it 127.0.0.1:3446

I'm using the WCF4.0 template -REST. I'm trying to make a method that uploads a file using a stream. The problem always occur at Stream serverStream = request.GetRequestStream(); Class for streaming: namespace LogicClass { public class StreamClass : IStreamClass { public bool UploadFile(…

Read More
The breakpoint will not currently be hit. No symbols have been loaded for this document in a Silverlight application

Ok, what I have: Visual Studio 2010 RC, W7 x64, started a new project type of Silverlight application. Hosting the Silverlight application in a ASP.NET Web Application Project. Silverlight Version 3.0. Added a LinqToSQL Class, a WCF Service, a Winform Tester …

Read More