使用 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文件中就是八仙过海，各显神通了。