Using Dapper with SQL Server for lightweight data access

Dapper gives C# developers a compact way to run SQL and materialise results without adopting a full ORM. It stays close to ADO.NET: queries are explicit, parameters are typed, and the mapper handles rows-to-object conversion. For small services, internal tools and reporting endpoints, that restraint makes Dapper straightforward to diagnose when a query misbehaves.

SQL Server remains a sensible centre of gravity for many Australian teams. The .NET developer market in Melbourne, Sydney and Brisbane regularly combines C# services with SQL Server and Azure, particularly in finance, logistics and business software. Dapper fits well when the data shape is straightforward and the team values visible SQL over change tracking, lazy loading and a large object-mapping layer.

Where Dapper earns its keep

Dapper is a micro-ORM built on ADO.NET rather than a replacement for it. A typical call maps query results to a record, a POCO or a dynamic object, while command execution still travels through DbConnection and DbCommand. For a broader look at the library and its trade-offs, Dapper usage overview provides a useful companion to the examples here.

That closeness matters during production incidents. There is no hidden SQL generated from navigation properties, so you can inspect the statement, parameters and execution plan. Methods such as QueryAsync, ExecuteAsync and QueryMultipleAsync also keep common database operations recognisable across a codebase.

Dapper is less suitable when a domain has deep object graphs, complex write-side invariants or needs optimistic concurrency across many aggregates. Those cases often justify EF Core or a dedicated persistence design. For straightforward CRUD, reporting queries and bounded integrations, the lower ceremony is a genuine advantage.

Setting up a focused data layer

Install Dapper beside the Microsoft SQL client, keeping package versions aligned with the target framework:

dotnet add package Dapper
dotnet add package Microsoft.Data.SqlClient

Dapper supplies extension methods for ADO.NET connection types, while Microsoft.Data.SqlClient provides the SQL Server implementation, TLS configuration and current provider fixes. Keep these dependencies separate so the boundary between the mapper and the database driver remains clear.

Create a small connection factory rather than copying connection strings into every repository. The factory gives you one place to obtain a SqlConnection, while the repository remains focused on a query:

public sealed class CustomerRepository(ISqlConnectionFactory factory)
{
    public async Task<Customer?> FindAsync(
        int id,
        CancellationToken cancellationToken)
    {
        await using var connection = factory.Create();
        await connection.OpenAsync(cancellationToken);

        const string sql = """
            SELECT Id, FullName, Email
            FROM dbo.Customers
            WHERE Id = @Id;
            """;

        return await connection.QuerySingleOrDefaultAsync<Customer>(
            new CommandDefinition(
                sql,
                new { Id = id },
                cancellationToken: cancellationToken));
    }
}

Keep connection strings outside source control through user secrets, environment variables or a secret manager. For cloud deployments, use managed identity where the hosting model supports it, grant the database account only the required permissions and retain settings such as Encrypt=True and TrustServerCertificate=False. SqlClient handles physical connection pooling, so opening and closing a SqlConnection for each operation does not mean creating a new database connection every time.

Building queries that map cleanly

Make column-to-property mapping deliberate. Dapper matches names without regard to case, but it does not automatically transform Customer_FullName into CustomerFullName. Alias unusual columns in the query and keep DTO properties intentionally small:

SELECT
    Id,
    FullName AS Name,
    Email
FROM dbo.Customers
WHERE Id = @Id;

A dedicated DTO also prevents persistence-only fields from reaching an API or user interface contract. It keeps the query readable and avoids loading large columns that the caller does not need.

Choose each query method according to its expected cardinality. QuerySingleOrDefaultAsync returns one result or null and throws when multiple rows arrive. QueryFirstOrDefaultAsync silently takes the first row, which can conceal duplicate business keys. QueryAsync returns a sequence. Use Guid, DateTimeOffset and decimal deliberately, and add a unique index whenever the database treats a column as an identifier.

DynamicParameters supports optional filters without interpolating values into SQL. Keep the statement static and let the parameters carry the variability:

var parameters = new DynamicParameters();
parameters.Add("CustomerId", customerId, DbType.Int32);
parameters.Add(
    "City",
    string.IsNullOrWhiteSpace(city) ? null : city.Trim(),
    DbType.String);

const string sql = """
    SELECT Id, FullName, Email
    FROM dbo.Customers
    WHERE CustomerId = @CustomerId
      AND (@City IS NULL OR City = @City);
    """;

Dapper can also expand a collection used with IN @CustomerIds. Check how an empty collection behaves in your chosen Dapper version and handle it explicitly when the business rule requires at least one identifier.

Transactions and multi-result commands

Use a transaction when several commands must succeed or fail together. Keep its scope narrow and pass the same transaction instance to every CommandDefinition:

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

try
{
    await connection.ExecuteAsync(new CommandDefinition(
        "UPDATE dbo.Customers SET Email = @Email WHERE Id = @Id;",
        new { Email = email, Id = customerId },
        transaction,
        cancellationToken: cancellationToken));

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

A long-running transaction can retain locks and increase latency, particularly when the application and database sit in different regions. Use the lowest isolation level that preserves the required business invariant, and avoid network calls while a transaction remains open.

Multiple result sets can share one round trip through QueryMultipleAsync. The GridReader must be consumed in statement order:

using var result = await connection.QueryMultipleAsync(
    new CommandDefinition(
        sql,
        new { CustomerId = customerId },
        cancellationToken: cancellationToken));

var customer = await result.ReadSingleOrDefaultAsync<Customer>();
var orders = (await result.ReadAsync<Order>()).ToList();

Temporal consistency deserves the same attention. Store timestamps in UTC, convert them at the boundaries and use IANA zone identifiers such as Australia/Sydney or Australia/Brisbane. Brisbane and Perth do not observe daylight saving, while Sydney and Melbourne do, so a hard-coded UTC offset can produce incorrect Australian business dates.

Production habits that prevent surprises

Measure the database work before optimising the C# layer. Dapper removes some mapping overhead, but it cannot rescue an inefficient query. Inspect execution plans, use Query Store where appropriate and check indexes for the columns used by filters, joins and ordering. Avoid SELECT * in frequently executed statements, and give unusually expensive reports their own path with a sensible command timeout.

Treat transient failures as part of the design. Set finite connection and command timeouts, pass cancellation tokens through asynchronous calls and enable provider retries for supported Azure SQL deployments. Retry reads more freely than writes; a retried update should either be idempotent or protected by an application-level idempotency key. Keep application and database workloads in the same Australian cloud region where possible, using Australia East or Australia Southeast to reduce network delay. Comparing infrastructure estimates in Australian dollars also makes regional pricing differences easier for local teams to assess.

Integration tests should exercise SQL against a real SQL Server instance, ideally through a disposable container or database per test run. A mocked repository cannot catch column aliases, constraint failures or transaction mistakes. Australian privacy obligations, including the Privacy Act and Australian Privacy Principles where applicable, also affect observability: log query names, durations and parameter names while avoiding raw customer records in application logs.

A pragmatic adoption checklist for Australian teams

The following sequence keeps an initial adoption small enough to measure while leaving a clear path for expansion:

• Start with a read-heavy endpoint or internal report where the required columns are already known.
• Keep every value parameterised, including identifiers supplied by users or external systems.
• Map results to purpose-built DTOs with property names that match the selected SQL columns.
• Use asynchronous methods, cancellation tokens and realistic command timeouts for every network-bound query.
• Keep transactions limited to the statements that require atomicity, and avoid external calls inside them.
• Cover each mapping and command with an integration test against a disposable SQL Server database.

Adopt Dapper with a small, measurable move rather than a platform-wide rewrite. Pick one bounded endpoint this week, run it beside the existing implementation, and publish the measured latency and maintenance comparison to your team.