Cannot detect SQL error when using ExecuteNonQuery()

Question

I have the following bit of code which runs a SQL statement:

int rowsEffected = 0;
using (SqlConnection dbConnection = new SqlConnection(dbConnectionString))
{
    try
    {
        dbConnection.InfoMessage += new SqlInfoMessageEventHandler(dbConnection_InfoMessage);
        dbConnection.FireInfoMessageEventOnUserErrors = true;

        dbConnection.Open();


        SqlCommand command = dbConnection.CreateCommand();
        command.CommandTimeout = 0;
        command.CommandText = sqlStatement;

        rowsEffected = command.ExecuteNonQuery();
    }
    catch (Exception e)
    {
        // Handle exception
    }
}

Long running SQL statements can report progress via the SqlInfoMessageEventHandler by raising an error using RAISERROR with the appropriate severity value.

The reasons for setting FireInfoMessageEventOnUserErrors to true is because, without this, all the messages from the SQL statement are only processed all at once when ExecuteNonQuery() returns. With this value set to true, the progress messages are processed as they are raised by the SQL statement.

As the name of the property suggests, the event handler also fires upon errors and not just on the specific severity level reserved for progress feedback by the SQL statement.

The event handler for the progress feedback looks like this:

public void dbConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    if (e.Errors.Count > 0)
    {
        throw new Exception("Something bad happened");
    }

    // Report progress
}

As you can see, I can detect when an error occurs by the property within 'e' but throwing the exception doesn't do anything. I was hoping execution would drop into the catch block but this isn't the case.

How can I get determine after the ExcecuteNonQuery() method that an error occurred?

TIA

You could run your code in a thread and and call a thread.abort in that function — SamFisher83, Mar 15 '12 at 16:25
+1: I love questions where I learn something just from reading it. — NotMe, Mar 16 '12 at 17:24

score 3 · Accepted Answer · answered Mar 16 '12 at 17:20

3

I managed to figure a work around. I've added a property to the class that contains the event handler. If there was an error, I sent the error flag property in that class which can then be read following the call to ExcecuteNonQuery(). If there was an error then I can do my clean-up code. Thanks for the reply.

answered Mar 16 '12 at 17:20

millie

2,642
10
39
58

I had to do something similar, but for a situation where an outside thread had to access the DB Command object, so I stored the last one used so blocking SQL calls could potentially be checked on or cancelled by another thread. – Brain2000 Jul 14 '15 at 15:52

score 1 · Answer 2 · answered Mar 16 '12 at 16:11

1

Use SqlException class instead of Exception class. And then look into e.Errors

answered Mar 16 '12 at 16:11

Marcin

3,232
4
31
48

Thanks for the answer. I'm not actually using Exception, I'm using a custom Exception but just for the sake of sample code I changed this to Exception. You don't have access to e.Errors in the code that calls ExcecuteNonQuery(). – millie Mar 16 '12 at 17:19

Cannot detect SQL error when using ExecuteNonQuery()

2 Answers2