Capture Stored Procedure print output in .NET
Is it possible to capture print output from a T-SQL stored procedure in .NET?
I have a lot of legacy procs that use the print as means of errorMessaging. An example, is it possible to access the outprint 'word' from following PROC?
-- The PROC
CREATE PROC usp_PrintWord AS
PRINT 'word'
// Some C# Code to would like to pull out 'word'
SqlCommand cmd = new SqlCommand("usp_printWord", TheConnection);
cmd.CommandType = CommandType.StoredProcedure;
// string ProcPrint = ???
You can do this by adding an event handler to the InfoMessage event on the connection.
myConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);
void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
myStringBuilderDefinedAsClassVariable.AppendLine(e.Message);
}
This is really handy if you want to capture Print output in LinqPad's output console:
SqlConnection conn = new SqlConnection(ConnectionString);
//anonymous function to dump print statements to output console
conn.InfoMessage += (object obj, SqlInfoMessageEventArgs e)=>{
e.Message.Dump();
};
To get the output into a variable:
string printOutput = "";
using (var conn = new SqlConnection(...))
{
// handle this event to receive the print output
conn.InfoMessage += (object obj, SqlInfoMessageEventArgs e) => {
printOutput += e.Message;
};
// execute command, etc.
}
Console.Write(printOutput);