访问SQL Server的查询InfoMessages在C＃ [英] Accessing InfoMessages for SQL Server queries in C#
本文介绍了访问SQL Server的查询InfoMessages在C＃的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想读的SQL Server通常返回SSMS中的邮件选项卡中的信息。从 SET STATISTICS IO具体信息和时间。下面的代码工作，但实际上并没有给这个输出。任何帮助是极大的赞赏。谢谢
使用系统; System.Collections中使用
;
使用System.Data.SqlClient的;
使用System.Data这;
命名空间ConsoleApp
{
类节目
{
静态无效的主要（字串[] args）
{
变种CN2 =新MedusaPerf.ConnectionStringBuilder（）GetTrustedConnectionString（本地主机，AdventureWorks2012，假）。
// VAR CN2 =新MedusaPerf.ConnectionStringBuilder（）GetStandardConnectionString（本地主机，AdventureWorks2012，testuser的，通行证，假）。
串infoMessageText =;
试
{
VAR CMD =SET统计IO ON; SET STATISTICS TIME ON; SELECT TOP（5）DatabaseLogID，发表时间，事件FROM [DBO] [DatabaseLog] ;;
cn2.StatisticsEnabled = TRUE;
//cn2.InfoMessage + =新SqlInfoMessageEventHandler（InfoMessageHandler）;
cn2.InfoMessage + =委托（对象发件人，SqlInfoMessageEventArgs E）
{
infoMessageText + = e.Message.ToString（）;
};
变种daDataOutput =新SqlDataAdapter的（CMD，CN2）;
的DataTable dtOutput =新的DataTable（）;
daDataOutput.Fill（dtOutput）;
的foreach（DataRow的我dtOutput.Rows）
{
串dataRowOutput =;
为（INT J = 0; J< dtOutput.Columns.Count; J ++）
{
dataRowOutput = dataRowOutput + I [J]的ToString（）;
}
Console.WriteLine（dataRowOutput）;
}
IDictionary的D = cn2.RetrieveStatistics（）;
的String []键=新的字符串[d.Count]
d.Keys.CopyTo（键，0）;
的for（int x = 0; X< d.Count; X ++）
{
Console.WriteLine（{0} \t {1}，钥匙[X]，（长）D [键[X]）;
}
Console.WriteLine（成功）;
}
赶上（例外）
{
扔;
}
Console.WriteLine（infoMessageText）;
Console.WriteLine（敲击回车键继续）;
System.Console.ReadKey（）;
}
静态无效InfoMessageHandler（对象发件人，SqlInfoMessageEventArgs E）
{
串myMsg = e.Message;
Console.WriteLine（e.Message）;
}
}
}
下面是输出：
13/14/2012 1点14分十八秒PMCREATE_TABLE
23/14/2012 1点14分十八秒PMALTER_TABLE
53/14/2012 1时14分十八秒PMCREATE_TYPE
63/14/2012 1时14分十八秒PMCREATE_TYPE
213/14/2012一时14分19秒PMCREATE_XML_SCHEMA_COLLECTION
ExecutionTime 46
UnpreparedExecs 1
SelectRows 5
准备0
BuffersSent 1
PreparedExecs 0
SelectCount 2
IduRows 0
与BytesReceived 911
交易0
IduCount 0
ServerRoundtrips 1
CursorOpens 0
SumResultSets 1
NetworkServerTime 0
ConnectionTime 0
BytesSent 262
BuffersReceived 1
成功
敲击回车键继续
解决方案
的决议最终是Fill方法不会触发InfoMessage方法因此我无法捕捉到的消息。我发现了另一个计算器后，解决这个道理。下面是工作代码。 http://stackoverflow.com/a/2914981/62511
使用系统; System.Collections中使用
;
使用System.Data.SqlClient的;
使用System.Data这;
命名空间ConsoleApp
{
类节目
{
静态无效的主要（字串[] args）
{
变种CN2 =新MedusaPerf.ConnectionStringBuilder（）GetTrustedConnectionString（本地主机，AdventureWorks2012，假）。
// VAR CN2 =新MedusaPerf.ConnectionStringBuilder（）GetStandardConnectionString（本地主机，AdventureWorks2012，testuser的，通行证，假）。
串infoMessageText =;
VAR CMD =SET统计IO ON; SET STATISTICS TIME ON; SELECT DatabaseLogID，发表时间，事件FROM [DBO] [DatabaseLog]。
cn2.StatisticsEnabled = TRUE;
cn2.InfoMessage + =委托（对象发件人，SqlInfoMessageEventArgs E）
{
infoMessageText + = e.Message.ToString（）;
};
cn2.Open（）;
试
{
的SqlCommand COMM =新的SqlCommand（CMD，CN2）;
comm.ExecuteNonQuery（）;
IDictionary的D = cn2.RetrieveStatistics（）;
的String []键=新的字符串[d.Count]
d.Keys.CopyTo（键，0）;
的for（int x = 0; X< d.Count; X ++）
{
Console.WriteLine（{0} \t {1}，钥匙[X]，（长）D [键[X]）;
}
Console.WriteLine（成功）;
}
赶上（例外）
{
扔;
}
//Console.Write（conn_InfoMessage（））;
cn2.Close（）;
Console.WriteLine（infoMessageText）;
Console.WriteLine（敲击回车键继续）;
System.Console.ReadKey（）;
}
}
}
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
解决方案
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. http://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();
}
}
}
这篇关于访问SQL Server的查询InfoMessages在C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
