Comment : Appeler une procédure stockée paramétrable à l’aide d’ADO.NET et Visual C# .NET

Pour obtenir une version Microsoft Visual Basic .NET de cet article, reportez-vous à la section 308049 .

Pour obtenir une version Microsoft Visual C++ .NET de cet article, reportez-vous à la section 310071 .

Pour obtenir une version Microsoft Visual J# .NET de cet article, reportez-vous à 320627 .


Cet article fait référence aux espaces de noms bibliothèque de classes Microsoft.NET Framework suivant :

  • System.Data.SqlClient
  • System.Data.OleDb

DANS CETTE TÂCHE.

Résumé

Il existe plusieurs manières d’utiliser ADO.NET pour appeler une procédure stockée et de récupérer des valeurs de retour et retourner des paramètres, y compris :

  • Utilisez un objet DataSet pour rassembler les lignes retournées et pour travailler avec ces lignes en plus les valeurs de retour et les paramètres de retour.
  • Utiliser un objet DataReader pour rassembler les lignes renvoyées, pour vous déplacer à travers ces lignes, puis collecte les valeurs de retour et les paramètres de retour.
  • Utilisez la méthode ExecuteScalar pour renvoyer la valeur de la première colonne de la première ligne de résultats avec les valeurs de retour et les paramètres de retour. Cela est particulièrement utile avec les fonctions d’agrégation.
  • Utilisez la méthode ExecuteNonQuery pour retourner uniquement les paramètres de retour et les valeurs de retour. Toutes les lignes retournés sont ignorés. Cela est particulièrement utile pour exécuter des requêtes action.
Cet article présente les trois dernières méthodes et utilise des objets OleDbCommand et SqlCommand . Assurez-vous que vous copiez uniquement le code pour le fournisseur managé que vous utilisez. Si vous n’êtes pas sûr de savoir quel fournisseur managé, vous devez utiliser, visitez le site Web de Microsoft Developer Network :
Dans chacun des exemples dans cet article, les paramètres sont ajoutés à la collection Parameters de l’objet Command . Lorsque vous utilisez l’objet SqlCommand , vous n’avez ajoutez pas les paramètres dans un ordre particulier, mais les paramètres doivent avoir le nom correct. Lorsque vous utilisez l’objet OleDbCommand , vous devez ajouter les paramètres dans l’ordre approprié, et vous ne pouvez pas utiliser les paramètres par nom.

Utiliser le DataReader pour retourner les lignes et les paramètres

Vous pouvez utiliser l’objet DataReader pour renvoyer un flux de données en lecture seule et avant uniquement. Les informations contenues dans le DataReader peuvent provenir d’une procédure stockée. Cet exemple utilise l’objet DataReader pour exécuter une procédure stockée qui a une entrée et un paramètre de sortie et se déplace ensuite à travers les enregistrements retournés pour afficher les paramètres de retour.

  1. Créez la procédure stockée suivante sur le serveur qui exécute Microsoft SQL Server :
    Create Procedure TestProcedure(
    @au_idIN varchar (11),
    @numTitlesOUT Integer OUTPUT
    )
    AS

    select A.au_fname, A.au_lname, T.title
    from authors as A join titleauthor as TA on
    A.au_id=TA.au_id
    join titles as T
    on T.title_id=TA.title_id
    where A.au_id=@au_idIN
    set @numTitlesOUT = @@Rowcount
    return (5)

  2. Créer un nouveau projet d’Application Windows Visual C# .NET.
  3. Utilisez l’instruction using sur le système et les espaces de noms System.Data afin que vous n’ayez pas à qualifier des déclarations dans ces espaces de noms ultérieurement dans votre code. Ajoutez ce code au début du module de code de formulaire. Assurez-vous de copier uniquement le code pour le fournisseur que vous avez choisi. SQL Client
    using System.Data.SqlClient;
    Fournisseur de données OLE DB
    using System.Data.OleDb;
  4. Remplacez le code dans l’événement Form_Load privé par le code suivant :SQL Client
    SqlConnection PubsConn = new SqlConnection ("Data Source=server;integrated " + 
    "Security=sspi;initial catalog=pubs;");
    SqlCommand testCMD = new SqlCommand
    ("TestProcedure", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    SqlParameter RetVal = testCMD.Parameters.Add
    ("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;
    SqlParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", SqlDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    SqlParameter NumTitles = testCMD.Parameters.Add
    ("@numtitlesout", SqlDbType.VarChar, 11);
    NumTitles.Direction = ParameterDirection.Output ;

    IdIn.Value = "213-46-8915";
    PubsConn.Open();

    SqlDataReader myReader = testCMD.ExecuteReader();
    Console.WriteLine ("Book Titles for this Author:");
    while (myReader.Read())
    {
    Console.WriteLine ("{0}", myReader.GetString (2));
    };
    myReader.Close() ;
    Console.WriteLine("Number of Rows: " + NumTitles.Value );
    Console.WriteLine("Return Value: " + RetVal.Value);

    Fournisseur de données OLE DB
    OleDbConnection PubsConn = new OleDbConnection    ("Provider=SQLOLEDB;Data Source=server;" + 
    "integrated Security=sspi;initial catalog=pubs;");
    OleDbCommand testCMD = new OleDbCommand
    ("TestProcedure", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    OleDbParameter RetVal = testCMD.Parameters.Add
    ("RetVal", OleDbType.Integer);RetVal.Direction = ParameterDirection.ReturnValue;
    OleDbParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", OleDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    OleDbParameter NumTitles = testCMD.Parameters.Add
    ("@numtitlesout", OleDbType.VarChar, 11);
    NumTitles.Direction = ParameterDirection.Output;

    IdIn.Value = "213-46-8915";

    PubsConn.Open();

    OleDbDataReader myReader = testCMD.ExecuteReader();
    Console.WriteLine ("Book Titles for this Author:");
    while (myReader.Read())
    {
    Console.WriteLine ("{0}", myReader.GetString (2));
    };
    myReader.Close() ;
    Console.WriteLine("Number of Rows: " + NumTitles.Value );
    Console.WriteLine("Return Value: " + RetVal.Value);

  5. Modifiez la chaîne de connexion pour l’objet de connexion pointer vers l’ordinateur qui exécute SQL Server.
  6. Exécutez le code. Notez que l' objet DataReader récupère les enregistrements et renvoie les valeurs de paramètre. Vous pouvez utiliser la méthode Read de l’objet DataReader pour vous déplacer entre les enregistrements retournés.

    La fenêtre Sortie affiche les titres des livres de deux, la valeur de retour de 5 et que le paramètre de sortie qui contient le nombre d’enregistrements (2). Notez que vous devez fermer le DataReader dans le code pour afficher les valeurs de paramètre. En outre, notez que vous n’êtes pas obligé de se déplacer dans tous les enregistrements pour voir les paramètres de retour si DataReader est fermé.

Utilisez la méthode ExecuteScalar de l’objet Command

Vous pouvez utiliser la méthode ExecuteScalar de l’objet Command pour récupérer des valeurs de paramètre. En outre, ExecuteScalar retourne la première colonne de la première ligne de la procédure stockée. Cela est particulièrement utile pour les fonctions d’agrégation comme dans l’exemple suivant.

  1. Créez la procédure stockée suivante sur le serveur qui exécute SQL Server :
    Create Procedure TestProcedure2(
    @au_idIN varchar (11)
    )
    As
    /* set nocount on */
    select count (T.title)
    from authors as A join titleauthor as TA on
    A.au_id=TA.au_id
    join titles as T
    on T.title_id=TA.title_id
    where A.au_id=@au_idIN
    Return(5)

  2. Créer un nouveau projet d’Application Windows Visual C# .NET.
  3. Utilisez l’instruction using sur le système et les espaces de noms System.Data afin que vous n’ayez pas à qualifier des déclarations dans ces espaces de noms ultérieurement dans votre code. Ajoutez ce code au début du module de code de formulaire. Assurez-vous que vous copiez uniquement le code pour le fournisseur que vous avez choisi. SQL Client
    using System.Data.SqlClient;
    Fournisseur de données OLE DB
    using System.Data.OleDb;
  4. Ajoutez le code suivant à l’événement Form_Load :SQL Client
    string strCount;SqlConnection PubsConn = new SqlConnection 
    ("Data Source=server;integrated " +
    "Security=sspi;initial catalog=pubs;");
    SqlCommand testCMD = new SqlCommand
    ("TestProcedure2", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    SqlParameter RetVal = testCMD.Parameters.Add
    ("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;
    SqlParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", SqlDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;

    IdIn.Value = "213-46-8915";

    PubsConn.Open();

    strCount =testCMD.ExecuteScalar ().ToString() ;

    Console.WriteLine("Number of Rows: " + strCount );
    Console.WriteLine("Return Value: " + RetVal.Value);

    Fournisseur de données OLE DB
    string strCount;OleDbConnection PubsConn = new OleDbConnection 
    ("Provider=SQLOLEDB;Data Source=server;" +
    "integrated Security=sspi;initial catalog=pubs;");
    OleDbCommand testCMD = new OleDbCommand
    ("TestProcedure2", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    OleDbParameter RetVal = testCMD.Parameters.Add
    ("RetVal", OleDbType.Integer);
    RetVal.Direction = ParameterDirection.ReturnValue;
    OleDbParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", OleDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;

    IdIn.Value = "213-46-8915";

    PubsConn.Open();

    strCount = testCMD.ExecuteScalar().ToString() ;

    Console.WriteLine("Number of Rows: " + strCount);
    Console.WriteLine("Return Value: " + RetVal.Value);

  5. Modifiez la chaîne de connexion pour l’objet de connexion pointer vers l’ordinateur qui exécute SQL Server.
  6. Exécutez le code. Notez que la méthode ExecuteScalar de l’objet Command renvoie les paramètres. ExecuteScalar retourne également la valeur de la colonne 1, ligne 1 de l’ensemble de lignes renvoyé. Par conséquent, la valeur de intCount est le résultat de la fonction count à partir de la procédure stockée.

Utilisez la méthode ExecuteNonQuery de l’objet Command

Cet exemple utilise la méthode ExecuteNonQuery pour exécuter la requête et pour renvoyer les valeurs de paramètre. ExecuteNonQuery retourne également le nombre d’enregistrements qui sont affectées par l’exécution de la requête. Toutefois, ExecuteNonQuery ne renvoie pas de lignes ou des colonnes à partir de la procédure stockée.

La méthode ExecuteNonQuery est particulièrement utile lorsque vous utilisez des instructions INSERT, UPDATE ou DELETE si vous n’avez qu’à connaître le nombre de lignes est modifié. Dans une procédure stockée dans laquelle vous utilisez uniquement une instruction SELECT, vous recevez -1 car aucune lignes ne sont affectées par la requête.

  1. Créez la procédure stockée suivante sur l’ordinateur qui exécute SQL Server :
    Create Procedure TestProcedure3(
    @au_idIN varchar (11),
    @au_fnam varchar (30)
    )

    As
    /* set nocount on */
    Update authors set au_fname = @au_fnam
    where au_id = @au_idin
    return (5)

  2. Créer un nouveau projet d’Application Windows Visual C# .NET.
  3. Utilisez l’instruction using sur le système et les espaces de noms System.Data afin que vous n’ayez pas à qualifier des déclarations dans ces espaces de noms ultérieurement dans votre code. Ajoutez ce code au début du module de code de formulaire. Assurez-vous que vous copiez uniquement le code pour le fournisseur que vous avez choisi. SQL Client
    using System.Data.SqlClient;
    Fournisseur de données OLE DB
    using System.Data.OleDb;
  4. Remplacez le code ci-dessous l’événement Form1_Load privée dans le module de code de Form1 par le code suivant :SQL Client
    string strRowAffect;SqlConnection PubsConn = new SqlConnection 
    ("Data Source=server;integrated Security=sspi;" +
    "initial catalog=pubs;");
    SqlCommand testCMD = new SqlCommand
    ("TestProcedure3", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    SqlParameter RetVal = testCMD.Parameters.Add
    ("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;
    SqlParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", SqlDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    SqlParameter FnameIn = testCMD.Parameters.Add
    ("@au_fnam", SqlDbType.VarChar, 30);
    FnameIn.Direction = ParameterDirection.Input;

    IdIn.Value = "213-46-8915";
    FnameIn.Value = "Marjorie";

    PubsConn.Open();

    strRowAffect =testCMD.ExecuteNonQuery ().ToString() ;

    Console.WriteLine("Number of Rows: " + strRowAffect );
    Console.WriteLine("Return Value: " + RetVal.Value);

    Fournisseur de données OLE DB
    int intRowAffected;OleDbConnection PubsConn = new OleDbConnection 
    ("Provider=SQLOLEDB;Data Source=server;" +
    "integrated Security=sspi;initial catalog=pubs;");
    OleDbCommand testCMD = new OleDbCommand
    ("TestProcedure3", PubsConn);

    testCMD.CommandType = CommandType.StoredProcedure;

    OleDbParameter RetVal = testCMD.Parameters.Add
    ("RetVal", OleDbType.Integer);
    RetVal.Direction = ParameterDirection.ReturnValue;
    OleDbParameter IdIn = testCMD.Parameters.Add
    ("@au_idIN", OleDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    OleDbParameter FnameIn = testCMD.Parameters.Add
    ("@au_fname", OleDbType.VarChar, 30);
    FnameIn.Direction = ParameterDirection.Input;

    IdIn.Value = "213-46-8915";
    FnameIn.Value = "Marjorie";

    PubsConn.Open();
    intRowAffected = testCMD.ExecuteNonQuery();

    Console.WriteLine("Number of Rows affected: " + intRowAffected);
    Console.WriteLine(RetVal.Value);

  5. Modifiez la chaîne de connexion pour l’objet de connexion pointer vers l’ordinateur qui exécute SQL Server.
  6. Exécutez le code. La fenêtre Sortie affiche le nombre de lignes affectées (intRowAffect) et la valeur du paramètre de retour.

Références

Propriétés

ID d'article : 310070 - Dernière mise à jour : 27 janv. 2017 - Révision : 1

Microsoft ADO.NET 1.1, Microsoft Visual C# .NET 2002 Initiation, Microsoft Visual C# .NET 2003 Initiation

Commentaires