Connecting to a Database with C# and ASP.NET Core Using Dapper

1. What Is Dapper?

Dapper is commonly described as a micro-ORM. Unlike a full-featured ORM such as Entity Framework Core, Dapper does not attempt to model your entire database and application domain automatically. Instead, it focuses primarily on mapping query results to .NET objects and executing SQL efficiently.

Dapper provides APIs such as QueryAsync<T>, QueryFirstAsync<T>, and ExecuteAsync. Its asynchronous query APIs operate on an IDbConnection, allowing Dapper to work with ADO.NET-compatible database providers.

A major advantage is that you retain ownership of the SQL. This makes Dapper a good fit for applications where queries are performance-sensitive, database-specific, relatively complex, or already exist as stored procedures.

Dapper versus a full ORM

2. Reference Architecture

A useful architecture separates HTTP concerns, business logic, and database access. The exact number of projects is less important than keeping responsibilities separated.

For a larger application, the structure might look like:

MyApplication/
├── Api/
│   ├── Controllers/
│   └── Program.cs
├── Application/
│   ├── Interfaces/
│   ├── Services/
│   └── DTOs/
├── Domain/
│   └── Entities/
└── Infrastructure/
└── Data/
├── Repositories/
└── DatabaseConnectionFactory.cs
                                    

This arrangement allows the application layer to depend on abstractions instead of directly depending on SQL Server or Dapper.

3. Create the ASP.NET Core Project

The examples below target modern ASP.NET Core and use the minimal hosting model. Create a Web API project with:

dotnet new webapi -n DapperDemo
cd DapperDemo

Add Dapper and the Microsoft SQL Server provider:

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

Microsoft.Data.SqlClient is Microsoft's .NET data provider for SQL Server and Azure SQL and is the modern provider to use for SQL Server connectivity.

4. Configure the Connection String

ASP.NET Core provides configuration through sources such as appsettings.json, environment variables, command-line arguments and secret-management mechanisms. Configuration can be accessed through dependency injection or through builder.Configuration.

For local development, an example configuration is:

{
    "ConnectionStrings": {
        "DefaultConnection": "Server=localhost;Database=OrdersDb;Trusted_Connection=True;TrustServerCertificate=True;"
    }
}

The connection string should be treated as configuration rather than as something hard-coded into repositories or controllers.

5. Create a Database Model

Suppose the database contains an Orders table:

CREATE TABLE Orders
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(200) NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    CreatedUtc DATETIME2 NOT NULL
);

The corresponding C# model could be:

public sealed class Order
{
    public int Id { get; init; }
    public string CustomerName { get; init; } = string.Empty;
    public decimal TotalAmount { get; init; }
    public DateTime CreatedUtc { get; init; }
}

Dapper maps returned column names to matching object properties. This keeps the model simple and avoids the need for a large mapping configuration in straightforward scenarios.

6. Create a Database Connection Factory

A connection factory centralizes how connections are created without forcing the rest of the application to know about SQL Server-specific connection construction.

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

public interface IDbConnectionFactory
{
    IDbConnection CreateConnection();
}

Implementation:

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

public sealed class SqlConnectionFactory : IDbConnectionFactory
{
    private readonly string _connectionString;
    public SqlConnectionFactory(IConfiguration configuration)
    {
        _connectionString =
            configuration.GetConnectionString("DefaultConnection")
            ?? throw new InvalidOperationException(
                "DefaultConnection was not configured.");
    }

    public IDbConnection CreateConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

This approach gives the repository an abstraction over connection creation and makes testing and future infrastructure changes easier.

7. Create a Repository Abstraction

Define an interface representing the operations the application actually needs:

public interface IOrderRepository
{
    Task GetByIdAsync(
    int id,
    CancellationToken cancellationToken = default);

    Task GetAllAsync(
        CancellationToken cancellationToken = default);

    Task<int> CreateAsync(
        Order order,
        CancellationToken cancellationToken = default);
}

The interface deliberately does not expose Dapper, SqlConnection, SQL strings, or ADO.NET implementation details.

8. Implement the Repository with Dapper

A straightforward implementation is:

using Dapper;

public sealed class OrderRepository : IOrderRepository
{
    private readonly IDbConnectionFactory _connectionFactory;

    public OrderRepository(IDbConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }

    public async Task<Order?> GetByIdAsync(
        int id,
        CancellationToken cancellationToken = default)
    {
        const string sql = """
            SELECT
                Id,
                CustomerName,
                TotalAmount,
                CreatedUtc
            FROM Orders
            WHERE Id = @Id;
            """;

        using var connection = _connectionFactory.CreateConnection();

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

    public async Task<IReadOnlyList<Order>> GetAllAsync(
        CancellationToken cancellationToken = default)
    {
        const string sql = """
            SELECT
                Id,
                CustomerName,
                TotalAmount,
                CreatedUtc
            FROM Orders
            ORDER BY Id DESC;
            """;

        using var connection = _connectionFactory.CreateConnection();

        var results = await connection.QueryAsync<Order>(
            new CommandDefinition(
                sql,
                cancellationToken: cancellationToken));

        return results.AsList();
    }

    public async Task<int> CreateAsync(
        Order order,
        CancellationToken cancellationToken = default)
        {
            const string sql = """
                INSERT INTO Orders
                    (CustomerName, TotalAmount, CreatedUtc)
                VALUES
                    (@CustomerName, @TotalAmount, @CreatedUtc);

                SELECT CAST(SCOPE_IDENTITY() AS INT);
                """;

            using var connection = _connectionFactory.CreateConnection();

            return await connection.ExecuteScalarAsync<int>(
                new CommandDefinition(
                    sql,
                    new
                    {
                        order.CustomerName,
                        order.TotalAmount,
                        order.CreatedUtc
                    },
                    cancellationToken: cancellationToken));
        }
}

Dapper supports parameter objects such as new { Id = id } and DynamicParameters. Parameterized queries are preferable to constructing SQL by concatenating user input. Dapper's documentation specifically describes its parameterization support, including dynamic parameter construction.

9. Why Parameterized SQL Matters

Never build SQL by concatenating untrusted input:

// BAD
var sql = $"SELECT * FROM Orders WHERE CustomerName = '{customerName}'";

Instead, use parameters:

// GOOD
const string sql = """
SELECT *
FROM Orders
WHERE CustomerName = @CustomerName;
""";

var orders = await connection.QueryAsync(
sql,
new { CustomerName = customerName });

Parameterized queries separate SQL code from data and are one of the primary defenses against SQL injection. OWASP recommends prepared statements or parameterized queries as a primary SQL-injection defense.

10. Register Dependencies with ASP.NET Core DI

ASP.NET Core has built-in dependency injection. Microsoft recommends using DI rather than service-locator patterns, and services can be grouped into extension methods for cleaner registration.

In Program.cs:

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddSingleton();
builder.Services.AddScoped();

var app = builder.Build();

app.MapControllers();

app.Run();

The connection factory can be singleton if it only retains immutable configuration and creates a new connection for each operation. Database connections themselves should not be registered as a singleton.

A repository that is used within an HTTP request is commonly registered as Scoped. The exact lifetime should follow the lifetime of the resources it owns and the application's unit-of-work requirements.

11. Add an Application Service

Controllers should generally coordinate HTTP concerns rather than contain SQL and business rules.

public interface IOrderService
{
    Task GetOrderAsync(
    int id,
    CancellationToken cancellationToken = default);
}

public sealed class OrderService : IOrderService
{
    private readonly IOrderRepository _repository;

    public OrderService(IOrderRepository repository)
    {
        _repository = repository;
    }

    public Task<Order?> GetOrderAsync(
        int id,
        CancellationToken cancellationToken = default)
    {
        return _repository.GetByIdAsync(id, cancellationToken);
    }
}

Register it:

builder.Services.AddScoped<IOrderService, OrderService>();

12. Create the API Controller

[ApiController]
[Route("api/orders")]
public sealed class OrdersController : ControllerBase
{
    private readonly IOrderService _service;

    public OrdersController(IOrderService service)
    {
        _service = service;
    }

    [HttpGet("{id:int}")]
    public async Task<ActionResult<Order>> Get(
        int id,
        CancellationToken cancellationToken)
    {
        var order = await _service.GetOrderAsync(
            id,
            cancellationToken);

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

The resulting dependency chain is:

13. Use Cancellation Tokens

Database operations can outlive the usefulness of an HTTP request. Passing CancellationToken through the controller, service, repository and Dapper command allows the operation to be cancelled when appropriate.

This is particularly useful when a client disconnects, a request exceeds an application timeout, or the application is shutting down.

The important design principle is to avoid accepting a cancellation token at the API boundary and then silently throwing it away further down the call chain.

14. Handling Transactions with Dapper

When multiple database operations must succeed or fail as one logical operation, use a database transaction.

public async Task CreateOrderWithAuditAsync(
    Order order,
    CancellationToken cancellationToken = default)
{
    using var connection = _connectionFactory.CreateConnection();


    await ((SqlConnection)connection).OpenAsync(cancellationToken);

    using var transaction = connection.BeginTransaction();

    try
    {
        const string insertOrder = """
            INSERT INTO Orders
                (CustomerName, TotalAmount, CreatedUtc)
            VALUES
                (@CustomerName, @TotalAmount, @CreatedUtc);

            SELECT CAST(SCOPE_IDENTITY() AS INT);
            """;

        var orderId = await connection.ExecuteScalarAsync<int>(
            new CommandDefinition(
                insertOrder,
                new
                {
                    order.CustomerName,
                    order.TotalAmount,
                    order.CreatedUtc
                },
                transaction: transaction,
                cancellationToken: cancellationToken));

        const string auditSql = """
            INSERT INTO OrderAudit (OrderId, Action, CreatedUtc)
            VALUES (@OrderId, @Action, @CreatedUtc);
            """;

        await connection.ExecuteAsync(
            new CommandDefinition(
                auditSql,
                new
                {
                    OrderId = orderId,
                    Action = "Created",
                    CreatedUtc = DateTime.UtcNow
                },
                transaction: transaction,
                cancellationToken: cancellationToken));

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

In a production implementation, it is worth encapsulating transaction management in an appropriate unit-of-work abstraction when several repositories participate in the same transaction.

15. Resilience: Why Database Calls Need Special Treatment

A database is an external dependency from the application's perspective. Even when the application and database are operated by the same organization, failures can occur because of network interruptions, failovers, connection-pool exhaustion, resource pressure, timeouts, deadlocks, maintenance, or transient infrastructure problems.

Modern .NET provides resilience abstractions through Microsoft.Extensions.Resilience, built on Polly. Microsoft describes resilience as the ability of an application to recover from transient failures and continue operating. Supported strategies include retry, circuit breaker, timeout, rate limiting, fallback and hedging.

16. Retry Carefully

Retries can help with transient failures, but retrying every database exception is dangerous. A retry may turn a temporary database problem into a much larger traffic spike.

A sensible retry strategy should consider:

• Which exceptions are genuinely transient.
• How many attempts are allowed.
• How much delay occurs between attempts.
• Whether exponential backoff is used.
• Whether jitter is added to avoid synchronized retries.
• Whether the operation is safe to execute more than once.

For example, retrying a read that failed because of a transient connection problem is fundamentally different from retrying a payment or an insert that may already have committed.

17. Idempotency and Database Retries

A robust write operation should have a way to determine whether it has already been processed.

Common techniques include:

• Unique business keys.
• Idempotency keys supplied by the caller.
• Unique database constraints.
• Upsert-style operations where appropriate.
• Recording a request or command identifier.
• Designing commands so repeated execution produces the same result.

Resilience therefore is not simply "add retry." It is a combination of failure classification, bounded retries, timeouts, idempotency, observability and appropriate degradation.

18. Timeouts

A timeout prevents a request from waiting indefinitely for a dependency. Application-level request timeouts and database command timeouts should be considered separately.

Dapper exposes command timeout functionality through its command APIs, allowing database operations to have explicit limits.

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

var result = await connection.QueryAsync(command);

A timeout should normally result in a controlled failure rather than an unlimited wait. However, timeouts should be selected according to the operation: a simple lookup and a deliberately long-running reporting query should not necessarily have the same limit.

19. Circuit Breakers

A circuit breaker prevents an application from continuously hammering a dependency that is already failing.

Conceptually, a circuit has three states:

Circuit breakers are particularly useful when a dependency outage could otherwise cause request queues, thread starvation, connection exhaustion or cascading failure.

20. Resilience Pipelines in Modern .NET

Modern .NET provides Microsoft.Extensions.Resilience for resilience pipelines and Microsoft.Extensions.Http.Resilience for HTTP clients. These packages build on Polly. Microsoft currently recommends these packages rather than the older Microsoft.Extensions.Http.Polly package, which is deprecated.

The HTTP package is particularly relevant when an ASP.NET Core application calls another service. Database resilience, however, requires more care because database operations can have transaction and idempotency semantics that ordinary HTTP GET requests often do not.

A useful architecture is to keep resilience policy close to the infrastructure boundary rather than spreading retry logic through controllers and business services.

21. Avoid the "Retry Everything" Anti-Pattern

Consider this hypothetical flow:

If multiple layers independently retry, three application-level attempts can become nine or more actual database attempts. This can amplify an outage.

Prefer one clearly defined resilience boundary and document which failures are retryable.

22. Health Checks

ASP.NET Core includes health-check infrastructure that can expose application health to load balancers, orchestrators and monitoring systems. Health checks can also test dependencies such as databases.

A basic endpoint is:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddHealthChecks();

var app = builder.Build();

app.MapHealthChecks("/health");

app.Run();

For database readiness, use an appropriately lightweight check. Microsoft notes that if a query is required, it should be quick—for example, SELECT 1—and that simply establishing a successful connection may be sufficient in many cases.

In containerized environments, distinguish between liveness and readiness. A temporarily unavailable database may mean that the application is not ready to serve traffic, but it does not necessarily mean the application process should be restarted.

23. SOLID Principles with Dapper

Dapper itself does not enforce an architecture. It is therefore easy to create a codebase where controllers contain SQL, repositories contain business rules and every class directly constructs SQL connections.

SOLID principles help prevent this deterioration.

Single Responsibility Principle

A class should have one primary reason to change.

For example:

• Controllers handle HTTP concerns.
• Application services handle use cases.
• Repositories handle persistence.
• Connection factories handle connection construction.
• Resilience components handle transient-failure policies.

Avoid a repository that validates HTTP request models, writes SQL, sends emails and publishes messages.

Open/Closed Principle

Software should be open for extension without requiring unnecessary modification to stable code.

For example, if your application depends on IOrderRepository, you can create a second implementation for an integration test or alternative persistence mechanism without rewriting the application service.

Liskov Substitution Principle

Implementations of an abstraction should remain valid substitutes for that abstraction.

If IOrderRepository.GetByIdAsync promises that a missing order returns null, an implementation should not unexpectedly throw a "not found" exception for the same condition.

Interface Segregation Principle

Avoid enormous repository interfaces such as:

public interface IRepository
{
    // 50 unrelated methods...
}

Prefer interfaces that represent meaningful capabilities or use cases:

public interface IOrderReader
{
    Task GetByIdAsync(
    int id,
    CancellationToken cancellationToken = default);
}

public interface IOrderWriter
{
    Task CreateAsync(
    Order order,
    CancellationToken cancellationToken = default);
}

Dependency Inversion Principle

High-level application logic should depend on abstractions rather than concrete infrastructure.

Instead of:

public class OrderService
{
    private readonly SqlConnection _connection;
}

prefer:

public class OrderService
{
    private readonly IOrderRepository _repository;
}

ASP.NET Core's dependency injection container then supplies the concrete implementation. Microsoft's ASP.NET Core guidance explicitly promotes dependency injection as a core mechanism for achieving inversion of control.

24. Keep SQL Close to the Persistence Boundary

One common mistake is to make every layer aware of SQL:

Controller
↓
SQL string
↓
Dapper
↓
Database

This makes SQL changes ripple through the application.

A cleaner arrangement is:

Controller
↓
Application Service
↓
Repository Interface
↓
Dapper Repository
↓
SQL

The application layer knows what it wants; the infrastructure layer knows how to obtain it.

25. Dynamic Queries with DynamicParameters

Search endpoints sometimes require optional filters. Dapper provides DynamicParameters for constructing parameterized queries dynamically.

var predicates = new List<string>();
var parameters = new DynamicParameters();

if (!string.IsNullOrWhiteSpace(customerName))
{
    predicates.Add("CustomerName = @CustomerName");
    parameters.Add("CustomerName", customerName);
}

if (minimumAmount.HasValue)
{
    predicates.Add("TotalAmount >= @MinimumAmount");
    parameters.Add("MinimumAmount", minimumAmount.Value);
}

var sql = """
SELECT
Id,
CustomerName,
TotalAmount,
CreatedUtc
FROM Orders
""";

if (predicates.Count > 0)
{
    sql += " WHERE " + string.Join(" AND ", predicates);
}

sql += " ORDER BY CreatedUtc DESC;";

var orders = await connection.QueryAsync(
sql,
parameters);

Notice that values are still parameters. The dynamically assembled portion contains only SQL fragments controlled by the application.

26. Pagination

Never assume that a table will remain small enough for an unbounded SELECT.

For SQL Server, pagination can use OFFSET and FETCH:

const string sql = """
SELECT
    Id,
    CustomerName,
    TotalAmount,
    CreatedUtc
FROM Orders
ORDER BY Id DESC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;
""";

var orders = await connection.QueryAsync(
sql,
new
{
    Offset = (page - 1) * pageSize,
    PageSize = pageSize
});

Pagination should also have a maximum page size enforced by the application. This prevents a client from requesting millions of rows in a single API call.

27. Performance Considerations

Dapper can be fast, but the database remains the most important part of the system.

Focus on:

• Appropriate indexes.
• Efficient SQL execution plans.
• Returning only required columns.
• Pagination for large datasets.
• Reasonable command timeouts.
• Connection pooling.
• Short transactions.
• Avoiding unnecessary round trips.
• Streaming or buffered results according to the use case.

Do not assume that replacing an ORM with Dapper automatically fixes database performance. A poorly indexed query remains poorly indexed regardless of which .NET library executes it.

28. Observability

Resilience without observability can hide problems rather than solve them.

Useful metrics and logs include:

• Database operation duration.
• Database timeout count.
• Transient failure count.
• Retry count.
• Circuit-breaker state changes.
• Connection-pool pressure.
• Transaction duration.
• Slow query frequency.

29. Testing the Repository

A repository's SQL is usually best tested with integration tests against a real database or an appropriate disposable database environment. Mocking Dapper itself often provides little confidence that the SQL actually works.

Application services, on the other hand, can usually be unit tested against an IOrderRepository mock or fake.

30. Common Anti-Patterns to Avoid

1. Creating connections in controllers

Controllers should not need to know how SQL Server connections are constructed.

2. Hard-coding connection strings

Keep connection configuration outside the application code and protect production credentials.

3. String-concatenating user input into SQL

Use parameters and allow-lists for dynamic SQL components.

4. Retrying every exception

Only retry failures that are known to be transient and safe to retry.

5. Putting business logic in repositories

Repositories should primarily translate application persistence requests into database operations.

6. Putting SQL in controllers

Keep persistence details behind repository or query abstractions.

7. One giant repository

Large interfaces are difficult to understand, test and evolve.

8. Unbounded queries

Use pagination and explicit projections for potentially large result sets.

9. Ignoring cancellation

Propagate request cancellation into database commands.

10. Treating resilience as an afterthought

Design retries, timeouts, idempotency and health behavior as part of the dependency contract.

31. A Practical Production Blueprint

A production-oriented Dapper application can follow this pattern:

32. Recommended Design Rules

33. Complete Dependency Registration Example

Bringing the main pieces together, a simplified Program.cs might look like this:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();

builder.Services.AddSingleton();

builder.Services.AddScoped();
builder.Services.AddScoped();

builder.Services.AddHealthChecks();

var app = builder.Build();

app.MapControllers();
app.MapHealthChecks("/health");

app.Run();

This gives the application a clear separation between web delivery, application behavior, persistence abstractions and SQL Server infrastructure.

34. Final Perspective

Dapper works particularly well in ASP.NET Core when an application needs direct control over SQL without adopting the complexity of a full ORM. Its simplicity, however, means that architectural discipline remains the developer's responsibility.

The most maintainable approach is not simply to inject Dapper into a controller. Instead, establish clear boundaries: controllers handle HTTP, application services handle use cases, repository abstractions represent persistence needs, and Dapper remains an infrastructure implementation detail.

Resilience should be designed around the actual semantics of database operations. Timeouts, carefully selected retries, circuit breakers, health checks, idempotency and observability complement one another. Modern .NET provides resilience infrastructure built around Polly, while ASP.NET Core provides dependency injection and health-check facilities.

Finally, SOLID principles provide a useful test for architectural quality. If a change to SQL requires modifications to controllers, if a business-rule change requires database infrastructure changes, or if every test requires a running database, the boundaries may be too tightly coupled.

A well-designed Dapper application therefore combines the strengths of explicit SQL with disciplined architecture: parameterized queries for security, asynchronous operations for scalability, transactions for consistency, resilience for availability, dependency injection for composition, and SOLID principles for maintainability.

Sources and Further Reading

• Dapper official repository and documentation
• Dapper asynchronous query implementation
• Microsoft ASP.NET Core dependency injection
• Microsoft ASP.NET Core configuration
• Microsoft .NET resilience guidance
• Microsoft HTTP resilience guidance
• Microsoft.Data.SqlClient on NuGet
• Microsoft ASP.NET Core health checks
• OWASP SQL Injection Prevention