♪
I. Création d'une procédure stockée▲
I-A. Pourquoi une procédure stockée ?▲
Une procédure stockée c'est quoi exactement ? Une procédure stockée est une routine écrite en Transact-SQL (langage dérivé du SQL, spécialisé pour des traitements avancés sous SQL Server) qui agit sur les lignes d'une ou plusieurs tables.
Toutes les instructions SQL permettent d'agir sur des lignes sélectionnées (elles sélectionnent, mettent à jour, suppriment les lignes).
Mais SQL n'est pas en mesure de changer le cours de l'action qui dépend des valeurs des champs. Il existe peu de fonctions permettant de réaliser des opérations un peu complexes, par exemple il n'y a pas d'instruction IF, peu de fonctions pour manipuler les Strings (Chaînes de caractères), aucune fonction de formatage, etc.
Donc tous les concepteurs de système de gestion de base de données font étendre le SQL standard avec des instructions qui ajoutent les fonctionnalités d'un langage de programmation.
Les procédures stockées sont rattachées aux bases de données SQL Server et deviennent des objets de la base de données, tout comme les tables et les vues. L'application la plus simple des procédures stockées est de rattacher des requêtes complexes à une base de données et de les appeler par leurs noms, afin que les utilisateurs n'aient pas à les taper plus d'une fois.
Une procédure stockée est exécutée sur le serveur et utilise des instructions T-SQL. T-SQL est pratiquement un langage de programmation. Il ne possède aucune interface utilisateur donc vous ne pouvez pas l'utiliser pour développer des applications complètes, mais lorsqu'il s'agit de faire des requêtes, de mettre à jour la base de données, de traiter les données, ce langage peut tout faire.
Alors on revient maintenant sur la question : pourquoi utiliser des procédures stockées au lieu des requêtes SQL directement ? La réponse est que T-SQL est le langage natif de SQL Server et les procédures stockées sont exécutées sur le serveur. Une procédure stockée peut scanner des milliers d'enregistrements, effectuer des calculs, etc. Si vous effectuez des calculs qui impliquent un grand nombre de lignes, vous pouvez éviter de télécharger trop d'informations vers le client en écrivant une procédure stockée pour effectuer le travail sur le serveur à la place ! Les Procédures Stockées sont exécutées plus rapidement, car elles sont compilées et elles n'ont pas besoin de déplacer les données du serveur vers le client.
Une autre bonne raison d'utiliser les procédures stockées est : une fois qu'elles sont définies, elles font partie de la base de données et apparaissent aux applications comme des objets de la base de données tout comme des tables et des vues. En incluant toutes ces fonctionnalités dans la procédure stockée, vous allez simplifier le codage de l'application client.
OUF ! C'est la fin du blabla de la théorie désolé ! Maintenant, allons à la pratique, plus fun !
I-B. Création d'une procédure stockée▲
Pour écrire déboguer et exécuter des procédures stockées dans une base de données SQL Server vous devez utiliser l'analyseur de requêtes (Query Analyser). Pour créer une nouvelle procédure, entrez sa définition dans le panneau Query puis pressez sur les touches Ctrl+E pour exécuter sa définition. Attention comprenez bien que cette action ne va que rattacher la procédure à la base de données, mais ne va pas en fait exécuter la procédure stockée même !
Pour exécuter une procédure stockée, vous devez lancer l'instruction suivante : EXECUTE.
Pour créer une nouvelle procédure stockée et la rattacher à la base de données en cours vous devez utiliser l'instruction CREATE PROCEDURE. La syntaxe générale de l'instruction est la suivante :
CREATE PROCEDURE procedure_name
AS
{ procedure definition }Où procedure_name est le nom de la nouvelle procédure stockée et le bloc d'instructions qui suit le mot clé AS est le corps de la procédure.
Je vais vous donner un exemple tout bête de création de procédure stockée. Ma procédure stockée nommée OrdersByDate va permettre de sélectionner les commandes effectuées entre deux dates tout simplement. Ma procédure stockée va prendre en entrée deux paramètres, lesquelles ? Les dates bien entendu ! Voyons comment cela se présente :
USE NORTHWIND
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OrdersByDate')
DROP PROCEDURE OrdersByDate
GO
CREATE PROCEDURE OrdersByDate
@StartDate datetime, @EndDate datetime
AS
SELECT * FROM Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDateVous allez me dire c'est quoi ce charabia ? Pas de panique c'est simple. La première instruction T-SQL
USE NORTHWINDElle permet de préciser la base de données à laquelle va se rattacher la procédure stockée que l'on va créer. Ici on choisit la célèbre base de données Northwind contenue dans SQL Server. Une fois que la base de données est bien claire dans notre tête, on va tester l'existence de la procédure stockée, et oui il est possible qu'une procédure stockée avec le même nom soit rattachée à la base de données Northwind. On utilise le mot clé EXISTS pour voir si la procédure stockée est dans la table système sysobjects qui liste tous les objets de la base de données. Rappelez-vous qu'une procédure stockée rattachée à une base de données est considérée comme un objet.
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OrdersByDate')Si cette condition est vraie (Exists renvoie true) alors on laisse tomber la création de la procédure stockée, on ne fait rien. On fait donc un drop Procedure (laisser tomber la procédure) suivi du nom de la procédure et on sort.
DROP PROCEDURE OrdersByDateSi cette condition est fausse, alors on poursuit la création de la procédure stockée à partir de l'instruction GO. GO permet de séparer les différentes parties de la création de la procédure stockée.
GO
CREATE PROCEDURE OrdersByDate
@StartDate datetime, @EndDate datetime
AS
SELECT * FROM Orders
WHERE OrderDate BETWEEN @StartDate and @EndDateOn fait appel à l'instruction CREATE PROCEDURE pour créer une procédure stockée suivie de son nom OrdersByDate, ensuite on peut préciser les paramètres (cela n'est pas obligatoire on peut créer des procédures stockées sans paramètres).
Ces paramètres sont des paramètres d'entrée par défaut (l'utilisateur doit fournir leurs valeurs), les paramètres peuvent être aussi en sortie (on renvoie une valeur), ou même d'entrée et de sortie.
On précise donc les paramètres en donnant leur nom avec le symbole @ pour dire à SQL Server qu'il s'agit d'un paramètre (ou plus généralement d'une variable locale) et non d'un mot clé. On précise ensuite le type du paramètre, il doit s'agir d'un type valide dans SQL Server. Dans notre cas nous avons besoin des dates : datetime. Les paramètres d'une procédure stockée sont séparés par des virgules.
Exemple général
@nom1 type1, @nom2 type2 Dans notre exemple on a des dates de début et de fin : StartDate datetime, @EndDate datetime
Ensuite on utilise le mot clé AS pour préciser le début du corps de la procédure stockée.
Le corps de la procédure stockée est une sélection de lignes de la table Orders (commandes) dont la date de commande (OrderDate) est comprise entre une date de début et de fin. Les intervalles des dates sont précisés par les paramètres d'entrée.
SELECT * FROM Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDateLancez cet ensemble d'instructions avec Ctrl+E, vous verrez le succès de l'exécution avec le message : « The command(s) completed successfully ».
La commande s'est terminée avec succès.
Si vous relancez cette commande, l'instruction DROP PROCEDURE sera exécutée. Vous pouvez vérifier avec la commande PRINT :
USE NORTHWIND
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OrdersByDate')
PRINT 'Déjà fait'
GO
CREATE PROCEDURE OrdersByDate
@StartDate datetime, @EndDate datetime
AS
SELECT * FROM Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDateLe message suivant s'affichera : « Déjà fait » Voilà Félicitations vous avez créé facilement une nouvelle procédure stockée ! Vous pouvez la voir dans la liste des procédures stockées de la base de données Northwind. Passez à la suite pour voir la liste des procédures stockées :
Et le code généré pour cette procédure stockée OrdersByDate est le suivant :
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE OrdersByDate
@StartDate datetime, @EndDate datetime
AS
SELECT * FROM Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
GO
SET QUOTED_IDENTIFIER OFF
GO SET ANSI_NULLS ONCe code possède l'instruction ALTER PROCEDURE, cela signifie que si vous changez le corps de la procédure stockée et que vous exécutiez cette commande, la procédure stockée serait altérée, modifiée. Vous pouvez donc changer la logique de la procédure stockée à tout moment sans changer le code de l'application client à condition que l'interface reste la même (on garde les mêmes paramètres).
OUF ! C'est terminé pour cette petite introduction sur SQL Server et ses procédures stockées, nous allons maintenant nous tourner vers notre environnement préféré le DOTNET avec Windows Forms et le langage C# (Csharp). Nous verrons comment utiliser cette procédure stockée à partir d'une application Windows écrite en C#. Mais avant testons notre procédure stockée directement sur SQL Server 2000 avant de l'utiliser dans DOTNET.
I-C. Test de la procédure stockée sous SQL Server 2000▲
Nous allons à présent tester notre procédure stockée avec le SQL Query Analyzer. Pour tester cette procédure OrdersByDate, vous devez d'abord l'attacher à la base de données Northwind. Sélectionnez Northwind dans la liste déroulante des bases de données :
Si vous ne sélectionnez pas la bonne base de données, vous verrez le message d'erreur suivant après avoir testé la procédure stockée :
Pour tester la procédure stockée OrdersByDate tapez le code suivant :
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate='1/1/1997'
SET @EndDate='1/20/1997'
EXECUTE OrdersByDate @StartDate,@EndDateVous déclarez deux variables qui seront passées en paramètres d'entrée de votre procédure stockée à savoir la date de début et de fin.
On déclare donc ces deux variables avec le mot clé DECLARE, utilisez le symbole @ en préfix pour le nom de chaque variable puis terminez l'instruction en précisant le type de donnée (datetime).
Initialisez les variables avec des valeurs à l'aide du mot clé SET (les dates doivent sur mon SQL server avoir le format américain). L'idéal est de passer les dates en format universel. N'oubliez pas d'encadrer les valeurs par des quotes !
Précisez que vous voulez maintenant lancer l'exécution de la procédure stockée avec le mot clé EXECUTE, suivi du nom de la procédure stockée, suivi des variables qui seront utilisées comme paramètres de la procédure stockée, ces variables (date de début et de fin) seront séparées par des virgules.
Une fois tapé le code, lancer l'exécution pour tester vous verrez le résultat suivant (pour mes dates) :
On ne voit qu'une partie du résultat.
Si je ne veux que les commandes du 1er janvier 1997 par exemple
On n'obtient alors que deux commandes pour ma table de données. Vous pouvez également tester votre procédure stockée sans la moindre ligne de code ! Allez dans la liste de procédures stockées de la base de données Northwind, sélectionnez la procédure stockée OrdersByDate avec le bouton droit et sélectionner Open (ouvrir) :
Vous verrez la fenêtre suivante :
Cette fenêtre vous permet de tester votre procédure stockée en l'exécutant. Entrer la valeur du premier paramètre @StartDate dans le champ de saisie "Value :" Faites de même en sélectionnant le deuxième paramètre @EndDate. Puis lancez l'exécution avec le bouton Execute !
Valeurs manquantes !
Server: Msg 201, Level 16, State 3, Procedure OrdersByDate, Line 0 Procedure 'OrdersByDate' expects parameter '@EndDate', which was not supplied.
Stored Procedure: Northwind.dbo.OrdersByDate
Mauvais format !
Server: Msg 241, Level 16, State 1, Line 4
Syntax error converting datetime from character string.
Si vous exécutez sans préciser les valeurs des paramètres, un message d'erreur sera affiché. Si vous précisez bien les dates dans le bon format, vous verrez le résultat suivant :
Le code est automatiquement généré, plus complexe, mais le résultat est similaire à celui obtenu auparavant. Notre procédure stockée fonctionne bien. Maintenant il faut l'utiliser dans notre application!
II. Utilisation de la procédure stockée dans une application Windows Forms avec C#▲
Pour tester notre procédure stockée dans une application Windows Forms C# je vais rester simple, un bouton et un datagrid suffisent. Double-cliquez sur le bouton pour générer le code qui sera le gestionnaire de l'événement Click du bouton. Dans ce gestionnaire d'événement je vais vous montrer le code nécessaire pour tester la procédure stockée.
Le code complet C# pour pouvoir utiliser notre procédure stockée OrdersByDate est le suivant :
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
private void button1_Click(object sender, System.EventArgs e)
{
SqlConnection con= new SqlConnection("Data Source=NICOLAS;" +
"Initial Catalog=Northwind;Integrated Security=SSPI");
try
{
SqlCommand com= new SqlCommand("OrdersByDate",con);
com.CommandType=CommandType.StoredProcedure;
com.Parameters.Add("@StartDate",SqlDbType.DateTime).Value=
new DateTime(1997,1,1);
com.Parameters.Add("@EndDate",SqlDbType.DateTime).Value=
new DateTime(1997,1,1);
SqlDataAdapter DA= new SqlDataAdapter(com);
DataSet dt=new DataSet("Orders");
DA.Fill(dt,"OrdersByDate");
dataGrid1.SetDataBinding(dt,"OrdersByDate");
}
catch(Exception ex)
{
MessageBox.Show(this,ex.Message,"Erreur",MessageBoxButtons.OK);
}
}Mon but est de vous donner le code minimal pour tester la procédure stockée, il ne s'agit pas d'un code c# solide ou robuste. Si vous voyez des améliorations à faire, envoyez un email à legrandnico@yahoo.com, je prendrai toute remarque en considération !
Pour pouvoir utiliser une procédure stockée, j'ai besoin de me connecter au système de gestion de base de données relationnelle MS SQL Server 2000 (ou version 7). Pour cela il me faut un fournisseur (provider) de donnée, je vais utiliser le provider .NET pour SQL-Server, car il donne des meilleures performances que OLE DB. Il faut indiquer à l'application notre intention d'utiliser ce fournisseur de donnée SQL avec la directive using System.Data.SqlClient. On utilise donc l'application client qui va travailler avec le serveur contenant la base de données Northwind. Les types utilisés par ce fournisseur seront : SqlConnection, SqlCommand, SqlDataAdapter.
De plus j'ai besoin de travailler avec les types de données appartenant à l'architecture déconnectée d'ADO.NET en utilisant la directive using System.Data ; On utilisera seulement le type DataSet qui est une représentation en mémoire du résultat de notre procédure stockée !
On a donc les deux directives :
using System.Data;
using System.Data.SqlClient;Ensuite dans le code du gestionnaire de l'événement Click du composant Button, on commence par créer un objet du type SqlConnection qui va représenter la connexion active vers la base de donnée Northwind. En paramètre on fait passer la chaîne de connexion (Connection String). Dans cette chaîne de connexion, on précise tous les paramètres permettant cette connexion, on a donc un ensemble de paires Nom/Valeur séparées par des points virgules :
- DataSource : précise le nom du serveur SQL Server (je sais le nom est mal choisi pour DataSource, je pensais que c'était la base de données) ;
- Initial Catalog : précise la base de données : Northwind ;
- Integrated Security : précise qu'on utilise la sécurité intégrée du Système Windows.
Si vous voulez plus d'informations sur le Connection String je vous conseille d'aller sur le site : http://www.connectionstrings.com/ qui est très pratique, car il vous guide comment établir votre chaine de connexion. Il est par contre en anglais seulement.
Une fois l'objet Connection créé, on va créer l'objet commande qui va utiliser la procédure stockée.
Pour créer un objet SqlCommand vous précisez en paramètre le nom de la procédure stockée « OrdersByDate » faites attention aux fautes d'orthographe sur le nom ! On précise en deuxième paramètre quel objet connection cet objet command va utiliser.
Ensuite on va préciser le type de la commande. Effectivement par défaut, le type est un texte représentant une requête SQL ! Donc il faut préciser que le premier paramètre du constructeur du SqlCommand n'est pas du texte pour une requête SQL, mais un nom d'une procédure stockée !
SqlCommand com= new SqlCommand("OrdersByDate",con);
com.CommandType=CommandType.StoredProcedure;Ainsi on n'aura aucune erreur. Remarquez que j'ai encadré toutes les instructions à risque entre les instructions Try et Catch pour attraper les exceptions (par exemple une mauvaise connexion, ou une procédure stockée non reconnue, etc.).
Ensuite avant d'appeler la procédure stockée, il s'agit de préciser les valeurs des paramètres en entrée: la date de début et de fin.
On va accéder à la collection des objets SqlParameter de l'objet SqlCommand à l'aide de sa propriété Parameters et on ajoute un nouvel objet Parameter à la collection à l'aide de la méthode Add() de la collection d'objets SqlParameter. En paramètre vous passez le nom du paramètre (attention ce nom doit être le même que celui correspondant dans la procédure stockée : @StartDate doit être @StartDate et pas un autre nom sinon cela plante, peu importe l'ordre des ajouts, c'est le nom qui compte !), vous précisez ensuite le type du paramètre (un type de SQL serveur : SqlDbType.DateTime). Une fois la méthode Add() appelée, elle renvoie un objet SqlParameter, vous pouvez directement faire appel à la propriété Value pour initialiser la valeur de cet objet SqlParameter :
com.Parameters.Add("@StartDate",SqlDbType.DateTime).Value = new DateTime(1997,1,1);
com.Parameters.Add("@EndDate",SqlDbType.DateTime).Value = new DateTime(1997,1,1);On donne comme valeur aux paramètres des objets DateTime. Je précise des valeurs statiques pour simplifier le code du test de la procédure stockée. Il faudrait en fait récupérer la valeur de la date donnée en format français par l'utilisateur, convertir cette date en format universel et obtenir l'objet DateTime correspondant. On pourrait dans ce but créer une fonction helper.
Maintenant que les paramètres sont précisés et initialisés, nous construisons un objet sqlDataAdapter à partir de l'objet SqlCommand. L'objet sqlDataAdapter représente le lien entre la couche connectée d'ADO.NET (connection, data adapter, data command, data reader, etc.) et la couche déconnectée d'ADO.NET (Dataset, DataTable, DataRow, DataColumn etc.). On construit l'objet data adapter en utilisant l'un de ses constructeurs surchargés. On fait passer au constructeur du SqlDataAdapter l'objet SqlCommand :
SqlDataAdapter DA= new SqlDataAdapter(com);Une fois que le data adapter est en place, construisons un objet DataSet en précisant le nom de ce groupe de données déconnecté. Je l'appellerai « Orders ». Le DataSet est pour l'instant totalement vide.
Dans un DataSet il y a plusieurs collections : une collection de DataTable (DataTableCollection), une collection de DataRelation (DataRelationCollection) et une collection de Propriétés du DataSet (PropertyCollection).
Dans notre cas, on n'a pas besoin de construire manuellement la structure interne du DataSet, on peut simplement demander au SqlDataAdapter de remplir (peupler) le DataSet en appelant sa méthode Fill(). La méthode Fill() est très puissante, elle permet de lancer la connexion à la base de données, d'exécuter la commande de l'objet SqlCommand (ici c'est la procédure stockée), d'utiliser la structure et les données du résultat de l'exécution de la procédure stockée, et enfin de fermer la connexion active à la fin. Fill() prend deux paramètres, elle prend un premier paramètre l'objet DataSet dt qui doit être peuplé, en deuxième paramètre on a donné un nom au DataTable qui va être généré. Ce DataTable aura la structure ou le schéma du résultat de notre procédure stockée « OrdersByDate ». Une fois l'exécution de la méthode Fill() achevée on obtient un DataSet ne contenant qu'un seul DataTable avec ses données.
Le DataTable est l'objet qui représente la table nommée « OrdersByDate » contenue dans le DataSet nommé « Orders » représenté par l'objet DataSet nommé dt.
Un schéma pour mieux comprendre ce charabia serait :
Une fois le DataSet peuplé, vous voulez observer l'ensemble de données sur votre application. Pour cela il suffit de faire la liaison de donnée entre votre composant grille de donnée DataGrid de votre application et le DataSet.
Pour cela plusieurs possibilités existent, une possibilité très simple est d'utiliser la méthode SetDataBinding du composant DataGrid, cette méthode prend deux paramètres, le premier prend le DataSet vers lequel le composant effectuera sa liaison de donnée, le deuxième paramètre représente le nom du DataTable contenu dans ce DataSet ici il s'agit de « OrdersByData » la seule table de notre DataSet.
Data binding :
dataGrid1.SetDataBinding(dt,"OrdersByDate");Après l'exécution de cette méthode, le DataGrid affichera l'ensemble des résultats de notre procédure stockée :
Un fait intéressant est que les dates sont affichées automatiquement au format local de la machine ! Le format français dans mon cas !
III. Conclusion▲
Grâce à cet article vous devez savoir maintenant comment créer n'importe quelle procédure stockée dans votre base de données SQL Server avec ou non des paramètres en entrée. Vous devez savoir maintenant quelles étapes vous devez suivre pour exécuter des procédures stockées dans vos applications DOTNET (Windows Form, ASP.NET ou autre).
Tout commentaire est la bienvenue ! Si vous remarquez une erreur de ma part, n'hésitez pas à me le dire par email
De même que toute amélioration de l'article ou du code utilisé est la bienvenue. Toute modification entraînera une nouvelle version de l'article comme dans nos applications. :-)
Je remercie Ronald pour son aide précieuse pour mes premiers pas. Je remercie loufab pour la relecture et ses suggestions.
