Need to get the SQL Server "PRINT" value in C#

Question

I have a SP that Prints the results into SQL Server, but I need to use that value in C#.

Changing the PRINT to SELECT is not an option right now. I tried to SqlCommand.ExecuteScalar() but that didn't work.

Does anybody know if it is possible to get the value of PRINT command in SP redirected to C#?

EXAMPLE:

CREATE PROCEDURE doXYZ
AS
BEGIN
   PRINT 'XYZ'
END

Now in C# I need to get the value 'XYZ'.... any ideas?

Answer 1

You can use the SqlConnection.InfoMessage event.

Answer 2

You can use the SqlConnection.InfoMessage event like so:

using System.Data;
using System.Data.SqlClient;

namespace foo
{
    class bar
    {
        static public void ExecuteStoredProc()
        {
            var connectionString = "Data Source=.;Integrated Security=True;Pooling=False;Initial Catalog=YourDatabaseName";
            using (var connection = new SqlConnection(connectionString))
            using (var command = new SqlCommand("dbo.YourStoredProcedure", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@YourParameterName", "YourParameterValue");

                connection.Open();
                // wire up an event handler to the connection.InfoMessage event
                connection.InfoMessage += connection_InfoMessage;
                var result = command.ExecuteNonQuery();             
                connection.Close();
            }
        }

        static void connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            // this gets the print statements (maybe the error statements?)
            var outputFromStoredProcedure = e.Message;              
        }
    }
}