Access to SQL Server messages via ADO.NET

Question

Is it possible to access the SQL Server "by-product messages" via ADO.NET? Due to the lack of words, by "by-product messages" I mean the output which appears in the Messages tab in Microsoft SQL Server Management Studio. What I particularly have it mind is to read the output of SET STATISTICS TIME ON. It appears that SqlDataReader does not offer anything in this matter.

Answer 1

Yes, there's an event hook on the SqlCommand object called SqlInfoMessage, which you can hook into:

SqlConnection _con = 
   new SqlConnection("server=.;database=Northwind;integrated Security=SSPI;");

_con.InfoMessage += new SqlInfoMessageEventHandler(InfoMessageHandler);

The event handler will look like this:

static void InfoMessageHandler(object sender, SqlInfoMessageEventArgs e)
{
    string myMsg = e.Message;            
}

The "e.Message" is the message printed out to the message window in SQL Server Mgmt Studio.

Marc

Answer 2

Thank you for the response above. I just did a little experiment and found out a little unexpected glitch (a bug?) when reading messages (in this case produced by SET STATISTICS TIME ON) from a multi recordset result. As indicated below, one has to call NextResult even after the last result in order to get the last message. This does not needed in the case of a single recordset result.

using System;
using System.Data.SqlClient;

namespace TimingTest
{
    class Program
    {

        static void Main(string[] args)
        {

            SqlConnection conn = new SqlConnection("some_conn_str");
            conn.Open();

            conn.InfoMessage += new SqlInfoMessageEventHandler(Message);

            SqlCommand cmd = new SqlCommand("some_sp", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read()) { };

            rdr.NextResult();

            while (rdr.Read()) { };

            // this is needed to print the second message
            rdr.NextResult();

            rdr.Close();

            conn.Close();

        }

        static void Message(object sender, SqlInfoMessageEventArgs e)
        {
            Console.Out.WriteLine(e.Message);
        }

    }
}