使用 SqlInfoMessageEventHandler 获取Sql中print或error消息

我们写Sql存储过程时经常要用print打印出一些信息以供我们调试和Debug存储过程。

但是一旦我们的应用程序部署了，为帮助我们快速诊断应用程序的错误，我们想把我们在储过程print的信息记录下来。

这样我们就需要从应用程序中获取Sql print的信息了，这里介绍如何使用SqlInfoMessageEventHandler 获取这些信息（其实我去年写了一篇英文版的http://blog.csdn.net/farawayplace613/article/details/5667245）。

一下是步聚（假设您已经有了Sql 实例数据库Northwind）：

1. 在Northwind中执行如下语句，创建一个用于测试SqlInfoMessageEventHandler 的存储过程（该存储过程只负责打印出一些消息，SqlInfoMessageEventHandler 将处理这些消息）

-- ============================================= -- This procedure is used for testing SqlInfoMessageEventHandler -- ============================================= -- Drop stored procedure if it already exists IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'TestPrintMessage' ) DROP PROCEDURE dbo.TestPrintMessage GO CREATE PROCEDURE dbo.TestPrintMessage AS Print 'Warning: This is a warning message test' Print 'Error: This is a error message ' Print 'Critical: This is critical message' Print 'Verbose: This is verbose message' Print 'Information: This is information message' select 'test' GO

2.创建一个控制台应用程序来调用上面的存储过程，并用SqlInfoMessageEventHandler 处理print消息

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Diagnostics; using System.Data.SqlClient; using System.Data; namespace TestSqlTrace { class Program { static void Main(string[] args) { using (SqlConnection sqlCon = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True")) { sqlCon.InfoMessage += new SqlInfoMessageEventHandler(OnReceivingInfoMessageFromSql); SqlCommand cmd = new SqlCommand("dbo.TestPrintMessage", sqlCon); cmd.CommandType = CommandType.StoredProcedure; sqlCon.Open(); cmd.ExecuteNonQuery(); } Console.WriteLine("Press any key to exist!"); Console.Read(); } /// <summary> /// The event handler for the InfoMessage event. /// </summary> /// <param name="sender">The sender.</param> /// <param name="e">The event arguments.</param> private static void OnReceivingInfoMessageFromSql(object sender, SqlInfoMessageEventArgs e) { string prevLogMessage = String.Empty; Regex traceLevelPattern = new Regex(@"^(Warning|Error|Critical|Verbose|Information):", RegexOptions.IgnoreCase); foreach (SqlError err in e.Errors) { string errMessage = err.Message; string logMessage = errMessage; TraceEventType level = TraceEventType.Verbose; Match m = traceLevelPattern.Match(errMessage); if (m.Success) { try { level = (TraceEventType)Enum.Parse(typeof(TraceEventType), m.Result("$1")); } catch (ArgumentException ae) { Console.WriteLine(ae.Message); } logMessage = errMessage.Substring(m.Result("$1:").Length); } if (prevLogMessage != logMessage) { prevLogMessage = logMessage; Console.WriteLine("level:" + level + "; message:" + logMessage); } } } } }

3. 执行上面的程序

一般会将这些print的消息记录到Log文件中，至于怎么写到Log文件中就是八仙过海，各显神通了。