Modification de données avec des procédures stockées

Définissez CommandType sur CommandType.StoredProcedure et définissez CommandText sur le nom de procédure qualifiée par le schéma. Ajouter un paramètre pour chaque paramètre de procédure, avec le type, la taille et la direction correspondants.

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 CommandType nécessitent using System.Data;également .

Appeler une procédure stockée

AdventureWorks inclut dbo.uspGetEmployeeManagers, qui accepte un paramètre d’entrée et retourne un ensemble de résultats :

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(
    "@BusinessEntityID",
    SqlDbType.Int).Value = 6;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetString(2)} {reader.GetString(3)}");
}

Utilisez les noms de paramètres déclarés par la procédure. La qualification de schéma évite l’ambiguïté de la résolution de noms et les variantes inutiles du plan de procédure.

Exécuter une procédure de modification des données

AdventureWorks inclut également des procédures qui modifient les données. HumanResources.uspUpdateEmployeePersonalInfo remplace tous les champs personnels d’un employé. L’exemple suivant exécute ces étapes à l’intérieur d’une transaction :

  1. Lit les valeurs actuelles de l’employé.
  2. Ça change l’état civil.
  3. Lit à nouveau la valeur modifiée.
  4. Ça annule la transaction pour que la base de données d’échantillons reste inchangée.
const int businessEntityId = 1;
using SqlTransaction transaction = connection.BeginTransaction();

string nationalIdNumber;
DateTime birthDate;
string maritalStatus;
string gender;

using (var select = new SqlCommand(
    """
    SELECT NationalIDNumber, BirthDate, MaritalStatus, Gender
    FROM HumanResources.Employee
    WHERE BusinessEntityID = @BusinessEntityID;
    """,
    connection,
    transaction))
{
    select.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;

    using SqlDataReader reader = select.ExecuteReader();
    if (!reader.Read())
    {
        throw new InvalidOperationException(
            $"Employee {businessEntityId} wasn't found.");
    }

    nationalIdNumber = reader.GetString(0);
    birthDate = reader.GetDateTime(1);
    maritalStatus = reader.GetString(2);
    gender = reader.GetString(3);
}

string newMaritalStatus = maritalStatus == "M" ? "S" : "M";

using (var command = new SqlCommand(
    "HumanResources.uspUpdateEmployeePersonalInfo",
    connection,
    transaction))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;
    command.Parameters.Add(
        "@NationalIDNumber",
        SqlDbType.NVarChar,
        15).Value = nationalIdNumber;
    command.Parameters.Add(
        "@BirthDate",
        SqlDbType.DateTime).Value = birthDate;
    command.Parameters.Add(
        "@MaritalStatus",
        SqlDbType.NChar,
        1).Value = newMaritalStatus;
    command.Parameters.Add(
        "@Gender",
        SqlDbType.NChar,
        1).Value = gender;

    command.ExecuteNonQuery();
}

using (var verify = new SqlCommand(
    """
    SELECT MaritalStatus
    FROM HumanResources.Employee
    WHERE BusinessEntityID = @BusinessEntityID;
    """,
    connection,
    transaction))
{
    verify.Parameters.Add(
        "@BusinessEntityID",
        SqlDbType.Int).Value = businessEntityId;
    Console.WriteLine(
        $"Marital status: {maritalStatus} -> {verify.ExecuteScalar()}");
}

transaction.Rollback();

La procédure définit SET NOCOUNT ON, donc ExecuteNonQuery retourne -1 au lieu d’un nombre de lignes. N’utilisez pas la valeur de retour pour confirmer la mise à jour. Lisez plutôt la ligne précédente.

Vérifiez le contrat et les effets secondaires d’une procédure avant de l’exécuter. Chaque commande qui s’exécute sur une connexion avec une transaction locale en attente doit définir Transaction. Assurez-vous que la gestion des transactions par la procédure prend en charge la transaction externe. Cette procédure détecte les erreurs et les enregistre avec dbo.uspLogError au lieu de les propager à l’appelant.

Lire les jeux de résultats

Utiliser ExecuteReader ou ExecuteReaderAsync lorsqu’une procédure renvoie des lignes. Lorsqu’il retourne plus d’un ensemble de résultats, appelez NextResult ou NextResultAsync jusqu’à ce qu’il retourne false.

Libérez le lecteur avant de lire les paramètres de sortie, d’entrée/sortie ou de valeur de retour. Ces valeurs ne sont pas définitives tant que le lecteur est ouvert.

Configurer les directions des paramètres

Définissez Direction pour chaque paramètre qui n’est pas en entrée seule :

SqlParameter output = command.Parameters.Add(
    "@outputValue",
    SqlDbType.NVarChar,
    50);
output.Direction = ParameterDirection.Output;

SqlParameter inputOutput = command.Parameters.Add(
    "@counter",
    SqlDbType.Int);
inputOutput.Direction = ParameterDirection.InputOutput;
inputOutput.Value = 1;

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

Définissez Size pour les paramètres de sortie, d’entrée et d’entrée/sortie à longueur variable. Un paramètre entrée/sortie nécessite également une valeur initiale Value. Un paramètre de valeur de retour reçoit l’entier à partir de l’instruction Transact-SQL (T-SQL) RETURN de la procédure. Il ne reçoit pas d’ensemble de résultats ni de valeur sélectionnée par la procédure.

Lisez le Value de chaque paramètre uniquement une fois l’exécution terminée et tous les lecteurs fermés :

command.ExecuteNonQuery();

Console.WriteLine(output.Value);
Console.WriteLine(inputOutput.Value);
Console.WriteLine(returnValue.Value);

Lorsqu’une procédure n’assigne pas un paramètre de sortie, sa valeur peut être DBNull.Value.

Choisir une méthode d’exécution

Utilisez la méthode d’exécution qui correspond au résultat de la procédure :

Méthode À utiliser quand
ExecuteReader La procédure retourne des lignes ou plusieurs ensembles de résultats.
ExecuteNonQuery Vous avez besoin de paramètres de sortie ou d’une valeur de retour, mais pas de lignes de résultats. Le client consomme toutes les lignes renvoyées avant la fin de l’appel.
ExecuteScalar Il suffit de la première colonne de la première ligne.

ExecuteNonQuery renvoie le nombre de lignes affectées lorsqu’elles sont disponibles. Une procédure qui utilise SET NOCOUNT ON peut modifier des lignes et tout de même retourner -1. Utilisez un paramètre de sortie ou une valeur de retour lorsque la procédure en définit un comme contrat de réussite.

Remarque

Lorsque SqlDataAdapter utilise une procédure stockée pour mettre à jour ou supprimer des lignes, ne pas utiliser SET NOCOUNT ON si l’adaptateur dépend du nombre de lignes affectées pour une concurrence optimiste. Une valeur de zéro peut causer DBConcurrencyException.

Gérer les échecs

Interceptez SqlException lors de l’appel d’exécution. Une procédure peut gérer sa propre gestion des transactions et des erreurs, mais le client doit tout de même décider si l’opération est sûre à retenter et si sa connexion reste utilisable.

Pour les erreurs de serveur et les messages PRINT, voir Gérer les erreurs et les messages.

Contenu connexe

  • Dernière mise à jour :