Async and Connection Management in Dapper: CancellationToken, Pooling and DI

Version note: The code in this post targets Dapper 2.1 and .NET 10.

Guess First

You have a CancellationToken that is already cancelled, and you hand it to QueryAsync. The code compiles and the IDE stays quiet. What happens when you run it: do you get an OperationCanceledException, or do rows come back?

using var cts = new CancellationTokenSource();
cts.Cancel(); // cancel the token up front

await using var conn = new SqliteConnection(cs);
var books = await conn.QueryAsync<Book>(
    "select id, title, price from books",
    cts.Token);

Console.WriteLine($"Rows: {books.Count()}");

We will get to the answer shortly. This post is about the two questions hiding behind that snippet: how cancellation works for async Dapper calls, and when that conn should be opened, when it should be closed, and who should own it.

Setup: The Database for This Post

Like the rest of the series, we use zero-setup SQLite. Create a console project and add two packages:

dotnet new console -n Bookstore
cd Bookstore
dotnet add package Dapper --version 2.1.89
dotnet add package Microsoft.Data.Sqlite --version 10.0.14

Every example starts with the small Setup class below. It deletes bookstore.db on each run and recreates the authors and books tables, so the output is the same every time:

using Dapper;
using Microsoft.Data.Sqlite;

public static class Setup
{
    public static async Task CreateAsync(string cs)
    {
        if (File.Exists("bookstore.db")) File.Delete("bookstore.db");
        await using var conn = new SqliteConnection(cs);
        await conn.ExecuteAsync("""
            create table authors (
                id      integer primary key,
                name    text not null,
                country text not null
            );
            create table books (
                id        integer primary key,
                author_id integer not null references authors(id),
                title     text not null,
                price     real not null,
                stock     integer not null check (stock >= 0)
            );
            insert into authors (id, name, country) values
                (1, 'Ursula K. Le Guin', 'USA'),
                (2, 'Frank Herbert', 'USA'),
                (3, 'Ted Chiang', 'USA');
            insert into books (id, author_id, title, price, stock) values
                (1, 1, 'A Wizard of Earthsea', 9.99, 12),
                (2, 1, 'The Left Hand of Darkness', 12.50, 4),
                (3, 2, 'Dune', 14.99, 20),
                (4, 3, 'Exhalation', 16.00, 7);
            """);
    }
}

public sealed class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
}

On the Program.cs side every example opens with const string cs = "Data Source=bookstore.db"; and await Setup.CreateAsync(cs);. To save space I don't repeat those two lines in the code blocks.

Why Was the Token Ignored?

The answer to the guessing game: there is no exception, and the program prints Rows: 4. The cancelled token was silently swallowed.

The cause is overload resolution. In the QueryAsync<T> signature the second positional parameter is object? param. CancellationToken is a struct, so it gets boxed and settles happily into param. Dapper treats it as a parameter object, looks for its properties (IsCancellationRequested, CanBeCanceled, WaitHandle) in the SQL text, finds no name like @IsCancellationRequested, and adds no parameters to the command. As far as the compiler is concerned everything is fine: you passed an object where an object was expected.

None of Dapper's extension methods on IDbConnection has a separate CancellationToken parameter. The one official way to pass a token is CommandDefinition:

var command = new CommandDefinition(
    "select id, title, price from books",
    cancellationToken: cts.Token);
var again = await conn.QueryAsync<Book>(command);

Rows: 4
TaskCanceledException: A task was canceled.

The first line is the output of the guessing example, the second is the version written with CommandDefinition. CommandDefinition is a struct that carries the SQL text, parameters, transaction, timeout, CommandType, CommandFlags and the token in one package. QueryAsync, ExecuteAsync, ExecuteScalarAsync, QueryFirstOrDefaultAsync and the rest all have an overload that takes a CommandDefinition. Note one more thing: QueryAsync has no buffered parameter like the sync Query does; buffering is also set through CommandDefinition.Flags. We will open that up in post 10 of the series.

Rule of thumb: if you are passing a token to an async Dapper call, write a CommandDefinition without thinking twice. If a code review shows a positional ct or cts.Token among the arguments of a Dapper call, raise an eyebrow.

How Async Is Async on SQLite?

We passed the token correctly, but can we actually stop a running query? Let's read a recursive CTE that produces 20 million rows and cancel it after 300 ms:

using System.Diagnostics;
using Dapper;
using Microsoft.Data.Sqlite;

const string manyRowsSql = """
    with recursive n(i) as (select 1 union all select i + 1 from n where i < 20000000)
    select i from n
    """;

Console.WriteLine($"Thread before: {Environment.CurrentManagedThreadId}");
using var cts = new CancellationTokenSource(TimeSpan.FromMilliseconds(300));
var sw = Stopwatch.StartNew();
try
{
    await using var conn = new SqliteConnection(cs);
    var rows = await conn.QueryAsync<long>(
        new CommandDefinition(manyRowsSql, cancellationToken: cts.Token));
    Console.WriteLine($"Rows: {rows.Count()}");
}
catch (Exception ex)
{
    Console.WriteLine($"{ex.GetType().Name}: {ex.Message}");
}
Console.WriteLine($"Elapsed: {sw.ElapsedMilliseconds} ms");
Console.WriteLine($"Thread after: {Environment.CurrentManagedThreadId}");

Thread before: 1
TaskCanceledException: A task was canceled.
Elapsed: 303 ms
Thread after: 1

The cancellation arrived right on time. Now let's try the same with a query that returns a single row but takes a long time to compute: the count(*) of a 100-million-row CTE.

const string slowSql = """
    with recursive n(i) as (select 1 union all select i + 1 from n where i < 100000000)
    select count(*) from n
    """;

using var cts = new CancellationTokenSource(TimeSpan.FromMilliseconds(300));
var sw = Stopwatch.StartNew();
await using var conn = new SqliteConnection(cs);
var total = await conn.ExecuteScalarAsync<long>(
    new CommandDefinition(slowSql, cancellationToken: cts.Token));
Console.WriteLine($"Total: {total}, elapsed: {sw.ElapsedMilliseconds} ms, cancelled: {cts.IsCancellationRequested}");

Total: 100000000, elapsed: 6271 ms, cancelled: True

The token was cancelled at 300 ms, yet the query ran for more than 6 seconds and returned its result. The difference between the two examples lives inside the provider. The Microsoft.Data.Sqlite documentation says it plainly: SQLite doesn't support asynchronous I/O, and the async ADO.NET methods execute synchronously. Looking at the 10.0 source, ExecuteReaderAsync checks the token only at the very start and then calls the synchronous ExecuteReader, and SqliteCommand.Cancel() is an empty method. The only place the token takes effect is between rows: in buffered reads Dapper calls ReadAsync(token) for each row, and that is where the cancellation gets caught. A single long statement is not interrupted. The thread id staying at 1 in the first example tells the same story: no real waiting happened anywhere.

That doesn't mean you should stop writing async code against SQLite. Your code stays the same when it moves to SQL Server or PostgreSQL tomorrow, and those providers do real asynchronous I/O and forward cancellation to the server. Just don't generalize the cancellation behavior you measure on SQLite.

When Does the Connection Open and Close?

Dapper's attitude toward the connection is simple: hand it a closed connection and it opens it at the start of the call and closes it at the end. Hand it an open one and it leaves it alone.

await using (var conn = new SqliteConnection(cs))
{
    Console.WriteLine($"Before: {conn.State}");
    var count = await conn.ExecuteScalarAsync<int>("select count(*) from books");
    Console.WriteLine($"Books: {count}, after: {conn.State}");
}

Before: Closed
Books: 4, after: Closed

For single-query jobs this is great: you don't even need to write OpenAsync. But if you run several commands in a row on the same connection, every call means a separate open/close. Session-bound things such as a transaction, a temp table or SQLite's CreateFunction are lost between those opens and closes. If several commands need to live in one session, open the connection yourself; that is exactly why the next post builds transactions on an open connection.

Isn't it expensive to create a new connection for every query? This is where connection pooling comes in. A connection you Dispose doesn't physically close; it goes back to the pool, and the next Open takes it from there. Microsoft.Data.Sqlite has had pooling since 6.0, and it is on by default. To see the difference, let's open-query-close 1000 times:

await MeasureAsync("Pooling=True", "Data Source=bookstore.db");
await MeasureAsync("Pooling=False", "Data Source=bookstore.db;Pooling=False");

static async Task MeasureAsync(string label, string connectionString)
{
    var sw = Stopwatch.StartNew();
    for (var i = 0; i < 1000; i++)
    {
        await using var conn = new SqliteConnection(connectionString);
        await conn.ExecuteScalarAsync<int>("select count(*) from books");
    }
    Console.WriteLine($"{label}: {sw.ElapsedMilliseconds} ms");
}

Pooling=True: 6 ms
Pooling=False: 79 ms

On my machine 1000 pooled rounds took 6 ms and unpooled ones took 79 ms. On a server database that talks over the network the gap grows much larger, because every unpooled open means a TCP connection, a TLS handshake and authentication. The takeaway: treat connections as "short-lived, created often"; the pool already takes care of the expensive part.

Sharing a Single Connection

Someone who has never heard of pooling might think "if it's expensive, let's open one and have everyone use it." Let's try it: one open connection, 50 parallel queries on it.

var shared = new SqliteConnection(cs);
await shared.OpenAsync();

var tasks = Enumerable.Range(0, 50).Select(i => Task.Run(async () =>
{
    var titles = await shared.QueryAsync<string>(
        "select title from books where id = @id", new { id = i % 4 + 1 });
    return titles.Count();
}));

try
{
    var counts = await Task.WhenAll(tasks);
    Console.WriteLine($"Done: {counts.Sum()} rows");
}
catch (Exception ex)
{
    Console.WriteLine($"{ex.GetType().Name}: {ex.Message}");
}

Done: 50 rows
Done: 50 rows
ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index')

I ran the same program back to back; 23 of 30 runs printed Done: 50 rows and 7 threw the exception above. This is the most dangerous kind of bug: it works most of the time, passes in the test environment and blows up under load. ADO.NET connections are not thread-safe; inside them is shared state such as the list of open commands, the reader state and the transaction. A connection is used by one piece of work at a time. A singleton connection, a static connection or a connection shared by several requests all lead to the same bug.

Connection Factory and DI in ASP.NET Core

In a real application the cleanest answer to "who creates the connection?" is a factory. The factory holds only the connection string and has no state, so it can be registered as a singleton. Each repository method takes its own connection from it and disposes it when done. In a project created with dotnet new web, Program.cs looks like this:

using System.Data.Common;
using Dapper;
using Microsoft.Data.Sqlite;

var builder = WebApplication.CreateBuilder(args);

var cs = builder.Configuration.GetConnectionString("Bookstore") ?? "Data Source=bookstore.db";
await Setup.CreateAsync(cs);

builder.Services.AddSingleton<IDbConnectionFactory>(new SqliteConnectionFactory(cs));
builder.Services.AddScoped<BookRepository>();

var app = builder.Build();

app.MapGet("/books", async (BookRepository repo, CancellationToken ct) =>
    await repo.GetAllAsync(ct));

app.MapGet("/books/{id:int}", async (int id, BookRepository repo, CancellationToken ct) =>
    await repo.GetByIdAsync(id, ct) is { } book ? Results.Ok(book) : Results.NotFound());

app.Run();

public interface IDbConnectionFactory
{
    Task<DbConnection> OpenAsync(CancellationToken ct = default);
}

public sealed class SqliteConnectionFactory(string connectionString) : IDbConnectionFactory
{
    public async Task<DbConnection> OpenAsync(CancellationToken ct = default)
    {
        var conn = new SqliteConnection(connectionString);
        await conn.OpenAsync(ct);
        return conn;
    }
}

public sealed class BookRepository(IDbConnectionFactory factory)
{
    public async Task<IReadOnlyList<Book>> GetAllAsync(CancellationToken ct)
    {
        await using var conn = await factory.OpenAsync(ct);
        var books = await conn.QueryAsync<Book>(new CommandDefinition(
            "select id, title, price from books order by id",
            cancellationToken: ct));
        return books.AsList();
    }

    public async Task<Book?> GetByIdAsync(int id, CancellationToken ct)
    {
        await using var conn = await factory.OpenAsync(ct);
        return await conn.QuerySingleOrDefaultAsync<Book>(new CommandDefinition(
            "select id, title, price from books where id = @id",
            new { id },
            cancellationToken: ct));
    }
}

$ curl -s http://127.0.0.1:5191/books/3
{"id":3,"title":"Dune","price":14.99}
$ curl -s -o /dev/null -w "%{http_code}\n" http://127.0.0.1:5191/books/99
404

A few details matter. When you add a CancellationToken parameter to a minimal API endpoint, ASP.NET Core binds it to HttpContext.RequestAborted; if the user closes the tab, the token is cancelled, and that token flows all the way into CommandDefinition. The factory returning DbConnection instead of IDbConnection is deliberate: OpenAsync, DisposeAsync and QueryUnbufferedAsync, which we will see in post 10, all live on DbConnection. AsList() is a small Dapper helper; since a buffered result is already a List<T>, it hands it back without copying.

As an alternative you can register the connection itself as scoped:

builder.Services.AddScoped<DbConnection>(_ => new SqliteConnection(cs));

app.MapGet("/books/count", async (DbConnection conn, CancellationToken ct) =>
    await conn.ExecuteScalarAsync<int>(new CommandDefinition(
        "select count(*) from books", cancellationToken: ct)));

Here is the tradeoff between the two. With a factory the connection's lifetime is visible in the code, each method carries the responsibility with its own await using line, and it works naturally for background jobs and parallel work; the cost is one extra line per method. With a scoped connection there is one connection per request, several repositories can easily share the same connection and therefore the same transaction, and the DI container disposes it at the end of the request; the cost is holding the connection for the whole request, and if you start parallel Tasks within the same request you are back at the sharing bug from the previous section. My default is the factory, and I switch to a scoped connection where a unit of work calls for it.

Common Pitfalls

Passing the token as a positional argument. As you saw, it compiles and does nothing. The token always goes through CommandDefinition.

Turning async into sync with .Result and .Wait(). In ASP.NET Core this blocks thread pool threads and leads to thread pool starvation under load. Once you are in an async chain, await all the way to the end; if you are in a sync context, use Dapper's sync methods, and don't mix the two.

Keeping the connection long-lived as a singleton or a static field, treating it like HttpClient. Reusing HttpClient is right; reusing a connection is not. For connections, the pool does the reusing.

Writing using instead of await using, or writing neither. A connection that is never disposed doesn't return to the pool; with providers whose pool is capped, such as SqlClient, new requests start waiting for a connection to free up once the cap is reached.

Generalizing the async behavior you measured on SQLite to server databases. On SQLite async calls run synchronously, and a single long statement is not interrupted by a token.

A note for those coming from EF Core: DbContext opens and closes the connection internally, and you only think about the context's lifetime. In Dapper that responsibility is yours. The Dapper counterpart of the "one DbContext per request" rule you know from the EF Core series is "one connection per unit of work."

A Small Experiment

★☆☆ Change the SQL in the guessing example to select id, title, price from books where price < @maxPrice and pass the token positionally again. Which exception do you get this time, and why does the message never mention the token?

★★☆ Add GetByAuthorAsync(int authorId, CancellationToken ct) to BookRepository and wire it to an /authors/{id:int}/books endpoint. Then change the repositories to take a scoped DbConnection, use both BookRepository and a new AuthorRepository in a single endpoint, and verify, by logging conn.GetHashCode(), that both get the same connection.

★★★ In the shared-connection example, replace Task.Run with a sequential foreach that awaits the 50 queries one by one. Does the error still show up? Explain the result together with why a connection is bound by the "one piece of work at a time" rule.

The real justification for keeping a connection open is to turn several commands into a single whole. In the next post we will put an order, its order item and the stock update into one transaction, and see why forgetting the transaction: parameter stays silent on SQLite. The series will keep being updated as new Dapper versions ship.