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?

How are you connecting to the database? Straight up ADO.NET or some sort of an ORM? — Roman
– Roman, Commented Apr 21, 2011 at 21:14
just straight up ADO.NET. SqlConnection with a connectionString and then SQLcommand. USING(SqlConnetion con = new SqlConnection("Server=LOCALHOST; Database=XYZ; Trusted_Connection=Yes;")) — Amin J
– Amin J, Commented Apr 21, 2011 at 21:18
@Akram Shahda: really nice edit, makes the question much more readable, if I could upvote I would =). — Roman
– Roman, Commented Apr 22, 2011 at 4:34

Mark Cidade · Accepted Answer · 2011-04-21 21:52:35Z

24

You can use the SqlConnection.InfoMessage event.

answered Apr 21, 2011 at 21:52

Mark Cidade

100k33 gold badges231 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Partridge Over a year ago

See my answer with working code based on this answer

Tim Partridge · Accepted Answer · 2014-03-07 15:29:07Z

22

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;              
        }
    }
}

edited Mar 7, 2014 at 15:29

answered Mar 7, 2014 at 14:11

Tim Partridge

3,4131 gold badge44 silver badges53 bronze badges

2 Comments

Chad Over a year ago

I'd much prefer a synchronous solution instead of having to handle an event in this case. To accomplish this, I simply converted the PRINT statement to a simple SELECT 'XYZ' AS RetVal and then processed the returned data's first row's first field's value.

tsilb Over a year ago

Doesn't disposing an object without unwiring its events prevent the GC from cleaning it up, thus incurring a memory leak?

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related