Accès aux données avec Dapper

ADO.NET++

Thomas Levesque

Pour accéder à une base de données avec .NET, on a traditionnellement le choix entre deux grandes approches : utiliser un ORM (Object-Relational Mapper), ou requêter directement en SQL à l'aide d'ADO.NET. Si les ORM permettent généralement de gagner du temps, ce sont aussi souvent des « usines à gaz », ce qui les rend peu adaptés pour certaines applications. Mais d'un autre côté, l'API ADO.NET pour requêter en SQL est un peu laborieuse à utiliser… Il faudrait donc un compromis entre les deux : c'est là qu'intervient Dapper, une petite librairie open source qui appartient à la catégorie des micro-ORM.

Commentez ♪

- Titre : Accès aux données avec Dapper

- Auteur : Thomas Levesque

- Parution : 20 octobre 2013

- Public visé :

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2013 Thomas Levesque. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.

Sommaire

- Prérequis

- I. État des lieux

- I-A. ORM : l'artillerie lourde

- I-B. SQL : l'âge de pierre

- I-C. Dapper : le meilleur des deux mondes

- II. Dapper, comment ça marche ?

- II-A. Intégration dans le projet

- II-B. Effectuer une requête « SELECT » : la méthode Query

- II-B-1. Lecture des résultats en tant qu'entités

- II-B-2. Lecture de résultats bruts

- II-C. Modifier les données : la méthode Execute

- II-D. Requêtes paramétrées

- III. Autres fonctionnalités

- III-A. Appeler une procédure stockée

- III-B. Utiliser une transaction

- III-C. Exécuter plusieurs requêtes

- III-D. Lire de gros volumes de données

- III-E. Insertion en masse

- Conclusion

- Remerciements

Prérequis

Cet article suppose que le lecteur est familier avec le langage C# et l'utilisation d'ADO.NET (établissement d'une connexion avec la base de données, écriture de requêtes SQL, etc.).

I - État des lieux

I-A - ORM : l'artillerie lourde

Depuis quelques années, les applications .NET utilisent de plus en plus des ORM pour l'accès aux données. Les ORM modernes comme Entity Framework ou NHibernate ont atteint un bon niveau de maturité, et proposent des fonctionnalités très avancées (lazy loading, génération de la base à partir du modèle, ou l'inverse, etc.). Ils sont particulièrement adaptés au développement d'applications métier qui, typiquement, font surtout des opérations CRUD (Create, Read, Update, Delete), manipulent beaucoup d'entités différentes avec des relations complexes entre elles, mais ne traitent pas de gros volumes de données à la fois.

Si tout cela est très bien sur le papier, les ORM ne sont malheureusement pas une panacée :

- ils sont souvent complexes : il faut beaucoup de temps pour vraiment les maîtriser et comprendre toutes leurs subtilités ;

- ils manquent de transparence : il n'est pas toujours évident de savoir ce qu'il se passe en coulisses, quelles requêtes sont effectivement exécutées sur la base de données, quelles données sont conservées en cache, dans quels cas le chargement tardif (lazy loading) s'applique, etc. Quand un bug lié à l'ORM se produit, il est parfois difficile de trouver son origine ;

- leurs performances sont médiocres, comparées à celles qu'on peut obtenir en requêtant directement la base en SQL.

Pour toutes ces raisons, les ORM ne sont pas forcément le bon choix pour toutes les applications. Typiquement, pour une application dont le modèle de données est très simple ou qui doit traiter de gros volumes de données, il est parfois plus judicieux de requêter la base manuellement.

I-B - SQL : l'âge de pierre

ADO.NET existe depuis la toute première version de .NET, et permet d'effectuer des requêtes SQL sur n'importe quelle base de données, pourvu qu'il existe un fournisseur (pilote) adéquat. Requêter la base de données directement en SQL présente deux avantages principaux : un contrôle total de la requête, qui permet d'exploiter au mieux les caractéristiques du SGBD, et des performances optimales (pour peu que la requête soit bien construite et la base bien configurée).

Malheureusement, du point de vue de la productivité, c'est loin d'être idéal, car le code ADO.NET est d'assez bas niveau, et donc assez laborieux à écrire :

- pour éviter les problèmes de sécurité (injection SQL) et de format des données, il est nécessaire d'utiliser des requêtes paramétrées. Or la définition de ces paramètres est extrêmement verbeuse et répétitive, et augmente beaucoup la quantité de code à écrire pour exécuter une requête ;

- .NET étant une plateforme résolument orientée objet, le code métier va généralement manipuler les données de la base sous forme d'objets (entités). Or, la matérialisation de ces entités à partir des résultats d'une requête est, là aussi, répétitive et donc assez pénible.

I-C - Dapper : le meilleur des deux mondes

Dapper est un micro-ORM très léger qui permet de requêter une base de données en SQL, mais sans les inconvénients associés à cette approche quand on utilise ADO.NET :

- pas besoin de manipuler les objets IDbCommand, IDbDataParameter, IDataReader, etc. ;

- la matérialisation des entités est automatique : on spécifie simplement le type des entités via un paramètre générique de la méthode qui effectue la requête ;

- le passage des paramètres d'une requête se fait de façon très simple, en passant un objet dont les noms des propriétés correspondent aux noms des paramètres (cet objet peut éventuellement être d'un type anonyme).

Il a été créé par l'équipe du site Stack Overflow, qui n'était pas satisfaite des performances d'Entity Framework. Vu la fréquentation de ce site aujourd'hui, cela donne une idée de la robustesse de Dapper…

II - Dapper, comment ça marche ?

II-A - Intégration dans le projet

Pour intégrer Dapper dans votre projet, il y a deux options principales :

- via Nuget, en ajoutant le package Dapper, directement à partir du gestionnaire de packages dans Visual Studio. Il contient une version compilée de la librairie, qui sera ajoutée à votre projet en tant que référence ;

- en incluant directement le fichier SqlMapper.cs dans votre projet (eh oui, le code de Dapper est constitué d'un seul fichier !). Personnellement, je ne trouve pas cette approche très propre, mais elle peut être utile si vous souhaitez adapter un peu le code pour un besoin spécifique.

II-B - Effectuer une requête « SELECT » : la méthode Query

Dapper est une mince surcouche au-dessus d'ADO.NET. Il se présente sous forme d'une classe (SqlMapper) qui fournit des méthodes d'extension pour l'interface IDbConnection. Pour l'utiliser, il faut donc :

- inclure l'espace de nom Dapper pour que les méthodes d'extension soient accessibles :

using Dapper;

- créer une connexion à la base de données, comme vous l'auriez fait en ADO.NET classique :

using (SqlConnection connection = new SqlConnection(connectionString))
{
// code qui utilise la connexion
...
}

II-B-1 - Lecture des résultats en tant qu'entités

Supposons que vous ayez dans la base une table Contact, et dans le code une classe Contact correspondant à la table :

class Contact
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime? DateOfBirth { get; set; }
public string PhoneNumber { get; set; }
}

Pour récupérer une liste d'objets représentant tous les contacts de la table, il suffit d'utiliser la méthode Query :

IEnumerable<Contact> contacts = connection.Query<Contact>("select * from Contact");

Difficile de faire plus simple… À titre de comparaison, voilà le code qu'on aurait écrit pour faire à peu près la même chose avec ADO.NET :

using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = "select * from Contact";
using (IDataReader reader = command.ExecuteReader())
{
List<Contact> contacts = new List<Contact>();
while (reader.Read())
{
Contact contact = new Contact
{
Id = (int)reader["Id"],
LastName = (string)reader["LastName"],
FirstName = (string)reader["FirstName"],
DateOfBirth = reader["DateOfBirth"] == DBNull.Value ? null : (DateTime?)reader["DateOfBirth"],
PhoneNumber = (string)reader["PhoneNumber"]
};
contacts.Add(contact);
}
}
}

Dapper : une ligne, rien de superflu ; ADO.NET : vingt lignes, essentiellement du code de plomberie. Je ne sais pas ce que vous en pensez, mais pour ma part, le choix est vite fait…

II-B-2 - Lecture de résultats bruts

Dans l'exemple précédent, on a vu que Dapper pouvait matérialiser automatiquement les objets Contact à partir des résultats de la requête. Cependant, il arrive parfois qu'on veuille simplement obtenir quelques champs, qui ne correspondent pas forcément à une entité. À cet effet, Dapper fournit une surcharge non générique de la méthode Query, qui renvoie un IEnumerable<dynamic> (cela suppose donc d'utiliser .NET 4 ou supérieur). Par exemple, voilà comment récupérer le nombre de contacts et le plus grand identifiant :

dynamic result = connection.Query("select count(*) as Count, max(Id) as MaxId from Contact").Single();
int count = result.Count;
int maxId = result.MaxId;

Deux choses à noter sur ce code :

- dans cet exemple, on ne veut récupérer qu'une seule ligne ; on utilise donc la méthode Single de Linq pour récupérer la seule ligne de résultat. S'il peut n'y avoir aucun résultat, utilisez SingleOrDefault (qui renverra null s'il n'y a pas de résultat). S'il peut y avoir plusieurs résultats, mais que vous ne voulez que le premier, utilisez First ou FirstOrDefault ;

- si vous sélectionnez des colonnes calculées (par exemple count(*)), n'oubliez pas de leur donner un alias ; s'il n'y a pas d'alias et que la colonne n'a pas un nom valide en C#, Dapper générera à la place des noms comme Column1, ce qui n'est pas très pratique… Si vous sélectionnez directement une colonne de la table, l'alias n'est pas nécessaire.

II-C - Modifier les données : la méthode Execute

Maintenant que nous savons lire les données, voyons comment les modifier… Contrairement à SELECT, les instructions INSERT, UPDATE et DELETE ne renvoient pas de données, la méthode Query n'est donc pas adaptée. À la place, on va utiliser la méthode Execute, qui s'utilise de la façon la plus simple du monde :

connection.Execute("insert into Contact(Id, FirstName, LastName) values (42, 'George', 'Abitbol')");

La méthode Execute renvoie le nombre de lignes affectées par l'instruction, de la même manière que la méthode ExecuteNonQuery de IDbCommand. Par exemple, voilà comment récupérer le nombre de lignes supprimées :

int n = connection.Execute("delete from Contact where FirstName = 'George'");

Notez que la méthode Execute permet également d'exécuter des commandes DDL (Data Definition Language), par exemple ALTER TABLE, CREATE INDEX, etc.

II-D - Requêtes paramétrées

Avec ADO.NET, beaucoup de gens construisent leurs requêtes par concaténation, pour inclure des données variables. C'est une mauvaise pratique pour différentes raisons :

- cela rend le code vulnérable aux attaques par injection SQL ;

- cela pose des problèmes de format des données (par exemple, quand on insère une date, il faut savoir exactement quel est le format attendu par le SGBD)

- dans le cas d'une requête exécutée de nombreuses fois avec des données différentes (par exemple, une insertion de nombreuses lignes dans une table), cela nuit aux performances, car le texte de la requête est différent à chaque fois le SGBD ne peut donc pas la mettre en cache.

(voir cet article pour une discussion plus approfondie sur le sujet)

Même ceux qui savent que c'est mal le font souvent par paresse, car le code ADO.NET pour écrire des requêtes paramétrées est vraiment très lourd… Mais avec Dapper, cette excuse ne tient plus !

En effet, Dapper propose un mécanisme très simple pour passer des paramètres : il suffit de passer à la méthode Query un objet qui contient les paramètres. Cet objet peut éventuellement être d'un type anonyme, ce qui est très pratique, comme on va le voir dans l'exemple suivant :

var results = connection.Query<Contact>(
"select * from Contact where DateOfBirth < @maxDate and FirstName = @firstName",
new { maxDate = new DateTime(1950, 1, 1), firstName = "George" });

Cette requête récupère tous les contacts prénommés George et nés avant le 1er janvier 1950. Notez que dans le texte de la requête, les paramètres sont dénotés par le caractère '@' ; cela peut changer selon le SGBD utilisé (par exemple pour Oracle le préfixe sera ':').

Encore une fois, voici le code ADO.NET équivalent, pour comparer :

using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = "select * from Contact where DateOfBirth < @maxDate and FirstName = @firstName";

IDbDataParameter maxDateParam = command.CreateParameter();
maxDateParam.ParameterName = "@maxDate";
maxDateParam.DbType = DbType.DateTime;
maxDateParam.Value = new DateTime(1950, 1, 1);
command.Parameters.Add(maxDateParam);

IDbDataParameter firstNameParam = command.CreateParameter();
firstNameParam.ParameterName = "@firstName";
firstNameParam.DbType = DbType.String;
firstNameParam.Value = "George";
command.Parameters.Add(firstNameParam);

using (IDataReader reader = command.ExecuteReader())
{
List<Contact> contacts = new List<Contact>();
while (reader.Read())
{
Contact contact = new Contact
{
Id = (int)reader["Id"],
LastName = (string)reader["LastName"],
FirstName = (string)reader["FirstName"],
DateOfBirth = reader["DateOfBirth"] == DBNull.Value ? null : (DateTime?)reader["DateOfBirth"],
PhoneNumber = (string)reader["PhoneNumber"]
};
contacts.Add(contact);
}
return contacts;
}
}

Dix lignes en plus par rapport au code d'origine pour ajouter deux paramètres ! Bon, j'avoue, j'ai un peu forcé le trait : j'ai utilisé la couche d'abstraction d'ADO.NET (qui permet d'être indépendant du SGBD utilisé), ce qui m'oblige à définir les paramètres de la façon la plus lourde possible… Si vous utilisez explicitement un fournisseur spécifique, il y a des raccourcis qui permettent de déclarer un paramètre en une ou deux lignes. Mais il n'en reste pas moins que l'approche de Dapper est nettement plus concise et élégante, et permet un scénario extrêmement pratique, comme on va le voir dans un instant…

Dans l'exemple précédent, on a passé les paramètres via un type anonyme. Mais il est également possible d'utiliser un type nommé… Par exemple, une classe représentant une entité ! Voilà par exemple comment on pourrait insérer une ligne dans la table Contact :

public void AddContact(Contact contact)
{
string sql =
@"insert into Contact(Id, FirstName, LastName, DateOfBirth, PhoneNumber)
values (@Id, @FirstName, @LastName, @DateOfBirth, @PhoneNumber)";
connection.Execute(sql, contact);
}

Remarquez que les noms des paramètres dans la requête correspondent exactement à ceux des propriétés de la classe Contact : de cette façon, on peut utiliser directement l'objet Contact lui-même pour transmettre les paramètres de la requête. Cela élimine la plus grande partie du code de plomberie, et rend le code beaucoup plus propre.

Autre exemple, la mise à jour d'un contact :

public void UpdateContact(Contact contact)
{
string sql =
@"update Contact
set FirstName = @FirstName,
LastName = @LastName,
DateOfBirth = @DateOfBirth,
PhoneNumber = @PhoneNumber
where Id = @Id";
connection.Execute(sql, contact);
}

Le principe est le même que dans l'exemple précédent, sauf que cette fois le paramètre Id est utilisé dans la condition du WHERE.

III - Autres fonctionnalités

Si vous avez observé attentivement les méthodes Query et Execute, vous aurez peut-être remarqué qu'il en existe plusieurs surcharges, et qu'elles ont beaucoup plus de paramètres que celles qu'on a utilisées… Ces paramètres sont optionnels et permettent d'utiliser d'autres fonctionnalités ; voyons les plus importantes.

III-A - Appeler une procédure stockée

Beaucoup de gens préfèrent utiliser des procédures stockées plutôt que des requêtes SQL complexes dans le code de l'application. Dapper permet d'appeler très facilement une procédure stockée, en précisant simplement commandType: CommandType.StoredProcedure dans les paramètres de Query ou Execute (selon que la procédure renvoie des données ou non). Par exemple, en supposant qu'il existe une procédure stockée GetContact avec un paramètre Id qui renvoie le contact avec l'identifiant spécifié, on pourrait l'appeler comme ceci avec Dapper :

var contact =
connection.Query<Contact>(
"GetContact",
new { Id = 42 },
commandType: CommandType.StoredProcedure)
.Single();

Pour une procédure stockée avec des paramètres de sortie, c'est un peu plus compliqué, car on ne peut plus utiliser de type anonyme, vu que les propriétés de ceux-ci sont en lecture seule. Il faut donc utiliser la classe DynamicParameters fournie par Dapper :

var p = new DynamicParameters();
p.Add("@Id", value: 42);
p.Add("@PhoneNumber", dbType: DbType.String, size: 100, direction: ParameterDirection.Output);
connection.Execute("GetPhoneNumber", p, commandType: CommandType.StoredProcedure);
string phoneNumber = p.Get<string>("PhoneNumber");

Notez la déclaration du paramètre PhoneNumber, où on précise que c'est un paramètre de sortie. Pour récupérer la valeur après l'exécution, il suffit d'appeler Get sur l'objet DynamicParameters, en précisant le type (via le paramètre générique) et le nom du paramètre.

III-B - Utiliser une transaction

Pour maintenir la cohérence des données, il est souvent important d'exécuter une série d'instructions dans une transaction. Les méthodes Query et Execute ont un paramètre optionnel transaction prévu à cet effet :

using (var tx = connection.BeginTransaction())
{
connection.Execute("delete from Contact where Id = 1", transaction: tx);
connection.Execute("delete from Contact where Id = 2", transaction: tx);
tx.Commit();
}

Cela fonctionne exactement de la même manière qu'en ADO.NET « classique » : si tout s'est bien passé, on appelle Commit sur la transaction pour valider les modifications, sinon on appelle Rollback pour les annuler. Si la transaction est utilisée dans un bloc using, il n'est pas nécessaire d'appeler Rollback explicitement : en effet, la fin du bloc using causera un appel à Dispose, qui lui-même appellera Rollback si un Commit n'a pas été effectué.

III-C - Exécuter plusieurs requêtes

Supposons maintenant que notre modèle de données est un peu différent : au lieu d'avoir le numéro de téléphone dans la table Contact, on a une autre table ContactPhoneNumber qui contient les numéros des contacts (chaque contact pouvant avoir plusieurs numéros, par exemple domicile, mobile, etc.).

Nous avons donc les entités suivantes :

class Contact
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime? DateOfBirth { get; set; }
public IList<ContactPhoneNumber> PhoneNumbers { get; set; }
}

class ContactPhoneNumber
{
public int Id { get; set; }
public int ContactId { get; set; }
public string PhoneNumber { get; set; }
public string NumberType { get; set; }
}

Pour récupérer une entité Contact complète, il faut donc récupérer les données de la table Contact et celle de la table ContactPhoneNumber. Le plus simple est de faire deux requêtes, mais cela a l'inconvénient de faire deux allers-retours entre le client et la base de données… Heureusement, Dapper permet de récupérer les résultats de deux requêtes en même temps (à condition bien sûr que le SGBD le supporte - c'est le cas de SQL Server par exemple).

Voilà comment ça fonctionne :

string sql =
@"select * from Contact where Id = @Id;
select * from ContactPhoneNumber where ContactId = @Id";
var results = connection.QueryMultiple(sql, new { Id = 42 });
var contact = results.Read<Contact>().Single();
contact.PhoneNumbers = results.Read<ContactPhoneNumber>().ToList();

Notez que les deux requêtes sont séparées par un point-virgule.

On récupère le résultat de la première requête en faisant un premier appel à Read sur le résultat, et en précisant le type des éléments. Puisqu'on ne veut récupérer qu'un contact, on utilise Single pour récupérer l'unique résultat. On affecte ensuite à PhoneNumbers le résultat de la deuxième requête.

Si on voulait récupérer tous les contacts avec tous leurs numéros, ce serait un peu plus complexe, mais le code reste assez concis :

string sql = "select * from Contact; select * from ContactPhoneNumber";
var results = connection.QueryMultiple(sql);
var contacts =
results.Read<Contact>()
.GroupJoin(
results.Read<ContactPhoneNumber>(),
contact => contact.Id,
number => number.ContactId,
(contact, numbers) =>
{
contact.PhoneNumbers = numbers.ToList();
return contact;
});

Remarquez que, puisque les résultats renvoyés par Dapper sont de type IEnumerable<T>, on peut utiliser Linq pour les manipuler et les combiner. Ici, on utilise l'opérateur GroupJoin, qui permet d'associer à chaque élément d'une séquence les éléments correspondants d'une autre séquence.

III-D - Lire de gros volumes de données

Par défaut, Dapper charge en mémoire tous les résultats de la requête, de façon à minimiser le temps de verrouillage de la base de données et le temps de connexion à la base. Ce comportement est adapté pour de petits volumes de données, mais si vous lisez des millions de lignes, cela peut poser des problèmes de consommation mémoire.

Si vous souhaitez traiter les résultats au fur et à mesure qu'ils sont lus depuis la base de données, il suffit de spécifier false pour le paramètre buffered de la méthode Query :

var contacts = connection.Query<Contact>(sql, buffered: false);

III-E - Insertion en masse

Si vous devez insérer plusieurs lignes dans une même table, vous pouvez bien sûr appeler la méthode Execute en boucle, mais Dapper propose un raccourci intéressant : le paramètre param qui contient les valeurs des paramètres peut être une collection. Dans ce cas Dapper se chargera lui-même d'exécuter la requête autant de fois qu'il y a d'éléments dans la collection, en changeant les valeurs des paramètres à chaque fois :

var odile = new Contact { Id = 13, FirstName = "Odile", LastName = "Deray" };
var simon = new Contact { Id = 14, FirstName = "Simon", LastName = "Jérémi" };
var kara = new Contact { Id = 15, FirstName = "Serge", LastName = "Karamasov" };
string sql =
@"insert into Contact(Id, FirstName, LastName)
values (@Id, @FirstName, @LastName)";
connection.Execute(sql, new[] { odile, simon, kara });

Conclusion

Ainsi se termine ce tutoriel sur l'utilisation de Dapper. J'espère qu'il vous aura convaincu de l'intérêt de cet outil, qui permet de profiter d'une partie des avantages d'un ORM (API très simple d'utilisation, matérialisation automatique des entités), tout en restant beaucoup plus léger et performant qu'un ORM complet. Je n'ai bien sûr pas fait le tour complet des fonctionnalités, n'hésitez pas à jeter un œil à la page d'accueil du dépôt Github pour plus de détails.

Remerciements

Je tiens à remercier h2s84 et rv26t pour la relecture technique de cet article, ainsi que zoom61 pour la correction orthographique.