How to log SQL output to text file on client from C#

I have a large auditing stored procedure that prints values and runs some 开发者_运维问答SELECT statements. When running within SQL Management Studio we have the use select to display "Results to Text" so all of the SQL results and print statement display in one place.

Now I need to have some C# code also call this auditing procedure at the end of the process and basically store all data that would be in the "Results to Text" window into a .txt file.

How can this be done?

---

You can get the PRINT output but not in the same way you get the resultsets. If your output assumes a specific sequence of PRINT and resultset data, this isn't really possible.

You can get the PRINT output via a callback/eventhandler:

SqlConnection c ...
c.InfoMessage += new SqlInfoMessageEventHandler(cb_Msg);

...

void cb_Msg(object sender, SqlInfoMessageEventArgs e)
{
    // e.Message has a line of PRINT output
}

When you execute something against that SqlConnection, it will call that callback for each line of printed output.

---

You would be better off adding an output parameter to the sp and putting the text output in that. Then when you call the sp in your code you get your resultset and the text seperately and you can display them however you want.