Exécution de procédures stockées retournant des valeurs

tirent leur potentiel
de performances du fait que SQL
Server les compile avant de les utiliser.
Une compilation précoce permet à  la base
de données de se dispenser des étapes
d’analyse généralement requises et de la
création d’un plan d’accès. Les procédures stockées constituent
la véritable cheville ouvrière de la plupart des applications
de base de données ; les développeurs les utilisent
presque systématiquement pour les opérations INSERT, UPDATE
et DELETE, ainsi que pour récupérer des valeurs individuelles
et des ensembles de résultats.
L’exemple suivant explique comment utiliser l’objet
SqlCommand pour exécuter une procédure stockée
SQL Server qui accepte un paramètre en entrée et retourne
une valeur scalaire. Dans l’Analyseur de requêtes (Query
Analyzer), exécutez le code T-SQL du listing 3 afin de créer la
procédure stockée StockValue dans la base de données
exemple Northwind. Le paramètre en entrée de StockValue
est un entier qui identifie le ProductID. Elle retourne la valeur
en stock de ce ProductID à  partir de la table Products
dans la base de données Northwind. La procédure stockée
calcule la valeur en stock en récupérant le nombre
UnitInStock et en le multipliant par la valeur de la colonne
UnitPrice. StockValue affecte ensuite le résultat à  la variable
@StockValue, que la procédure stockée retourne sous
forme de valeur scalaire.
Après avoir créé l’exemple de procédure stockée dans la
base de données Northwind, votre application ADO.NET
peut l’appeler. L’exemple du listing 4 explique comment utiliser
l’objet SqlCommand pour exécuter la procédure stockée
StockValue et récupérer la valeur scalaire retournée. Le
code placé au début de cette sous-routine SQLCommandSPScalar
crée l’objet SqlConnection cn, puis un objet
SqlCommand cmd. Dans cet exemple, le constructeur de
l’objet SqlCommand utilise deux paramètres. Le premier est
une chaîne qui accepte la commande que l’objet
SqlCommand va exécuter. Cette chaîne peut contenir soit
une instruction SQL, soit le nom d’une procédure stockée.
Notre exemple utilise le nom de la procédure StockValue. Le
deuxième paramètre est le nom de l’objet SqlConnection utilisé
par la sous-routine pour se connecter à  la base de données
cible. Après avoir créé l’objet SqlCommand cmd, la section
suivante du code de la sous-routine affecte la valeur
commandType.StoredProcedure à  la propriété Command-
Type de l’objet, afin que l’objet SqlCommand exécute une
procédure stockée. Les valeurs que peut prendre la propriété
CommandType sont présentées dans le tableau 3.
La section suivante du code, présentée dans l’encadré A
du listing 4, crée l’objet SqlParameter qui conservera la valeur
scalaire contenant la valeur en stock retournée par la
procédure stockée StockValue. Le code attribue alors la valeur
de Parameter.ReturnValue à  la propriété Parameter-
Direction du paramètre de valeur retournée @StockValue. La sous-routine crée ensuite l’objet SqlParameter qui va fournir
la valeur en entrée à  la procédure stockée StockValue.
Pour créer les objets SqlParameter, vous pouvez soit employer
le constructeur de classe SqlParameter, soi exécuter la
méthode Add de la collection Parameters de l’objet
SqlCommand. Dans cet exemple, j’ai créé le paramètre à 
l’aide de la deuxième approche. Le premier paramètre fourni
à  la méthode Add est une chaîne contenant le nom du paramètre,
dans ce cas @ProductID. Gardez à  l’esprit que les paramètres
remplaçables utilisés par l’objet SqlParameter doivent
commencer par @. Le deuxième paramètre utilise
l’énumération SqlDbType.Int pour indiquer que le paramètre
contiendra une valeur de type entier. La ligne suivante affecte
la valeur ParameterDirection.Input à  la propriété Direction,
afin d’indiquer qu’il s’agit d’un paramètre en entrée.
Pour terminer, la sous-routine attribue la valeur 1 à  la propriété
Value de l’objet SqlParameter, stockant une valeur de
1 à  passer à  la procédure stockée StockValue. Dans l’encadré
B, le code met en place un bloc Try-Catch pour ouvrir la
connexion vers le système SQL Server, puis exécute la procédure
stockée StockValue. Notez dans le bloc Try-Catch que
la méthode ExecuteScalar de l’objet SqlCommand cmd exécute
la procédure stockée StockValue et que la sous-routine
affecte la valeur retournée à  la variable TextBox1. Comme
dans les exemples précédents, si la connexion ou la procédure
stockée échoue, un zone de message affiche le texte
d’erreur à  l’utilisateur et la connexion se ferme.