How can I get individual rowcounts like SSMS?

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