What are advantages of capturing the Infomessages of SQL connections?

Question

I'm currently reviewing/redoing code of a collegue of mine and stumbled upon a construct I've never seen done before:

con = new SqlConnection("Data Source=.....");
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += new SqlInfoMessageEventHandler(myInfoMessage);

With:

void myInfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    foreach (SqlError err in e.Errors) 
    {
        errors += err.LineNumber.ToString() + ": " + err.Message + "\r\n";
    }
}

Now as far as I understand it all but really severe errors can be captured this way (17+ class errors not as they will stay exceptions). Now what I get is that in some cases you could not want to have an exception thrown and catched, but instead want it as an info message (or a flag set). In this case I see this as a useful construct.

In the case of the code I'm revieweing the errors is immediately checked after the sql command is executed and then an exception thrown with it as text.

This got me to wonder if I'm correctly seeing it that this negates the only advantage I could see of capturing the infomessage in the first place.

So my question here is: what are the advantages of capturing the info messages of SQL connections?

Or with other words, is the one advantage I saw the only one or am I overlooking additional ones there?

Answer 1

It is not pointless.

As you mentioned, a SQL error with a severity level of 17 or above, will throw an exception, which stops the execution of the SQL command. Using a try catch block you can capture that exception.

But if the severity is less than 17, the execution of command does not stop and SQL is not throwing any exceptions. Using InfoMessage, you can capture those warnings and information messages from the SQL server.

Now the magic of your code is in this statement:

con.FireInfoMessageEventOnUserErrors = true;

If you want to continue processing the rest of the statements in a command regardless of any errors produced by the server, you set the FireInfoMessageEventOnUserErrors property of the SqlConnection to true. Doing this causes the connection to fire the InfoMessage event for errors instead of throwing an exception and interrupting processing.

For more information see the

Handling Errors as InfoMessages

section in this MSDN article:https://msdn.microsoft.com/en-us/library/a0hee08w(v=vs.110).aspx