MSDN Documentation

Dapper Micro-ORM Tutorial

Welcome to the Dapper micro-ORM tutorial for .NET data access. Dapper is a popular, high-performance, lightweight object-relational mapper (ORM) for .NET. It allows you to execute SQL queries and map the results directly to your .NET objects with minimal overhead.

Introduction to Dapper

In many applications, direct SQL interaction with an ORM like Entity Framework can be overkill or lead to performance bottlenecks. Dapper bridges this gap by providing a simple yet powerful way to interact with your database. It focuses on executing SQL and mapping results, leaving complex mapping and change tracking to you or other specialized libraries.

Installation

Dapper is available as a NuGet package. You can install it using the .NET CLI or the NuGet Package Manager in Visual Studio:

dotnet add package Dapper

Or via the Package Manager Console:

Install-Package Dapper

Basic Usage

The core of Dapper involves extending IDbConnection. Once you have an open database connection, you can use Dapper's extension methods directly on it.

Querying Data

Dapper excels at fetching data. The primary methods for querying are Query and QueryFirst/QueryFirstOrDefault.

Querying a Single Object

To retrieve a single record and map it to a specific type:

using System.Data;
using System.Data.SqlClient; // Or your specific provider
using Dapper;

// Assuming 'connectionString' is defined
using (IDbConnection db = new SqlConnection(connectionString))
{
    var user = db.QueryFirstOrDefault<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = 1 });

    if (user != null)
    {
        Console.WriteLine($"Found user: {user.Name}");
    }
}
            

In this example, @Id is a parameter, and Dapper automatically maps the result set columns to the properties of the User class (case-insensitive by default).

Querying a List of Objects

To fetch multiple records and map them to a list:

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

using (IDbConnection db = new SqlConnection(connectionString))
{
    var products = db.Query<Product>("SELECT * FROM Products WHERE Category = @Category", new { Category = "Electronics" });

    foreach (var product in products)
    {
        Console.WriteLine($"- {product.Name} (${product.Price})");
    }
}
            

Querying Dynamic Objects

If you don't have a specific type to map to, or for ad-hoc queries, you can use dynamic:

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

using (IDbConnection db = new SqlConnection(connectionString))
{
    var results = db.Query<dynamic>("SELECT Name, COUNT(*) AS ItemCount FROM Items GROUP BY Name");

    foreach (var row in results)
    {
        Console.WriteLine($"{row.Name} has {row.ItemCount} items.");
    }
}
            

Executing Commands

For operations that don't return data (INSERT, UPDATE, DELETE), use Execute:

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

using (IDbConnection db = new SqlConnection(connectionString))
{
    int affectedRows = db.Execute("UPDATE Orders SET Status = @Status WHERE OrderDate < @CutoffDate", new { Status = "Shipped", CutoffDate = new DateTime(2023, 1, 1) });

    Console.WriteLine($"{affectedRows} orders updated.");
}
            

Execute returns the number of rows affected by the command.

Parameter Handling

Dapper supports several ways to pass parameters:

• Anonymous Objects: As shown in examples above, this is the most common and convenient method.
• DynamicParameters: For more complex scenarios, like output parameters or specifying types.
• IDictionary<string, object>: Less common, but supported.

using Dapper;
using System.Data.SqlClient;

using (IDbConnection db = new SqlConnection(connectionString))
{
    var parameters = new DynamicParameters();
    parameters.Add("@UserId", 101, DbType.Int32);
    parameters.Add("@UserName", "NewUser");

    // Execute a stored procedure with output parameter
    parameters.Add("@OutputId", dbType: DbType.Int32, direction: ParameterDirection.Output);

    db.Execute("sp_CreateUser", parameters, commandType: CommandType.StoredProcedure);

    int newUserId = parameters.Get<int>("@OutputId");
    Console.WriteLine($"New user ID: {newUserId}");
}
            

Async Operations

Dapper fully supports asynchronous operations, crucial for modern web applications:

using System.Data;
using System.Data.SqlClient;
using Dapper;
using System.Threading.Tasks;

public async Task<User> GetUserByIdAsync(string connectionString, int userId)
{
    using (IDbConnection db = new SqlConnection(connectionString))
    {
        return await db.QueryFirstOrDefaultAsync<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = userId });
    }
}
            

Methods like QueryAsync, QueryFirstOrDefaultAsync, and ExecuteAsync are available.

Advanced Topics

Dapper offers more advanced features like:

• Multi-mapping: Mapping a single query result to multiple C# objects (e.g., One-to-Many relationships).
• Tuple Mapping: Mapping result sets to tuples.
• Executing Multiple Queries: Running several queries in one go.
• Buffered vs. Unbuffered Results: Controlling how results are consumed.

These topics are covered in more detail in the official Dapper documentation.