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?
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With