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 specifySET NOCOUNT OFF;
. - All events fire at the completion of each
Execute___()
, not during the execution. - The
StatementCompletedEventArgs.RecordCount
includes row counts fromSELECT
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 thesender
as an input parameter and this is theSqlCommand
of the query batch, and you can see that batch by castingsender
toSqlCommand
and then looking at theCommandText
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 firesEXEC(' 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: 453Query Batch: #TestProc
Row(s) affected: 17