Are exceptions thrown reliably when using Ado.Net stored procedures?

Question

If I try and call a stored procedure and there is a database error, will that raise as an exception in my C# code? Or do I need to check the result of the stored procedure and raise an exception myself?

Eg:

using (SqlCommand cmd = new SqlCommand("prc_InsertSomething", conn))
{
    if (cmd.ExecuteNonQuery() != 1) // should I be doing this bit or not?
    {
        throw new DataException("Could not insert something");
    }
}

Thanks

Answer 1

It depends on the error, but normally, if a stored procedure fails it will cause a SqlException to be thrown.

If you are not catching the error/exception in the stored procedure itself, it will bubble out through the database provider.

Answer 2

I really depends on the severity of the errror. Check out the following link:

http://msdn.microsoft.com/en-us/library/ms177497.aspx

A RAISERROR severity of 11 to 19 executed in the TRY block of a TRY…CATCH construct causes control to transfer to the associated CATCH block.

Specify a severity of 10 or lower to return messages using RAISERROR without invoking a CATCH block. PRINT does not transfer control to a CATCH block.

So it will depend on the severity of the error that is thrown by your stored procedure whether or not it caught in the exception handler of the calling code.

Answer 3

For ExecuteNonQuery, you will generally be OK as long as the severity is high enough; however, be careful if you are reading data; for example, if you have a stored procedure that returns multiple grids and throws an exception (for example) just before the penultimate result-set, then if you don't iterate over the data you may never see the error.

This is because the error is injected into the TDS stream (not out-of-band). You need to consume the TDS at least as far as the error for your code to become aware of it.

Something as simple as (in the case of IDataReader):

while (reader.NextResult()) { }

after your consuming code will ensure you consume the entire inbound TDS stream.