C # - Exécuter des requêtes SQL de base avec Dapper

Voici un exemple d'utilisation de Dapper pour exécuter une requête SELECT de base :

using System.Data.SqlClient;
using Dapper;

public IEnumerable<Movie> GetAllMovies()
{
	using (var con = new SqlConnection(connectionString))
	{
		return con.Query<Movie>("SELECT * FROM Movies");
	}
}
Code language: C# (cs)

Dapper fait abstraction du code répétitif impliqué dans l'exécution des requêtes SQL, y compris les paramètres de mappage et les résultats des requêtes. Il le fait sans aucune configuration (il mappe en utilisant la réflexion).

Dans cet article, je vais montrer d'autres exemples d'utilisation de Dapper dans des scénarios courants, tels que l'ajout de paramètres de requête et l'insertion d'enregistrements.

Remarque :Si vous ne l'avez pas déjà fait, ajoutez le package de nugets Dapper .

Ajout de paramètres de requête

Pour ajouter des paramètres de requête avec Dapper, transmettez un objet pour le param argument :

public IEnumerable<Movie> GetMoviesForYear(int year)
{
	using (var con = new SqlConnection(connectionString))
	{
		return con.Query<Movie>("SELECT * FROM Movies WHERE YearOfRelease=@year", 
			param: new { year });
	}
}
Code language: C# (cs)

Vous pouvez transmettre n'importe quel objet, y compris les types anonymes (comme indiqué ci-dessus). Dapper essaiera de mapper les propriétés du param s'opposer aux paramètres fictifs (c'est-à-dire "@année") dans la requête.

Appeler une procédure stockée

Supposons que vous souhaitiez appeler la procédure stockée suivante :

CREATE PROCEDURE spGetMoviesForYear
	@year int
AS
BEGIN
	SELECT * FROM Movies WHERE YearOfRelease=@year
END
Code language: plaintext (plaintext)

Voici un exemple d'appel de cette procédure stockée avec Dapper :

public IEnumerable<Movie> GetMoviesForYearSP(int year)
{
	using (var con = new SqlConnection(connectionString))
	{
		return con.Query<Movie>("dbo.spGetMoviesForYear", 
			param: new { year }, 
			commandType: System.Data.CommandType.StoredProcedure);
	}
}
Code language: C# (cs)

Vous spécifiez le nom de la procédure stockée, tous les paramètres et définissez l'argument commandType sur CommandType.StoredProcedure.

Insérer des enregistrements

Voici un exemple d'insertion d'un seul enregistrement de film :

private const string INSERT_SQL =
@"INSERT INTO [Movies]
	([Name]
	,[YearOfRelease]
	,[Description]
	,[Director]
	,[BoxOfficeRevenue])	   
VALUES
	(@Name,
	@YearOfRelease,
	@Description,
	@Director,
	@BoxOfficeRevenue)";

public void Insert(Movie movie)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute(INSERT_SQL, param: movie);
	}
}
Code language: C# (cs)

Les insertions sont un peu fastidieuses à coder car l'instruction INSERT est très détaillée par rapport aux autres instructions SQL. Il existe des bibliothèques d'extension pour Dapper qui résument les opérations CRUD afin que vous n'ayez pas à écrire de SQL, mais personnellement, je ne les utilise pas. L'un des avantages de Dapper est qu'il exécute la requête SQL que vous écrivez, il n'y a donc pas de surprises.

Pouvoir passer l'objet movie pour le param L'argument aide, puisque vous n'avez pas à taper la liste des paramètres. Pour écrire l'instruction INSERT, j'utilise généralement Script Table comme> INSERT INTO dans SSMS pour créer un script de point de départ ou générer les requêtes à l'aide de métadonnées.

Obtenir la valeur d'identité insérée

Lorsque vous insérez un enregistrement dans une table avec une colonne d'identité, vous pouvez obtenir la valeur d'identité insérée en ajoutant OUTPUT INSERTED.Id à la requête. Utilisez ExecuteScalar() pour obtenir la seule valeur renvoyée :

private const string INSERT_OUTPUT_ID_SQL =
@"INSERT INTO [Movies]
	([Name]
	,[YearOfRelease]
	,[Description]
	,[Director]
	,[BoxOfficeRevenue])
OUTPUT INSERTED.Id
VALUES
	(@Name,
	@YearOfRelease,
	@Description,
	@Director,
	@BoxOfficeRevenue)";
	
public int InsertAndReturnId(Movie movie)
{
	using (var con = new SqlConnection(connectionString))
	{
		return con.ExecuteScalar<int>(INSERT_OUTPUT_ID_SQL, param: movie);
	}
}
Code language: C# (cs)

Insérer plusieurs enregistrements

Lorsque vous passez une liste d'objets pour le param argument, Dapper exécutera la requête SQL pour chaque objet de la liste :

private const string INSERT_SQL =
@"INSERT INTO [dbo].[Movies]
	([Name]
	,[YearOfRelease]
	,[Description]
	,[Director]
	,[BoxOfficeRevenue])	   
VALUES
	(@Name,
	@YearOfRelease,
	@Description,
	@Director,
	@BoxOfficeRevenue)";

public void InsertMultiple(List<Movie> movies)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute(INSERT_SQL, param: movies);
	}
}
Code language: C# (cs)

L'exécution séquentielle de nombreuses instructions INSERT peut vraiment dégrader les performances. Assurez-vous de tester les performances de votre code si vous prévoyez d'insérer fréquemment de nombreuses données comme celle-ci. Je vous suggère fortement de faire un BULK INSERT si vous rencontrez des problèmes de performances.

Mise à jour des enregistrements

Voici un exemple de mise à jour d'un enregistrement avec Dapper :

public void UpdateYear(Movie movie)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("UPDATE Movies SET YearOfRelease=@year WHERE Id=@id", 
			param: new {year = movie.YearOfRelease, id = movie.Id });
	}
}
Code language: C# (cs)

Vous pouvez garder l'instruction UPDATE aussi simple que possible en n'incluant que le strict minimum de colonnes dans la requête. Si une colonne n'a pas besoin d'être dans la liste de mise à jour ou dans la clause where, laissez-la de côté.

Mettre à jour plusieurs enregistrements

Si vous mettez à jour plusieurs enregistrements avec des valeurs différentes, vous devrez exécuter plusieurs instructions UPDATE. Par exemple, supposons que vous souhaitiez définir :

• Film A YearOfRelease=2021
• Film B YearOfRelease=2022

Pour ce faire avec Dapper, vous pouvez passer une liste pour le param dispute. Il exécutera l'instruction UPDATE pour chaque objet vidéo de la liste :

public void UpdateMultipleToDifferentYears(List<Movie> movies)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("UPDATE Movies SET YearOfRelease=@YearOfRelease WHERE Id=@Id",
			param: movies);
	}
}
Code language: C# (cs)

Si vous mettez à jour plusieurs enregistrements avec la même valeur, vous pouvez exécuter une seule instruction UPDATE avec une clause WHERE IN. Par exemple, supposons que vous souhaitiez mettre à jour plusieurs films pour avoir YearOfRelease=2022.

Voici comment procéder avec Dapper :

using System.Linq;

public void UpdateMultipleWithSameYear(int year, List<Movie> movies)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("UPDATE Movies SET YearOfRelease=@year WHERE Id IN @ids",
			param: new { year, ids = movies.Select(m => m.Id) });
	}
}
Code language: C# (cs)

C'est plus efficace que d'exécuter plusieurs instructions UPDATE lorsque vous n'en avez besoin que d'une seule.

Suppression d'enregistrements

La suppression d'un enregistrement est simple avec Dapper :

public void Delete(Movie movie)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("DELETE FROM Movies WHERE Id=@id",
			param: new { id = movie.Id });
	}
}
Code language: C# (cs)

Supprimer plusieurs enregistrements

Lorsque vous supprimez plusieurs enregistrements, vous pouvez exécuter une seule instruction DELETE avec une clause WHERE IN. Voici comment procéder avec Dapper :

using System.Linq;

public void DeleteMultiple(List<Movie> movies)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("DELETE FROM Movies WHERE Id IN @ids",
			param: new { ids = movies.Select(m => m.Id) });
	}
}
Code language: C# (cs)

C'est plus efficace que d'exécuter plusieurs instructions DELETE.