Advanced Dapper Features in C#

In this article, we will explore advanced features of Dapper, such as stored procedures, query pagination, caching, and other capabilities that can help simplify data access and improve application performance.

What is Dapper and Why Use It?

Object-Relational Mapping (ORM) tools bridge the gap between the object-oriented world of programming languages and the relational world of databases. Dapper was created by the Stack Overflow team to provide a lightweight and efficient ORM for .NET. It supports a wide range of databases, including SQL Server, MySQL, PostgreSQL, and SQLite. Unlike more feature-heavy ORMs like Entity Framework, Dapper focuses on performance, making it ideal for applications where speed is critical.

Advanced Features in Dapper

1. Multi-Mapping

Dapper's multi-mapping feature allows you to map query results to multiple objects, which is useful when you want to retrieve related data from multiple tables in one query.

For example, if you have Category and Product tables, you can retrieve the products for each category with the following classes:

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Product> Products { get; set; }
}

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

To fetch categories and their related products:

string query = "SELECT * FROM Categories C INNER JOIN Products P ON C.Id = P.CategoryId";
var categories = connection.Query<Category, Product, Category>(
    query,
    (category, product) =>
    {
        category.Products = category.Products ?? new List<Product>();
        category.Products.Add(product);
        return category;
    },
    splitOn: "Id"
).Distinct().ToList();

2. Broad Database Support

Dapper can connect to various types of databases, making it versatile. You can use it with SQL Server, MySQL, PostgreSQL, and more.

var connection = new SqlConnection(connectionString); // SQL Server
var connection = new MySqlConnection(connectionString); // MySQL
var connection = new NpgsqlConnection(connectionString); // PostgreSQL

3. Bulk Insert

When you need to insert large amounts of data efficiently, Dapper supports bulk inserts. For example, you can use the Execute method to insert multiple products into the database at once:

using (var connection = new SqlConnection(connectionString))
{
    // Open the connection
    connection.Open();

    // Create a transaction
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            // List of products to insert
            var products = new List<Product>
            {
                new Product { CategoryId = 1, Name = "Laptop", Price = 999.99m },
                new Product { CategoryId = 1, Name = "Smartphone", Price = 499.99m },
                // More products...
            };

            // Execute the insert statement within the transaction
            connection.Execute(
                "INSERT INTO Products (CategoryId, Name, Price) VALUES (@CategoryId, @Name, @Price)",
                products,
                transaction: transaction
            );

            // Commit the transaction if everything is successful
            transaction.Commit();
        }
        catch (Exception ex)
        {
            // Rollback the transaction if there is an error
            transaction.Rollback();
            // Optionally log the exception
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

4. Stored Procedures

Dapper makes it easy to call stored procedures and handle output parameters. For instance, if you have a stored procedure GetProductsByCategory, you can call it like this:

var parameters = new DynamicParameters();
parameters.Add("@CategoryId", 1, DbType.Int32);
var products = connection.Query<Product>("GetProductsByCategory", parameters, commandType: CommandType.StoredProcedure).ToList();

This code executes a stored procedure to fetch all products for a specific category, and it uses dynamic parameters to pass the CategoryId.

5. Query Pagination

When working with large datasets, it's important to paginate the results. Dapper supports pagination by using SQL clauses like OFFSET and FETCH NEXT. Here's an example of paginating products:

var pageNumber = 1;
var pageSize = 10;
var offset = (pageNumber - 1) * pageSize;
var query = @"
    SELECT * 
    FROM Products 
    WHERE CategoryId = @CategoryId
    ORDER BY Name
    OFFSET @Offset ROWS 
    FETCH NEXT @PageSize ROWS ONLY";
var parameters = new { CategoryId = 1, Offset = offset, PageSize = pageSize };
var products = connection.Query<Product>(query, parameters).ToList();

This code retrieves a specific page of products from the Products table based on the category.

6. Query Caching

Dapper supports caching query results to improve performance, especially when the same query is executed multiple times.

var sql = "SELECT * FROM Products WHERE CategoryId = @CategoryId";
var products = connection.Query<Product>(sql, new { CategoryId = 1 }).ToList();
// On subsequent calls, cached results will be used if available
var cachedProducts = connection.Query<Product>(sql, new { CategoryId = 1 }).ToList();

7. Dynamic Parameters

Dapper allows you to pass parameters dynamically using anonymous types or dictionaries. This feature is useful when the parameters' values or names are not known at compile time.

var parameters = new { CategoryId = 1, PriceThreshold = 100.00 };
var query = "SELECT * FROM Products WHERE CategoryId = @CategoryId AND Price > @PriceThreshold";
var products = connection.Query<Product>(query, parameters).ToList();

In this example, products from a specific category that have a price greater than the given threshold are fetched using dynamic parameters.