Exécution d’instructions SQL paramétrées

créer
un plan d’accès pour
celles-ci avant de les exécuter.
SQL Server gère de manière
intelligente l’exécution
du SQL dynamique. Il
stocke les instructions dynamiques
dans son cache
de procédures pendant un
certain temps, puis dès
qu’une application exécute
l’instruction, il utilise le
plan d’accès existant.
Même ainsi, la disponibilité de l’instruction dans le cache dépend
de l’activité de la base de données et le SQL dynamique
ne peut garantir que le plan sera disponible à  la prochaine
exécution de l’instruction. En revanche, le SQL paramétré
demeure dans le cache de procédure jusqu’à  la fermeture de
la connexion de l’application.
Les instructions SQL paramétrées constituent en
quelque sorte de passerelle entre les procédures stockées et
le SQL dynamique. Comme les premières, elles peuvent accepter
différentes valeurs de paramètres au moment de
l’exécution. Comme le second, elles ne sont pas persistantes
dans la base de données. Toutefois, à  la différence du SQL
dynamique, SQL Server analyse le SQL paramétré et crée le plan d’accès une seule fois, lors
de la première préparation de
l’instruction. Les exécutions suivantes
de l’instruction tirent
parti du plan d’accès existant.
L’exemple de code du listing
2 explique comment utiliser
l’objet SqlCommand pour créer
et exécuter une instruction SQL
préparée. Tout en haut de la
sous-routine SQLCommandPreparedSQL,
l’application utilise
les variables sServer et sDB pour
passer le nom du serveur de
base de données cible et le nom
de la base de données à  la sousroutine.
Ensuite, la sous-routine
crée un nouvel objet SqlConnection
nommé cn, puis un nouvel
objet SqlCommand nommé
cmd. Dans cet exemple, le
constructeur de l’objet SqlCommand
accepte deux paramètres.
Le premier paramètre affecte
une instruction SQL à  l’objet
cmd. Il peut s’agir d’une instruction
SQL ou du nom d’une procédure stockée. Dans notre
cas, une instruction INSERT est employée pour insérer des
valeurs dans deux colonnes de la table Department. Le
deuxième paramètre fournit le nom de l’objet SqlConnection.
L’aspect essentiel de cet exemple est le format des marqueurs
de paramètre dans l’instruction SQL. Ces marqueurs
indiquent les caractères remplaçables dans une instruction
SQL préparée. Au moment de l’exécution, la
sous-routine remplace ces paramètres par
les valeurs fournies par la collection
Parameters de l’objet SqlCommand.
A la différence d’ADO ou de l’objet
ADO.NET OleDbCommand, qui utilise un
point d’interrogation (?) pour indiquer les
paramètres remplaçables, l’objet SqlCommand
impose à  tous les marqueurs de
paramètres de commencer par un arobas
(symbole @). Cet exemple illustre deux
marqueurs de paramètres : @DepartmentID
et @DepartmentName. le deuxième argument du
constructeur de l’objet SqlCommand associe l’objet
SqlCommand cmd et l’objet SqlConnection cn créé précédemment.
Ensuite, le code de l’encadré A du listing 2 crée deux
objets SqlParameter. Le premier objet paramètre, parm
DepartmentID, fournit des valeurs au premier marqueur de
paramètre (@DepartmentID). De la même manière, le
deuxième objet paramètre, parmDepartmentName, fournit
des valeurs utilisées par le deuxième paramètre remplaçable
(@DepartmentName). L’exemple de code de cette sousroutine
passe deux arguments au constructeur de
l’objet SqlParameter. Le premier paramètre fournit le nom de
l’objet SqlParameter. A ce stade, vous devez être sûr que le
nom fourni au constructeur de l’objet SqlParameter correspond
à  celui employé dans le marqueur de paramètre de l’instruction SQL préparée. Le deuxième paramètre passé
par la sous-routine au constructeur SqlParameter spécifie le
type de données du paramètre. Ensuite, la sous-routine utilise
l’énumération ParameterDirection.Input afin de placer la
propriété Direction de l’objet
SqlParameter en mode entrée. Le
tableau 2 répertorie les énumérations
valides pour cette propriété.
Après avoir créé les objets
SqlParameter, la prochaine étape
consiste à  les ajouter à  la collection
Parameters de l’objet Sql
Command. Pour ce faire, vous
employez la méthode Add de la
collection pour ajouter les objets
SqlParameter parmDepart –
mentID et parmDepartment-
Name à  l’objet SqlCommand cmd. L’ordre dans lequel les objets
SqlParameter sont ajoutés n’a pas d’importance. Ensuite,
au niveau du bloc Try-Catch dans l’encadré B du listing 2, la
sous-routine utilise la méthode Open de l’objet SqlConnection
cn pour ouvrir une connexion
avec SQL Server, puis emploie
l’instruction Prepare pour préparer
l’instruction SQL. Il convient de noter
que la sous-routine exécute la méthode
Prepare une fois décrits tous
les attributs des paramètres. Une
boucle For-Next ajoute ensuite 10
lignes à  la table Department que vous
avez créée. Au sein de cette boucle, la
sous-routine affecte une nouvelle valeur
de données à  la propriété Value
de chaque objet paramètre. Par souci
de simplicité, le code utilise une
boucle et ajoute simplement 1 à  la valeur
de départ. La sous-routine affecte
au paramètre parmDepartementID
la valeur du compteur de
boucle contenu dans la variable i et
assigne au paramètre parmDepartementName
une chaîne contenant
le littéral « New Department » avec la
valeur courante du compteur de
boucle.
Pour finir, la méthode Execute-
NonQuery de l’objet SqlCommand
exécute l’instruction SQL. J’ai employé
cette méthode car cet exemple
fait appel à  une requête d’action SQL
qui ne retourne aucune valeur. Du
point de vue SQL Server, l’exécution
de la méthode ExecuteNonQuery entraîne l’exécution par l’application
d’une commande sp_execute
en vue d’effectuer l’insertion.
Le recours au Générateur
de profils (Profiler) SQL Server
peut permettre de connaître
exactement les commandes SQL
envoyées par l’application à  SQL
Server. En cas d’erreur dans le
déroulement des opérations du bloc Try, le code du bloc
Catch s’exécute et une zone de message affiche le texte de la
condition d’exception. A la fin de la sous-routine, l’application
exécute la méthode Close de l’objet SqlConnection afin
de fermer la connexion avec la base de données SQL Server.