Accès aux données avec Dapper

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.

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.

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…

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.

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 :

   

  Sélectionnez

  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▲

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; }
}

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

    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);
            }
        }
    }

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

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

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.

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 1^er 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.

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.

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é.

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.

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);

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 });