Paramètres des commandes d'adaptateur de données

Article
08/21/2008

Mise à jour : novembre 2007

Les commandes d'un adaptateur de données (définies dans la propriété CommandText des objets SelectCommand,InsertCommand, UpdateCommand etDeleteCommand) impliquent souvent des paramètres. Au moment de l'exécution, ces paramètres sont utilisés pour passer des valeurs aux instructions SQL ou aux procédures stockées représentées par les commandes.

Remarque :
Dans la version antérieure de Visual Studio, des adaptateurs de données étaient utilisés pour la communication entre une application et une base de données. Même si les adaptateurs de données figurent toujours parmi les principaux composants des .Fournisseurs de données .NET Framework (ADO.NET), les TableAdapters sont des composants générés par le concepteur qui simplifient le processus de déplacement des données entre votre application et une base de données. Pour plus d'informations sur l'utilisation de TableAdapters, consultez Vue d'ensemble de TableAdapter.

Dans la version antérieure de Visual Studio, des adaptateurs de données étaient utilisés pour la communication entre une application et une base de données. Même si les adaptateurs de données figurent toujours parmi les principaux composants des .Fournisseurs de données .NET Framework (ADO.NET), les TableAdapters sont des composants générés par le concepteur qui simplifient le processus de déplacement des données entre votre application et une base de données. Pour plus d'informations sur l'utilisation de TableAdapters, consultez Vue d'ensemble de TableAdapter.

Ils sont de deux types :

Paramètres de sélection : dans les applications de production, vous n'extrayez souvent qu'un sous-ensemble des données stockées dans une base. Pour ce faire, vous utilisez une instruction SQL ou une procédure stockée dans laquelle une clause WHERE inclut un critère de sélection obtenu au moment de l'exécution. En outre, lorsque vous mettez à jour ou supprimez des enregistrements, vous utilisez une clause WHERE désignant le ou les enregistrements à modifier. Les valeurs utilisées dans la clause WHERE sont habituellement dérivées au moment de l'exécution.

Paramètres de mise à jour - lorsque vous mettez à jour un enregistrement existant ou que vous en insérez un nouveau, les valeurs des colonnes de l'enregistrement modifié ou nouveau sont fixées au moment de l'exécution. En outre, les valeurs utilisées lors d'un contrôle d'accès concurrentiel optimiste sont fixées à l'aide de paramètres.

Remarque :
Pour Oracle, lorsque vous utilisez des paramètres nommés dans une instruction SQL ou une procédure stockée, vous devez faire précéder le nom du paramètre de deux-points (:). En revanche, lorsque vous faites référence à un paramètre nommé ailleurs dans le code (par exemple lors de l'appel à Add), ne faites pas précéder le paramètre nommé de deux-points (:). Le fournisseur de données ajoute automatiquement les deux-points. Pour plus d'informations, consultez la classe OracleParameter.

Pour Oracle, lorsque vous utilisez des paramètres nommés dans une instruction SQL ou une procédure stockée, vous devez faire précéder le nom du paramètre de deux-points (:). En revanche, lorsque vous faites référence à un paramètre nommé ailleurs dans le code (par exemple lors de l'appel à Add), ne faites pas précéder le paramètre nommé de deux-points (:). Le fournisseur de données ajoute automatiquement les deux-points. Pour plus d'informations, consultez la classe OracleParameter.

Paramètres de sélection

Lorsque vous sélectionnez des enregistrements pour remplir un groupe de données, vous incluez souvent un ou plusieurs paramètres dans la clause WHERE pour spécifier, au moment de l'exécution, les enregistrements à extraire. Par exemple, les utilisateurs peuvent chercher dans une base de données de livres, un mot clé correspondant à un titre qu'ils tapent dans une page Web. Pour permettre cela, vous pouvez spécifier une instruction SQL telle que la suivante comme propriété CommandText de SelectCommand. Les paramètres sont indiqués soit par un espace réservé (un point d'interrogation) soit par une variable de paramètre nommé. Les paramètres des requêtes impliquant des objets OleDbCommand et OdbcCommand utilisent des points d'interrogation ; les requêtes qui utilisent des objets SqlCommand utilisent des paramètres nommés commençant par le symbole @, tandis que les objets OracleCommand utilisent des paramètres nommés commençant par deux-points (:).

Voici un exemple de requête utilisant des espaces réservés :

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE ?)

Voici un exemple de requête utilisant des paramètres nommés SqlCommand :

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE @title)

Voici un exemple de requête utilisant des paramètres nommés OracleCommand :

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE :title)

Dans votre application, vous demandez à l'utilisateur d'entrer un mot clé de titre. Vous définissez ensuite la valeur du paramètre et exécutez la commande.

Remarque :
Dans certaines occasions, vous pouvez décider d'obtenir tout le contenu d'une table de base de données (par exemple, si vous configurez une table de correspondance), mais en général, vous souhaitez n'extraire que les données dont vous avez besoin pour conserver à votre application son efficacité.

Dans Visual Studio, vous pouvez créer des instructions SQL comportant des paramètres à l'aide du Générateur de requêtes. Si vous faites glisser des éléments à partir de l'Explorateur de serveurs, Visual Studio peut configurer les paramètres dans certains cas, mais pas dans tous, et vous devrez terminer manuellement la configuration.

Paramètres de mise à jour

Les commandes des propriétés UpdateCommand, InsertCommand et DeleteCommand contiennent toujours une commande paramétrée, ce qui n'est pas toujours vrai pour un objet SelectCommand d'un adaptateur.

Les commandes des propriétés UpdateCommand et InsertCommand exigent un paramètre pour chaque colonne de la base de données à mettre à jour. En outre, les instructions UpdateCommand et DeleteCommand requièrent une clause WHERE paramétrée identifiant l'enregistrement à mettre à jour et rappelant la manière dont l'objet SelectCommand est souvent configuré.

Prenons l'exemple d'une application permettant aux utilisateurs d'acheter des livres. Tandis qu'ils font leurs achats, les utilisateurs remplissent un panier, lequel est implémenté en tant que table de données. Dans la table ShoppingCart, les utilisateurs maintiennent pour chaque livre qu'ils souhaitent acheter un enregistrement dont la clé du panier d'achat est définie conjointement par l'ID du livre et celui du client.

Lorsque les utilisateurs placent un livre dans leur panier d'achat, l'application peut appeler une instruction SQL INSERT. Dans l'adaptateur, la syntaxe de l'instruction peut être la suivante :

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (?, ?, ?)

Les trois points d'interrogation représentent les espaces réservés des paramètres qui seront remplis au moment de l'exécution par des valeurs correspondant à l'ID du client, à celui du livre et à la quantité. Si vous utilisez des paramètres nommés, la même requête ressemblera à ceci :

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (@bookid, @custid, @quantity)

Si l'utilisateur décide de modifier un élément relatif à un article dans son panier d'achat, par exemple la quantité, l'application peut appeler une instruction SQL UPDATE. La syntaxe de l'instruction peut ressembler à ceci :

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

Oui bien, si vous utilisez des paramètres nommés, elle peut ressembler à ceci :

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

Dans cette instruction, les valeurs des paramètres de la clause SET sont égales aux valeurs mises à jour de l'enregistrement modifié. Les paramètres de la clause WHERE identifient l'enregistrement à mettre à jour et sont initialisés avec les valeurs originales issues de l'enregistrement.

Un utilisateur peut également supprimer un article de son panier d'achat. Dans ce cas, l'application peut appeler une instruction SQL DELETE dont la syntaxe, si vous utilisez les espaces réservés, ressemble à ceci :

DELETE FROM ShoppingCart
WHERE (BookId = ? AND CustId = ?)

Ou bien à ceci, si vous utilisez les paramètres nommés :

DELETE FROM ShoppingCart
WHERE (BookId = @bookid AND CustId = @custid)

Collection de paramètres et objets de paramètre

Pour vous permettre de passer des valeurs de paramètre au moment de l'exécution, chacun des quatre objets de commande d'un adaptateur de données prend en charge une propriété Parameters. La propriété contient une collection d'objets de paramètre correspondant chacun à un espace réservé d'une instruction.

Le tableau suivant illustre la collection de paramètres correspondant à chaque adaptateur de données :

Adaptateur de données	Collection de paramètres
SqlDataAdapter	SqlParameterCollection
OleDbDataAdapter	OleDbParameterCollection
OdbcDataAdapter	OdbcParameterCollection
OracleDataAdapter	OracleParameterCollection

Remarque :
Pour Oracle, lorsque vous utilisez des paramètres nommés dans une instruction SQL ou une procédure stockée, vous devez faire précéder le nom du paramètre de deux-points (:). En revanche, lorsque vous faites référence à un paramètre nommé ailleurs dans le code (par exemple lors de l'appel à Add), ne faites pas précéder le paramètre nommé de deux-points (:). Le fournisseur de données .NET Framework pour Oracle ajoute automatiquement les deux-points.

Pour Oracle, lorsque vous utilisez des paramètres nommés dans une instruction SQL ou une procédure stockée, vous devez faire précéder le nom du paramètre de deux-points (:). En revanche, lorsque vous faites référence à un paramètre nommé ailleurs dans le code (par exemple lors de l'appel à Add), ne faites pas précéder le paramètre nommé de deux-points (:). Le fournisseur de données .NET Framework pour Oracle ajoute automatiquement les deux-points.

En utilisant la collection de paramètres, vous n'êtes pas obligé de constituer manuellement une commande SQL en tant que chaîne avec valeurs à l'exécution ; en outre, vous bénéficiez du contrôle de type dans vos paramètres.

Si vous utilisez l'Assistant Configuration d'adaptateur de données pour configurer l'adaptateur, la collection de paramètres est automatiquement configurée pour les quatre commandes d'adaptateur. Si vous faites glisser des éléments à partir de l'Explorateur de serveurs vers le formulaire ou le composant, Visual Studio peut effectuer les configurations suivantes :

Si vous faites glisser une table ou quelques colonnes dans le concepteur, Visual Studio génère un objet SelectCommand (plus précisément, une instruction SQL SELECT) sans paramètre ainsi que des objets UpdateCommand, InsertCommand et DeleteCommand paramétrés. Si vous souhaitez que l'instruction de l'objet SelectCommand contienne des paramètres, vous devez les configurer manuellement.
Si vous faites glisser une procédure stockée dans le concepteur, Visual Studio génère un objet SelectCommand contenant les paramètres requis par la procédure stockée. Toutefois, si vous avez besoin d'eux, vous devez configurer vous-même les objets UpdateCommand, InsertCommand et DeleteCommand ainsi que leurs paramètres.

En règle générale, si vous voulez créer des requêtes paramétrées pour l'adaptateur, vous devez utiliser l'Assistant Configuration d'adaptateur de données. Toutefois, au besoin, vous pouvez configurer les paramètres manuellement dans la fenêtre Propriétés.

Structure de la collection de paramètres

Il existe une correspondance exacte entre les éléments d'une collection de paramètres d'une commande et les paramètres requis par l'objet de commande correspondant. Si l'objet de commande est une instruction SQL, les éléments de la collection correspondent aux espaces réservés (points d'interrogation) de l'instruction. L'instruction UPDATE suivante requiert une collection de cinq éléments de paramètre :

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

Voici la même instruction avec des paramètres nommés :

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

Si l'objet de commande référence une procédure stockée, le nombre d'éléments de paramètres contenus dans la collection est déterminé par la procédure elle-même. Les paramètres peuvent ne pas correspondre exactement aux espaces réservés d'une instruction SQL.

Dans les procédures stockées, les paramètres peuvent également être nommés. Dans ce cas, la position d'un paramètre dans la collection est sans importance. En effet, chaque paramètre de la collection est doté d'une propriété ParameterName qui est utilisée pour l'associer au paramètre correspondant de la procédure stockée.

Si vous configurez manuellement la collection de paramètres, vous devez savoir exactement de quels paramètres la procédure stockée a besoin. Beaucoup de procédures stockées retournent une valeur ; si tel est le cas, la valeur est repassée à votre application dans la collection de paramètres, une opération que vous devez par conséquent autoriser. En outre, certaines procédures stockées incluent plusieurs instructions SQL, et vous devez être sûr que la collection de paramètres reflète bien toutes les valeurs qui ont été passées à toutes les instructions de la procédure.

Si les paramètres ne sont pas nommés (ce qui est le cas dans les procédures stockées), il existe une correspondance de position entre les éléments de la collection et les paramètres requis par la commande. Si la commande est une procédure stockée et qu'elle retourne une valeur, le premier élément de la collection (élément zéro) est réservé pour cette valeur de retour.

Vous pouvez donc référencer des objets de paramètre par leur position d'index dans la collection. Toutefois, les objets de paramètre prennent également en charge une propriété ParameterName qui offre un moyen de référencer les paramètres indépendamment de leur rang. Par exemple, les deux instructions suivantes peuvent être équivalentes (en supposant que le second paramètre de la collection soit appelé Title_Keyword) :

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters(1).Value = titleKeyword
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword

// Encloses the keyword in SQL wildcard characters.
string titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters[1].Value = titleKeyword;
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;

L'utilisation d'un nom de paramètre est généralement une meilleure méthode de programmation que celle qui consiste à référencer les paramètres par leur valeur d'index, parce qu'elle réduit les besoins de maintenance en cas de changement du nombre de paramètres et que vous n'avez plus à vous rappeler si une procédure stockée retourne ou non une valeur. Le fait de référencer un paramètre par son nom plutôt que par sa valeur d'index impose une légère charge supplémentaire au système, mais ce petit inconvénient est aisément contrebalancé par la facilité de programmation et par la plus grande facilité de maintenance de l'application.

Fixation des valeurs de paramètre

Il existe deux manières d'établir la valeur d'un paramètre :

En définissant explicitement la propriété Value du paramètre.
En mappant les paramètres avec les colonnes d'une table de groupe de données de telle sorte que les valeurs soient extraites des lignes de données en fonction des besoins.

Vous définissez la valeur d'un paramètre explicitement lorsque vous remplissez un groupe de données ou que vous appelez une commande (pour les paramètres de sélection). Ainsi, dans l'exemple cité plus haut de recherche de livres, l'application pourrait contenir une zone de texte dans laquelle les utilisateurs entreraient un mot clé de titre. Vous utiliseriez ensuite le contenu de la zone de texte pour définir la valeur du paramètre, avant d'appeler la méthode Fill de l'adaptateur. Ainsi, le code suivant utilise le contenu d'une zone de texte pour définir la valeur d'un paramètre avant de remplir un groupe de données.

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword
OleDbDataAdapter1.Fill(dsAuthors1)

// Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;
this.OleDbDataAdapter1.Fill(dsAuthors1);

Les valeurs de paramètre mappées sont utilisées lors des mises à jour. Lorsque vous appelez la méthode Update d'un adaptateur, elle visite un à un tous les enregistrements d'une table de groupe de données, effectuant dans chacun la mise à jour appropriée (mise à jour, insertion ou suppression). Dans ce cas, les valeurs de paramètre sont déjà disponibles en tant que colonnes dans les enregistrements du groupe de données. Par exemple, lorsque le processus de mise à jour obtient un nouvel enregistrement de la table du groupe de données (un enregistrement pour lequel il doit appeler une instruction INSERT dans la base de données) les valeurs de la clause VALUE de l'instruction INSERT peuvent être directement lues à partir de l'enregistrement.

Ces exemples ne couvrent que les cas de figure les plus courants. En effet, il arrive parfois que les procédures stockées retournent des données en utilisant des paramètres de sortie ou la valeur de retour de la procédure. Dans ce cas, les valeurs retournées doivent être mises en correspondance avec les colonnes d'une table de groupe de données.

Il est également possible de définir explicitement les paramètres de mise à jour. L'adaptateur prend en charge un événement RowUpdating qui est appelé chaque fois qu'une ligne est mise à jour. Vous pouvez créer un gestionnaire pour cet événement et y définir des valeurs de paramètre. Vous avez ainsi un plein contrôle sur la valeur des paramètres et vous pouvez effectuer des processus tels que créer dynamiquement des valeurs de paramètre avant qu'elles ne soient écrites dans un enregistrement de la base de données.