Access InfoMessages for SQL Server Queries in C #

Question

I am trying to read the messages that SQL Server usually returns to SSMS in the message tab. In particular, information from SET STATISTICS IOand TIME. The code below works, but does not really give this result. Any help is appreciated. Thank.

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 result:

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

Ultimately, the resolution was that the Fill method did not call the InfoMessage method, so I could not commit the messages. I found another post on StackOverflow that addresses this reasoning. Below is the working code. fooobar.com/questions/1740489 / ...

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



        }

    }
}

Answer 2

,

SET STATISTICS IO ON

... io, , StatisticsEnabled SqlConnection...

... ... , SET STATISTICS TIME ON SELECT..., InfoMessage ... ...

ps: , ( " " )... , ...