Skip to content
Skills Library
Programming · Building & coding

dapper

/dapper

USE FOR: High-performance SQL queries mapped to POCOs, read-heavy and latency-critical data access, stored procedure invocation, multi-mapping joins, bulk operations, and scenarios requiring full SQL control. DO NOT USE FOR: Schema migrations (use EF Core or FluentMigrator), complex object-graph change tracking, or applications where a full ORM with lazy loading is needed.

Licence
MIT
Last repository change
09.06.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
16
Scripts
0
HTML comments
0
URLs in SKILL.md
3
Deprecation

No signals detected. Repository changed on 09.06.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

USE FOR: High-performance SQL queries mapped to POCOs, read-heavy and latency-critical data access, stored procedure invocation, multi-mapping joins, bulk operations, and scenarios requiring full SQL control. DO NOT USE FOR: Schema migrations (use EF Core or FluentMigrator), complex object-graph change tracking, or applications where a full ORM with lazy loading is needed.

Original author description

USE FOR: High-performance SQL queries mapped to POCOs, read-heavy and latency-critical data access, stored procedure invocation, multi-mapping joins, bulk operations, and scenarios requiring full SQL control. DO NOT USE FOR: Schema migrations (use EF Core or FluentMigrator), complex object-graph change tracking, or applications where a full ORM with lazy loading is needed.

How it works

  1. Overview
  2. Basic Query and Command
  3. Multi-Mapping (Joins)
  4. Stored Procedures
  5. Multiple Result Sets
  6. Transaction Support
  7. DI Registration Pattern
  8. Dapper vs Entity Framework Core
  9. Best Practices
Original SKILL.md headings · 325 lines, 1,282 words
SKILL.md preview
---
name: dapper
description: >
  USE FOR: High-performance SQL queries mapped to POCOs, read-heavy and latency-critical data
  access, stored procedure invocation, multi-mapping joins, bulk operations, and scenarios
  requiring full SQL control. DO NOT USE FOR: Schema migrations (use EF Core or FluentMigrator),
  complex object-graph change tracking, or applications where a full ORM with lazy loading is
  needed.
license: MIT
metadata:
  displayName: "Dapper"
  author: "Tyler-R-Kendrick"
  version: "1.0.0"
compatibility:
  - claude
  - copilot
  - cursor
references:
  - title: "Dapper GitHub Repository"
    url: "https://github.com/DapperLib/Dapper"
  - title: "Dapper NuGet Package"
    url: "https://www.nuget.org/packages/Dapper"
  - title: "Dapper Documentation"
    url: "https://www.learndapper.com/"
---

# Dapper

## Overview

Dapper is a lightweight, high-performance micro-ORM for .NET that extends `IDbConnection` with methods for executing SQL and mapping results to strongly-typed objects. It does not generate SQL, manage change tracking, or handle migrations -- it maps query results to POCOs with minimal overhead. Dapper is used when you need full control over SQL, maximum query performance, or when working with legacy databases where an ORM's conventions do not fit.

Dapper supports parameterized queries, multi-mapping (joins across tables), multiple result sets, stored procedures, and dynamic parameters. It is the data access layer used by Stack Overflow and is one of the most widely adopted .NET data access libraries.

Install via NuGet: `dotnet add package Dapper`

## Basic Query and Command

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

public sealed class ProductRepository
{
    private readonly string _connectionString;

    public ProductRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task<IReadOnlyList<Product>> GetAllAsync(CancellationToken ct)
    {
        using IDbConnection db = new SqlConnection(_connectionString);
        var products = await db.QueryAsync<Product>(
            "SELECT Id, Name, Price, CategoryId FROM Products ORDER BY Name");
        return products.AsList();
    }

    public async Task<Product?> GetByIdAsync(int id, CancellationToken ct)
    {
        using IDbConnection db = new SqlConnection(_connectionString);
        return await db.QuerySingleOrDefaultAsync<Product>(
            "SELECT Id, Name, Price, CategoryId FROM Products WHERE Id = @Id",
            new { Id = id });
    }

    public async Task<int> CreateAsync(Product product, CancellationToken ct)
    {
        using IDbConnection db = new SqlConnection(_connectionString);
        return await db.ExecuteScalarAsync<int>(
            @"INSERT INTO Products (Name, Price, CategoryId)
              VALUES (@Name, @Price, @CategoryId);
              SELECT SCOPE_IDENTITY();",
            new { product.Name, product.Price, product.CategoryId });
    }

    public async Task<bool> UpdateAsync(Product product, CancellationToken ct)
    {
        using IDbConnection db = new SqlConnection(_connectionString);
        int rowsAffected = await db.ExecuteAsync(
            @"UPDATE Products SET Name = @Name, Price = @Price, CategoryId = @CategoryId
              WHERE Id = @Id",
            new { product.Id, product.Name, product.Price, product.CategoryId });
        return rowsAffected > 0;
    }
}

public sealed class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
}
```

## Multi-Mapping (Joins)

Map a single query result row to multiple objects, useful for JOIN queries.

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

public sealed class OrderRepository
{
    private readonly string _connectionString;

    public OrderRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task<IReadOnlyList<Order>> GetOrdersWithCustomerAsync(CancellationToken ct)
    {
        const string sql = @"
            SELECT o.Id, o.OrderDate, o.Total,
                   c.Id, c.Name, c.Email
            FROM Orders o
            INNER JOIN Customers c ON o.CustomerId = c.Id
            ORDER BY o.OrderDate DESC";

        using IDbConnection db = new SqlConnection(_connectionString);
        var orders = await db.QueryAsync<Order, Customer, Order>(
            sql,
            (order, customer) =>
            {
                order.Customer = customer;
                return order;
            },
            splitOn: "Id");

        return orders.AsList();
    }
}

public sealed class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Total { get; set; }
    public Customer Customer { get; set; } = null!;
}

public sealed class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
}
```

## Stored Procedures

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

public sealed class ReportRepository
{
    private readonly string _connectionString;

    public ReportRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task<IReadOnlyList<SalesReport>> GetSalesReportAsync(
        DateTime startDate, DateTime endDate, CancellationToken ct)
    {
        using IDbConnection db = new SqlConnection(_connectionString);
        var results = await db.QueryAsync<SalesReport>(
            "usp_GetSalesReport",
            new { StartDate = startDate, EndDate = endDate },
            commandType: CommandType.StoredProcedure);
        return results.AsList();
    }

    public async Task<int> ArchiveOldOrdersAsync(int daysOld, CancellationToken ct)
    {
        using IDbConnection db = new SqlConnection(_connectionString);
        var parameters = new DynamicParameters();
        parameters.Add("@DaysOld", daysOld);
        parameters.Add("@RowsArchived", dbType: DbType.Int32, direction: ParameterDirection.Output);

        await db.ExecuteAsync(
            "usp_ArchiveOrders",
            parameters,
            commandType: CommandType.StoredProcedure);

        return parameters.Get<int>("@RowsArchived");
    }
}

public sealed class SalesReport
{
    public string CategoryName { get; set; } = string.Empty;
    public int TotalOrders { get; set; }
    public decimal TotalRevenue { get; set; }
}
```

## Multiple Result Sets

Read multiple result sets from a single query to reduce round trips.

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

public sealed class DashboardRepository
{
    private readonly string _connectionString;

    public DashboardRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task<DashboardData> GetDashboardDataAsync(CancellationToken ct)
    {
        const string sql = @"
            SELECT COUNT(*) AS TotalOrders, SUM(Total) AS Revenue FROM Orders WHERE OrderDate >= @Today;
            SELECT TOP 5 Id, Name, Email FROM Customers ORDER BY CreatedAt DESC;
            SELECT TOP 10 Id, Name, Price FROM Products ORDER BY SoldCount DESC;";

        using IDbConnection db = new SqlConnection(_connectionString);
        using var multi = await db.QueryMultipleAsync(sql, new { Today = DateTime.UtcNow.Date });

        var summary = await multi.ReadSingleAsync<OrderSummary>();
        var recentCustomers = (await multi.ReadAsync<Customer>()).AsList();
        var topProducts = (await multi.ReadAsync<Product>()).AsList();

        return new DashboardData
        {
            Summary = summary,
            RecentCustomers = recentCustomers,
            TopProducts = topProducts
        };
    }
}

public sealed class DashboardData
{
    public OrderSummary Summary { get; set; } = null!;
    public List<Customer> RecentCustomers { get; set; } = new();
    public List<Product> TopProducts { get; set; } = new();
}

public sealed class OrderSummary
{
    public int TotalOrders { get; set; }
    public decimal Revenue { get; set; }
}
```

## Transaction Support

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

public sealed class TransferService
{
    private readonly string _connectionString;

    public TransferService(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task TransferFundsAsync(int fromAccountId, int toAccountId, decimal amount)
    {
        using IDbConnection db = new SqlConnection(_connectionString);
        db.Open();
        using IDbTransaction transaction = db.BeginTransaction();

        try
        {
            await db.ExecuteAsync(
                "UPDATE Accounts SET Balance = Balance - @Amount WHERE Id = @Id",
                new { Amount = amount, Id = fromAccountId },
                transaction);

            await db.ExecuteAsync(
                "UPDATE Accounts SET Balance = Balance + @Amount WHERE Id = @Id",
                new { Amount = amount, Id = toAccountId },
                transaction);

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

## DI Registration Pattern

```csharp
using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.DependencyInjection;

public static class ServiceCollectionExtensions
{
    public static IServiceCollection AddDataAccess(
        this IServiceCollection services, string connectionString)
    {
        services.AddScoped<IDbConnection>(_ => new SqlConnection(connectionString));
        services.AddScoped<ProductRepository>();
        services.AddScoped<OrderRepository>();
        return services;
    }
}
```

## Dapper vs Entity Framework Core

| Aspect | Dapper | Entity Framework Core |
|---|---|---|
| SQL control | Full (you write SQL) | Generated (LINQ to SQL) |
| Performance | Fastest (near raw ADO.NET) | Good (with optimization) |
| Change tracking | None | Built-in |
| Migrations | None | Built-in |
| Learning curve | Low (just SQL + mapping) | Medium (LINQ, conventions) |
| Best for | Read-heavy, reports, legacy DBs | CRUD, complex domains |

## Best Practices

1. Always use parameterized queries with anonymous objects (e.g., `new { Id = id }`) instead of string interpolation to prevent SQL injection and enable query plan caching.
2. Create and dispose `IDbConnection` per operation or per request; do not share a single connection across concurrent operations or store it in a singleton.
3. Use `QuerySingleOrDefaultAsync` when you expect zero or one row and `QueryFirstOrDefaultAsync` when you want the first of potentially many rows, to clearly express intent.
4. Specify `splitOn` explicitly in multi-mapping queries to avoid ambiguity when join columns share the same name across tables.
5. Use `DynamicParameters` with `ParameterDirection.Output` for stored procedures that return values through output parameters rather than result sets.
6. Wrap multiple related write operations in an explicit `IDbTransaction` and call `Commit` only after all operations succeed to maintain data consistency.
7. Use `QueryMultipleAsync` to batch multiple SELECT statements into a single round trip when a single method needs data from several tables.
8. Keep Dapper queries in dedicated repository or query classes with single-responsibility boundaries rather than scattering SQL throughout controllers or services.
9. Use `buffered: false` in `QueryAsync` for very large result sets that should be streamed row-by-row to avoid loading the entire result into memory at once.
10. Add a thin abstraction (e.g., `IDbConnectionFactory`) over connection creation to simplify testing with in-memory databases like SQLite.
Files in this package (16)
  • AGENTS.md 11 KB
  • README.md 1 KB
  • SKILL.md 12 KB
  • metadata.json 1 KB
  • rules/_sections.md 2 KB
  • rules/_template.md 1 KB
  • rules/dapper-add-a-thin-abstraction-e.md 1 KB
  • rules/dapper-always-use-parameterized-queries-with-anonymous-objects-e.md 1 KB
  • rules/dapper-create-and-dispose-idbconnection-per-operation-or-per.md 1 KB
  • rules/dapper-keep-dapper-queries-in-dedicated-repository-or-query.md 1 KB
  • rules/dapper-specify-spliton-explicitly-in-multi-mapping-queries-to.md 1 KB
  • rules/dapper-use-buffered.md 1 KB
  • rules/dapper-use-dynamicparameters-with-parameterdirection.md 1 KB
  • rules/dapper-use-querymultipleasync-to-batch-multiple-select.md 1 KB
  • rules/dapper-use-querysingleordefaultasync-when-you-expect-zero-or-one.md 1 KB
  • rules/dapper-wrap-multiple-related-write-operations-in-an-explicit.md 1 KB

Versions

The database has no other skills named dapper.

Database snapshot: 13.11.2026 · package version 9b1c6f24fed8 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100query-with-dapper
    Run raw SQL via Dapper inside an ABP repository for hot-path read/aggregate queries while sharing ABP's unit of work, connection, and transaction. USE FOR: complex or slow queries where EF Core's generated SQL is too slow, hand-written SQL you want full control over, deriving…
  • 100entity-framework-core
    USE FOR: CRUD data access with LINQ, database migrations, change tracking, complex domain models with relationships, database-first and code-first workflows, and multi-provider support (SQL Server, PostgreSQL, SQLite, Cosmos DB). DO NOT USE FOR: Bulk operations on millions of…
  • 100optimizing-ef-core-queries
    Review and optimize application data access that uses Entity Framework Core (EF Core). Use for EF Core performance and correctness reviews of services, repositories, imports, and background jobs: slow or memory-heavy queries, excessive round-trips, hidden SaveChanges, premature…
  • 100orm-patterns
    Writes efficient, correct data access code with ORMs (Prisma, Hibernate/JPA, EF Core, SQLAlchemy, Django ORM, ActiveRecord) - finding and fixing N+1 queries, eager vs lazy loading, transaction boundaries, bulk operations, safe raw SQL and optimistic locking. Use when writing or…
  • 100sql-integrity-check
    Review SQL queries and data-model changes for correctness, access scope, safe parameterization, and performance. Use when writing joins, filters, migrations, reports, or analytics queries.
  • 100generating-stored-procedures
    Use when you need to generate, validate, or deploy stored procedures for PostgreSQL, MySQL, or SQL Server. Creates database functions, triggers, and procedures with proper error handling and transaction management. Trigger with phrases like "generate stored procedure", "create…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.