RAISERROR―How to distinguish with SqlException?

Question

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 1

When you catch a SqlException, you can inspect its Errors collections which contains the detailed error messages.

Those SqlError objects contained very detailed information - including error code, message and so forth.

Using that information, you should be able to easily distinguish between connection-based errors, or errors that your raise yourself.

Answer 2

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.