Recuperar dados usando um DataReader

Ele SqlDataReader lê uma sequência somente para frente de resultados de um banco de dados SQL. Ele não carrega o resultado completo em um DataSet, então é adequado para processar linhas à medida que elas chegam.

Os exemplos deste artigo assumem que um SqlConnection aberto chamado connection tem como alvo o banco de dados de exemplo AdventureWorks.

Os exemplos de C# requerem using Microsoft.Data.SqlClient;. Exemplos que usam SqlDbType ou CommandBehavior também exigem using System.Data;.

Ler linhas

O fluxo de trabalho básico do leitor é:

  1. Executar um comando com ExecuteReader ou ExecuteReaderAsync.
  2. Resolva as posições ordinais das colunas para o resultado atual.
  3. Chame Read uma vez para cada linha.
  4. Verifique as colunas IsDBNull nullable antes de chamar um getter tipado.
  5. Descarte o leitor antes de reutilizar a conexão ou ler os parâmetros de saída.

Este exemplo armazena em cache ordinais de coluna, usa getters tipados e verifica a coluna anulável Color :

using var command = new SqlCommand("""
    SELECT ProductID, Name, Color
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
    ORDER BY ProductID;
    """, connection);
command.Parameters.Add("@subcategoryId", SqlDbType.Int).Value = 2;

using SqlDataReader reader = command.ExecuteReader();
int idOrdinal = reader.GetOrdinal("ProductID");
int nameOrdinal = reader.GetOrdinal("Name");
int colorOrdinal = reader.GetOrdinal("Color");

while (reader.Read())
{
    int id = reader.GetInt32(idOrdinal);
    string name = reader.GetString(nameOrdinal);
    string? color = reader.IsDBNull(colorOrdinal)
        ? null
        : reader.GetString(colorOrdinal);

    Console.WriteLine($"{id} | {name} | {color ?? "(no color)"}");
}

Chamar GetString em um valor NULL lança SqlNullValueException, então verifique IsDBNull primeiro.

HasRows informa se o resultado atual contém linhas, mas geralmente você pode chamar Read diretamente. Para um resultado vazio, a primeira Read chamada retorna false:

using var command = new SqlCommand("""
    SELECT ProductID
    FROM Production.Product
    WHERE ProductID = @productId;
    """, connection);
command.Parameters.Add("@productId", SqlDbType.Int).Value = -1;

using SqlDataReader reader = command.ExecuteReader();
Console.WriteLine($"HasRows: {reader.HasRows}");
Console.WriteLine($"First Read: {reader.Read()}");

Para valores grandes, execute com CommandBehavior.SequentialAccess e use GetStream, GetTextReader, ou GetXmlReader. Para mais informações, veja suporte a streaming do SqlClient.

Fechar o DataReader

Descarte o leitor assim que o processamento terminar. Uma instrução await using ou using fecha mesmo quando o processamento de linha gera uma exceção.

Parâmetros de saída, parâmetros de entrada/saída e valores de retorno de procedimentos armazenados não são finais até que o leitor seja fechado:

using var command = new SqlCommand("""
    SELECT TOP (5) ProductID, Name
    FROM Production.Product
    ORDER BY ProductID;
    SET @count = @@ROWCOUNT;
    """, connection);

SqlParameter count = command.Parameters.Add("@count", SqlDbType.Int);
count.Direction = ParameterDirection.Output;

using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        Console.WriteLine(reader.GetString(1));
    }
}

Console.WriteLine($"Rows returned: {count.Value}");

Sem Múltiplos Conjuntos Ativos de Resultados (MARS), um leitor aberto mantém sua conexão ocupada e outro comando não pode ser executado nessa conexão. Com o MARS ativado, comandos separados podem ter resultados pendentes em uma conexão, mas suas operações ainda não são thread-safe. Para mais informações, veja Multiple Active Result Sets.

Se o comando passado CommandBehavior.CloseConnection, fechar o leitor também fecha a conexão. Caso contrário, o código que abriu a conexão permanece responsável por descartá-la.

Contagem de linhas afetadas

RecordsAffected informa as linhas alteradas pelas instruções INSERT, UPDATE e DELETE no comando. Leia depois que o visualizador fechar. Este exemplo reverte sua atualização:

using SqlTransaction transaction = connection.BeginTransaction();
using var command = new SqlCommand("""
    UPDATE Production.Product
    SET ModifiedDate = ModifiedDate
    WHERE ProductID IN (706, 707);

    SELECT ProductID, Name
    FROM Production.Product
    WHERE ProductID IN (706, 707)
    ORDER BY ProductID;
    """, connection, transaction);

SqlDataReader reader = command.ExecuteReader();
using (reader)
{
    while (reader.Read())
    {
        Console.WriteLine($"{reader.GetInt32(0)} | {reader.GetString(1)}");
    }
}

Console.WriteLine($"RecordsAffected: {reader.RecordsAffected}");
transaction.Rollback();

Para um comando somente de SELECT, RecordsAffected é -1. SET NOCOUNT ON suprime as contagens, o que também deixa o valor em -1.

Recupere múltiplos conjuntos de resultados usando NextResult

Um comando ou procedimento armazenado pode retornar múltiplos resultados. Processe completamente o resultado atual e então chame NextResult ou NextResultAsync:

using var command = new SqlCommand("""
    SELECT TOP (3) ProductID, Name
    FROM Production.Product
    ORDER BY ProductID;

    SELECT COUNT(*) AS ProductCount
    FROM Production.Product;
    """, connection);

using SqlDataReader reader = command.ExecuteReader();
int result = 1;

do
{
    Console.WriteLine($"Result {result++}: {reader.FieldCount} column(s)");
    while (reader.Read())
    {
        var values = new object[reader.FieldCount];
        reader.GetValues(values);
        Console.WriteLine(string.Join(" | ", values));
    }
}
while (reader.NextResult());

A contagem de colunas, nomes, ordinais e tipos podem mudar para cada resultado. Recalcule ordinais em cache após NextResult retornar true.

Apenas uma operação assíncrona pode estar pendente em um leitor por vez. Aguarde ReadAsync, NextResultAsync ou uma operação assíncrona em campo antes de iniciar outra operação no mesmo leitor.

Obtendo informações de esquema por meio do DataReader

Chame GetColumnSchema para obter uma descrição fortemente tipada das colunas do resultado atual. Chame GetSchemaTable quando o código existente precisar do schema em um DataTable:

using var command = new SqlCommand("""
    SELECT ProductID, Name, Color
    FROM Production.Product;
    """, connection);

using SqlDataReader reader = command.ExecuteReader(
    CommandBehavior.SchemaOnly);

foreach (System.Data.Common.DbColumn column in reader.GetColumnSchema())
{
    Console.WriteLine(
        $"{column.ColumnOrdinal}: {column.ColumnName} " +
        $"({column.DataTypeName}), nullable={column.AllowDBNull}");
}

CommandBehavior.SchemaOnly solicita metadados de coluna sem linhas. Informações do esquema descrevem o conjunto de resultados atual. Solicite novamente depois de ir para o próximo resultado. Dependendo da consulta e das permissões do servidor, alguns metadados da tabela base podem não estar disponíveis.

Conteúdo relacionado

  • Última atualização em