Récupérer les données en utilisant un DataReader

Le SqlDataReader lit une séquence de résultats en avant uniquement à partir d’une base de données SQL. Cela ne charge pas le résultat complet dans un DataSet, donc il convient au traitement des lignes au fur et à mesure de leur arrivée.

Les exemples de cet article supposent qu’un connection ouvert nommé SqlConnection cible la base de données d’échantillons AdventureWorks.

Les exemples de C# nécessitent using Microsoft.Data.SqlClient;. Exemples qui utilisent SqlDbType ou CommandBehavior nécessitent using System.Data;également .

Lire les lignes

Le flux de travail de base du lecteur est le suivant :

  1. Exécuter une commande avec ExecuteReader ou ExecuteReaderAsync.
  2. Déterminer les indices des colonnes pour le résultat actuel.
  3. Appelez Read une fois pour chaque ligne.
  4. Vérifiez les colonnes nullables avec IsDBNull avant d’appeler un getter typé.
  5. Éliminez le lecteur avant de réutiliser la connexion ou de lire les paramètres de sortie.

Cet exemple met en cache les ordinaux de colonnes, utilise des getters typés, et vérifie la colonne nullable 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)"}");
}

Appeler GetString sur une valeur NULL lève SqlNullValueException, donc vérifiez IsDBNull en premier.

HasRows indique si le résultat actuel contient des lignes, mais vous pouvez généralement appeler Read directement. Pour un résultat vide, le premier appel Read retourne 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()}");

Pour les grandes valeurs, exécuter avec CommandBehavior.SequentialAccess et utiliser GetStream, GetTextReader, ou GetXmlReader. Pour plus d’informations, voir le support du streaming SqlClient.

Fermer le DataReader

Libérez le lecteur dès que le traitement est terminé. Une instruction await using ou using la ferme même lorsque le traitement des lignes lève une exception.

Les paramètres de sortie, les paramètres d’entrée/sortie et les valeurs de retour des procédures stockées ne sont pas définitives tant que le lecteur n’est pas fermé :

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

Sans Multiple Active Result Sets (MARS), un lecteur de données ouvert maintient sa connexion occupée et une autre commande ne peut pas s’exécuter sur cette connexion. Avec MARS activé, des commandes séparées peuvent avoir des résultats en attente sur une connexion, mais leurs opérations ne sont toujours pas thread-safe. Pour plus d’informations, voir Ensembles de résultats actifs multiples.

Si la commande a été transmise CommandBehavior.CloseConnection, fermer le lecteur ferme aussi la connexion. Sinon, le code qui a ouvert la connexion reste responsable de la libérer.

Lire le nombre de lignes affectées

RecordsAffected rapporte les lignes modifiées par les instructions INSERT, UPDATE et DELETE dans la commande. Lisez-le après la fermeture du lecteur. Cet exemple revient sur sa mise à jour :

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();

Pour une SELECTcommande -uniquement, RecordsAffected est -1. SET NOCOUNT ON supprime les décomptes, ce qui laisse aussi la valeur à -1.

Récupérer plusieurs ensembles de résultats en utilisant NextResult

Une commande ou une procédure stockée peut renvoyer plusieurs résultats. Traitez complètement le résultat actuel, puis appelez 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());

Le nombre de colonnes, les noms, les ordinaux et les types peuvent changer pour chaque résultat. Recalculer les ordinaux mis en cache après que NextResult renvoie true.

Une seule opération asynchrone peut être en attente sur un lecteur à la fois. Attendez ReadAsync, NextResultAsync ou une opération asynchrone sur un champ avant de commencer une autre opération sur le même lecteur.

Obtenir des informations sur le schéma à partir du DataReader

Appelez GetColumnSchema pour obtenir une description fortement typée des colonnes du résultat actuel. Appelez GetSchemaTable si le code existant a besoin du schéma dans 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 demande des métadonnées de colonne sans lignes. Les informations de schéma décrivent l’ensemble de résultats actuel. Demandez-le à nouveau après être passé au résultat suivant. Selon la requête et les permissions du serveur, certaines métadonnées de la table de base peuvent être indisponibles.

Contenu connexe

  • Dernière mise à jour :