8

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.

6

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

  • 1
    I have tried this and it does work for me. Oddly, the StatementCompletions and the InfoMessages from PRINT statements in the stored procedures don't seem to be synchronized wrt each other (I get a bunch of StatementCompletions, then a bunch of PRINT statement outputs, though they are supposed to be interleaved) But I guess that's an SSMS trick for another day... – RBarryYoung Nov 9 '15 at 14:59
  • 1
    @RBarryYoung Yes, that behavior, I believe is to be expected, even if annoying. It has to do with the order of the items in the TDS (Tabular Data Stream): msdn.microsoft.com/en-us/library/dd304523.aspx . I know that the PRINT and RAISERROR(..., 10, 1) messages all come after the result sets. I am trying to find the message ordering in that documentation but so far have not come across it. – Solomon Rutzky Nov 9 '15 at 15:31
  • The mystery to me is how SSMS sorts it out correctly. – RBarryYoung Nov 9 '15 at 15:35
  • 1
    @RBarryYoung Perhaps this should be a separate question since this one was just about row counts from individual queries? It's a good question, and I have figured it out :). I will post it as a question if I get a chance to do so before you get to it. – Solomon Rutzky Nov 20 '15 at 5:31
  • 1
    @RBarryYoung Yikes. Sorry to hear about that. Hope you are on the mend. I will try to get to it within the next few days. I just have one or two variations left to test that I thought of after I posted that last message. I will post the link to it here. – Solomon Rutzky Nov 21 '15 at 4:20
-1

The executenonquery result just isn't going to do what you want here. But you can still get there, it just depends on what you want to use the information for.

You can add this line after each insert "PRINT @@ROWCOUNT" and you should get the number of rows affected by the prior operation as part of the output (where you get "bar".

Alternatively, you can add an "OUTPUT" parameter to your stored procedure to hold the results and then just capture that when you run the executenonquery.

EDIT:

I managed to modify the sample that Jonathan Kehasias put together to include the statementcompleted event handling. Just add these two lines.

#Add handler for StatementCompleted
$statementhandler = {param($sender, [System.Data.StatementCompletedEventArgs]$event) Write-Host $event.RecordCount };

#Attach handler...
$cmd.add_StatementCompleted($statementhandler)
  • I cannot modify these procedures. I can modify the client code, including using something other than ExecuteNonQuery. – RBarryYoung Oct 27 '15 at 16:50
  • You could try attaching an event handler to the sqlcommand infomessage event. This article shows how to do it using powershell. sqlskills.com/blogs/jonathan/… – Jonathan Fite Oct 27 '15 at 16:54
  • If you had read my question, you would have seen that I am already doing that. It is not in there. – RBarryYoung Oct 27 '15 at 16:55
  • 1
    This question in the C# area says that adding a listener to the SQLCommand.StatementCompleted event gave them what they were looking for. stackoverflow.com/questions/27993049/… – Jonathan Fite Oct 27 '15 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.