Daily Archives: March 1, 2016

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'.