Dapper: Complete Data Access Learning Guide for .NET Developers

Learn Dapper from fundamentals to advanced data access: SQL execution, parameterized queries, asynchronous programming, transactions, stored procedures, mapping, performance, testing, and production-ready architecture with C# 14 and .NET 10.

Introduction

Every application that works with relational data needs a reliable way to communicate with a database. A .NET application may need to read employee records, insert orders, update customer details, execute stored procedures, or generate reports. The data-access approach you choose affects how much control you have over SQL, how your objects are populated, and how your application handles performance and maintainability.

Dapper is a lightweight object-mapping library for .NET. It extends ADO.NET database connections with methods that execute SQL and map returned rows to .NET objects. It is commonly described as a micro-ORM because it provides useful object-mapping capabilities without trying to manage the entire database model or application persistence lifecycle.

This guide focuses primarily on C# and ASP.NET Core development with SQL Server, while explaining concepts that also apply to developers using other relational databases and .NET application types.

We will use a simple Employee Management application as a running example. It will contain employees, departments, and employee-related operations. The examples target .NET 10, C# 14, and Dapper 2.1.89 (the latest stable Dapper release verified for this guide on November 15, 2026).

Learning approach: Start with database fundamentals and simple queries, then move to parameterized commands, asynchronous access, relationships, transactions, stored procedures, reusable data-access design, testing, and production practices. The goal is to understand not only how to write Dapper code, but also why and when to use each feature.

Version note: This guide intentionally uses .NET 10 and C# 14 as requested. .NET 10 selects C# 14 by default. Dapper is a separate NuGet library; it does not require a special C# language version. Check the official package page before upgrading because stable package versions can change.

What Is Data Access?

Data access is the part of an application responsible for communicating with a data store. In a typical business application, the database stores information while the application retrieves, creates, changes, and deletes that information according to business rules.

For example, when a user opens an employee list in an Angular application, the request may travel through an ASP.NET Core API, reach a data-access component, execute a SQL query, and return employee records as JSON.

A simplified request and response path in a full-stack application.

Common Data Access Responsibilities

Opening and managing database connections.
Executing SQL statements and stored procedures.
Passing values safely to SQL commands.
Mapping database rows to application objects.
Managing transactions for operations that must succeed or fail together.
Handling timeouts, cancellation, errors, and resource cleanup.

Remember: Data access is not the same as business logic. A repository or query component should retrieve and persist data; business rules such as whether an employee can be assigned to a department belong in the appropriate application or domain layer.

What Is Dapper?

Dapper is an open-source micro-ORM for .NET, maintained by the DapperLib project. It adds extension methods to ADO.NET connections so developers can execute SQL and map result rows to objects with relatively little code.

Dapper does not generate most of your SQL from entity expressions. You normally write and control the SQL yourself, then pass it to Dapper along with parameters and the expected result type.

Why Developers Use Dapper

Lightweight: It focuses on query execution and object mapping rather than a large change-tracking model.
SQL control: You write the SQL statements and can tune them directly.
Simple mapping: Rows can be mapped to classes, records, or dynamic objects.
Async support: Asynchronous methods are available for common database operations.
ADO.NET foundation: It works with database connections and providers that support the relevant ADO.NET interfaces.
Flexible: It supports parameterized SQL, stored procedures, transactions, multi-mapping, and multiple result sets.

What Dapper Does Not Automatically Do

It does not design your database schema.
It does not automatically create and apply schema migrations like EF Core migrations.
It does not provide EF Core-style change tracking for entities.
It does not remove the need to understand SQL, indexes, query plans, or database transactions.
It does not automatically enforce your application's business rules.

Simple definition: Dapper helps .NET developers execute SQL and map database results to .NET objects while keeping direct control over the SQL.

ORM vs Micro-ORM vs ADO.NET

Before using Dapper, understand where it fits in the .NET data-access ecosystem.

ADO.NET

ADO.NET is the foundational .NET data-access technology. It provides connection, command, parameter, and data-reader abstractions. You have detailed control, but you write more repetitive code to execute commands and map rows.

ORM

An Object-Relational Mapper maps relational database structures to application objects. Entity Framework Core is a full-featured ORM. It provides features such as LINQ queries, change tracking, relationship handling, and migrations.

Micro-ORM

A micro-ORM focuses on a smaller set of data-access responsibilities. Dapper primarily executes SQL and maps results, leaving query design and much of the persistence workflow to the developer.

Area	ADO.NET	Dapper	EF Core
SQL control	Full	Full	LINQ or raw SQL
Object mapping	Manual	Built-in row mapping	Built-in entity mapping
Change tracking	No built-in entity tracking	No EF-style tracking	Yes
LINQ-to-database	No built-in query provider	No built-in query provider	Yes
Schema migrations	External/manual approach	External/manual approach	EF Core migrations
Best fit	Low-level control	SQL-centric data access	Object-centric data access

Important: Dapper is not automatically faster for every workload. Database design, SQL quality, network latency, indexing, result size, and application architecture often matter more than the choice of mapper alone. Benchmark your actual workload.

How Dapper Works

Dapper works on top of an ADO.NET connection. Your application supplies a connection, SQL command, parameters, and (for typed queries) a result type. Dapper executes the command through the underlying provider and maps returned columns to the requested .NET type.

Dapper coordinates command execution and result mapping; the ADO.NET provider communicates with the database.

The Main Steps

Create or obtain an ADO.NET database connection.
Write SQL or identify a stored procedure.
Pass values as parameters rather than concatenating user input.
Call a Dapper method such as QueryAsync<T> or ExecuteAsync.
Dapper executes the command and maps returned rows when applicable.
Consume the result and dispose the connection when its work is complete.

Dapper does not replace the SQL Server driver. For SQL Server, a common provider is Microsoft.Data.SqlClient. Dapper works with the connection object supplied by that provider.

Dapper and .NET 10 Project Setup

Prerequisites

.NET 10 SDK.
Visual Studio 2026 or a compatible editor.
SQL Server, SQL Server Developer Edition, LocalDB, or an accessible SQL Server instance.
Basic C# and SQL knowledge.

Create a Web API Project

dotnet new webapi -n EmployeeApi
cd EmployeeApi

Install Dapper and SQL Server Provider

dotnet add package Dapper --version 2.1.89
dotnet add package Microsoft.Data.SqlClient

The Dapper package provides the Dapper extension methods. The SQL client package provides the SQL Server ADO.NET connection implementation.

Confirm the Target Framework

Your project file should target .NET 10. C# 14 is the default language version for .NET 10 projects, so you generally do not need to set it explicitly.

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.1.89" />
    <PackageReference Include="Microsoft.Data.SqlClient" Version="YOUR_APPROVED_VERSION" />
  </ItemGroup>
</Project>

Package management: The SQL client version above is deliberately represented as a placeholder. Select a currently supported stable version through NuGet and keep dependency updates under your normal review process. The Dapper version is pinned to the stable version verified for this guide.

Recommended Folder Structure

EmployeeApi/
|
+-- Controllers/
|   +-- EmployeesController.cs
|
+-- Models/
|   +-- Employee.cs
|   +-- Department.cs
|
+-- Data/
|   +-- IDbConnectionFactory.cs
|   +-- SqlConnectionFactory.cs
|
+-- Repositories/
|   +-- IEmployeeRepository.cs
|   +-- EmployeeRepository.cs
|
+-- Services/
|   +-- IEmployeeService.cs
|   +-- EmployeeService.cs
|
+-- Program.cs
+-- appsettings.json

This is an example structure, not a mandatory Dapper convention. Small applications can use fewer folders; larger applications may separate contracts, infrastructure, domain logic, and API projects.

Database and Model Preparation

Before writing queries, create a small database schema. The examples in this guide use SQL Server syntax.

Example Tables

CREATE TABLE Departments
(
    DepartmentId INT IDENTITY(1,1) PRIMARY KEY,
    DepartmentName NVARCHAR(100) NOT NULL
);

CREATE TABLE Employees
(
    EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(60) NOT NULL,
    LastName NVARCHAR(60) NOT NULL,
    Email NVARCHAR(150) NOT NULL UNIQUE,
    Salary DECIMAL(12,2) NOT NULL,
    DepartmentId INT NULL,
    CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Employees_Departments
        FOREIGN KEY (DepartmentId)
        REFERENCES Departments(DepartmentId)
);

Insert Sample Data

INSERT INTO Departments (DepartmentName)
VALUES ('Engineering'), ('Human Resources');

INSERT INTO Employees
    (FirstName, LastName, Email, Salary, DepartmentId)
VALUES
    ('Ava', 'Sharma', 'ava@example.com', 75000, 1),
    ('Noah', 'Patel', 'noah@example.com', 68000, 1),
    ('Mia', 'Khan', 'mia@example.com', 62000, 2);

Create a C# Model

namespace EmployeeApi.Models;

public sealed class Employee
{
    public int EmployeeId { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public decimal Salary { get; set; }
    public int? DepartmentId { get; set; }
    public DateTime CreatedAt { get; set; }
}

Dapper maps result columns to matching properties by name (with configurable mapping behavior and type handling). If your SQL column names differ from your C# property names, use SQL aliases or an explicit mapping strategy.

Your First Dapper Query

The most common way to retrieve typed records is Query<T> or its asynchronous counterpart, QueryAsync<T>.

Create a Connection

using Microsoft.Data.SqlClient;

var connectionString =
    builder.Configuration.GetConnectionString("DefaultConnection");

await using var connection = new SqlConnection(connectionString);

For a local development environment, the connection string can be stored in user secrets or local configuration. Do not commit real production credentials to source control.

Read All Employees

using Dapper;

const string sql = """
    SELECT
        EmployeeId,
        FirstName,
        LastName,
        Email,
        Salary,
        DepartmentId,
        CreatedAt
    FROM Employees
    ORDER BY EmployeeId;
    """;

await using var connection = new SqlConnection(connectionString);

var employees = await connection.QueryAsync<Employee>(sql);

foreach (var employee in employees)
{
    Console.WriteLine(
        $"{employee.FirstName} {employee.LastName}");
}

Here, Dapper executes the SQL and maps each returned row to an Employee object. The generic type argument tells Dapper which .NET type should receive the column values.

Remember: A query returning several rows normally uses Query or QueryAsync. A command that changes data and does not return rows normally uses Execute or ExecuteAsync.

Query Methods and Return Types

Dapper provides different methods for different result shapes. Choosing the correct method makes your intent clearer and helps catch unexpected result counts.

Method	Purpose	Typical result
`Query<T>`	Execute a query and map multiple rows.	`IEnumerable<T>`
`QueryAsync<T>`	Asynchronous multi-row query.	`Task<IEnumerable<T>>`
`QueryFirst<T>`	Return the first row; fail if none.	`T`
`QueryFirstOrDefault<T>`	Return the first row or default.	`T?`
`QuerySingle<T>`	Require exactly one row.	`T`
`QuerySingleOrDefault<T>`	Allow zero or one row; fail if more.	`T?`
`Execute`	Execute a command without mapping rows.	Affected row count
`ExecuteScalar<T>`	Read the first column of the first row.	`T`
`QueryMultiple`	Read multiple result grids from one command.	`GridReader`

Which Single-Row Method Should You Use?

Use QueryFirst when you need the first row and expect at least one row.
Use QueryFirstOrDefault when no match is a normal outcome.
Use QuerySingle when the query is expected to return exactly one row.
Use QuerySingleOrDefault when zero or one row is valid, such as a lookup by a unique key.

Do not confuse them: First permits multiple matching rows and takes the first. Single treats more than one matching row as an error. Use a unique database constraint when the data itself must be unique.

Parameterized Queries and SQL Safety

Whenever a query uses values supplied by a user or another part of the application, pass those values as SQL parameters. Do not concatenate untrusted input into a SQL statement.

Unsafe String Concatenation

// Avoid this pattern
var sql =
    "SELECT * FROM Employees WHERE Email = '" + email + "'";

Concatenating untrusted values can allow SQL injection. It can also cause quoting and formatting problems.

Safe Parameterized Query

const string sql = """
    SELECT EmployeeId, FirstName, LastName, Email
    FROM Employees
    WHERE Email = @Email;
    """;

var employee = await connection.QuerySingleOrDefaultAsync<Employee>(
    sql,
    new { Email = email });

Dapper binds the value from the anonymous object to the named SQL parameter. SQL Server receives the parameter value separately from the command text.

Multiple Parameters

const string sql = """
    SELECT EmployeeId, FirstName, LastName, Salary
    FROM Employees
    WHERE DepartmentId = @DepartmentId
      AND Salary >= @MinimumSalary;
    """;

var parameters = new
{
    DepartmentId = 1,
    MinimumSalary = 65000m
};

var employees =
    await connection.QueryAsync<Employee>(sql, parameters);

Security rule: Parameters are for values, not SQL identifiers. If a user can choose a sort column or table name, validate that choice against a fixed allow-list and then compose only the approved identifier into the SQL. Never insert arbitrary user text into SQL structure.

Insert, Update and Delete with Execute

Use Execute or ExecuteAsync for commands that do not need Dapper to map a result set into objects. These methods return the number of rows affected for common commands.

Insert an Employee

const string sql = """
    INSERT INTO Employees
        (FirstName, LastName, Email, Salary, DepartmentId)
    VALUES
        (@FirstName, @LastName, @Email, @Salary, @DepartmentId);
    """;

var affectedRows = await connection.ExecuteAsync(sql, new
{
    FirstName = "Liam",
    LastName = "Mehta",
    Email = "liam@example.com",
    Salary = 71000m,
    DepartmentId = 1
});

Update an Employee

const string sql = """
    UPDATE Employees
    SET Salary = @Salary
    WHERE EmployeeId = @EmployeeId;
    """;

var affectedRows = await connection.ExecuteAsync(sql, new
{
    EmployeeId = 1,
    Salary = 79000m
});

if (affectedRows == 0)
{
    // No matching employee was updated.
}

Delete an Employee

const string sql = """
    DELETE FROM Employees
    WHERE EmployeeId = @EmployeeId;
    """;

var affectedRows = await connection.ExecuteAsync(
    sql,
    new { EmployeeId = 3 });

Insert and Return the Generated Identity

For SQL Server, use OUTPUT INSERTED to return the newly created row or its generated key.

const string sql = """
    INSERT INTO Employees
        (FirstName, LastName, Email, Salary, DepartmentId)
    OUTPUT INSERTED.EmployeeId
    VALUES
        (@FirstName, @LastName, @Email, @Salary, @DepartmentId);
    """;

var newEmployeeId = await connection.ExecuteScalarAsync<int>(
    sql,
    new
    {
        FirstName = "Zara",
        LastName = "Singh",
        Email = "zara@example.com",
        Salary = 72000m,
        DepartmentId = 1
    });

Remember: The affected-row count is useful for checking whether an update or delete matched a record. It is not a substitute for validating business rules or handling database constraints.

Asynchronous Database Operations

ASP.NET Core applications commonly use asynchronous database operations so request-handling threads are not blocked while waiting for database I/O. Dapper provides asynchronous methods such as QueryAsync, ExecuteAsync, and ExecuteScalarAsync.

Asynchronous Query

public async Task<IReadOnlyList<Employee>> GetEmployeesAsync(
    CancellationToken cancellationToken)
{
    const string sql = """
        SELECT EmployeeId, FirstName, LastName, Email,
               Salary, DepartmentId, CreatedAt
        FROM Employees;
        """;

    await using var connection = new SqlConnection(_connectionString);

    var command = new CommandDefinition(
        sql,
        commandTimeout: 30,
        cancellationToken: cancellationToken);

    var rows = await connection.QueryAsync<Employee>(command);

    return rows.AsList();
}

CommandDefinition lets you supply command options such as timeout, transaction, command type, flags, and cancellation token. Passing a cancellation token allows the operation to respond to cancellation when supported by the provider and execution path.

Async Does Not Mean Parallel by Default

Awaiting a query asynchronously does not automatically make multiple SQL commands run in parallel. It allows the current operation to yield while I/O is pending. If you need parallel database work, consider connection limits, transaction boundaries, database load, and whether the operations are truly independent.

Important: Do not share one open connection concurrently across unrelated operations unless the provider and usage pattern explicitly support it. A common approach is to create a connection per unit of work and dispose it promptly.

Single Row and Scalar Results

Not every SQL statement returns a list. Sometimes you need one record, one value, or a count.

Get One Employee

const string sql = """
    SELECT EmployeeId, FirstName, LastName, Email,
           Salary, DepartmentId, CreatedAt
    FROM Employees
    WHERE EmployeeId = @EmployeeId;
    """;

var employee = await connection.QuerySingleOrDefaultAsync<Employee>(
    sql,
    new { EmployeeId = 10 });

if (employee is null)
{
    // Handle not found.
}

Count Employees with ExecuteScalar

const string sql =
    "SELECT COUNT(1) FROM Employees WHERE DepartmentId = @DepartmentId;";

var employeeCount = await connection.ExecuteScalarAsync<int>(
    sql,
    new { DepartmentId = 1 });

Read a Single Aggregate Value

const string sql =
    "SELECT AVG(Salary) FROM Employees WHERE DepartmentId = @DepartmentId;";

var averageSalary = await connection.ExecuteScalarAsync<decimal?>(
    sql,
    new { DepartmentId = 1 });

Use a nullable result type when the SQL expression can return NULL, for example when an aggregate has no qualifying rows.

Stored Procedures and DynamicParameters

Dapper supports stored procedures as well as inline SQL. To call a stored procedure, pass its name and set commandType: CommandType.StoredProcedure.

Stored Procedure Example

CREATE PROCEDURE dbo.GetEmployeeById
    @EmployeeId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeId, FirstName, LastName, Email,
           Salary, DepartmentId, CreatedAt
    FROM Employees
    WHERE EmployeeId = @EmployeeId;
END;

using System.Data;

var employee = await connection.QuerySingleOrDefaultAsync<Employee>(
    "dbo.GetEmployeeById",
    new { EmployeeId = 1 },
    commandType: CommandType.StoredProcedure);

What Is DynamicParameters?

DynamicParameters is useful when you need to build a parameter collection explicitly, specify database types, define input/output directions, or retrieve output values.

Output Parameter Example

CREATE PROCEDURE dbo.GetEmployeeCount
    @DepartmentId INT,
    @TotalEmployees INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @TotalEmployees = COUNT(*)
    FROM Employees
    WHERE DepartmentId = @DepartmentId;
END;

var parameters = new DynamicParameters();

parameters.Add(
    "@DepartmentId",
    1,
    DbType.Int32,
    ParameterDirection.Input);

parameters.Add(
    "@TotalEmployees",
    dbType: DbType.Int32,
    direction: ParameterDirection.Output);

await connection.ExecuteAsync(
    "dbo.GetEmployeeCount",
    parameters,
    commandType: CommandType.StoredProcedure);

int totalEmployees = parameters.Get<int>("@TotalEmployees");

When to Use Stored Procedures

When database-side logic is an established team or organizational standard.
When several applications need to reuse the same database operation.
When permissions should be granted around stored procedure execution.
When a complex operation is best maintained and tuned within the database.

Inline SQL is also a valid approach. Choose based on maintainability, deployment practices, security, and the team's database standards rather than assuming one approach is always superior.

Transactions and Connection Management

A database transaction groups multiple operations into one logical unit. If all operations succeed, the transaction can commit. If an operation fails, the transaction can roll back so partial changes are not left behind.

Example: Transfer an Employee Between Departments

Imagine an operation that updates an employee's department and writes an audit record. Both changes should succeed together.

Commit only after all required database operations complete successfully.

Dapper Transaction Example

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync(cancellationToken);

await using var transaction =
    await connection.BeginTransactionAsync(cancellationToken);

try
{
    const string updateSql = """
        UPDATE Employees
        SET DepartmentId = @DepartmentId
        WHERE EmployeeId = @EmployeeId;
        """;

    await connection.ExecuteAsync(
        updateSql,
        new { EmployeeId = 1, DepartmentId = 2 },
        transaction);

    const string auditSql = """
        INSERT INTO EmployeeAudit
            (EmployeeId, Action, ChangedAt)
        VALUES
            (@EmployeeId, @Action, SYSUTCDATETIME());
        """;

    await connection.ExecuteAsync(
        auditSql,
        new { EmployeeId = 1, Action = "DepartmentChanged" },
        transaction);

    await transaction.CommitAsync(cancellationToken);
}
catch
{
    await transaction.RollbackAsync(CancellationToken.None);
    throw;
}

Every command that belongs to the transaction must use the same connection and transaction. A transaction does not automatically include commands executed through another connection.

Connection Lifetime

Create a connection for a short unit of work.
Open it when needed and dispose it when finished.
Use asynchronous open and command methods in asynchronous request paths.
Do not keep connections open while waiting for user input or unrelated network calls.
Connection pooling is generally managed by the ADO.NET provider; disposing a connection returns it to the pool when pooling is enabled.

Relationships and Multi-Mapping

Relational databases often store related information in separate tables. For example, an employee row may contain a DepartmentId that references the department table.

A SQL join can return columns from both tables. Dapper's multi-mapping feature can split a row into multiple objects and combine them through a mapping function.

Models

public sealed class Department
{
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; } = string.Empty;
}

public sealed class EmployeeWithDepartment
{
    public int EmployeeId { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public Department? Department { get; set; }
}

Join Query and Multi-Mapping

const string sql = """
    SELECT
        e.EmployeeId,
        e.FirstName,
        e.LastName,
        d.DepartmentId,
        d.DepartmentName
    FROM Employees e
    LEFT JOIN Departments d
        ON e.DepartmentId = d.DepartmentId;
    """;

var employees = await connection.QueryAsync<
    EmployeeWithDepartment, Department, EmployeeWithDepartment>(
    sql,
    (employee, department) =>
    {
        employee.Department =
            department.DepartmentId == 0 ? null : department;

        return employee;
    },
    splitOn: "DepartmentId");

The splitOn argument tells Dapper where the columns for the next mapped object begin. The default split column is commonly Id; when your query uses a different key column, specify it explicitly.

A join can be mapped into an object graph without requiring a separate query for every related row.

Watch the split column: Column order and aliases matter in multi-mapping. Design the SELECT list intentionally and test the mapping when changing the query. For one-to-many relationships, you may also need to group repeated parent rows in your mapping code.

Multiple Result Sets with QueryMultiple

Sometimes an API needs several related result collections, such as a dashboard summary, employee list, and department list. Instead of sending several independent commands, you can write multiple SELECT statements and read the result grids with QueryMultiple.

Example SQL

const string sql = """
    SELECT COUNT(1) AS TotalEmployees
    FROM Employees;

    SELECT EmployeeId, FirstName, LastName, Email
    FROM Employees
    ORDER BY EmployeeId;

    SELECT DepartmentId, DepartmentName
    FROM Departments
    ORDER BY DepartmentName;
    """;

Read Each Result Grid

using var results = await connection.QueryMultipleAsync(sql);

var totalEmployees = await results.ReadSingleAsync<int>();
var employees = (await results.ReadAsync<Employee>()).AsList();
var departments = (await results.ReadAsync<Department>()).AsList();

Read result grids in the same order in which the SQL statements return them. The reader should be disposed after use so database resources are released.

When useful: QueryMultiple can reduce round trips when several result sets are naturally needed together. It does not guarantee better performance in every case; measure the query and consider the size and consistency requirements of the results.

Dynamic Queries and IN Parameters

Applications often need optional filters, paging, sorting, or a list of selected identifiers. Build dynamic SQL carefully: keep SQL structure controlled by the application and pass data values as parameters.

IN Clause with a List

const string sql = """
    SELECT EmployeeId, FirstName, LastName, DepartmentId
    FROM Employees
    WHERE DepartmentId IN @DepartmentIds;
    """;

int[] departmentIds = [1, 2];

var employees = await connection.QueryAsync<Employee>(
    sql,
    new { DepartmentIds = departmentIds });

Dapper expands an enumerable parameter for an IN expression. Check how your application handles an empty list, because an empty filter may require a separate query path or a condition that intentionally returns no rows.

Optional Filters with DynamicParameters

var sql = new StringBuilder("""
    SELECT EmployeeId, FirstName, LastName, Email, DepartmentId
    FROM Employees
    WHERE 1 = 1
    """);

var parameters = new DynamicParameters();

if (departmentId is not null)
{
    sql.Append(" AND DepartmentId = @DepartmentId");
    parameters.Add("DepartmentId", departmentId);
}

if (!string.IsNullOrWhiteSpace(searchTerm))
{
    sql.Append("""
         AND (FirstName LIKE @Search
              OR LastName LIKE @Search)
        """);

    parameters.Add("Search", $"%{searchTerm}%");
}

var employees = await connection.QueryAsync<Employee>(
    sql.ToString(),
    parameters);

Sorting Safely

SQL parameters cannot stand in for a column name or the direction keyword. Use a fixed allow-list:

var sortColumn = sortBy switch
{
    "name" => "LastName",
    "salary" => "Salary",
    "created" => "CreatedAt",
    _ => "EmployeeId"
};

var sortDirection =
    descending ? "DESC" : "ASC";

var sql = $"""
    SELECT EmployeeId, FirstName, LastName, Salary
    FROM Employees
    ORDER BY {sortColumn} {sortDirection};
    """;

This interpolation is safe only because both inserted fragments come from fixed application-controlled values. Do not interpolate raw user input into the SQL command text.

Type Mapping and Type Handlers

Dapper maps database column values to .NET members. Mapping works most smoothly when column names and property names align and the underlying types are compatible.

Use SQL Aliases for Different Names

const string sql = """
    SELECT
        EmployeeId AS Id,
        FirstName AS GivenName,
        LastName AS FamilyName
    FROM Employees;
    """;

Use a DTO whose properties match those aliases. Aliases make the mapping intention visible in the SQL and avoid relying on global conventions.

Nullable Values

If a database column allows NULL, the corresponding C# property should usually be nullable. For example, an optional department relationship should use int?, not int.

Custom Type Handlers

Dapper supports type handlers for custom conversions between database values and .NET types. This is useful when a domain value object or custom type needs consistent conversion.

public readonly record struct EmployeeCode(string Value);

public sealed class EmployeeCodeHandler
    : SqlMapper.TypeHandler<EmployeeCode>
{
    public override void SetValue(
        IDbDataParameter parameter,
        EmployeeCode value)
    {
        parameter.Value = value.Value;
    }

    public override EmployeeCode Parse(object value)
    {
        return new EmployeeCode(
            Convert.ToString(value)!);
    }
}

// Register once during application startup:
SqlMapper.AddTypeHandler(new EmployeeCodeHandler());

Type-handler registration is process-wide for Dapper's mapping configuration. Register handlers during startup, not repeatedly for every request. Confirm provider-specific database types and conversion behavior with tests.

Buffered vs Unbuffered Queries

By default, Dapper buffers query results: it reads the result set and materializes the returned objects before the query call completes. This is convenient for many API operations and releases the underlying reader sooner.

For very large result sets, buffering everything can consume significant memory. Dapper also supports unbuffered query patterns so results can be consumed progressively, subject to the API and provider behavior.

Buffered	Unbuffered
Results are materialized before the query returns.	Rows can be consumed progressively.
Simple to use with typical API responses.	Can reduce memory pressure for large result sets.
Usually releases the reader sooner.	Reader and connection remain in use during enumeration.
Good default for ordinary result sizes.	Requires careful resource and error handling.

For synchronous queries, Dapper's buffered: false option is a common unbuffered pattern. For asynchronous streaming, consult the current Dapper API for the relevant unbuffered method and ensure the connection remains alive while enumerating.

Important: Do not choose unbuffered queries simply because they sound faster. Streaming keeps database resources occupied while the consumer processes rows. Use it when result size and memory constraints justify the added lifecycle complexity.

Repository and Service Layer Design

Dapper does not force a repository pattern or prescribe a project structure. You can call Dapper directly from an endpoint in a small application, but separating data access becomes useful as query count, business rules, and testing needs grow.

Connection Factory

A connection factory centralizes connection creation while allowing each repository operation to own and dispose its connection.

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

public interface IDbConnectionFactory
{
    IDbConnection CreateConnection();
}

public sealed class SqlConnectionFactory(
    IConfiguration configuration) : IDbConnectionFactory
{
    public IDbConnection CreateConnection()
    {
        var connectionString =
            configuration.GetConnectionString("DefaultConnection")
            ?? throw new InvalidOperationException(
                "Database connection string is missing.");

        return new SqlConnection(connectionString);
    }
}

Repository Contract

public interface IEmployeeRepository
{
    Task<IReadOnlyList<Employee>> GetAllAsync(
        CancellationToken cancellationToken);

    Task<Employee?> GetByIdAsync(
        int employeeId,
        CancellationToken cancellationToken);

    Task<int> CreateAsync(
        CreateEmployeeRequest request,
        CancellationToken cancellationToken);
}

Repository Implementation

public sealed class EmployeeRepository(
    IDbConnectionFactory connectionFactory)
    : IEmployeeRepository
{
    public async Task<IReadOnlyList<Employee>> GetAllAsync(
        CancellationToken cancellationToken)
    {
        const string sql = """
            SELECT EmployeeId, FirstName, LastName, Email,
                   Salary, DepartmentId, CreatedAt
            FROM Employees
            ORDER BY EmployeeId;
            """;

        using var connection = connectionFactory.CreateConnection();

        var command = new CommandDefinition(
            sql,
            cancellationToken: cancellationToken);

        var rows = await connection.QueryAsync<Employee>(command);
        return rows.AsList();
    }

    public async Task<Employee?> GetByIdAsync(
        int employeeId,
        CancellationToken cancellationToken)
    {
        const string sql = """
            SELECT EmployeeId, FirstName, LastName, Email,
                   Salary, DepartmentId, CreatedAt
            FROM Employees
            WHERE EmployeeId = @EmployeeId;
            """;

        using var connection = connectionFactory.CreateConnection();

        var command = new CommandDefinition(
            sql,
            new { EmployeeId = employeeId },
            cancellationToken: cancellationToken);

        return await connection.QuerySingleOrDefaultAsync<Employee>(command);
    }

    public async Task<int> CreateAsync(
        CreateEmployeeRequest request,
        CancellationToken cancellationToken)
    {
        const string sql = """
            INSERT INTO Employees
                (FirstName, LastName, Email, Salary, DepartmentId)
            OUTPUT INSERTED.EmployeeId
            VALUES
                (@FirstName, @LastName, @Email, @Salary, @DepartmentId);
            """;

        using var connection = connectionFactory.CreateConnection();

        var command = new CommandDefinition(
            sql,
            request,
            cancellationToken: cancellationToken);

        return await connection.ExecuteScalarAsync<int>(command);
    }
}

The sample uses IDbConnection because Dapper's common extension methods support it. The exact connection type and asynchronous disposal strategy depend on your provider and abstraction choices. In production code, prefer a consistent connection factory contract that matches the asynchronous capabilities of the provider you use.

Register Dependencies

builder.Services.AddSingleton<IDbConnectionFactory,
    SqlConnectionFactory>();

builder.Services.AddScoped<IEmployeeRepository,
    EmployeeRepository>();

The factory is stateless after configuration is captured, so singleton registration is suitable for this example. Repositories are scoped to the request in this API design. Choose lifetimes according to the actual dependencies and state held by each service.

Architecture guidance: Do not create a generic repository solely to hide every SQL statement behind a large abstraction. Dapper works well with focused query/repository classes that express the application's actual data operations.

Dapper in ASP.NET Core Web API

In a Web API, controllers or minimal API endpoints should handle HTTP concerns. The service layer can coordinate business operations, while repositories or query classes execute SQL through Dapper.

A practical separation of HTTP, business, and database responsibilities.

Example Controller

[ApiController]
[Route("api/[controller]")]
public sealed class EmployeesController(
    IEmployeeRepository repository) : ControllerBase
{
    [HttpGet]
    public async Task<ActionResult<IReadOnlyList<Employee>>> GetAll(
        CancellationToken cancellationToken)
    {
        var employees =
            await repository.GetAllAsync(cancellationToken);

        return Ok(employees);
    }

    [HttpGet("{id:int}")]
    public async Task<ActionResult<Employee>> GetById(
        int id,
        CancellationToken cancellationToken)
    {
        var employee =
            await repository.GetByIdAsync(id, cancellationToken);

        return employee is null ? NotFound() : Ok(employee);
    }
}

ASP.NET Core can bind the request cancellation token and pass it to the data-access layer. This helps stop work when a request is cancelled, provided the database provider and command path support cancellation.

Configuration

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=...;Database=EmployeeDb;..."
  }
}

This is an illustrative configuration shape. Keep secrets in user secrets during local development and use a managed secret store or secure environment configuration in deployed environments. Avoid storing production passwords in committed JSON files.

Dapper vs Entity Framework Core

Dapper and EF Core solve related but different data-access problems. Many .NET applications use one or the other, and some use both in separate parts of the same system.

Consideration	Dapper	Entity Framework Core
Query style	SQL written by developer	LINQ, plus raw SQL options
Mapping	Maps query results to objects	Maps entities and relationships
Change tracking	No built-in EF-style tracking	Built-in change tracker
Schema workflow	Usually separate SQL migration tooling	EF Core migrations available
Complex SQL	Direct control over SQL	Can use LINQ or raw SQL
Learning focus	SQL and ADO.NET concepts	LINQ, entity modeling, ORM behavior
Typical fit	SQL-centric applications and query-heavy areas	Domain models and object-centric persistence

When Dapper Is a Good Choice

Your team wants direct control over SQL.
You already have well-designed SQL queries or stored procedures.
You need lightweight mapping without entity change tracking.
You are building reporting or read-heavy data-access paths.
You are comfortable maintaining SQL and database deployment scripts.

When EF Core May Be a Better Choice

You want LINQ-based queries for much of your application.
You need change tracking and unit-of-work behavior.
You want entity relationship management and migrations in the ORM workflow.
Your application benefits from a richer object-relational abstraction.

Can You Use Both?

Yes. A solution can use EF Core for ordinary transactional workflows and Dapper for carefully selected reporting or SQL-intensive operations. Keep transaction and connection coordination explicit when both tools must participate in the same unit of work.

Performance and Production Best Practices

Dapper is designed to keep the mapping layer lightweight, but a fast data-access library cannot compensate for poor SQL, missing indexes, oversized result sets, or excessive database round trips.

SQL and Database Practices

Return only the columns the application needs; avoid unnecessary SELECT *.
Use indexes that support real filtering, joining, and ordering patterns.
Review execution plans for slow or high-volume queries.
Use parameterized commands for values.
Apply paging for large user-facing lists.
Keep database constraints as a final integrity boundary.
Use transactions when multiple changes must be atomic.

Dapper and Application Practices

Use asynchronous methods for I/O-bound request paths.
Pass cancellation tokens and configure sensible command timeouts.
Dispose connections and readers promptly.
Use DTOs for API contracts rather than exposing database rows blindly.
Keep SQL readable and organized by feature or query responsibility.
Log useful diagnostic context without logging secrets or sensitive parameter values.
Handle expected database exceptions at the appropriate application boundary.

Paging Example

const string sql = """
    SELECT EmployeeId, FirstName, LastName, Email
    FROM Employees
    ORDER BY EmployeeId
    OFFSET @Offset ROWS
    FETCH NEXT @PageSize ROWS ONLY;
    """;

var offset = (pageNumber - 1) * pageSize;

var employees = await connection.QueryAsync<Employee>(
    sql,
    new
    {
        Offset = offset,
        PageSize = pageSize
    });

Validate page number and page size before calculating the offset. For large datasets, keyset pagination may be more efficient than deep offset paging, depending on the query and indexes.

Observability

Monitor query duration, failure rate, timeout count, connection-pool pressure, and database resource utilization. Use structured logs and distributed tracing where appropriate, while avoiding exposure of personal or confidential data.

Production reminder: Never assume that a benchmark from a library README predicts your application's performance. Test with realistic data volume, indexes, concurrency, network conditions, and the database engine you will deploy.

Testing Dapper-Based Data Access

Dapper repositories execute real SQL against a database provider. Unit tests can verify service behavior by mocking repository interfaces, but they do not prove that SQL syntax, mappings, constraints, or stored procedures work against your actual database engine.

What to Test

Repository queries against a disposable test database or isolated test schema.
Mapping of nullable columns and joined result sets.
Stored procedure input and output parameters.
Transaction rollback behavior.
Paging, filtering, and sorting edge cases.
Database constraint and concurrency behavior where relevant.

Testing Strategy

Test type	What it verifies
Service unit test	Business rules using a mocked repository contract.
Repository integration test	SQL execution and mapping against a real database provider.
API integration test	HTTP routing, validation, DI, serialization, and data flow.
Performance test	Latency and throughput under representative workload.

For SQL Server-specific applications, prefer integration tests against SQL Server or a compatible SQL Server test environment. A lightweight in-memory substitute may not reproduce SQL Server syntax, types, transaction behavior, or query semantics.

Common Mistakes and Troubleshooting

1. Column Name Does Not Match the Property

Use a SQL alias or a deliberate mapping strategy. Check spelling, casing conventions, and nullability.

2. Forgetting SQL Parameters

Use named parameters or DynamicParameters. Do not concatenate untrusted values into SQL.

3. Using Query for an INSERT or UPDATE

Use Execute or ExecuteAsync when no result rows are needed. Use scalar or query methods when SQL intentionally returns a value or row.

4. Using QuerySingle When Multiple Rows Are Possible

QuerySingle throws if more than one row is returned. Use a multi-row query method if multiple rows are valid.

5. Forgetting to Pass the Transaction

Every command that belongs to a transaction must receive the same transaction instance.

6. Keeping Connections Open Too Long

Dispose connections after the unit of work. Do not hold a reader open while performing unrelated work.

7. Returning Too Much Data

Select only the required columns and apply filtering and paging. Large result sets can consume memory and increase network and database costs.

8. Interpolating User-Controlled SQL Identifiers

Parameters cannot represent table or column names. Use a strict allow-list for dynamic identifiers and never trust raw request input.

9. Treating Dapper as a Full ORM

Dapper maps query results but does not provide EF Core-style change tracking, migrations, or automatic relationship persistence. Design those workflows explicitly.

10. Ignoring Provider Differences

SQL syntax, parameter markers, data types, and stored procedure behavior can vary by database provider. Write and test SQL for the actual database engine you use.

Practical Projects and Learning Sequence

The best way to learn Dapper is to build small features and progressively add realistic data-access requirements.

Project 1: Employee CRUD API

Create an ASP.NET Core Web API targeting .NET 10.
Connect to SQL Server using Dapper 2.1.89.
Implement create, read, update, and delete operations.
Use parameterized SQL and proper not-found responses.

Project 2: Department and Employee Relationships

Create department and employee tables.
Write joins to retrieve employee and department details.
Use Dapper multi-mapping.
Build a paginated employee search endpoint.

Project 3: Stored Procedure-Based Workflow

Create stored procedures for selected operations.
Call procedures with CommandType.StoredProcedure.
Use DynamicParameters for output values.
Handle errors and validate the returned results.

Project 4: Transactional Business Operation

Create an employee transfer operation.
Update the employee's department.
Insert an audit record.
Commit both changes together or roll back on failure.

Project 5: Reporting Dashboard

Build queries for totals, department summaries, and recent employees.
Use QueryMultiple where several result grids belong together.
Review execution plans and indexes.
Measure response time with representative data.

Suggested Learning Sequence

Understand ADO.NET, relational tables, and SQL basics.
Install Dapper and connect to SQL Server.
Learn Query, QueryAsync, Execute, and ExecuteScalar.
Practice parameterized SQL and CRUD operations.
Learn async methods, cancellation, and connection lifetime.
Use stored procedures, DynamicParameters, and transactions.
Understand joins, multi-mapping, and QueryMultiple.
Build repository and service layers in ASP.NET Core.
Test SQL with integration tests and improve query performance.
Apply security, observability, and deployment practices.

Important Dapper Interview Questions

1. What is Dapper?

Dapper is a lightweight micro-ORM for .NET that extends ADO.NET connections with methods for executing SQL and mapping result rows to .NET objects.

2. Why is Dapper called a micro-ORM?

It focuses on query execution and object mapping rather than providing a complete ORM workflow with change tracking and schema migrations.

3. What is the difference between Query and Execute?

Query maps rows returned by a SQL query. Execute runs a command and commonly returns the number of affected rows.

4. What is the difference between QueryFirst and QuerySingle?

QueryFirst returns the first row even if more rows exist. QuerySingle requires exactly one row and throws if the result contains zero or multiple rows.

5. How does Dapper help prevent SQL injection?

It supports parameterized commands. Developers must pass untrusted values as parameters rather than concatenate them into SQL text.

6. What is DynamicParameters?

DynamicParameters is a parameter collection that supports explicit database types, directions, and output values.

7. What is QueryMultiple?

QueryMultiple executes a command that returns multiple result sets and exposes them through a grid reader.

8. What is multi-mapping?

Multi-mapping splits columns from a row into multiple object types and combines them using a mapping function.

9. What is splitOn?

splitOn identifies the column where Dapper should begin mapping the next object in a multi-mapping query.

10. Does Dapper support stored procedures?

Yes. Pass the procedure name and set commandType to CommandType.StoredProcedure.

11. Does Dapper support transactions?

Yes. Begin a transaction on a connection and pass that transaction to every Dapper command that must participate in it.

12. What is the difference between buffered and unbuffered queries?

Buffered queries materialize results before returning. Unbuffered queries allow progressive consumption and can reduce memory use for large result sets, but keep database resources occupied longer.

13. Does Dapper support asynchronous operations?

Yes. It provides methods such as QueryAsync, ExecuteAsync, and ExecuteScalarAsync.

14. Does Dapper replace ADO.NET?

No. Dapper builds on ADO.NET connection and provider abstractions and adds convenient execution and mapping methods.

15. Does Dapper support databases other than SQL Server?

Dapper is designed to work with ADO.NET-compatible providers. The SQL syntax and provider-specific behavior still depend on the database engine.

16. Does Dapper track entity changes?

No. It does not provide EF Core-style automatic change tracking. Developers write the commands needed to persist changes.

17. Can Dapper and EF Core be used in the same application?

Yes. They can be used for different data-access requirements, provided connection, transaction, and persistence boundaries are designed carefully.

18. How do you improve Dapper query performance?

Optimize SQL, indexes, selected columns, paging, round trips, connection lifetime, and result sizes. Measure the real workload rather than relying on assumptions.

Official Learning Resources

Use the official documentation and project sources to verify current package versions, API behavior, language features, and runtime support.

Final Thought

Dapper gives .NET developers a practical balance between direct SQL control and convenient object mapping. It is small in scope, but using it well still requires strong fundamentals: relational database design, SQL, parameterization, transactions, connection management, and performance analysis.

Start with a simple query, understand exactly how the result maps to your C# model, and then build toward repositories, transactions, stored procedures, and real application workflows. The strongest data-access code is not the code with the most abstraction; it is code that is secure, understandable, testable, and appropriate for the application's needs.

Programming

Dapper in .NET 10: Complete Developer Learning Guide