High-performance data access with Dapper in C#

Dapper is a lightweight micro-ORM for .NET that stays close to SQL while removing repetitive object-mapping code from ADO.NET. It builds parameterised commands, materialises results into C# records or classes, and adds little abstraction overhead. For read-heavy services and carefully controlled writes, that balance can produce fast data access without giving up control of the database.

Australian delivery teams often juggle on-premises SQL Server estates with Azure SQL Database deployments. Teams in Sydney, Melbourne and Brisbane serve customers across AEST and AEDT, and hosted applications may sit far from internal databases. Dapper suits this mixed environment because it runs in APIs, workers and serverless functions without requiring a particular hosting model.

The gains come from disciplined use, not from an ORM label. Correct parameter mapping, sensible connection handling, targeted indexes and measured execution plans work together. The practices below show how to use Dapper for high-performance data access in C#, with examples that remain effective on local workstations, in Azure and inside background processing systems.

Why Dapper suits .NET data access

Dapper sits close to ADO.NET. It handles command definition, parameter binding and simple row mapping, but leaves queries visible and predictable. That makes it attractive for services with stable SQL, modest domain objects and high query volume. It also avoids automatic change tracking, lazy loading and generated provider SQL, behaviours that can be useful elsewhere but unwanted in a latency-sensitive path.

The performance gain comes from doing less unnecessary work, not from changing SQL automatically. A Dapper call can still trigger a broad scan, duplicate joins or hold results in memory. Compare it with EF Core and hand-written ADO.NET using production-like data, measuring end-to-end latency and server resource use rather than relying on synthetic microbenchmarks alone.

Setting up a focused .NET project

Use a currently supported .NET release and add the Dapper and Microsoft.Data.SqlClient packages. Keep connection strings outside source control: user secrets suit local work, while deployed applications should retrieve credentials from a managed secret service. Australian teams frequently run separate development, test and production estates, so configuration mistakes deserve isolation from the start.

Create a repository around a narrow business purpose rather than a universal data gateway. Expose asynchronous methods, accept explicit filters and pass a CancellationToken through each command. This small surface makes the SQL visible to tests, lets the team replace a query without redesigning an entire ORM context, and supports predictable behaviour in APIs and workers.

A typical lookup keeps the query beside the mapping it returns:

using Dapper;
using Microsoft.Data.SqlClient;

public sealed class CustomerRepository
{
    private readonly string _connectionString;

    public CustomerRepository(string connectionString) =>
        _connectionString = connectionString;

    public async Task<Customer?> FindAsync(
        int id, CancellationToken cancellationToken)
    {
        await using var connection = new SqlConnection(_connectionString);

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

Mapping parameters and reading results

Dapper binds anonymous objects or dictionaries to SQL parameters by matching names, so a property named Id supplies @Id. It maps returned columns to C# properties using a case-insensitive default. Always pass values as parameters: string concatenation creates injection risks and produces a different query for every value, defeating SQL Server's execution-plan cache.

Multiple result sets can share one command and connection:

using var connection = new SqlConnection(connectionString);
using var grids = await connection.QueryMultipleAsync(
    new CommandDefinition(
        """
        SELECT Id, Name, Email
        FROM dbo.Customers;

        SELECT COUNT(*)
        FROM dbo.Orders
        WHERE CustomerId = @CustomerId;
        """,
        new { CustomerId = customerId },
        cancellationToken: cancellationToken));

var customers = (await grids.ReadAsync<Customer>()).ToList();
var orderCount = await grids.ReadSingleAsync<int>();

Use records for immutable query results and explicit aliases such as TotalAmount when database and C# names differ. Dapper can also split a joined row across several types using splitOn and combine the fragments in code. Fetching related rows in one round trip avoids the N+1 pattern of querying every order or invoice separately for the same customer.

Large exports need care because Dapper buffers each result set by default. Use the unbuffered option, represented by CommandFlags.None, to stream rows and keep memory usage bounded. The trade-off is an occupied connection until enumeration finishes, so dispose the reader promptly and avoid leaving a stream open while waiting for another service.

Building queries that stay efficient

Let SQL perform filtering, joins, aggregation and sorting. Dapper will not optimise an inefficient statement for you, and moving those operations into C# usually increases transferred data. Use indexed columns with sargable predicates, avoid wrapping indexed fields in functions and request only the columns the application needs. Stored procedures can also work well when their plans are tested and their contracts are stable.

A grouped report can use a CommandDefinition for explicit timeout and cancellation:

var totals = await connection.QueryAsync<OrderTotal>(
    new CommandDefinition(
        """
        SELECT CustomerId,
               COUNT(*) AS OrderCount,
               SUM(Total) AS LifetimeValue
        FROM dbo.Orders
        WHERE CreatedAt >= @From
          AND CreatedAt < @To
        GROUP BY CustomerId;
        """,
        new { From = fromUtc, To = toUtc },
        cancellationToken: cancellationToken,
        commandTimeout: 30));

Inspect actual execution plans under realistic volumes, then add or adjust indexes based on the measured workload. Use UTC boundaries for reporting periods because daylight-saving changes between AEST and AEDT can otherwise move results. Connection reuse can shave latency, yet it cannot compensate for a query that scans millions of irrelevant rows.

Controlling connections and transactions

Open connections as late as possible and dispose them promptly. Microsoft.Data.SqlClient pools connections by default, so disposal returns a connection to the pool rather than destroying the session. Use one connection per request in web applications and per unit of work in workers. Never share a mutable SqlConnection between concurrent operations, and avoid holding it open while calling an unrelated network service.

For related writes, pass the same transaction to every Dapper command:

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

try
{
    var updated = await connection.ExecuteAsync(
        new CommandDefinition(
            """
            UPDATE dbo.Accounts
            SET Balance = Balance - @Amount
            WHERE Id = @Id AND Balance >= @Amount;
            """,
            new { Amount = amount, Id = accountId },
            transaction,
            cancellationToken: cancellationToken));

    if (updated != 1)
        throw new InvalidOperationException("Insufficient balance.");

    await connection.ExecuteAsync(
        new CommandDefinition(
            "INSERT INTO dbo.Entries (AccountId, Amount) VALUES (@Id, @Amount);",
            new { Id = accountId, Amount = amount },
            transaction,
            cancellationToken: cancellationToken));

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

Dapper has no ambient transaction, so every statement must receive the transaction object. Retry policies must cover the entire unit of work without duplicating a debit, inventory change or payment. A database in another Australian state can spend a meaningful share of a timeout on network travel, so measure connection time separately from execution time before extending command limits.

Operational habits for dependable production data access

Production failures need context without exposing sensitive data. Log elapsed time, a command identifier, affected-row counts and exception details, but avoid customer records, credentials and full parameter payloads. For event-driven capture, this serverless ingestion walkthrough provides a useful reference for placing Azure Functions between incoming work and durable storage.

Australian privacy obligations, including the Privacy Act 1988 and the Australian Privacy Principles, shape how personal information is collected and retained. Limit returned columns, protect exported files and apply suitable access controls and retention periods. Logging is still data processing, so give operational telemetry the same scrutiny as an application database.

Before deployment, focus on these design checks:

• Keep SQL under source control and review query changes alongside schema changes.
• Use separate credentials and connection settings for local, test and production.
• Run representative load tests against the intended Azure SQL or SQL Server version.

At runtime, make the following signals visible:

• Track command duration, timeout failures and connection-pool saturation.
• Alert on repeated rollback failures and unusual query-volume increases.
• Sample execution plans after significant schema or workload changes.

Comparing Dapper with other approaches

Dapper, EF Core and raw ADO.NET occupy different points on the control-versus-convenience spectrum. Dapper keeps SQL explicit and mapping compact, which suits APIs, reporting services and predictable schemas. EF Core offers change tracking, migrations and richer persistence patterns for domain-heavy applications. Raw ADO.NET remains appropriate for specialised bulk paths or infrastructure needing complete control of provider behaviour.

One application can use more than one approach: EF Core might manage aggregate lifecycles while Dapper serves a high-volume read model. Choose by workload, then benchmark comparable queries with realistic indexes, data volumes and network latency. Include memory pressure and server CPU in the results, since a fast client can still overload SQL Server with poorly filtered requests.

For straightforward SQL and modest mapping needs, Dapper offers a compact, predictable layer between application code and the database. Its performance depends on the quality of each command, the surrounding connection strategy and the database design.