1# Dapper Patterns and Best Practices
2 
3Advanced patterns for high-performance data access with Dapper in .NET.
4 
5## Why Dapper?
6 
7| Aspect           | Dapper                         | EF Core                |
8| ---------------- | ------------------------------ | ---------------------- |
9| Performance      | ~10x faster for simple queries | Good with optimization |
10| Control          | Full SQL control               | Abstracted             |
11| Learning curve   | Low (just SQL)                 | Higher                 |
12| Complex mappings | Manual                         | Automatic              |
13| Change tracking  | None                           | Built-in               |
14| Migrations       | External tools                 | Built-in               |
15 
16**Use Dapper when:**
17 
18- Performance is critical (hot paths)
19- You need complex SQL (CTEs, window functions)
20- Read-heavy workloads
21- Legacy database schemas
22 
23**Use EF Core when:**
24 
25- Rich domain models with relationships
26- Need change tracking
27- Want LINQ-to-SQL translation
28- Complex object graphs
29 
30## Connection Management
31 
32### 1. Proper Connection Handling
33 
34```csharp
35// Register connection factory
36services.AddScoped<IDbConnection>(sp =>
37{
38    var connectionString = sp.GetRequiredService<IConfiguration>()
39        .GetConnectionString("Default");
40    return new SqlConnection(connectionString);
41});
42 
43// Or use a factory for more control
44public interface IDbConnectionFactory
45{
46    IDbConnection CreateConnection();
47}
48 
49public class SqlConnectionFactory : IDbConnectionFactory
50{
51    private readonly string _connectionString;
52 
53    public SqlConnectionFactory(IConfiguration configuration)
54    {
55        _connectionString = configuration.GetConnectionString("Default")
56            ?? throw new InvalidOperationException("Connection string not found");
57    }
58 
59    public IDbConnection CreateConnection() => new SqlConnection(_connectionString);
60}
61```
62 
63### 2. Connection Lifecycle
64 
65```csharp
66public class ProductRepository
67{
68    private readonly IDbConnectionFactory _factory;
69 
70    public ProductRepository(IDbConnectionFactory factory)
71    {
72        _factory = factory;
73    }
74 
75    public async Task<Product?> GetByIdAsync(string id, CancellationToken ct)
76    {
77        // Connection opens automatically, closes on dispose
78        using var connection = _factory.CreateConnection();
79 
80        return await connection.QueryFirstOrDefaultAsync<Product>(
81            new CommandDefinition(
82                "SELECT * FROM Products WHERE Id = @Id",
83                new { Id = id },
84                cancellationToken: ct));
85    }
86}
87```
88 
89## Query Patterns
90 
91### 3. Basic CRUD Operations
92 
93```csharp
94// SELECT single
95var product = await connection.QueryFirstOrDefaultAsync<Product>(
96    "SELECT * FROM Products WHERE Id = @Id",
97    new { Id = id });
98 
99// SELECT multiple
100var products = await connection.QueryAsync<Product>(
101    "SELECT * FROM Products WHERE CategoryId = @CategoryId",
102    new { CategoryId = categoryId });
103 
104// INSERT with identity return
105var newId = await connection.QuerySingleAsync<int>(
106    """
107    INSERT INTO Products (Name, Price, CategoryId)
108    VALUES (@Name, @Price, @CategoryId);
109    SELECT CAST(SCOPE_IDENTITY() AS INT);
110    """,
111    product);
112 
113// INSERT with OUTPUT clause (returns full entity)
114var inserted = await connection.QuerySingleAsync<Product>(
115    """
116    INSERT INTO Products (Name, Price, CategoryId)
117    OUTPUT INSERTED.*
118    VALUES (@Name, @Price, @CategoryId);
119    """,
120    product);
121 
122// UPDATE
123var rowsAffected = await connection.ExecuteAsync(
124    """
125    UPDATE Products
126    SET Name = @Name, Price = @Price, UpdatedAt = @UpdatedAt
127    WHERE Id = @Id
128    """,
129    new { product.Id, product.Name, product.Price, UpdatedAt = DateTime.UtcNow });
130 
131// DELETE
132await connection.ExecuteAsync(
133    "DELETE FROM Products WHERE Id = @Id",
134    new { Id = id });
135```
136 
137### 4. Dynamic Query Building
138 
139```csharp
140public async Task<IReadOnlyList<Product>> SearchAsync(ProductSearchCriteria criteria)
141{
142    var sql = new StringBuilder("SELECT * FROM Products WHERE 1=1");
143    var parameters = new DynamicParameters();
144 
145    if (!string.IsNullOrWhiteSpace(criteria.SearchTerm))
146    {
147        sql.Append(" AND (Name LIKE @SearchTerm OR Sku LIKE @SearchTerm)");
148        parameters.Add("SearchTerm", $"%{criteria.SearchTerm}%");
149    }
150 
151    if (criteria.CategoryId.HasValue)
152    {
153        sql.Append(" AND CategoryId = @CategoryId");
154        parameters.Add("CategoryId", criteria.CategoryId.Value);
155    }
156 
157    if (criteria.MinPrice.HasValue)
158    {
159        sql.Append(" AND Price >= @MinPrice");
160        parameters.Add("MinPrice", criteria.MinPrice.Value);
161    }
162 
163    if (criteria.MaxPrice.HasValue)
164    {
165        sql.Append(" AND Price <= @MaxPrice");
166        parameters.Add("MaxPrice", criteria.MaxPrice.Value);
167    }
168 
169    // Pagination
170    sql.Append(" ORDER BY Name");
171    sql.Append(" OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY");
172    parameters.Add("Offset", (criteria.Page - 1) * criteria.PageSize);
173    parameters.Add("PageSize", criteria.PageSize);
174 
175    using var connection = _factory.CreateConnection();
176    var results = await connection.QueryAsync<Product>(sql.ToString(), parameters);
177    return results.ToList();
178}
179```
180 
181### 5. Multi-Mapping (Joins)
182 
183```csharp
184// One-to-One mapping
185public async Task<Product?> GetProductWithCategoryAsync(string id)
186{
187    const string sql = """
188        SELECT p.*, c.*
189        FROM Products p
190        INNER JOIN Categories c ON p.CategoryId = c.Id
191        WHERE p.Id = @Id
192        """;
193 
194    using var connection = _factory.CreateConnection();
195 
196    var result = await connection.QueryAsync<Product, Category, Product>(
197        sql,
198        (product, category) =>
199        {
200            product.Category = category;
201            return product;
202        },
203        new { Id = id },
204        splitOn: "Id");  // Column where split occurs
205 
206    return result.FirstOrDefault();
207}
208 
209// One-to-Many mapping
210public async Task<Order?> GetOrderWithItemsAsync(int orderId)
211{
212    const string sql = """
213        SELECT o.*, oi.*, p.*
214        FROM Orders o
215        LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
216        LEFT JOIN Products p ON oi.ProductId = p.Id
217        WHERE o.Id = @OrderId
218        """;
219 
220    var orderDictionary = new Dictionary<int, Order>();
221 
222    using var connection = _factory.CreateConnection();
223 
224    await connection.QueryAsync<Order, OrderItem, Product, Order>(
225        sql,
226        (order, item, product) =>
227        {
228            if (!orderDictionary.TryGetValue(order.Id, out var existingOrder))
229            {
230                existingOrder = order;
231                existingOrder.Items = new List<OrderItem>();
232                orderDictionary.Add(order.Id, existingOrder);
233            }
234 
235            if (item != null)
236            {
237                item.Product = product;
238                existingOrder.Items.Add(item);
239            }
240 
241            return existingOrder;
242        },
243        new { OrderId = orderId },
244        splitOn: "Id,Id");
245 
246    return orderDictionary.Values.FirstOrDefault();
247}
248```
249 
250### 6. Multiple Result Sets
251 
252```csharp
253public async Task<(IReadOnlyList<Product> Products, int TotalCount)> SearchWithCountAsync(
254    ProductSearchCriteria criteria)
255{
256    const string sql = """
257        -- First result set: count
258        SELECT COUNT(*) FROM Products WHERE CategoryId = @CategoryId;
259 
260        -- Second result set: data
261        SELECT * FROM Products
262        WHERE CategoryId = @CategoryId
263        ORDER BY Name
264        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
265        """;
266 
267    using var connection = _factory.CreateConnection();
268    using var multi = await connection.QueryMultipleAsync(sql, new
269    {
270        CategoryId = criteria.CategoryId,
271        Offset = (criteria.Page - 1) * criteria.PageSize,
272        PageSize = criteria.PageSize
273    });
274 
275    var totalCount = await multi.ReadSingleAsync<int>();
276    var products = (await multi.ReadAsync<Product>()).ToList();
277 
278    return (products, totalCount);
279}
280```
281 
282## Advanced Patterns
283 
284### 7. Table-Valued Parameters (Bulk Operations)
285 
286```csharp
287// SQL Server TVP for bulk operations
288public async Task<IReadOnlyList<Product>> GetByIdsAsync(IEnumerable<string> ids)
289{
290    // Create DataTable matching TVP structure
291    var table = new DataTable();
292    table.Columns.Add("Id", typeof(string));
293 
294    foreach (var id in ids)
295    {
296        table.Rows.Add(id);
297    }
298 
299    using var connection = _factory.CreateConnection();
300 
301    var results = await connection.QueryAsync<Product>(
302        "SELECT p.* FROM Products p INNER JOIN @Ids i ON p.Id = i.Id",
303        new { Ids = table.AsTableValuedParameter("dbo.StringIdList") });
304 
305    return results.ToList();
306}
307 
308// SQL to create the TVP type:
309// CREATE TYPE dbo.StringIdList AS TABLE (Id NVARCHAR(40));
310```
311 
312### 8. Stored Procedures
313 
314```csharp
315public async Task<IReadOnlyList<Product>> GetTopProductsAsync(int categoryId, int count)
316{
317    using var connection = _factory.CreateConnection();
318 
319    var results = await connection.QueryAsync<Product>(
320        "dbo.GetTopProductsByCategory",
321        new { CategoryId = categoryId, TopN = count },
322        commandType: CommandType.StoredProcedure);
323 
324    return results.ToList();
325}
326 
327// With output parameters
328public async Task<(Order Order, string ConfirmationCode)> CreateOrderAsync(Order order)
329{
330    var parameters = new DynamicParameters(new
331    {
332        order.CustomerId,
333        order.Total
334    });
335    parameters.Add("OrderId", dbType: DbType.Int32, direction: ParameterDirection.Output);
336    parameters.Add("ConfirmationCode", dbType: DbType.String, size: 20, direction: ParameterDirection.Output);
337 
338    using var connection = _factory.CreateConnection();
339 
340    await connection.ExecuteAsync(
341        "dbo.CreateOrder",
342        parameters,
343        commandType: CommandType.StoredProcedure);
344 
345    order.Id = parameters.Get<int>("OrderId");
346    var confirmationCode = parameters.Get<string>("ConfirmationCode");
347 
348    return (order, confirmationCode);
349}
350```
351 
352### 9. Transactions
353 
354```csharp
355public async Task<Order> CreateOrderWithItemsAsync(Order order, List<OrderItem> items)
356{
357    using var connection = _factory.CreateConnection();
358    await connection.OpenAsync();
359 
360    using var transaction = await connection.BeginTransactionAsync();
361 
362    try
363    {
364        // Insert order
365        order.Id = await connection.QuerySingleAsync<int>(
366            """
367            INSERT INTO Orders (CustomerId, Total, CreatedAt)
368            OUTPUT INSERTED.Id
369            VALUES (@CustomerId, @Total, @CreatedAt)
370            """,
371            order,
372            transaction);
373 
374        // Insert items
375        foreach (var item in items)
376        {
377            item.OrderId = order.Id;
378        }
379 
380        await connection.ExecuteAsync(
381            """
382            INSERT INTO OrderItems (OrderId, ProductId, Quantity, UnitPrice)
383            VALUES (@OrderId, @ProductId, @Quantity, @UnitPrice)
384            """,
385            items,
386            transaction);
387 
388        await transaction.CommitAsync();
389 
390        order.Items = items;
391        return order;
392    }
393    catch
394    {
395        await transaction.RollbackAsync();
396        throw;
397    }
398}
399```
400 
401### 10. Custom Type Handlers
402 
403```csharp
404// Register custom type handler for JSON columns
405public class JsonTypeHandler<T> : SqlMapper.TypeHandler<T>
406{
407    public override T Parse(object value)
408    {
409        if (value is string json)
410        {
411            return JsonSerializer.Deserialize<T>(json)!;
412        }
413        return default!;
414    }
415 
416    public override void SetValue(IDbDataParameter parameter, T value)
417    {
418        parameter.Value = JsonSerializer.Serialize(value);
419        parameter.DbType = DbType.String;
420    }
421}
422 
423// Register at startup
424SqlMapper.AddTypeHandler(new JsonTypeHandler<ProductMetadata>());
425 
426// Now you can query directly
427var product = await connection.QueryFirstAsync<Product>(
428    "SELECT Id, Name, Metadata FROM Products WHERE Id = @Id",
429    new { Id = id });
430// product.Metadata is automatically deserialized from JSON
431```
432 
433## Performance Tips
434 
435### 11. Use CommandDefinition for Cancellation
436 
437```csharp
438// Always use CommandDefinition for async operations
439var result = await connection.QueryAsync<Product>(
440    new CommandDefinition(
441        commandText: "SELECT * FROM Products WHERE CategoryId = @CategoryId",
442        parameters: new { CategoryId = categoryId },
443        cancellationToken: ct,
444        commandTimeout: 30));
445```
446 
447### 12. Buffered vs Unbuffered Queries
448 
449```csharp
450// Buffered (default) - loads all results into memory
451var products = await connection.QueryAsync<Product>(sql);  // Returns list
452 
453// Unbuffered - streams results (lower memory for large result sets)
454var products = await connection.QueryUnbufferedAsync<Product>(sql);  // Returns IAsyncEnumerable
455 
456await foreach (var product in products)
457{
458    // Process one at a time
459}
460```
461 
462### 13. Connection Pooling Settings
463 
464```json
465{
466  "ConnectionStrings": {
467    "Default": "Server=localhost;Database=MyDb;User Id=sa;Password=xxx;TrustServerCertificate=True;Min Pool Size=5;Max Pool Size=100;Connection Timeout=30;"
468  }
469}
470```
471 
472## Common Patterns
473 
474### Repository Base Class
475 
476```csharp
477public abstract class DapperRepositoryBase<T> where T : class
478{
479    protected readonly IDbConnectionFactory ConnectionFactory;
480    protected readonly ILogger Logger;
481    protected abstract string TableName { get; }
482 
483    protected DapperRepositoryBase(IDbConnectionFactory factory, ILogger logger)
484    {
485        ConnectionFactory = factory;
486        Logger = logger;
487    }
488 
489    protected async Task<T?> GetByIdAsync<TId>(TId id, CancellationToken ct = default)
490    {
491        var sql = $"SELECT * FROM {TableName} WHERE Id = @Id";
492 
493        using var connection = ConnectionFactory.CreateConnection();
494        return await connection.QueryFirstOrDefaultAsync<T>(
495            new CommandDefinition(sql, new { Id = id }, cancellationToken: ct));
496    }
497 
498    protected async Task<IReadOnlyList<T>> GetAllAsync(CancellationToken ct = default)
499    {
500        var sql = $"SELECT * FROM {TableName}";
501 
502        using var connection = ConnectionFactory.CreateConnection();
503        var results = await connection.QueryAsync<T>(
504            new CommandDefinition(sql, cancellationToken: ct));
505 
506        return results.ToList();
507    }
508 
509    protected async Task<int> ExecuteAsync(
510        string sql,
511        object? parameters = null,
512        CancellationToken ct = default)
513    {
514        using var connection = ConnectionFactory.CreateConnection();
515        return await connection.ExecuteAsync(
516            new CommandDefinition(sql, parameters, cancellationToken: ct));
517    }
518}
519```
520 
521## Anti-Patterns to Avoid
522 
523```csharp
524// ❌ Bad - SQL injection risk
525var sql = $"SELECT * FROM Products WHERE Name = '{userInput}'";
526 
527// ✅ Good - Parameterized query
528var sql = "SELECT * FROM Products WHERE Name = @Name";
529await connection.QueryAsync<Product>(sql, new { Name = userInput });
530 
531// ❌ Bad - Not disposing connection
532var connection = new SqlConnection(connectionString);
533var result = await connection.QueryAsync<Product>(sql);
534// Connection leak!
535 
536// ✅ Good - Using statement
537using var connection = new SqlConnection(connectionString);
538var result = await connection.QueryAsync<Product>(sql);
539 
540// ❌ Bad - Opening connection manually when not needed
541await connection.OpenAsync();  // Dapper does this automatically
542var result = await connection.QueryAsync<Product>(sql);
543 
544// ✅ Good - Let Dapper manage connection
545var result = await connection.QueryAsync<Product>(sql);
546```
547