Recuperar datos utilizando un DataReader

El SqlDataReader lee una secuencia de resultados solo hacia adelante de una base de datos SQL. No carga el resultado completo en un DataSet, por lo que es adecuado para procesar filas a medida que llegan.

Los ejemplos de este artículo asumen que un connection abierto denominado SqlConnection apunta a la base de datos de ejemplo AdventureWorks.

Los ejemplos de C# requieren using Microsoft.Data.SqlClient;. Ejemplos que usan SqlDbType o CommandBehavior también requieren using System.Data;.

Leer filas

El flujo de trabajo básico del lector es:

  1. Ejecuta un comando con ExecuteReader o ExecuteReaderAsync.
  2. Resuelve los ordinales de columna para el resultado actual.
  3. Llame a Read una vez por cada fila.
  4. Comprueba las columnas anulables con IsDBNull antes de llamar a un getter tipado.
  5. Libera el lector antes de reutilizar la conexión o leer los parámetros de salida.

Este ejemplo almacena en caché ordinales de columna, utiliza getters tipados y comprueba la columna anulable 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)"}");
}

Llamar a GetString sobre un valor NULL lanza SqlNullValueException, así que comprueba IsDBNull primero.

HasRows Informa si el resultado actual contiene filas, pero normalmente puedes llamar Read directamente. Para un resultado vacío, la primera Read llamada devuelve 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, ejecuta con CommandBehavior.SequentialAccess y usa GetStream, GetTextReader, o GetXmlReader. Para más información, consulte soporte para streaming de SqlClient.

Cierre el DataReader

Libera el lector tan pronto como finalice el procesamiento. Una sentencia using o await using la cierra incluso cuando el procesamiento de filas genera una excepción.

Los parámetros de salida, los parámetros de entrada/salida y los valores de retorno de los procedimientos almacenados no son definitivos hasta que se cierra el lector:

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}");

Sin Múltiples Conjuntos de Resultados Activos (MARS), un lector abierto mantiene su conexión ocupada y otro comando no puede ejecutarse en esa conexión. Con MARS activado, los comandos separados pueden tener resultados pendientes en una conexión, pero sus operaciones aún no son seguras para hilos. Para más información, consulte Conjuntos de resultados activos múltiples.

Si el comando pasó CommandBehavior.CloseConnection, al cerrar el lector también se cierra la conexión. De lo contrario, el código que abrió la conexión sigue siendo responsable de liberarla.

Leer recuentos de filas afectadas

RecordsAffected informa de las filas cambiadas por las sentencias INSERT, UPDATE y DELETE del comando. Léalo después de que se cierre el lector. Este ejemplo revierte su actualización:

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 un comando que solo admita SELECT, RecordsAffected es -1. SET NOCOUNT ON suprime los conteos, lo que también deja el valor en -1.

Recuperar múltiples conjuntos de resultados usando NextResult

Un solo comando o procedimiento almacenado puede devolver múltiples resultados. Procese completamente el resultado actual y luego llame a NextResult o 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());

El recuento de columnas, nombres, ordinales y tipos pueden cambiar para cada resultado. Recalcular los ordinales en caché después de que NextResult devuelva true.

Solo puede estar pendiente una operación asíncrona en un lector a la vez. Espere ReadAsync, NextResultAsync o una operación de campo asíncrona antes de iniciar otra operación en el mismo lector.

Obtención de información del esquema a partir del objeto DataReader

Llama a GetColumnSchema para obtener una descripción fuertemente tipada de las columnas del resultado actual. Llame GetSchemaTable cuando el código existente necesite el esquema en un 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 metadatos de columna sin filas. La información del esquema describe el conjunto de resultados actual. Solicitarlo de nuevo después de pasar al siguiente resultado. Dependiendo de la consulta y los permisos del servidor, algunos metadatos de la tabla base pueden no estar disponibles.

Contenido relacionado

  • Última actualización o