Trace the sql print message in .net code

The scenario is we usually print some message in the sql PROCEDURE to help us debug the PROCEDURE, but some time once we delopy the database we still want to trace the print message from the sql PROCEDURE to help us debug some bugs. This article will give a intrudce to implemented this scenarios.

1. First we create a sql procedure for test

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'

2. Use the C# code to call the sql procedure and trace these pring messages

SqlConnection sqlCon = new SqlConnection("Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=True");

sqlCon.InfoMessage +=

new SqlInfoMessageEventHandler(OnReceivingInfoMessageFromSql);

SqlCommand cmd = new SqlCommand("dbo.TestPrintMessage");

cmd.CommandType =

CommandType.StoredProcedure;

cmd.ExecuteNonQuery();

3. Code for OnReceivingInfoMessageFromSql

/// <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);

TraceSource tracer = new TraceSource("DBLayer");

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)

{

tracer.TraceEvent(

TraceEventType.Warning,

0,

"Unexpected failure to parse the message trace event type. {0}",

ae.ToString());

}

logMessage = errMessage.Substring(m.Result("$1:").Length);

}

if (prevLogMessage != logMessage)

{

tracer.TraceEvent(level,0," {0}@{1}: {2}",err.Procedure,err.LineNumber,logMessage);

prevLogMessage = logMessage;

}

}

}