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
– SamFisher83, Commented Mar 15, 2012 at 16:25
+1: I love questions where I learn something just from reading it. — ChrisLively
– ChrisLively, Commented Mar 16, 2012 at 17:24

millie · Accepted Answer · 2012-03-16 17:20:26Z

4

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, 2012 at 17:20

millie

2,90511 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brain2000 Over a year ago

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.

Marcin · Accepted Answer · 2012-03-16 16:11:05Z

1

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

answered Mar 16, 2012 at 16:11

Marcin

3,2935 gold badges34 silver badges50 bronze badges

1 Comment

millie Over a year ago

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().

Collectives™ on Stack Overflow

Cannot detect SQL error when using ExecuteNonQuery()

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related