我们写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文件中就是八仙过海,各显神通了。