Implémentez votre base de données relationnelle avec ASP.NET Core

Découvrez les procédures stockées dans SSMS

La vidéo ci-dessus présente les concepts suivants :

1. Les procédures stockées sont des objets exécutables. Ils sont stockés dans une base de données.

2. Les deux principaux avantages de l’utilisation des procédures stockées sont la rapidité et la sécurité.

3. T-SQL sert à écrire des procédures stockées, et il incorpore tout le SQL avec quelques fonctionnalités supplémentaires pour vous aider à construire des requêtes performantes et des fonctions complexes.

4. Les entrées utilisateur sont paramétrées dans les procédures stockées.

5. Les procédures stockées sont l’un des moyens (et même le meilleur) d’empêcher les attaques par injection SQL.

Ce que vous ne savez pas, c’est la manière de les écrire. Je ne vais pas vous encombrer avec un guide utilisateur pour T-SQL. Les procédures stockées sont en grande partie du SQL standard, et le reste vient avec la pratique. Là-dessus, SSMS vous sera d’une très grande utilité. Vous pouvez créer facilement vos procédures stockées dans SSMS de deux façons :

1. Utiliser le modèle générique proposé par SSMS et effectuer les modifications manuellement.

2. Utiliser l’assistant qui vous aide à définir ce que vous voulez créer, puis laisser SSMS générer la majorité du code pour vous.

Je vais vous présenter la première méthode, puis je vous montrerai comment exploiter la seconde.

Créez une procédure stockée dans SSMS

Les procédures stockées se trouvent dans le dossier Programmability de votre base de données dans l’explorateur d’objets de SSMS. Ouvrez SSMS, connectez-vous à votre serveur et trouvez la base de données de vos recettes. Développez ensuite la base de données et cherchez le dossier Programmability. Le premier sous-dossier dans Programmability est Stored Procedures (procédures stockées). C’est là que vous devez créer les vôtres. Vous remarquerez peut-être que ce dossier Stored Procedures contient un sous-dossier intitulé System Stored Procedures. Ne le touchez pas. Laissez-le tel quel. Ce sont les procédures stockées que le serveur SQL utilise en interne pour gérer votre base de données. Il ne faut surtout pas y toucher.

Pour créer une nouvelle procédure stockée, faites un clic droit sur le dossier Stored Procedures et sélectionnez Stored Procedure ou New Stored Procedure. Le résultat est une nouvelle fenêtre de requête contenant un modèle générique de procédure stockée.

Vous pouvez maintenant modifier le modèle pour créer votre requête personnalisée. En regardant le code, vous pouvez facilement identifier les éléments à modifier. Mais il y a une autre façon de procéder : cliquez sur le menu Query dans la barre des menus, puis sélectionnez Specify Values for Template Parameters (Spécifier les valeurs des paramètres du modèle).

Ce qui se passe ensuite est très intéressant. D’abord, une nouvelle fenêtre s’affiche. Ensuite, tous les espaces réservés dans le modèle sont mis en évidence.

Vous pouvez maintenant personnaliser la procédure stockée. Commençons à partir du haut de la fenêtre vers le bas.

1. Au niveau du paramètre Author, saisissez votre nom.

2. Saisissez la date d’aujourd’hui pour le paramètre Create Date.

3. Pour le paramètre Description, saisissez Toutes les recettes dont le temps de cuisson est inférieur au nombre de minutes défini par l’utilisateur.

4. Saisissez TempsDeCuissonInferieurAX pour le paramètre Procedure_Name.

5. Saisissez @tempsDeCuisson pour le paramètre @param1.

6. Sélectionnez int pour le paramètre Datatype_For_Param1.

7. Saisissez 0 pour le paramètre Default_Value_For_Param1.

8. Supprimez les valeurs des autres paramètres et cliquez sur OK.

Maintenant, tous les espaces réservés seront modifiés en fonction des valeurs de paramètre que vous avez saisies.

Votre procédure stockée est presque terminée. Tout ce qu’il reste à faire, c’est d’insérer la logique de la requête. C’est très simple pour cette requête. Il suffit d’apporter une petite modification à l’instruction SELECT :

SELECT * FROM Recette WHERE tempsDeCuisson < @tempsDeCuisson

Vous devez maintenant tester la syntaxe pour vous assurer que tout est correct. Pour ce faire, dans le menu Query, cliquez sur Analyze. Corrigez votre syntaxe en cas de message d’erreur.

Lorsque tout est correctement défini, validez la création de la procédure dans la base de données. Pour ce faire, dans le menu Query, cliquez sur Execute. Cette action crée la procédure en tant qu’objet dans la base de données. Pour vérifier qu’elle a été créée, faites un clic droit sur le dossier Stored Procedures dans l’explorateur d’objets, puis cliquez sur Refresh. Vous devriez voir votre nouvelle procédure stockée dans le dossier Stored Procedures. 

Vous pouvez fermer la fenêtre de l’éditeur de code de votre procédure. Vous n’avez pas besoin de sauvegarder le fichier SQL. La procédure est désormais un objet de la base de données, il n’est donc pas nécessaire d’enregistrer le code.

Testez une procédure stockée

Il est important de tester vos procédures stockées dans la base de données pour vérifier qu’elles fonctionnent comme prévu avant de commencer à les appeler depuis le code d’application.

Dans l’explorateur d’objets, faites un clic droit sur le nom de votre procédure stockée, puis sélectionnez ExecuteProcedure. Une fenêtre pour les valeurs des paramètres s’affiche ; par exemple, j’ai entré 60 pour le paramètre @TempsDeCuisson.  

Après avoir cliqué sur OK, la procédure s’exécute et retourne les résultats, qui sont affichés dans le volet Results sous le code SQL généré par l’exécution de la procédure.

Exercice de codage : écrivez vos procédures stockées

Les requêtes écrites dans la première partie étaient toutes codées en dur. Pour vos procédures stockées, toutes les valeurs précédemment spécifiées deviendront des variables pour contenir les données fournies par l’utilisateur. Maintenant que vous avez créé votre première procédure stockée, vous devriez avoir compris comment les écrire.

Prenez quelques minutes et créez de nouvelles procédures stockées dans votre base de données en utilisant SSMS pour exécuter les requêtes suivantes :

1. Toutes les recettes dont le nom comprend une valeur fournie par l’utilisateur.

2. Toutes les recettes dont la température de cuisson est supérieure ou égale à un nombre de degrés fourni par l’utilisateur.

3. Toutes les recettes dont l’origine est spécifiée par l’utilisateur.

4. Toutes les recettes pour petit-déjeuner, déjeuner, dîner ou dessert (valeur fournie par l’utilisateur).

Lorsque vous aurez terminé, nous pourrons exécuter ces procédures stockées dans votre application.

Exécutez les procédures stockées en C#

Dans le chapitre 4 de la première partie, vous avez fait des exercices de codage dans lesquels vous avez écrit du code dans votre contrôleur HomeController pour exécuter des requêtes SQL et LINQ dans votre base de données. Rappelez-vous du code que vous avez écrit pour ces exercices, car vous ferez pareillement ici.

Nous allons maintenant exécuter les procédures stockées que vous venez d’écrire. Ce sera exactement comme avec les requêtes dans la première partie.

Il existe trois façons d’exécuter une procédure stockée en C# :

1. Sauvegarder la valeur saisie dans un paramètre et insérer le nom de ce paramètre dans l’espace réservé  {0} :

var tempsDeCuisson = "30";
var recettes = db.Recettes
   .FromSql("EXECUTE dbo.TempsDeCuissonInferieurAX {0}", tempsDeCuisson)
   .ToList();

2. Utiliser l’interpolation de chaîne de caractères. Elle est prise en charge par EF Core 2.0 et les versions ultérieures. Elle consiste à insérer la variable contenant la valeur du paramètre comme un espace réservé dans une chaîne de caractères :

var tempsDeCuisson = "30";
var recettes = db.Recettes
 .FromSql($"EXECUTE dbo.TempsDeCuissonInferieurAX {tempsDeCuisson}")
   .ToList();

3. Créer un objetDbParameter et le passer comme argument à la requête. Cette option vous permet d’utiliser des paramètres nommés comme faisant partie de la requête SQL :  

var tempsDeCuisson = new SqlParameter("TempsDeCuisson", 30);
var recettes = db.Recettes
   .FromSql("EXECUTE dbo.TempsDeCuissonInferieurAX @tempsDeCuisson", tempsDeCuisson)
   .ToList();

Vous pouvez utiliser n’importe laquelle de ces méthodes pour exécuter votre procédure stockée. Notez que ces trois méthodes retournent la même chose : une liste d’objets Recette correspondant aux critères définis.

Normalement, vous devriez construire une page Web pour permettre à l’utilisateur de saisir les valeurs requises, mais nous ne créerons pas de pages dans ce cours. Examinons plutôt comment vous pouvez exécuter votre code C# et vérifier que vous appelez correctement votre procédure stockée.

Testez votre code

Dans la première partie, vous avez utilisé toutes les requêtes SQL et LINQ dans les méthodes de votre contrôleur HomeController. Vous allez faire de même pour vos procédures stockées, mais en utilisant votre nouveau projet, MesRecettes2.

Pour commencer, assurez-vous que les instructions using suivantes sont présentes au début du fichier :

using Microsoft.EntityFrameworkCore;
using MesRecettes.Data;

Injectez ensuite votre nouveauDbContext dans le contrôleur :

private readonly MesRecettes2Context _contexte;

public HomeController(MesRecettes2Context contexte)
{
   _contexte = contexte;
}

Créez maintenant des méthodes pour vos procédures stockées. Commencez par une nouvelle appelée  RecupererRecettesParTempsDeCuisson . Puisque vous n’allez pas construire une interface utilisateur pour cette procédure, vous n’aurez pas de vues à retourner. Ainsi, au lieu de retourner un  IActionResult , ces méthodes doivent retourner un  JsonResult , comme nous l’avons fait pour les requêtes de la première partie.

public JsonResult RecupererRecettesParTempsDeCuisson(int tempsDeCuisson)
{
 var recettes = _contexte.Recettes
      .FromSql("EXECUTE dbo.TempsDeCuissonInferieurAX {0}", tempsDeCuisson)
      .ToList();

   return Json(recettes);
}

Maintenant, compilez votre code et corrigez les éventuelles erreurs. Si tout est correct, vous pouvez tester votre nouvelle méthode et vérifier qu’elle fonctionne correctement. Pour exécuter votre méthode, il vous suffit de saisir l’URL dans la barre d’adresse de votre navigateur :

https://localhost:votre-port/Home/RecupererRecettesParTempsDeCuisson/30

La méthode que vous venez d’écrire sera exécutée. Elle appelle votre procédure stockée et récupère toutes les recettes dont le temps de cuisson est inférieur au paramètre indiqué. Dans cet exemple, nous avons utilisé 30, mais vous pouvez utiliser n’importe quelle valeur pour tester votre procédure. Votre navigateur affichera le résultat sous la forme d’un objet JSON. Si la requête ne retourne aucune recette, un objet JSON vide (crochets vides) sera affiché.

Exercice de codage : ajoutez et testez le reste des méthodes

Ajoutez des méthodes à votre contrôleur pour le reste de vos procédures stockées, puis testez-les comme vous l’avez fait dans l’exemple précédent.

En résumé

Vous avez effectué tous les exercices d’entraînement de ce cours. Cela vous a permis de maîtriser :

• l’utilisation de SSMS pour créer et sauvegarder rapidement des procédures stockées dans votre base de données (n’oubliez pas que les procédures stockées constituent le moyen le plus sûr et le plus rapide d’exécuter des requêtes SQL) ;

• comment tester les procédures stockées au sein de l’IDE SSMS ; 

• comment injecter un objet contexte de base de données dans un contrôleur MVC ;

• les trois différentes façons d’appeler et d’exécuter une procédure stockée dans le code C# en utilisant un objet contexte de base de données.

Étudier les procédures stockées est une excellente façon de clôturer ce cours. Prenons un moment pour passer en revue les connaissances que vous avez acquises ici, puis vous pourrez terminer le cours avec le quiz final.