Skip to main content

LINQ to SQL (Partie 6 – Récupérer des données en utilisant les procédures stockées)

 

Fabien Lavocat

L’article ci-dessous est la traduction de l’article de Scott Guthrie.

Par Fabien Lavocat, International Program Manager Visual Studio, formateur au laboratoire .NET à l’école supérieure d’informatique SUPINFO.

Note de l’auteur : Cet article est rédigé pour Visual Studio 2008 Bêta 2 - .NET Framework 3.5 Bêta 2. [Version RTM] Les lignes en rouge comme celles-ci, correspondent aux mises à jour pour la version RTM de Visual Studio 2008 et du .NET Framework 3.5.


Au cours des dernières semaines j'ai écrit une série d’articles sur LINQ to SQL. LINQ to SQL est un O/RM intégré (object relational mapper) qui est fourni dans le .NET Framework 3.5, et qui vous permet de modéliser facilement des bases de données relationnelles à l'aide des classes .NET. Vous pouvez utiliser des expressions LINQ pour interroger la base de données, ainsi que mettre à jour / insérer / supprimer des données.

Voici les cinq premières parties de ma série d’articles sur LINQ to SQL :

Dans les articles précédents sur LINQ to SQL, j’ai démontré comment vous pouvez utiliser les expressions de requêtes LINQ pour récupérer des données par programmation dans une base de données.

Dans l’article d’aujourd'hui, je vais montrer comment vous pouvez également utiliser procédures stockée dans la base de données (SPROCs) et les fonctions définies par l’utilisateur (UDF) avec votre modèle de données LINQ to SQL. L’article montrera précisément comment appeler des SPROCs pour interroger et récupérer des données de la base de données. Dans mon prochain article je vous montrerez comment vous pouvez éventuellement utiliser des SPROCs pour mettre à jour/Insérer/supprimer dans la base de données.

Procédures stockées ou pas procédures stockées ? That is the question....

La question de savoir si utiliser un code SQL dynamique généré par un ORM ou plutôt utiliser des procédures stockées lors de la création d'une couche Données est un sujet qui génère d’interminables débats (très passionnés) entre les développeurs, architectes et administrateurs de base de données.  Beaucoup de gens bien plus intelligents que moi ont écrit sur ce sujet, de sorte que je ne remanierai pas les arguments pour et contre ici.

Le concepteur LINQ to SQL fourni dans .NET 3.5 est assez souple et peut servir pour créer des classes du modèle de données dont le modèle objet peut être indépendant du schéma de base de données sous-jacent, et qui encapsule la logique métier et règles de validation qui fonctionnent indépendamment de savoir si le modèle de données est rempli/persisté via un code SQL dynamique ou via des procédures stockées.

Dans la partie Partie 3 : Interroger notre base de données j’ai montré comment vous pouvez écrire des expressions de requête LINQ sur un modèle de données LINQ to SQL en code comme ci-dessous :

 

 

Lorsque vous écrivez des expressions de requêtes LINQ comme celle que le concepteur LINQ to SQL exécutera le code SQL dynamique nécessaire pour pouvoir récupérer des objets Product qui correspondent à votre requête.

Comme vous allez le voir dans cet article, vous pouvez également mapper des procédures stockées dans la base de données dans votre classe DataContext de LINQ to SQL, qui vous permet de récupérer les mêmes objets Product en appelant à la place une procédure stockée :

 

 

Cette capacité d'utiliser le code SQL dynamique et les procédures stockées avec une couche du modèle de données propres est assez puissante et fournit une grande flexibilité lorsque l’on travaille sur des projets.

Les étapes pour mapper et appeler des procédures stockées en utilisant LINQ to SQL

Dans la partie Partie 2 : Définition de nos classes du modèle de données j’ai montré comment utiliser le designer LINQ to SQL pour créer un modèle de classe LINQ to SQL comme ci-dessous :

 

 

Notez ci-dessus que deux volets sont présents sur l’aire de conception LINQ to SQL. Le volet de gauche permet de définir des classes de modèle de données qui correspondent à notre base de données. Le volet de droite, aux méthodes que nous avons éventuellement mappées aux procédures stockées (et des fonctions définies par l'utilisateur) à notre objet DataContext LINQ to SQL, que nous pouvons alors utiliser à la  place du code SQL dynamique pour remplir les objets du modèle de données.

Comment lier des procédures stockées à un DataContext LINQ to SQL

Pour mapper des procédures stockées à notre classe DataContext, nous allons tout d'abord accéder à la fenêtre Explorateur de serveurs de VS 2008 et regardez les procédures stockées au sein de notre base de données :

 

 

Nous pouvons double-cliquer sur n’importe quelle procédure stockée ci-dessus pour l’ouvrir et l’éditer. Par exemple, vous pouvez retrouver ci-dessous, la procédure stockée « CustOrderHist » dans la base Northwind.

 

 

Pour mapper la procédure stockée ci-dessus à notre DataContext LINQ to SQL, nous pouvons la glisser/déplacer partir de l'explorateur de serveurs sur notre concepteur LINQ to SQL. Cela créera automatiquement une nouvelle méthode à notre classe DataContext LINQ to SQL comme ci-dessous :

 

 

Par défaut le nom de la méthode créé dans la classe DataContext sera identique au nom de la procédure stockée, et le type de retour de la méthode peut être un type créé automatiquement suivant le modèle d'affectation de noms « [NomProcedure]Resultat ». Par exemple : la procédure stockée ci-dessus retournera une séquence d'objets « CustOrderHistResult ». Nous pourrions éventuellement modifier le nom de la méthode en la sélectionnant dans le concepteur et ensuite utiliser la grille des propriétés pour la renommer.

Comment appeler notre procédure stockée nouvellement mappée

Une fois que nous avons fait les étapes ci-dessus pour mapper une procédure stockée à notre classe DataContext, il est facile de l’utiliser pour récupérer des données par programmation. Tout ce dont nous avons besoin de faire c’est d’appeler la nouvelle méthode que nous avons mappé sur notre classe DataContext pour récupérer une séquence de résultats fortement typés de la procédure stockée :

 

Appeler la procédure stockée en VB:

 

 

Appeler la procédure stockée en C#:

 

 

En plus de programmer une  boucle sur les résultats comme dans les exemples de code ci-dessus, je pourrai évidemment aussi lier les résultats à tout contrôle de l'interface utilisateur pour les afficher.  Par exemple, le code ci-dessous lie les données des résultats de notre procédure stockée à un contrôle <asp:gridview> :

 

 

Qui affiche ensuite l'historique des produits de nos clients sur une page comme suit :

 

 

Mapper le type de retour de la méthode de la procédure stockée aux classes du modèle de données

Dans l’exemple de procédure stockée CustOrderHist ci-dessus, celle-ci retourne une séquence d’historique de Product contenant deux colonnes de données : le ProductName du produit et le nombre total de commandes que le client a réalisé pour ce produit. Le concepteur LINQ to SQL défini automatiquement une nouvelle classe « CustOrderHistResult » pour représenter ce résultat.

Nous pourrions également choisir d'avoir le résultat de retour d’une procédure stockée liée à une classe du modèle de données existant que nous avons déjà défini dans le concepteur LINQ to SQL (par exemple : une classe entité Product ou Order existante). 

Par exemple, supposons que nous avons une procédure stockée GetProductsByCategory dans notre base de données qui retourne les informations des produits comme suit :

 

 

Comme précédemment, nous pouvons créer une méthode « GetProductsByCategory » sur notre DataContext qui appelle cette procédure stockée, en la faisant glisser sur notre concepteur LINQ to SQL. Plutôt que de simplement placer la procédure stockée n'importe où sur le concepteur, nous allons plutôt déplacer la procédure stockée au dessus de la classe « Product » existante dans notre concepteur du modèle de données :

 

 

Ce geste de déplacer la procédure stockée sur la classe Product indique au le concepteur LINQ to SQL que la méthode « GetProductsByCategory » retourne une séquence d'objets « Product » :

 

 

Une des choses cool sur avoir notre procédure stockée qui retour des objets « Product » comme ci-dessus est que LINQ to SQL surveille automatiquement les modifications apportées aux objets Product renvoyées comme s’ils étaient retournés par une simple requête LINQ. Lorsque nous appelons la méthode « SubmitChanges() » sur notre DataContext, les modifications que nous avons apporté à ces objets sont automatiquement enregistrées dans la base de données.

Par exemple, nous pourrions écrire le code ci-dessous pour récupérer (en utilisant une procédure stockée) et modifier le prix de tous les produits dans une catégorie spécifique à 90 % de leur valeur actuelle :

 

 

Lorsque nous appelons la méthode SubmitChanges() à la fin, de façon transactionnelle elle mettra à jour tous les prix des produits. Pour comprendre comment changer le suivi et le fonctionnement de la méthode SubmitChanges(), ainsi que comment la logique de validation métier peut être ajouté aux entités du modèle de données, lisez l’article Partie 4 : Mettre à jour votre base de données.

Dans le prochain article de cette série sur LINQ to SQL j'aborderai aussi comment vous pouvez remplacer la dynamique INSERT/UPDATE/DELETE SQL générée par le concepteur avec des procédures stockées personnalisés qui gèrent les mises à jour de la base de données. Ce qui est agréable, c'est que le code ci-dessus ne serait pas capable de tout changer si j’ai configuré mon DataContext pour utiliser procédures stockées pour les mises à jour – ce serait purement un changement de couche de liaison et il serait inconscient d’écrire le code pour le modèle de données.

Gestion des paramètres de sortie des procédures stockées

LINQ to SQL mappe les paramètres de sortie dans une procédure stockée comme paramètre par référence (mot clé « ref »), et pour les types valeur, déclare les paramètres comme étant nullable.

Par exemple, considérons la procédure stockée « GetCustomerDetails » ci-dessous qui prend « CustomerID » comme paramètre d’entrée et qui retourne le nom de la société comme paramètre de sortie en plus de son historique de commande comme un résultat de requête :

 

 

Si nous glissons la procédure stockée ci-dessus vers notre classe « Order » dans le concepteur LINQ to SQL, nous pourrions écrire ensuite le code ci-dessous pour l’appeler :

VB :

 

 

C#:

 

 

Notez dans le code ci-dessus comment la méthode de la procédure stockée retourne une séquence d'objets Order – mais elle renvoie également le CompanyName comme paramètre de sortie de la méthode.

 

Gestion de plusieurs formes de résultats depuis les procédures stockées

Une procédure stockée peut retourner plusieurs formes de résultat, le type de retour de la méthode de la procédure stockée sur le DataContext ne peut pas être fortement typé à un seul type de classe.  Imaginons par exemple, la procédure stockée ci-dessous qui renvoie un résultat Product ou Order selon un paramètre d'entrée :

 

 

LINQ to SQL prend en charge la possibilité de créer des procédures stockées pouvant soit renvoyer un Product soit une Order en ajoutant une classe partielle NorthwindDataContext au projet qui définit une méthode (que nous appellerons, dans ce cas « VariablesShapeSample ») qui appelle la procédure stockée et renvoie un objet IMultipleResult comme suit :

VB:

 

 

C#:

 

 

Une fois que cette méthode est ajoutée dans notre projet nous pouvons l'appeler et convertir le résultat pour être une séquence de produit (Product) ou de commande (Order) lorsque nous l'utilisons :

VB:

 

 

C#:

 

 

Prise en charge des User Defined Functions (UDF – Fonctions Définies par L’utilisateur)

En plus des procédures stockées, LINQ to SQL prend également en charge à la fois les fonctions scalaire et de table définies par l’utilisateur, ainsi que l'équivalent en ligne. Une fois ajouté comme une méthode à votre DataContext, vous pouvez utiliser ces fonctions UDF dans vos requêtes LINQ.

Par exemple, considérons une simple fonction scalaire définie par l'utilisateur appelée « MyUpperFunction » :

 

 

Nous pouvons le glisser / déposer depuis l’explorateur de serveurs Visual Studio dans notre concepteur LINQ to SQL pour l’ajouter comme en tant que méthode sur notre DataContext :

 

 

Nous pouvons alors utiliser cette fonction définie par l’utilisateur au sein de nos expressions LINQ lorsque nous écrivons les requêtes sur notre modèle de données LINQ to SQL (elle est utilisée au sein de la clause « where » ci-dessous) :

VB:

 

 

C#:

 

 

Si vous utilisez le visualiseur de débogage de LINQ to SQL dont j’ai parlé sur mon blog (en Anglais), vous pouvez visualiser comment LINQ to SQL transforme les expressions de requêtes ci-dessus en code SQL brut qui s'exécutent l’UDF (User Defined Functions) dans la base de données au moment de l'exécution :

 

 

Résumé

LINQ to SQL prend en charge la possibilité d'appeler des procédures stockées et des fonctions définies par l'utilisateur dans la base de données et de les intégrer parfaitement dans notre modèle de données. Dans cet article, j’ai démontré comment vous pouvez utiliser procédures stockées pour extraire facilement des données et de remplir nos classes du modèle de données. Dans le prochain article de cette série, j'aborderai comment vous pouvez également utiliser les procédures stockées pour remplacer la logique de mise à jour/d’insertion/de suppression lorsque vous appelez la méthode SubmitChanges() sur votre DataContext pour persister les données dans la base de données.

En espérant que ceci vous aura aidé,

Scott

Microsoft réalise une enquête en ligne pour comprendre votre opinion sur le site Web de. Si vous choisissez de participer, l’enquête en ligne vous sera présentée lorsque vous quitterez le site Web de.

Souhaitez-vous y participer ?