Dapper Micro-ORM in .NET: Fast Data Access Guide

In the world of .NET data access, developers often face a trade-off between productivity and performance. Full-featured ORMs like Entity Framework Core offer convenience with automatic change tracking and LINQ queries, but they can introduce overhead that becomes problematic under high load. On the other end, raw ADO.NET gives you maximum speed but requires tedious boilerplate code for mapping results to objects.

Enter Dapper: a micro-ORM created by the Stack Overflow team to handle their massive traffic demands. Dapper strikes a perfect balance by providing a thin wrapper around ADO.NET that eliminates boilerplate while maintaining near-raw performance. It extends the IDbConnection interface with simple methods like Query, Execute, and QueryMultiple, automatically mapping database rows to strongly-typed objects.

In this tutorial, you'll learn how to integrate Dapper into your .NET applications, write efficient SQL queries, handle complex mappings, and avoid common pitfalls. We'll cover real-world scenarios including multi-table joins, stored procedures, and async operations. By the end, you'll be able to use Dapper to build fast, reliable data access layers that scale.

Setting Up Dapper in Your .NET Project

To get started with Dapper, you need to install the NuGet package. Dapper is a single-file library that adds extension methods to IDbConnection. You can install it via the .NET CLI or Package Manager Console.

First, create a new console application or use an existing ASP.NET Core project. Then add the Dapper package:

``bash dotnet add package Dapper ``

Dapper works with any ADO.NET provider (SQL Server, PostgreSQL, MySQL, SQLite, etc.). You'll also need the appropriate data provider package, e.g., Microsoft.Data.SqlClient for SQL Server.

Once installed, you can start using Dapper by opening a connection and calling its extension methods. Here's a minimal example:

```csharp using System.Data; using Microsoft.Data.SqlClient; using Dapper;

string connectionString = "Server=.;Database=MyDb;Trusted_Connection=true;"; using (IDbConnection db = new SqlConnection(connectionString)) { var products = db.Query<Product>("SELECT * FROM Products"); foreach (var product in products) { Console.WriteLine($"{product.Id}: {product.Name}"); } }

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

Dapper automatically maps the result columns to the Product properties by name (case-insensitive). No manual mapping or configuration needed.

Key Takeaway: Dapper is easy to set up: install the package, open a connection, and use Query<T> to get strongly-typed results.

using System.Data;
using Microsoft.Data.SqlClient;
using Dapper;

string connectionString = "Server=.;Database=MyDb;Trusted_Connection=true;";
using (IDbConnection db = new SqlConnection(connectionString))
{
    var products = db.Query<Product>("SELECT * FROM Products");
    foreach (var product in products)
    {
        Console.WriteLine($"{product.Id}: {product.Name}");
    }
}

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

Executing Queries and Commands

Dapper provides several extension methods for different operations:

• Query<T>: Executes a query and maps results to a collection of T.
• QueryFirst<T>, QuerySingle<T>: Return a single result (throws if not exactly one).
• QueryFirstOrDefault<T>, QuerySingleOrDefault<T>: Return default if no rows.
• Execute: Executes a command (INSERT, UPDATE, DELETE) and returns the number of affected rows.
• ExecuteScalar<T>: Returns a single value (e.g., COUNT, SUM).

All methods have async counterparts: QueryAsync, ExecuteAsync, etc.

Parameterized Queries

Always use parameters to prevent SQL injection. Dapper supports anonymous objects for parameters:

``csharp var product = db.QueryFirstOrDefault<Product>( "SELECT * FROM Products WHERE Id = @Id", new { Id = 42 }); ``

You can also use DynamicParameters for more control:

``csharp var parameters = new DynamicParameters(); parameters.Add("@Id", 42, DbType.Int32, ParameterDirection.Input); var product = db.QueryFirstOrDefault<Product>("SELECT * FROM Products WHERE Id = @Id", parameters); ``

Stored Procedures

To execute a stored procedure, set the command type:

``csharp var result = db.Query<Product>("usp_GetProducts", new { CategoryId = 5 }, commandType: CommandType.StoredProcedure); ``

Multiple Result Sets

Use QueryMultiple to handle multiple result sets from a single query:

``csharp using (var multi = db.QueryMultiple("SELECT FROM Products; SELECT FROM Categories")) { var products = multi.Read<Product>(); var categories = multi.Read<Category>(); } ``

Key Takeaway: Dapper's methods cover all common data access patterns, with built-in parameterization and async support.

using (IDbConnection db = new SqlConnection(connectionString))
{
    // Parameterized query
    var product = db.QueryFirstOrDefault<Product>(
        "SELECT * FROM Products WHERE Id = @Id",
        new { Id = 42 });

    // Execute insert
    int rowsAffected = db.Execute(
        "INSERT INTO Products (Name, Price) VALUES (@Name, @Price)",
        new { Name = "NewProduct", Price = 9.99m });

    // Stored procedure
    var results = db.Query<Product>("usp_GetProducts",
        new { CategoryId = 5 },
        commandType: CommandType.StoredProcedure);

    // Multiple result sets
    using (var multi = db.QueryMultiple("SELECT * FROM Products; SELECT * FROM Categories"))
    {
        var products = multi.Read<Product>();
        var categories = multi.Read<Category>();
    }
}

Mapping Results to Objects

Dapper maps columns to properties by name, ignoring case. If your SQL column names don't match your C# properties, you can use column aliases or custom mapping.

Column Aliases

``csharp var products = db.Query<Product>("SELECT Id AS ProductId, Name AS ProductName FROM Products"); ``

Custom Mapping with Dapper's SqlMapper

You can define a custom mapping for a type:

``csharp SqlMapper.SetTypeMap(typeof(Product), new CustomPropertyTypeMap( typeof(Product), (type, columnName) => type.GetProperty(columnName) )); ``

Handling Nulls

Dapper handles nullable types automatically. If a column is NULL, it maps to null for nullable types (int?, string, etc.). For non-nullable types, it throws an exception.

Multi-Mapping for Joins

When you have a join that returns data from multiple tables, use Dapper's multi-mapping feature:

```csharp var sql = @"SELECT * FROM Orders o INNER JOIN Customers c ON o.CustomerId = c.Id";

var orders = db.Query<Order, Customer, Order>(sql, (order, customer) => { order.Customer = customer; return order; }, splitOn: "Id"); ```

The splitOn parameter tells Dapper where to split the result columns between objects. By default, it splits on "Id". You can specify multiple split columns for more complex mappings.

Key Takeaway: Dapper's automatic mapping works well for simple cases. Use aliases or multi-mapping for complex scenarios.

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public Customer Customer { get; set; }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// Multi-mapping example
var sql = @"SELECT o.Id, o.OrderDate, c.Id, c.Name 
            FROM Orders o 
            INNER JOIN Customers c ON o.CustomerId = c.Id";

var orders = db.Query<Order, Customer, Order>(sql,
    (order, customer) =>
    {
        order.Customer = customer;
        return order;
    },
    splitOn: "Id").ToList();

Console.WriteLine(orders[0].Customer.Name);

Working with Transactions

Dapper works seamlessly with database transactions. You can pass a transaction object to any Dapper method.

Basic Transaction

``csharp using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var transaction = connection.BeginTransaction()) { try { connection.Execute("UPDATE Products SET Price = Price * 1.1", transaction: transaction); connection.Execute("INSERT INTO AuditLog (Action) VALUES (@Action)", new { Action = "PriceUpdate" }, transaction: transaction); transaction.Commit(); } catch { transaction.Rollback(); throw; } } } ``

Async Transactions

For async operations, use the async versions:

``csharp await connection.ExecuteAsync(sql, transaction: transaction); ``

Using TransactionScope

You can also use System.Transactions.TransactionScope for ambient transactions:

``csharp using (var scope = new TransactionScope()) { using (var connection = new SqlConnection(connectionString)) { connection.Execute("UPDATE ..."); } using (var connection2 = new SqlConnection(connectionString2)) { connection2.Execute("UPDATE ..."); } scope.Complete(); } ``

Note that TransactionScope requires the Microsoft.Data.SqlClient package and may need configuration for distributed transactions.

Key Takeaway: Dapper supports transactions via the transaction parameter or TransactionScope, giving you full control over atomicity.

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            connection.Execute("UPDATE Products SET Price = Price * 1.1", transaction: transaction);
            connection.Execute("INSERT INTO AuditLog (Action) VALUES (@Action)", 
                new { Action = "PriceUpdate" }, transaction: transaction);
            transaction.Commit();
            Console.WriteLine("Transaction committed.");
        }
        catch
        {
            transaction.Rollback();
            Console.WriteLine("Transaction rolled back.");
            throw;
        }
    }
}

Performance Optimization and Best Practices

Dapper is already fast, but you can optimize further with these techniques:

1. Use Buffered vs Unbuffered Queries

By default, Dapper buffers query results (loads all into memory). For large result sets, use unbuffered to stream:

``csharp var products = db.Query<Product>("SELECT * FROM Products", buffered: false); // Iterate without loading all into memory ``

2. Avoid SELECT *

Select only the columns you need to reduce data transfer and mapping overhead.

3. Use Async Methods

In web applications, use async methods to free up threads:

``csharp var products = await db.QueryAsync<Product>("SELECT * FROM Products"); ``

4. Cache Compiled Queries

Dapper caches the generated IL code for mapping. You can pre-compile queries for repeated use:

``csharp var query = SqlMapper.Query<Product>(connection, "SELECT * FROM Products"); ``

5. Use DynamicParameters for Complex Parameters

DynamicParameters allows you to specify types and directions explicitly, which can improve performance.

6. Connection Pooling

Rely on ADO.NET connection pooling. Open and close connections quickly; don't hold them open.

7. Batch Operations

For multiple inserts/updates, use table-valued parameters or bulk insert via SqlBulkCopy. Dapper's Execute can handle multiple statements in one call if you pass a list:

``csharp var products = new List<Product> { ... }; db.Execute("INSERT INTO Products (Name, Price) VALUES (@Name, @Price)", products); ``

Key Takeaway: Optimize by selecting only needed columns, using unbuffered queries for large sets, and leveraging async I/O.

// Unbuffered query for large result set
var products = db.Query<Product>("SELECT Id, Name FROM Products", buffered: false);
foreach (var product in products)
{
    // Process one at a time
}

// Batch insert
var newProducts = new List<Product>
{
    new Product { Name = "A", Price = 1.0m },
    new Product { Name = "B", Price = 2.0m }
};
int rows = db.Execute("INSERT INTO Products (Name, Price) VALUES (@Name, @Price)", newProducts);
Console.WriteLine($"Inserted {rows} rows.");

Handling Dynamic Results and ExpandoObject

Sometimes you don't have a predefined POCO for the result. Dapper supports dynamic results using dynamic or ExpandoObject.

QueryDynamic

Use Query without a type parameter to get a collection of dynamic objects:

``csharp var rows = db.Query("SELECT Id, Name FROM Products"); foreach (dynamic row in rows) { Console.WriteLine($"{row.Id}: {row.Name}"); } ``

Using ExpandoObject

You can also map to ExpandoObject explicitly:

``csharp var rows = db.Query<ExpandoObject>("SELECT Id, Name FROM Products"); ``

When to Use Dynamic

• When the result schema is unknown at compile time.
• For quick prototyping or ad-hoc queries.
• When working with NoSQL-like data or varying columns.

However, dynamic results lose compile-time safety and performance (no IL caching). Use sparingly in production.

Key Takeaway: Dapper can return dynamic objects for flexible scenarios, but prefer strongly-typed POCOs for performance and safety.

var rows = db.Query("SELECT Id, Name FROM Products");
foreach (dynamic row in rows)
{
    Console.WriteLine($"{row.Id}: {row.Name}");
}

// Or with ExpandoObject
var expandoRows = db.Query<ExpandoObject>("SELECT Id, Name FROM Products");
foreach (var row in expandoRows)
{
    var dict = (IDictionary<string, object>)row;
    Console.WriteLine($"{dict["Id"]}: {dict["Name"]}");
}

Integrating Dapper with ASP.NET Core

In ASP.NET Core applications, you typically register the database connection as a service and inject it into controllers or services.

Registering Dapper Services

In Program.cs:

``csharp builder.Services.AddTransient<IDbConnection>(sp => new SqlConnection(builder.Configuration.GetConnectionString("DefaultConnection"))); ``

Then inject IDbConnection into your repository or service:

```csharp public class ProductRepository { private readonly IDbConnection _db; public ProductRepository(IDbConnection db) { _db = db; }

public async Task<IEnumerable<Product>> GetAllAsync() { return await _db.QueryAsync<Product>("SELECT * FROM Products"); } } ```

Using Dapper in Controllers

```csharp [ApiController] [Route("api/[controller]")] public class ProductsController : ControllerBase { private readonly IDbConnection _db; public ProductsController(IDbConnection db) { _db = db; }

[HttpGet] public async Task<IActionResult> Get() { var products = await _db.QueryAsync<Product>("SELECT * FROM Products"); return Ok(products); } } ```

Best Practices

• Use transient or scoped lifetime for connections. Scoped is common for web apps.
• Avoid holding connections open across requests.
• Use async methods to avoid blocking.
• Consider using a repository pattern to abstract Dapper calls.

Key Takeaway: Dapper integrates easily with ASP.NET Core via dependency injection. Register IDbConnection and inject it where needed.

// Program.cs
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddTransient<IDbConnection>(sp =>
    new SqlConnection(builder.Configuration.GetConnectionString("DefaultConnection")));

var app = builder.Build();
app.MapControllers();
app.Run();

// ProductsController.cs
[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly IDbConnection _db;
    public ProductsController(IDbConnection db)
    {
        _db = db;
    }

    [HttpGet]
    public async Task<IActionResult> Get()
    {
        var products = await _db.QueryAsync<Product>("SELECT * FROM Products");
        return Ok(products);
    }
}