SQL Development

Capturing output from SQL Server using C#

3 Comments

I have recently been doing some preliminary work on a tool that needs to read non-tabular output from SQL Server. This is the stuff that normally appears on the “messages” tab in Management Studio.

Consider the following query:

1
2
3

set nocount on;
print 'Now selecting from sys.databases';
select * from sys.databases;

When I run this in SSMS, I get the following on the messages tab:

Now selecting from sys.databases

So how we capture the print statement? It turns out to be pretty easy.

using System;
using System.Data;
using System.Data.SqlClient;
 
namespace Playground
{
    class SqlOutputCapture
    {
        public static void Test()
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = @"servername";
            sb.InitialCatalog = "master";
            sb.IntegratedSecurity = true;
 
            using (SqlConnection connection = new SqlConnection(sb.ToString()))
            {
                connection.InfoMessage += OnInfoMessageGenerated;                string sql = @"set nocount on; print 'Now selecting from sys.databases';
					select * from sys.databases;";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        using (DataSet set = new DataSet())
                        {
                            adapter.Fill(set);
                        }
                    }
                }
            }
        }
 
        private static void OnInfoMessageGenerated(object sender, SqlInfoMessageEventArgs args)        {            Console.WriteLine("{0}", args.Message);        }    }
}

This writes the following to the console.

Now selecting from sys.databases

So let’s flip the NOCOUNT to off:

                string sql = @"set nocount off; print 'Now selecting from sys.databases';
					select * from sys.databases;";

When we run the query in SSMS, we now get:

Now selecting from sys.databases

(33 row(s) affected)

But executing the C# code outputs:

Now selecting from sys.databases

So why don’t we get the message about the number of rows?

It turns out that not everything that appears in Management Studio’s “messages” tab directly comes from SQL Server, at least in literal text format. Setting NOCOUNT OFF doesn’t cause SQL Server to return a literal string “(33 row(s) affected)” but rather affects the data sent in the underlying TDS network stream.

We can sort of emulate what SSMS does.

        public static void Test()
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = @"servername";
            sb.InitialCatalog = "master";
            sb.IntegratedSecurity = true;
 
            using (SqlConnection connection = new SqlConnection(sb.ToString()))
            {
                connection.InfoMessage += OnInfoMessageGenerated;
                string sql = @"set nocount off; print 'Now selecting from sys.databases';
					select * from sys.databases;";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.StatementCompleted += OnStatementCompleted;                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        using (DataSet set = new DataSet())
                        {
                            adapter.Fill(set);
                        }
                    }
                }
            }
        }
 
        static void OnStatementCompleted(object sender, StatementCompletedEventArgs args)        {            Console.WriteLine("({0} row(s) affected)", args.RecordCount);        } 
        private static void OnInfoMessageGenerated(object sender, SqlInfoMessageEventArgs args)
        {
            Console.WriteLine("{0}", args.Message);
        }

And now we get:

Now selecting from sys.databases
(33 row(s) affected)

Another option is to use the connection statistics:

        public static void Test()
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = @"servername";
            sb.InitialCatalog = "master";
            sb.IntegratedSecurity = true;
 
            using (SqlConnection connection = new SqlConnection(sb.ToString()))
            {
                connection.InfoMessage += OnInfoMessageGenerated;
                connection.StatisticsEnabled = true;                string sql = @"set nocount off; print 'Now selecting from sys.databases';
					select * from sys.databases;";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        using (DataSet set = new DataSet())
                        {
                            adapter.Fill(set);
                        }
                    }
                }
 
                System.Collections.IDictionary statistics = connection.RetrieveStatistics();
                object rowsAffected = statistics["SelectRows"];                Console.WriteLine("({0} rows(s) affected)", rowsAffected);            }
        }

Which also returns:

Now selecting from sys.databases
(33 row(s) affected)

However, be aware that the number of rows returned is cumulative for the connection. The statistics that are returned contain other interesting properties, including ConnectionTime, ExecutionTime, and ServerRoundtrips, as well as data about the network traffic, including BytesSent and BytesReceived.

It is worth nothing that the SqlConnection.InfoMessage sends more than a simple message to the output stream. The SqlInfoMessageEventArgs object also contains an Errors property which is a collection of errors being returned to the client. Using this, we can capture error information similar to what is display in SSMS. Let’s put a couple of errors into our SQL statement.

        public static void Test()
        {
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = @"servername";
            sb.InitialCatalog = "master";
            sb.IntegratedSecurity = true;
 
            using (SqlConnection connection = new SqlConnection(sb.ToString()))
            {
                connection.Open();
                connection.InfoMessage += OnInfoMessageGenerated;
                connection.FireInfoMessageEventOnUserErrors = true;                string sql = "printf 'Now selecting from sys.databasii'; select * from sys.databasii;";                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        using (DataSet set = new DataSet())
                        {
                            adapter.Fill(set);
                        }
                    }
                }
            }
        }
 
        private static void OnInfoMessageGenerated(object sender, SqlInfoMessageEventArgs args)        {            foreach (SqlError err in args.Errors)            {                Console.WriteLine("Msg {0}, Level {1}, State {2}, Line {3}",                    err.Number, err.Class, err.State, err.LineNumber);                Console.WriteLine("{0}", err.Message);            }        }

The output is:

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'printf'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.databasii'.

3 thoughts on “Capturing output from SQL Server using C#”

  1. Thank you for a good post.

    What about getting prints after a select statement?

    SELECT….
    PRINT….
    RETURN…

    Reply

  2. That’s a good question. In my experience, the PRINT output comes through as message just like in the other situation. I tested it with:

    declare @var int;

    select top 1 @var = session_id
    from sys.dm_exec_sessions
    order by session_id desc;

    print @var;

    Reply

  3. Dear, I have a Procedure and How to print messages in that procedure on c#.net ? can you help me?
    ALTER PROCEDURE AProc_Getback(
    @Fcode VARCHAR(MAX)

    )
    AS
    BEGIN

    CREATE TABLE #FCodes (Item VARCHAR(30))
    DECLARE @Insert VARCHAR(MAX) = ‘INSERT INTO #FCodes VALUES (”’+REPLACE(@Fcode,’,’,”’),(”’)+”’);’
    EXEC (@Insert)
    Declare @code int
    Select @code = count(fcode) From t_PALM_PersonnelFileMst Where fcode in(SELECT Item FROM #FCodes)
    If(@code)>0
    Begin

    UPDATE t_PALM_PersonnelFileMst
    SET
    fdflag = 0,
    frdate = NULL,
    fddate = NULL,
    fdtype = NULL,
    fdreason = NULL
    WHERE fcode IN(SELECT Item FROM #FCodes)
    DELETE FROM t_pald_dimissiontrm
    WHERE fcode IN(SELECT Item FROM #FCodes)
    Print N’OK’;
    End
    else
    PRINT N’This code not exit in database, please check again.’;

    END

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *