Dapper : le micro-ORM performant pour .NET

Aperçu des méthodes de requête, paramètres, et exemples de mapping simples et avancés.

Liste des méthodes de requête Dapper

// Lecture
conn.Query<T>(sql, params);
conn.QueryFirst<T>(sql, params);
conn.QueryFirstOrDefault<T>(sql, params);
conn.QuerySingle<T>(sql, params);
conn.QuerySingleOrDefault<T>(sql, params);

// Lecture multi‑types (multi‑mapping)
conn.Query<TFirst, TSecond, TReturn>(sql, map, splitOn: "Id");

// Lecture multiple jeux de résultats
using var grid = conn.QueryMultiple(sql, params);
var a = grid.Read<A>();
var b = grid.Read<B>();

// Écriture
conn.Execute(sql, params, transaction);

// Paramètres
var p = new DynamicParameters();
p.Add("@Id", 42);

// Procédures stockées
conn.Query<T>("spGetItems", p, commandType: CommandType.StoredProcedure);

Exemple simple : DTO + QueryFirstOrDefault

public class ProductDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

using (var conn = new SqlConnection(connString))
{
    const string sql = @"
        SELECT Id, Name, Price
        FROM Products
        WHERE Id = @ProductId";

    var product = await conn.QueryFirstOrDefaultAsync<ProductDto>(
                       sql,
                       new { ProductId = 42 });

  if (product == null)
  {
    Console.WriteLine("Product not found.");
  }
  else
  {
    Console.WriteLine($"{product.Name} : {product.Price:C}");
  }
}
// Advantage: automatic mapping via property names.

Mapping via alias SQL

public class OrderSummary
{
    public int OrderId { get; set; }
    public string CustomerName { get; set; }
    public DateTime OrderDate { get; set; }
}

// Requête SQL
SELECT 
    o.Id       AS OrderId,
    c.FullName AS CustomerName,
    o.OrderDate
FROM Orders o
JOIN Customers c ON o.CustomerId = c.Id
WHERE o.Id = @OrderId;

// Code C#
using (var conn = new SqlConnection(connString))
{
    const string sql = @"
        SELECT 
            o.Id       AS OrderId,
            c.FullName AS CustomerName,
            o.OrderDate
        FROM Orders o
        JOIN Customers c ON o.CustomerId = c.Id
        WHERE o.Id = @OrderId";

    var summary = await conn.QueryFirstOrDefaultAsync<OrderSummary>(
                       sql,
                       new { OrderId = 101 });

  // summary.CustomerName is populated via the alias
}
// Tip: use alias (AS) to match columns -> properties.

Multi-mapping (relations)

public class OrderDto
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public List<OrderLineDto> Lines { get; set; } = new List<OrderLineDto>();
}

public class OrderLineDto
{
    public int LineId { get; set; }
    public string ProductName { get; set; }
    public int Quantity { get; set; }
}

// Requête SQL
SELECT 
    o.Id        AS Id,
    o.OrderDate AS Date,
    ol.LineId   AS LineId,
    p.Name      AS ProductName,
    ol.Quantity AS Quantity
FROM Orders o
JOIN OrderLines ol ON ol.OrderId = o.Id
JOIN Products p  ON p.Id = ol.ProductId
WHERE o.Id = @OrderId;

// Code C#
using (var conn = new SqlConnection(connString))
{
    const string sql = @"
        SELECT 
            o.Id        AS Id,
            o.OrderDate AS Date,
            ol.LineId   AS LineId,
            p.Name      AS ProductName,
            ol.Quantity AS Quantity
        FROM Orders o
        JOIN OrderLines ol ON ol.OrderId = o.Id
        JOIN Products p  ON p.Id = ol.ProductId
        WHERE o.Id = @OrderId";

    var lookup = new Dictionary<int, OrderDto>();

    var order = (await conn.QueryAsync<OrderDto, OrderLineDto, OrderDto>(
                       sql,
                       (orderObj, line) =>
                       {
                           if (!lookup.TryGetValue(orderObj.Id, out var root))
                           {
                               root = orderObj;
                               lookup[root.Id] = root;
                           }
                           if (line != null && !root.Lines.Any(l => l.LineId == line.LineId))
                               root.Lines.Add(line);
                           return root;
                       },
                       new { OrderId = 101 },
                       splitOn: "LineId")).FirstOrDefault();

  // order is the single OrderDto with all its lines
}

// Keys:
// - splitOn: "LineId" indicates where Dapper should split into OrderLineDto.
// - The delegate is called per row; group via lookup to consolidate.