C# - Capture SELECT and PRINT statements from Stored Procedure to String Variable

I am trying to call a stored procedure in C# that contains both SELECT statements and PRINT statements. I've managed to get the PRINT statements back by way of a SqlInfoMessageEventHandler, but that doesn't help since there are SELECT statements mixed in that contain information pertaining to the PRINT statements.

The other fun part of this is that the SELECT statements all have varying #s and names of columns.

All I really want to do is capture all of that output into a string variable (kinda like executing the stored procedure in SSMS with the query results being output as text.

Changing the Stored Procedure isn't possible.

Is this possible?
street9009IT Project ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
Yes it is. Use a dataset to read the return of your SP and each SELECT statement result will be a datatable?

How do you execute your SP today?
Éric MoreauSenior .Net ConsultantCommented:
your code should look like:
    DataSet ds = new DataSet("TimeRanges");
    using(SqlConnection conn = new SqlConnection("ConnectionString"))
    {               
            SqlCommand sqlComm = new SqlCommand("Procedure1", conn);               
            sqlComm.Parameters.AddWithValue("@Start", StartTime);
            sqlComm.Parameters.AddWithValue("@Finish", FinishTime);
            sqlComm.Parameters.AddWithValue("@TimeRange", TimeRange);

            sqlComm.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = sqlComm;

            da.Fill(ds);
     }

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
street9009IT Project ManagerAuthor Commented:
            SqlCommand command = new SqlCommand("SPNAME", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlParameter p = new SqlParameter("@Variable", Variable);
            p.SqlDbType = SqlDbType.VarChar;
            connection.InfoMessage += new SqlInfoMessageEventHandler(CaptureMessage);
            command.Parameters.Add(p);
            command.ExecuteNonQuery();

Open in new window


I had tried to use a dataset but it only seemed to return the first PRINT statement and then stop.
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Éric MoreauSenior .Net ConsultantCommented:
ExecuteNonQuery is not returning anything. Have a look at the sample I provided earlier
street9009IT Project ManagerAuthor Commented:
I'm testing it now. I switched to ExecuteNonQuery due to the InfoMessage Event Handler I was using (that's working fine for the PRINT outputs).
hilltopCommented:
using System.Data.SqlClient;

Open in new window


SqlConnection Conn = new SqlConnection("Server=yourserver;Database=testdb;UID=user;PWD=password;");
Conn.Open();

            SqlCommand Lookcmd = new SqlCommand("yourproc", Conn);
            SqlDataReader Lookread = Lookcmd.ExecuteReader();
            Object[] values = new Object[Lookread.FieldCount];
            int fieldCount = Lookread.GetValues(values);
            for (int i = 0; i < fieldCount; i++)
            {
                Console.WriteLine(values[i]); //build your string here
            }
            Lookread.Close();
            Lookcmd.Dispose();

Open in new window

Éric MoreauSenior .Net ConsultantCommented:
You can retrieve the output of PRINT and also the results of SELECT. Have a look at https://www.emoreau.com/Entries/Articles/2011/10/Retrieving-SQL-Print-statement-from-a-Net-Application.aspx
street9009IT Project ManagerAuthor Commented:
hilltop - I'm getting an exception when I run your code (after fixing to work with my DB):

System.InvalidOperationException was unhandled
  HResult=-2146233079
  Message=Invalid attempt to read when no data is present.
  Source=System.Data
  StackTrace:
       at System.Data.SqlClient.SqlDataReader.CheckDataIsReady(Int32 columnIndex, Boolean allowPartiallyReadColumn, Boolean permitAsync, String methodName)
       at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)
       at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
       at APBatchTroubleshooter.frmMain.btnRun_Click(Object sender, EventArgs e) in h:\stephen\Visual Studio 2013\Projects\APBatchTroubleshooter\APBatchTroubleshooter\Form1.cs:line 221
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at APBatchTroubleshooter.Program.Main() in h:\stephen\Visual Studio 2013\Projects\APBatchTroubleshooter\APBatchTroubleshooter\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:


It's pointing at the int fieldCount line.
street9009IT Project ManagerAuthor Commented:
Éric Moreau - I tried your solution and it worked but I only got the first little bit of output, just like before. I need to get the PRINT and SELECT statements in the order they are output.
hilltopCommented:
That may need a while read like this.

            SqlConnection Conn = new SqlConnection("Server=WORK;Database=mintdata;UID=sa;PWD=nnnnnnn2;");
            Conn.InfoMessage += new SqlInfoMessageEventHandler(Conn_InfoMessage);
           // Conn.FireInfoMessageEventOnUserErrors = true;
            Conn.Open();

            SqlCommand Lookcmd = new SqlCommand("yourproc", Conn);
            SqlDataReader Lookread = Lookcmd.ExecuteReader();
            Object[] values = new Object[Lookread.FieldCount];
            while (Lookread.Read()) {
                int fieldCount = Lookread.GetValues(values);
            
            for (int i = 0; i < fieldCount; i++)
            {
                mystring = mystring + values[i].ToString();
            }
            }
            Lookread.Close();
            Lookcmd.Dispose();
             Conn.Close();
            // mystring should have all values and print results
        
        public void Conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            mystring = mystring + e.Message;
        }

Open in new window

street9009IT Project ManagerAuthor Commented:
hilltop - OK, that worked just like Éric Moreau's code. I only got basically the first SELECT output, nothing more.
hilltopCommented:
Is your proc only returning one row? I just made that and tested on multiple rows.
street9009IT Project ManagerAuthor Commented:
No. The proc has many SELECT and PRINT statements in it.

IF (@Debug = 1)
  BEGIN
    SELECT 'Debug...', CONVERT(varchar(30), GETDATE(), 113)
    SELECT 'Version 4.60'
  END

Open in new window


There is more but this will illustrate what I'm receiving back. I'm getting the result of the first SELECT only.
hilltopCommented:
Can you try adding the below text right before the closing } of your while read loop?
Lookread.NextResult();

Open in new window


Like
SqlCommand Lookcmd = new SqlCommand("yourproc", Conn);
            SqlDataReader Lookread = Lookcmd.ExecuteReader();
            Object[] values = new Object[Lookread.FieldCount];
            while (Lookread.Read()) {
                int fieldCount = Lookread.GetValues(values);
            
            for (int i = 0; i < fieldCount; i++)
            {
                mystring = mystring + values[i].ToString() + ",";
            }
            Lookread.NextResult();
            }
            Lookread.Close();
            Lookcmd.Dispose();

Open in new window


Stored Procedures with Multiple Result Sets
https://docs.microsoft.com/en-us/ef/ef6/modeling/designer/advanced/multiple-result-sets
Éric MoreauSenior .Net ConsultantCommented:
A dataset can contain multiple datatables (one for each SELECT). have you loop through the datatables collection?
street9009IT Project ManagerAuthor Commented:
hilltop - that did a little better. I got a few more lines but still not anywhere near everything.

Éric Moreau - yes, I looped through all datatables and all rows in each table:

        private String DatasetToString(DataSet ds)
        {
            int i = 0;
            int j = 0;
            String str = "";

            while (j < ds.Tables.Count)
            {
                DataTable dt = ds.Tables[j];
                while (i < dt.Rows.Count)
                {
                    str += ";" + dt.Rows[i][1].ToString() + ";" + dt.Rows[i][0].ToString();
                    i++;
                }
                j++;
            }
            return (str);
        }

Open in new window

Éric MoreauSenior .Net ConsultantCommented:
I am a bit lost now? What do you get in your string and what are you expecting?

If you place a breakpoint in your code on the first line of your DatasetToString method, inspect your ds variable. Do you see all your tables and your data in there?
street9009IT Project ManagerAuthor Commented:
Éric Moreau - I figured out most of the problem. The DatasetToString had errors in it. I re-coded it to the below and I'm getting a lot of output now:

        private String DatasetToString(DataSet ds)
        {
            String str = "";

            for (int j = 0; j < ds.Tables.Count; j++)
            {
                DataTable dt = ds.Tables[j];
                for (int i = 0; i < dt.Rows.Count;i++)
                {
                    for (int k = 0; k < dt.Rows[i].ItemArray.Count(); k++)
                    {
                        str += dt.Rows[i][k].ToString() + "|";
                    }
                    str += Environment.NewLine;
                }
            }
            return (str);
        }

Open in new window


Now my only thing is the table outputs don't have headers on them. Is that correctable?
Éric MoreauSenior .Net ConsultantCommented:
what do you want for your headers? from what I see in your SP, you don't even have columns names!
street9009IT Project ManagerAuthor Commented:
They're in there, but they vary. There are a lot of Select * statements from various tables.
hilltopCommented:
Here is a tested solution. Sorry, I should have done this earlier. I added a comma delimiter, and you should have an ordered string. Print and select values should fall in order etc..

using System.Data.SqlClient;

Open in new window


public string mystring = "";
            SqlConnection Conn = new SqlConnection("Server=WORK;Database=test;UID=sam;PWD=sdfsdfsd;");
            Conn.InfoMessage += new SqlInfoMessageEventHandler(Conn_InfoMessage);
            Conn.Open();

            SqlCommand Lookcmd = new SqlCommand("yourproc", Conn);
            SqlDataReader Lookread = Lookcmd.ExecuteReader();
            Object[] values = new Object[Lookread.FieldCount];
            while (Lookread.HasRows)
                while (Lookread.Read())
            {
                int fieldCount = Lookread.GetValues(values);
            
            for (int i = 0; i < fieldCount; i++)
            {
                mystring = mystring + values[i].ToString() + ",";
              
            }
            Lookread.NextResult();
            }
            Lookread.Close();
            Lookcmd.Dispose();

          
        }

Open in new window


        public void Conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            mystring = mystring + e.Message + ",";
        }

Open in new window

street9009IT Project ManagerAuthor Commented:
hilltop - that appears to get some of it, but I don't see any results of "SELECT *" statements in the string when it's finished.
street9009IT Project ManagerAuthor Commented:
Thank you both for your help. While what was provided did work in some capacity, it wasn't everything I needed (apparently what I was trying to do isn't completely possible). Éric Moreau's solution was cleanest so I marked his answer as the solution. hilltop's last post was also helpful so I marked it as such.

Thank you both again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.