Início / dba / Perguntas / 119334
Accepted
RBarryYoung
Asked: 2015-10-28 08:00:56 +0800 CST

Como posso obter contagens de linhas individuais como o SSMS?

  • 772

Eu tenho um programa c# cliente que está executando procedimentos armazenados via ExectueNonQuery, incluindo captura de PRINTsaída de erro e com eventos InfoMessage. Funciona bem, mas notei algo estranho.

Quando executo um procedimento armazenado do SSMS, ele exibe o número de linhas para cada instrução SQL individual executada na guia Mensagens (como se viesse das InfoMessages). No entanto, meu programa nunca vê essas mensagens, embora capture todas as outras saídas. Em vez disso, ele apenas retorna as linhas afetadas no resultado da função ExecuteNonQuery, que é a soma de todas as contagens de linhas individuais (o que é meio inútil).

Por exemplo, este procedimento:

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

Quando o fooproc é executado, o SSMS exibe as contagens de linhas de 662 e 59, mas ExecuteNonQueryretorna apenas o total de 721.

Então, como posso obter as mesmas informações que o SSMS está recebendo?

Só para deixar claro aqui: não estou interessado em como alterar os procedimentos armazenados para adicionar PRINT @@ROWCOUNTs após cada instrução SQL. Eu sei como fazer isso e não é uma opção na maioria das vezes por vários motivos.

Estou perguntando como fazer o que o SSMS está fazendo aqui. Posso alterar o código do cliente o quanto quiser neste ponto (por enquanto, pelo menos) e gostaria de fazer isso direito.

sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    O SqlCommand.StatementCompletedevento será acionado após cada instrução em um lote, e uma das propriedades do evento (bem, praticamente a única propriedade) é o número de linhas afetadas pela instrução que disparou o evento.

    Algumas notas:

    • Um requisito para obter essas informações é que você não especificou SET NOCOUNT ON;ou, inversamente , especificou SET NOCOUNT OFF;.
    • Todos os eventos são disparados na conclusão de cada Execute___(), não durante a execução.
    • StatementCompletedEventArgs.RecordCountInclui contagens de linhas de instruções SELECT, enquanto a propriedade SqlDataReader.RecordsAffected relata apenas contagens de linhas de instruções DML ( INSERT, UPDATE, DELETE, etc).
    • O StatementCompletedevento não inclui a instrução SQL individual do lote que disparou o evento. No entanto, o manipulador de eventos é enviado sendercomo um parâmetro de entrada e este é o SqlCommandlote de consulta, e você pode ver esse lote convertendo senderpara SqlCommande, em seguida, observando a CommandTextpropriedade (isso é mostrado no exemplo abaixo).

    A documentação é muito esparsa sobre isso, então criei um exemplo que mostra esse evento disparando para ExecuteNonQueryand ExecuteScalar, bem como para consultas ad hoc e stored procedures (ou seja, SqlCommand.CommandTypeof Textvs 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();
        }
    }
}

    RESULTADO:

    Lote de consulta:
    SET NOCOUNT OFF; -- garante que o evento 'StatementCompleted' seja acionado

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

    SELECT * FROM sys.tables; ');

    SELECT * FROM sys.objects;

    Linha(s) afetada(s): 453

    Lote de consulta:
    SELECT 123 AS [Bob];

    WAITFOR DELAY '00:00:05.000'; --5 segundos de pausa

    SELECT 2 AS [Sally] UNION ALL SELECT 5;

    Linha(s) afetada(s): 1

    Lote de consulta:
    SELECT 123 AS [Bob];

    WAITFOR DELAY '00:00:05.000'; --5 segundos de pausa

    SELECT 2 AS [Sally] UNION ALL SELECT 5;

    Linha(s) afetada(s): 2

    123

    Lote de consulta: #TestProc
    Linha(s) afetada(s): 453

    Lote de consulta: #TestProc
    Linha(s) afetada(s): 17

    • 7

  2. O resultado executenonquery simplesmente não fará o que você deseja aqui. Mas você ainda pode chegar lá, depende apenas de para que você deseja usar as informações.

    Você pode adicionar esta linha após cada inserção "PRINT @@ROWCOUNT" e obter o número de linhas afetadas pela operação anterior como parte da saída (onde você obtém "bar".

    Alternativamente, você pode adicionar um parâmetro "OUTPUT" ao seu procedimento armazenado para armazenar os resultados e apenas capturá-los ao executar o executenonquery.

    EDITAR:

    Consegui modificar a amostra que Jonathan Kehasias montou para incluir a manipulação de eventos statementcompleted. Basta adicionar essas duas linhas.

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

#Attach handler...
$cmd.add_StatementCompleted($statementhandler)
    • -1

relate perguntas