在C＃中访问SQL Server查询的InfoMessages

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.

我试图在消息选项卡中读取SQL Server通常在SSMS中返回的消息。具体来自SET STATISTICS IO和TIME的信息。下面的代码有效但实际上并没有给出这个输出。任何帮助是极大的赞赏。谢谢。

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

2 个解决方案

#1

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 * that addresses this reasoning. Below is the working code. https://*.com/a/2914981/62511

最终解决方案是Fill方法不会触发InfoMessage方法,因此我无法捕获消息。我发现*上的另一篇文章解决了这个问题。以下是工作代码。 https://*.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();



        }

    }
}

#2

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

需要... io统计似乎被控制为SqlConnection类的StatisticsEnabled属性...

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

时间统计的东西真的很奇怪...我有同样的问题......我发现当你在SET STATISTICS TIME ON和SELECT之间插入一个print语句时,InfoMessage的处理程序被调用就像它应该是...一次用于打印声明,一次用于统计...

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

ps:试图把完整的声明放在这里,但无法提交(“提交答案时出错”)...希望你能自己找到...