MSDN Documentation

Dapper Usage Guide

This tutorial provides a comprehensive guide on how to effectively use Dapper, a lightweight ORM for .NET, to interact with your databases.

Prerequisites

Before you begin, ensure you have the following installed:

• .NET SDK (latest version recommended)
• A database server (e.g., SQL Server, PostgreSQL, MySQL)
• A database to connect to

Installation

You can install Dapper via NuGet Package Manager. Open your project's Package Manager Console and run:

Alternatively, you can use the .NET CLI:

Establishing a Connection

To interact with your database, you need a connection. Dapper works with any ADO.NET connection object. Here's how to establish a basic SQL Server connection:

using System.Data;
using Microsoft.Data.SqlClient; // Or appropriate provider for your DB

// ...

public IDbConnection GetOpenConnection()
{
    var connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_user_id;Password=your_password;";
    var connection = new SqlConnection(connectionString);
    connection.Open();
    return connection;
}
            

Executing Queries

Querying for a List of Objects

Use the Query<T> method to fetch multiple records and map them to a list of objects.

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

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

public class ProductRepository
{
    public IEnumerable<Product> GetAllProducts()
    {
        using (IDbConnection db = new SqlConnection("YourConnectionString"))
        {
            return db.Query<Product>("SELECT ProductId, Name, Price FROM Products");
        }
    }
}
            

Querying for a Single Object

Use QueryFirstOrDefault<T> or QuerySingleOrDefault<T> to fetch a single record.

public Product GetProductById(int id)
{
    using (IDbConnection db = new SqlConnection("YourConnectionString"))
    {
        return db.QueryFirstOrDefault<Product>("SELECT ProductId, Name, Price FROM Products WHERE ProductId = @Id", new { Id = id });
    }
}
            

Executing Commands (INSERT, UPDATE, DELETE)

Use the Execute method for operations that don't return data.

public int AddProduct(Product product)
{
    using (IDbConnection db = new SqlConnection("YourConnectionString"))
    {
        string sql = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price); SELECT CAST(SCOPE_IDENTITY() as int)";
        return db.Execute(sql, product);
    }
}

public void UpdateProductPrice(int id, decimal newPrice)
{
    using (IDbConnection db = new SqlConnection("YourConnectionString"))
    {
        string sql = "UPDATE Products SET Price = @Price WHERE ProductId = @Id";
        db.Execute(sql, new { Price = newPrice, Id = id });
    }
}

public void DeleteProduct(int id)
{
    using (IDbConnection db = new SqlConnection("YourConnectionString"))
    {
        string sql = "DELETE FROM Products WHERE ProductId = @Id";
        db.Execute(sql, new { Id = id });
    }
}
            

Using Parameters

Dapper automatically handles parameter mapping by matching property names in anonymous objects or specific parameter types to column names or parameter placeholders in your SQL queries. This helps prevent SQL injection vulnerabilities.

Executing Stored Procedures

You can execute stored procedures using Query or Execute with the command type set to StoredProcedure.

public IEnumerable<Product> GetProductsByCategory(string categoryName)
{
    using (IDbConnection db = new SqlConnection("YourConnectionString"))
    {
        return db.Query<Product>(
            "GetProductsByCategorySP", // Name of the stored procedure
            new { CategoryName = categoryName },
            commandType: CommandType.StoredProcedure);
    }
}
            

Transactions

Dapper integrates seamlessly with ADO.NET transactions.

public void TransferFunds(int fromAccountId, int toAccountId, decimal amount)
{
    using (IDbConnection db = new SqlConnection("YourConnectionString"))
    {
        db.Open();
        using (var transaction = db.BeginTransaction())
        {
            try
            {
                db.Execute("UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountId = @FromId",
                           new { Amount = amount, FromId = fromAccountId }, transaction: transaction);

                db.Execute("UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountId = @ToId",
                           new { Amount = amount, ToId = toAccountId }, transaction: transaction);

                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}
            

Asynchronous Operations

Dapper provides asynchronous methods for non-blocking database operations.

using Dapper;
using System.Threading.Tasks;

public async Task<IEnumerable<Product>> GetAllProductsAsync()
{
    using (IDbConnection db = new SqlConnection("YourConnectionString"))
    {
        return await db.QueryAsync<Product>("SELECT ProductId, Name, Price FROM Products");
    }
}
            

Advanced Scenarios

Mapping Complex Types

Dapper can map results to complex types with relationships using split on.

public class Order
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public List<OrderItem> Items { get; set; } = new List<OrderItem>();
}

public class OrderItem
{
    public int OrderItemId { get; set; }
    public string ProductName { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

public Order GetOrderWithItems(int orderId)
{
    using (IDbConnection db = new SqlConnection("YourConnectionString"))
    {
        var sql = @"
            SELECT o.OrderId, o.OrderDate, oi.OrderItemId, oi.ProductName, oi.Quantity, oi.UnitPrice
            FROM Orders o
            LEFT JOIN OrderItems oi ON o.OrderId = oi.OrderId
            WHERE o.OrderId = @Id;
        ";

        // Use SplitOn to tell Dapper how to separate different types of objects
        var result = db.Query<Order, OrderItem, Order>(
            sql,
            (order, orderItem) =>
            {
                if (orderItem != null)
                {
                    order.Items.Add(orderItem);
                }
                return order;
            },
            splitOn: "OrderItemId", // Column name where the split occurs
            param: new { Id = orderId }
        );

        return result.FirstOrDefault();
    }
}
            

Performance Considerations

• Keep your SQL queries as optimized as possible.
• Use Dapper's built-in caching mechanisms where appropriate.
• For very complex object graphs, consider manual mapping or a more powerful ORM if Dapper becomes unwieldy.