Préparez vos données
Dans ce chapitre, vous allez utiliser deux méthodes pour exécuter des requêtes SQL sur votre base de données :
Les requêtes SQL brutes exécutées avec C#
Les requêtes objet avec LINQ
Seulement, un problème se pose. La base de données que vous avez créée est vierge, donc il n’y a rien à interroger. Maintenant, nous pouvons faire comme bon nous semble avec ce projet : générer automatiquement des modèles des vues et des contrôleurs, ajouter un modèle bootstrap, appliquer un joli design visuel et le déployer. Mais ce n’est pas vraiment l’objet de ce cours.
C’est uniquement la base de données qui nous intéresse ici, et non pas l’ensemble de l’application. Donc si vous le souhaitez, n’hésitez pas à ajouter les contrôleurs et les vues plus tard. Achevez le projet si cela vous plaît. Dans le cadre de ce cours, nous allons uniquement nous concentrer sur ces deux méthodes permettant d’exécuter des requêtes SQL sur la base de données. Mais avant cela, il nous faut des données.
Nos requêtes aussi performantes soient-elles sont inutiles s’il n’y a pas de recettes. J’ai donc préparé quelques échantillons de données que vous pouvez importer dans votre base de données. Cependant, j’ai ajouté quelques éléments au modèle pour le rendre plus intéressant. Nous allons donc mettre à jour votre modèle, pour qu’il corresponde à ces échantillons, puis je vous montrerai comment les importer dans votre base de données.
Actualisez votre modèle
Passons en revue le modèle initial :
La première chose qui saute aux yeux, ce sont les trois objets sur la gauche. En dehors du nom des entités, ils sont tous identiques. C’est parce qu’il s’agit de tables de consultation. On utilise parfois le type énumération pour ce genre de choses, mais cette façon de faire est idéale quand la liste des éléments énumérés est définie. Sinon, il est préférable d’utiliser une table de consultation pour cette entité dans votre base de données.
En tenant compte de cela, vous pouvez vous débarrasser de certaines redondances dans le code en ajoutant une classe abstraite que vous pouvez étendre avec les trois autres. Que pensez-vous de ceci ?
public abstract class Consultation
{
public int Id { get; set; }
public string Nom { get; set; }
}
public class TypeAliment : Consultation { }
public class OrigineAliment : Consultation { }
public class UniteDeMesure : Consultation { }Il s’agit d’une modification utile qui facilite la création de tables de consultation supplémentaires si on en a besoin plus tard. Je vous invite à effectuer cette modification dans votre propre code.
Ensuite, selon votre lieu de résidence, vous pouvez utiliser le système de mesure impérial ou métrique pour vos recettes. Il n’est pas très pratique de concevoir une application n’ayant qu’un seul système de mesure. Nous devons donc trouver un moyen de définir un système de mesure pour chaque recette. Voici une façon de le faire :
1. Commencez par ajouter deux variables de type enum pour représenter les systèmes de mesure et les unités de température pour ces systèmes. Vous pouvez le faire dans n’importe quel fichier de code pour vos classes de modèle. Je place la mienne juste en dessous de ma classeConsultation :
public abstract class Consultation
{
public int Id { get; set; }
public string Nom { get; set; }
}
public enum SystemeDeMesure { Imperial, Metrique }
public enum UniteTemperature { Celsius, Fahrenheit }2. Ajoutez ensuite une propriété SystemeDeMesure à deux classes du modèle : UniteDeMesure et Recette . PourUniteDeMesure , ajoutez un constructeur par défaut ainsi qu’un constructeur qui prend comme argument une valeurSystemeDeMesure pour définir le système de mesure de l’objet :
public class UniteDeMesure : Consultation
{
public UniteDeMesure() : base() { }
public UniteDeMesure(SystemeDeMesure s) : base()
{
this.SystemeDeMesure = s;
}
public SystemeDeMesure SystemeDeMesure { get; set; }
}3. Pour la classe Recette , ajoutez une propriété pourSystemeDeMesure :
public class Recette
{
public int Id { get; set; }
public string Nom { get; set; }
public SystemeDeMesure SystemeDeMesure { get; set; }
public int TemperatureDeCuisson { get; set; }
public int TempsDeCuisson { get; set; }
public string Instructions { get; set; }
public int TypeAlimentId { get; set; }
public int OrigineAlimentId { get; set; }
public virtual TypeAliment TypeAliment { get; set; }
public virtual OrigineAliment OrigineAliment { get; set; }
public virtual ICollection<IngredientRecette> IngredientsRecette { get; set;}
}4. Enfin, modifiez quelques propriétés pour qu’elles puissent accepter la valeur Null, au cas où certaines recettes n’auraient pas de valeur pour ces propriétés. Par exemple, dans les données que vous allez importer, il y a une recette pour des cookies qu’on ne fait pas cuire. Elles n’auront donc pas de température de cuisson, ni de temps de cuisson. Je pense que ce sont les deux seules propriétés qui doivent accepter la valeur Null, en dehors de la propriété UniteDeMesureId de la classe Ingredient, qui acceptait déjà la valeur Null dans la conception initiale. Donc, modifiez les propriétés TemperatureDeCuisson et TempsDeCuisson de la classe Recette pour qu’elles acceptent la valeur Null.
public class Recette
{
public int Id { get; set; }
public string Nom { get; set; }
public SystemeDeMesure SystemeDeMesure { get; set; }
public int? TemperatureDeCuisson { get; set; }
public int? TempsDeCuisson { get; set; }
public string Instructions { get; set; }
public int TypeAlimentId { get; set; }
public int OrigineAlimentId { get; set; }
public virtual TypeAliment TypeAliment { get; set; }
public virtual OrigineAliment OrigineAliment { get; set; }
public virtual ICollection<IngredientRecette> IngredientsRecette { get; set;}
}Mettez à jour la base de données
Chaque fois que vous modifiez une classe de modèle, elle ne correspond plus exactement à la base de données, ce qui pourrait compliquer la tâche de l’ORM. Si vous essayez d’accéder à la base de données avant de procéder à sa mise à jour pour correspondre aux nouvelles modifications du modèle, un message d’erreur indique que le contexte de la base de données a changé. Il n’est plus valide et vous devez effectuer une nouvelle migration.
Occupons-nous de cela immédiatement :
Faites une nouvelle migration tout comme vous l’avez fait avec la migration initiale. Vous pouvez l’appeler comme vous le souhaitez. J’ai appelé la mienne ModeleMisAJour1.
Lorsque la migration est prête, mettez à jour la base de données.
Lorsque la mise à jour est terminée, importez les données.
Importez les données
Assurez-vous que SQL Server Express est installé sur votre ordinateur. Si tel est le cas, cliquez sur le bouton Démarrer de Windows. Vous trouverez un dossier Microsoft SQL Server 2017 dans votre liste d’applications. S’il n’y figure pas, installez-le maintenant à partir de Microsoft.
Les données dont vous avez besoin sont conservées dans le dépôt GitHub de ce cours. Voici les fichiers que vous devez télécharger avant de pouvoir importer les données de test dans votre base de données :
OrigineAliment.csv
TypeAliment.csv
UniteDeMesure.csv
Recette.csv
Ingredient.csv
IngredientRecette.csv
Chaque fichier contient les données de la table du même nom. Ainsi, pour chaque fichier de la liste, vous devez effectuer les étapes suivantes pour importer les données :
1. Ouvrez SSMS et faites un clic droit sur votre base de données. Choisissez Tasks puis Import Data.
2. Dans la fenêtre de bienvenue, cliquez sur Next.
3. Dans la fenêtre qui s’ouvre, Choose a Data Source (choisir une source de données), sélectionnez Flat File Source (source du fichier plat) dans la liste déroulante, puis parcourez et sélectionnez le fichier souhaité.
4. Dans la partie Advanced, vérifiez que la colonne Id est bien de type signed integer.
Et que la colonne Nom est bien de type Unicode string (chaîne de caractères unicode).
5. Cliquez sur Next.
6. Vous devez maintenant choisir votre base de données cible. Dans la liste déroulante Destination, sélectionnez .NET Framework Data Provider for SqlServer (fournisseur de données .NET Framework pour SQL Server). Ensuite, dans le volet sous la liste déroulante Destination, localisez l’en-tête Données et la ligne ConnectionString.
7. Copiez la chaîne de connexion du fichier appsettings.json de votre application et collez-la dans ce champ. Ma chaîne de connexion ressemble à ceci :
Server=(localdb)\\mssqllocaldb;Database=MesRecettes;Trusted_Connection=True;MultipleActiveResultSets=true;
En cas de doubles barres obliques dans votre chaîne de connexion (comme vous pouvez le voir dans la section Server de la mienne) après l’avoir collée, supprimez-en une, puis cliquez sur Next.
8. Dans la fenêtre qui suit, cliquez sur Next.
9. Cliquez sur Finish dans les deux fenêtres suivantes. L’importation va maintenant s’exécuter. Répétez ce processus pour les autres fichiers téléchargés.
Interrogez une base de données avec SQL en C#
Maintenant que vous avez importé les données dans votre base de données, voyons comment les interroger. La première chose à faire est d’accéder à votre objet contexte de base de données. Cet objet est créé au démarrage de l’application, dans la méthode ConfigureServices du fichier Startup.cs.
public void ConfigureServices(IServiceCollection services)
{
services.Configure<CookiePolicyOptions>(options =>
{
options.CheckConsentNeeded = context => true;
options.MinimumSameSitePolicy = SameSiteMode.None;
});
services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(
Configuration.GetConnectionString(
"DefaultConnection")));
services.AddDefaultIdentity<IdentityUser>()
.AddDefaultUI(UIFramework.Bootstrap4)
.AddEntityFrameworkStores<ApplicationDbContext>();
services.AddMvc().SetCompatibilityVersion(
CompatibilityVersion.Version_2_2);
}Notez ici la référence à DefaultConnection . Il s’agit du nom de la chaîne de connexion qui pointe vers votre base de données dans le fichier appsettings.json. Lorsque vous configurez des chaînes de connexion supplémentaires, par exemple vers une base de données en ligne sur Azure, vous pouvez rediriger votre application vers la base de données souhaitée en modifiant cette référence.
Pour recevoir une instance de l’objet contexte de base de données dans un contrôleur, vous pouvez l’ajouter comme paramètre dans le constructeur du contrôleur. Si vous avez créé automatiquement les classes de votre modèle, cette opération se fait automatiquement. Si vous créez des contrôleurs vides, vous devrez ajouter l’injection du contexte. L’exemple suivant montre comment le contexte de la base de données est injecté dans le contrôleur HomeController .
public class HomeController : Controller
{
private readonly ApplicationDbContext _contexte;
public HomeController(ApplicationDbContext contexte)
{
_contexte = contexte;
}
public IActionResult Index()
{
return View();
}
...
}Vous pouvez maintenant utiliser _contexte n’importe où dans le contrôleur HomeController pour interroger et modifier la base de données.
Supposons que vous souhaitiez récupérer immédiatement la liste de vos recettes dès l’ouverture de l’application et de la page d’accueil. Vous pouvez le faire en exécutant une requête SQL brute sur votre objet _contexte. Accédez à l’objet et aux tables référencés à travers LINQ, puis exécutez la requête SQL comme indiqué ci-après.
var mesRecettes = _contexte.Recettes.FromSql(
"SELECT * FROM dbo.Recette").ToList();Remarquez que la méthode FromSql accepte une chaîne contenant la requête que vous souhaitez exécuter. Vous devez paramétrer la requête lorsque vous transmettez des données utilisateur dans le contenu de la requête. Supposons que vous souhaitiez lister toutes les recettes dont le nom contient un mot-clé saisi par l’utilisateur :
// var motCle = mot-clé fourni par l’utilisateur
var mesRecettes = _contexte.Recettes.FromSql(
"SELECT * FROM dbo.Recipes WHERE [Name] LIKE ‘%{0}%’", motCle
).ToList();L’utilisation des accolades dans la requête SQL indique l’utilisation d’une entrée paramétrée. Le nombre à l’intérieur des accolades est l’index commençant par zéro et indiquant le numéro du paramètre (c’est-à-dire premier, deuxième, troisième) en fonction de l’ordre d’entrée dans la chaîne. Dans ce cas, motCle est le premier paramètre de la liste et est donc le paramètre 0.
En paramétrant vos chaînes de requête de cette manière, vous empêchez les attaques par injection SQL résultant d’une tentative de l’utilisateur d’insérer une commande SQL.
Vous avez vu des exemples montrant comment exécuter des requêtes qui récupèrent la liste complète des recettes ainsi que celles qui contiennent un texte saisi par l’utilisateur. Il est maintenant temps de s’entraîner !
Exercice de codage : exécutez les requêtes SQL
Avant de commencer à exécuter des requêtes, ajoutez l’injection du contexte à votre contrôleurHomeController (si vous ne l’avez pas encore fait), puis ajoutez également les instructions suivantes :
using MesRecettes.Models;
using MesRecettes.Data;
using Microsoft.EntityFrameworkCore;À présent, faites jouer vos connaissances en SQL. En utilisant l’exemple suivant comme référence, écrivez des méthodes individuelles dans le contrôleur HomeController pour exécuter chacune des requêtes indiquées dans cet exercice.
Exemple
[HttpGet]
public JsonResult RecupererToutesLesRecettes()
{
var recettes = _contexte.Recettes.FromSql(
"SELECT * FROM dbo.Recette").ToList();
return Json(recettes);
}Requêtes
Toutes les recettes dont le temps de cuisson est inférieur à 30 minutes.
Toutes les recettes dont le nom comprend "BBQ".
Toutes les recettes dont la température de cuisson est supérieure ou égale à 350 degrés Fahrenheit.
Toutes les recettes dont les instructions contiennent les mots "fouetter" et "plier".
Toutes les recettes d’origine italienne.
Tous les dîners.
Lorsque vous avez terminé, testez-les en exécutant votre application. Vous devez saisir l’URL de chaque méthode dans votre navigateur (par exemple, http:\\votre_domaine\Home\RecupererToutesLesRecettes).
Dans la section suivante, nous examinerons les équivalents LINQ de ces requêtes pour montrer les similitudes et les différences dans la structure des requêtes.
Interroger une base de données avec LINQ en C#
LINQ, acronyme de Language-Integrated Query, est un moyen d’interroger une base de données par le biais de l’objet contexte de base de données.
Commençons par là. Vous vous souvenez des deux requêtes SQL que je vous ai montrées dans la précédente section ? Découvrons comment les construire en utilisant LINQ. Ensuite, nous allons l’exécuter pas à pas pour que vous puissiez mieux comprendre ce qui se passe.
var mesRecettesLINQ = _contexte.Recettes.ToList();Dans cet exemple, commencez de la même manière que précédemment, mais cette fois, vous n’avez pas besoin de définir la requête. Puisque vous demandez le contenu de la table Recettes, indiquez le nom de la table suivi de.ToList() . Cela convertit le résultat de la requête en un objet List facile à manipuler et contenant toutes les recettes.
Si vous souhaitez trouver les recettes contenant les mots-clés saisis par un utilisateur (comme dans l’exemple précédent), la requête LINQ ressemblerait à ceci :
// var motCle = mot-clé fourni par l’utilisateur
var mesRecettesLINQ2 = _contexte.Recettes.Where(x =>
x.Nom.Contains(motCle)).ToList();Remarquez la méthode Where que nous appelons dans l’objet Recettes . C’est l’équivalent du mot-clé WHERE en SQL. En vous familiarisant avec LINQ, vous constaterez que de nombreuses méthodes disponibles dans le contexte de la base de données correspondent presque directement à leurs équivalents SQL. Dans le bout de code ci-dessus, il y a quelque chose d’inhabituel. L’avez-vous remarqué ?
L’objet x.Nom , qui indique l’attribut Nom de l’objet Recette , utilise une méthode Contains pour déterminer si motCle existe dans le nom de la recette. Contains est l’équivalent du mot-cléLIKE en SQL.
Si cette syntaxe vous semble complexe, vous n’êtes probablement pas familier avec les expressions lambda. Une expression lambda est une fonction anonyme qui vous permet d’écrire des fonctions locales qui peuvent être transmises comme arguments. Dans l’exemple ci-dessus,x => x.Nom.Contains(motCle) est une expression lambda. Dans le contexte de l’instruction Where, elle signifie :
Donnez-moi toutes les recettes (et indiquez que x représente une instance de recette) dont l’attribut Nom de l’objet Recette (x.Nom) contient la chaîne trouvée dans motCle.
Ne soyez pas inquiet si vous n’avez pas encore bien cerné le principe. Vous y arriverez. Votre meilleur professeur dans ce cas ne sera pas moi ou ce cours, mais l’IntelliSense de Visual Studio. Vous l’avez déjà vu à l’œuvre. Lorsque vous tapez une expression, l’IntelliSense affiche des listes et des suggestions pour vous aider à écrire du code plus rapidement. Voici un exemple. Je suis en train de saisir la requête ci-dessus :
L’IntelliSense comprend quex représente dans ce cas une instance de Recette , et me donne donc tout ce qui est associé à l’objet Recette . De même, lorsque je sélectionne Nom et que je tape un point, j’obtiens une liste de toutes les actions que je peux effectuer avec un objet string :
Vous pouvez utiliser LINQ pour naviguer dans le contexte de la base de données. Mettre un point après la variable de contexte fait apparaître toutes les tables de votre base de données. La sélection d’une table et la saisie d’un autre point font apparaître toutes les fonctions de requête que vous pouvez exécuter sur cette table. Avec cela en tête, nous pouvons passer à la tâche suivante.
Exercice de codage : exécutez des requêtes LINQ
Il est temps de s’entraîner un peu plus. Découvrez ce que vous pouvez apprendre sur LINQ en utilisant l’IntelliSense comme guide. Vous avez écrit six requêtes SQL dans la section précédente. Cette fois, vous allez utiliser LINQ pour écrire ces mêmes requêtes. Utilisez la logique que vous avez employée précédemment en SQL pour vous aider à naviguer dans vos requêtes LINQ. C’est assez intuitif, vous verrez. Continuez avec votre contrôleur HomeController . Ajoutez simplement une deuxième version de chacune des méthodes que vous avez écrites, en les identifiant comme des méthodes LINQ, par exemple :
public JsonResult RecupererToutesLesRecettesLINQ() { }Lorsque vous avez terminé le code de cet exercice, testez vos requêtes LINQ de la même manière que vous avez testé les requêtes SQL. Vous devriez obtenir les mêmes résultats.
En résumé
Nous avons abordé beaucoup de notions dans ce chapitre.
Tout d’abord, vous avez modifié vos classes de modèle, puis vous avez effectué une deuxième migration code first pour préparer la base de données à recevoir des échantillons de données ;
Ensuite, vous avez utilisé l’outil Importer et exporter des données de SQL Server 2017 pour importer les données des fichiers CSV dans les tables de la base de données ;
Après avoir importé ces données de test dans votre base de données, vous avez appris à exécuter les requêtes SQL brutes avec Entity Framework et le contexte de base de données ;
Vous avez également appris l’importance de paramétrer toutes les données saisies par l’utilisateur dans vos requêtes afin de protéger votre base de données contre les attaques par injection SQL ;
Enfin, vous avez exécuté des requêtes sur votre base de données à travers le contexte de base de données en utilisant LINQ au lieu du SQL brut.
Maintenant, faisons rapidement le tour sur ce que vous avez appris ici dans la première moitié de ce cours.
