Accessing InfoMessages for SQL Server queries in C#

Question

I am trying to read the messages that SQL Server normally returns in SSMS in the messages tab. Specifically information from the SET STATISTICS IO and TIME. The code below works but does not actually give this output. Any help is greatly appreciated. Thank you.

using System;
using System.Collections;
using System.Data.SqlClient;
using System.Data;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            var cn2 = new MedusaPerf.ConnectionStringBuilder().GetTrustedConnectionString("localhost", "AdventureWorks2012", false);
            //var cn2 = new MedusaPerf.ConnectionStringBuilder().GetStandardConnectionString("localhost", "AdventureWorks2012", "testuser", "pass", false);
            string infoMessageText = "";

            try
            {
                var cmd = "SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT TOP(5) DatabaseLogID, PostTime, Event FROM [dbo].[DatabaseLog];";
                cn2.StatisticsEnabled = true;
                //cn2.InfoMessage += new SqlInfoMessageEventHandler(InfoMessageHandler);
                cn2.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
                    {
                        infoMessageText += e.Message.ToString();
                    };
                var daDataOutput = new SqlDataAdapter(cmd, cn2);

                DataTable dtOutput = new DataTable();
                daDataOutput.Fill(dtOutput);

                foreach (DataRow i in dtOutput.Rows)
                {
                    string dataRowOutput = "";

                    for (int j = 0; j < dtOutput.Columns.Count; j++)
                    {
                        dataRowOutput = dataRowOutput + i[j].ToString();
                    }

                    Console.WriteLine(dataRowOutput);
                }

                IDictionary d = cn2.RetrieveStatistics();
                string[] keys = new string[d.Count];
                d.Keys.CopyTo(keys,0);

                for (int x = 0; x < d.Count; x++)
                {
                    Console.WriteLine("{0}\t{1}",keys[x], (long)d[keys[x]]);
                }

                Console.WriteLine("Success ");
            }
            catch (Exception)
            {
                throw;
            }

            Console.WriteLine(infoMessageText);
            Console.WriteLine("Hit Enter to Continue");

            System.Console.ReadKey();
        }

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

Here is the output:

13/14/2012 1:14:18 PMCREATE_TABLE
23/14/2012 1:14:18 PMALTER_TABLE
53/14/2012 1:14:18 PMCREATE_TYPE
63/14/2012 1:14:18 PMCREATE_TYPE
213/14/2012 1:14:19 PMCREATE_XML_SCHEMA_COLLECTION
ExecutionTime   46
UnpreparedExecs 1
SelectRows      5
Prepares        0
BuffersSent     1
PreparedExecs   0
SelectCount     2
IduRows 0
BytesReceived   911
Transactions    0
IduCount        0
ServerRoundtrips        1
CursorOpens     0
SumResultSets   1
NetworkServerTime       0
ConnectionTime  0
BytesSent       262
BuffersReceived 1
Success

Hit Enter to Continue

Answer 1

first of all I don't think the

SET STATISTICS IO ON

is needed ... io statistics seem to be controlled be the StatisticsEnabled property of the SqlConnection class ...

the thing with the time statistics is really strange ... I had the same problem ... I found out that when you insert a print statement between SET STATISTICS TIME ON and SELECT ... the handler for InfoMessage gets called like it should be ... once for the print statement and once for the statistics ...

ps: tried to put the complete statement here but could not submit ("an error occurred submitting the answer") ... hope you can find out yourself ...

Answer 2

The resolution ultimately was that Fill method does not trigger InfoMessage method thus I was unable to capture the messages. I found another post on StackOverflow that addresses this reasoning. Below is the working code. https://stackoverflow.com/a/2914981/62511

using System;
using System.Collections;
using System.Data.SqlClient;
using System.Data;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            var cn2 = new MedusaPerf.ConnectionStringBuilder().GetTrustedConnectionString("localhost", "AdventureWorks2012", false);
            //var cn2 = new MedusaPerf.ConnectionStringBuilder().GetStandardConnectionString("localhost", "AdventureWorks2012", "testuser", "pass", false);
            string infoMessageText = "";

            var cmd = "SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT DatabaseLogID, PostTime, Event FROM [dbo].[DatabaseLog];";
            cn2.StatisticsEnabled = true;
            cn2.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
            {
                infoMessageText += e.Message.ToString();
            };

            cn2.Open();


            try
            {
                SqlCommand comm = new SqlCommand(cmd, cn2);
                comm.ExecuteNonQuery();



                IDictionary d = cn2.RetrieveStatistics();
                string[] keys = new string[d.Count];
                d.Keys.CopyTo(keys, 0);

                for (int x = 0; x < d.Count; x++)
                {
                    Console.WriteLine("{0}\t{1}", keys[x], (long)d[keys[x]]);
                }
                Console.WriteLine("Success ");

            }
            catch (Exception)
            {

                throw;
            }

            //Console.Write(conn_InfoMessage());

            cn2.Close();
            Console.WriteLine(infoMessageText);
            Console.WriteLine("Hit Enter to Continue");
            System.Console.ReadKey();



        }

    }
}