SQL Server – Get Individual Rowcounts Like SSMS

ado.netsql serversql-server-2008-r2

I have a client c# program that is executing stored procedures via ExectueNonQuery, including catching the PRINT and Error output with InfoMessage events. It works fine, but I have noticed something odd.

When I execute a stored procedure from SSMS, it displays rowcounts for each individual SQL statement that is executed in the Messages tab (as though it were coming from the InfoMessages). However my program never sees these messages, though it does catch all of the same other output. Instead, it just returns the rows affected in the ExecuteNonQuery function result that is the sum of all of the individual rowcounts (which is kind of useless).

For example, this procedure:

use [tempdb]
go

SELECT  * 
INTO    MyCols
FROM    sys.columns
go

CREATE PROC foo As

    UPDATE  MyCols
    SET     name = name + N''
-- SSMS shows (662 row(s) affected)

    UPDATE  MyCols
    SET     name = name + N''
    WHERE   name like '%x%'
-- SSMS shows (59 row(s) affected)

PRINT 'bar'
-- both SSMS and ExecuteNonQuery get this

-- ExecuteNonQuery returns 721 rows affected
GO

When the foo proc is run, SSMS displays rowcounts of 662 and 59, but ExecuteNonQuery only returns the total of 721.

So, how can I get the same information that SSMS is getting?

Just to be clear here: I am not interested in how to change the stored procedures to add PRINT @@ROWCOUNTs after every SQL statement. I know how to do that and it's not an option most of the time for a variety of reasons.

I am asking how to do what SSMS is doing here. I can change the client code all I want at this point (for right now, anyway) and I would like to do it right.

Best Answer

The SqlCommand.StatementCompleted event will fire after each statement in a batch, and one of the properties of the event (well, pretty much the only property) is the number of rows affected by the statement that fired the event.

Some notes:

  • A requirement of getting this info is that you did not specify SET NOCOUNT ON;, or conversely, you did specify SET NOCOUNT OFF;.
  • All events fire at the completion of each Execute___(), not during the execution.
  • The StatementCompletedEventArgs.RecordCount includes row counts from SELECT statements, whereas the SqlDataReader.RecordsAffected property only reports row counts from DML statements (INSERT, UPDATE, DELETE, etc).
  • The StatementCompleted event does not include the individual SQL statement from the batch that fired the event. However, the event handler is sent the sender as an input parameter and this is the SqlCommand of the query batch, and you can see that batch by casting sender to SqlCommand and then looking at the CommandText property (this is shown in the example below).

The documentation is very sparse on this so I have worked up an example that shows this event firing for both ExecuteNonQuery and ExecuteScalar, as well as for both ad hoc queries and stored procedures (i.e. SqlCommand.CommandType of Text vs StoredProcedure):

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

namespace StatementCompletedFiring
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection _Connection =
                          new SqlConnection("Integrated Security = True;"))
            {
                using (SqlCommand _Command = new SqlCommand(@"
SET NOCOUNT OFF; --  ensures that the 'StatementCompleted' event fires

EXEC('
CREATE PROCEDURE #TestProc
AS
SELECT * FROM sys.objects;

SELECT * FROM sys.tables;
');

SELECT * FROM sys.objects;
", _Connection))
                {

                    _Command.StatementCompleted += _Command_StatementCompleted;

                    try
                    {
                        _Connection.Open();

                        _Command.ExecuteNonQuery();

                        _Command.CommandText = @"
SELECT 123 AS [Bob];

WAITFOR DELAY '00:00:05.000'; --5 second pause to shows when the events fire

SELECT 2 AS [Sally]
UNION ALL
SELECT 5;
";
                        Console.WriteLine("\n\t");
                        Console.WriteLine(_Command.ExecuteScalar().ToString());
                        Console.WriteLine("\n");


                        _Command.CommandType = CommandType.StoredProcedure;
                        _Command.CommandText = "#TestProc";
                        _Command.ExecuteNonQuery();
                    }
                    catch (Exception _Exception)
                    {
                        throw new Exception(_Exception.Message);
                    }
                }
            }
        }

        static void _Command_StatementCompleted(object sender,
                                                StatementCompletedEventArgs e)
        {
            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write("\nQuery Batch: ");
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine(((SqlCommand)sender).CommandText);

            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write("Row(s) affected: ");
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine(e.RecordCount.ToString() + "\n");

            Console.ResetColor();
        }
    }
}

OUTPUT:

Query Batch:
SET NOCOUNT OFF; -- ensures that the 'StatementCompleted' event fires

EXEC(' CREATE PROCEDURE #TestProc AS SELECT * FROM sys.objects;

SELECT * FROM sys.tables; ');

SELECT * FROM sys.objects;

Row(s) affected: 453

Query Batch:
SELECT 123 AS [Bob];

WAITFOR DELAY '00:00:05.000'; --5 second pause

SELECT 2 AS [Sally] UNION ALL SELECT 5;

Row(s) affected: 1

Query Batch:
SELECT 123 AS [Bob];

WAITFOR DELAY '00:00:05.000'; --5 second pause

SELECT 2 AS [Sally] UNION ALL SELECT 5;

Row(s) affected: 2

123

Query Batch: #TestProc
Row(s) affected: 453

Query Batch: #TestProc
Row(s) affected: 17

Related Question