Published / updated29 Aug 2026

Data Access with Dapper and SQL Server

Before you start

You need: REST design (Article 08) and Dapper (Track 07).

Time: about 50 minutes, plus the practice.

Learning objective

Connect an ASP.NET Core application to SQL Server through a service and repository layer, with tenant isolation and transaction boundaries in the right places.

Topics

• The layering
• Registering the connection factory
• Repositories with Dapper
• Services and business rules
• Transaction boundaries
• Stored procedures
• Async and cancellation
• Translating database errors
• Testing

The layering

Controller  →  IStudentService  →  IStudentRepository  →  SQL Server

Layer	Knows about	Never knows about
Controller	HTTP, DTOs, claims	SQL, connection strings
Service	Business rules, repositories, transactions	HTTP, HttpContext, IActionResult
Repository	SQL, parameters, mapping	Business rules, HTTP

A service must not take HttpContext or return IActionResult. Both tie business logic to the web layer, so it cannot be reused by a background job or a console tool and cannot be tested without a request.

The tenant id is passed as a parameter, resolved from claims by the controller:

public static class ClaimsPrincipalExtensions
{
    public static int GetSchoolId(this ClaimsPrincipal user)
    {
        var claim = user.FindFirst("SchoolId")
            ?? throw new InvalidOperationException("The SchoolId claim is missing.");

        return int.Parse(claim.Value);
    }
}

[HttpGet]
public async Task<ActionResult<PagedResult<StudentDto>>> Search(
    [FromQuery] StudentQueryParameters query, CancellationToken ct)
{
    var result = await _studentService.SearchAsync(User.GetSchoolId(), query, ct);

    return Ok(result);
}

SchoolId comes from the token, never from the request. A schoolId query parameter is a request from the client to choose which tenant's data to read.

Registering the connection factory

public interface ISqlConnectionFactory
{
    SqlConnection Create();
}

public sealed class SqlConnectionFactory : ISqlConnectionFactory
{
    private readonly string _connectionString;

    public SqlConnectionFactory(string connectionString)
    {
        _connectionString = connectionString;
    }

    public SqlConnection Create() => new SqlConnection(_connectionString);
}

public static class DataServiceCollectionExtensions
{
    public static IServiceCollection AddSchoolData(
        this IServiceCollection services, IConfiguration configuration)
    {
        var connectionString = configuration.GetConnectionString("SchoolDb")
            ?? throw new InvalidOperationException(
                "Connection string 'SchoolDb' is not configured. " +
                "Set it via user secrets in development or ConnectionStrings__SchoolDb in production.");

        services.AddSingleton<ISqlConnectionFactory>(new SqlConnectionFactory(connectionString));

        services.AddScoped<IStudentRepository, StudentRepository>();
        services.AddScoped<IFeeRepository, FeeRepository>();
        services.AddScoped<IExamRepository, ExamRepository>();

        return services;
    }
}

builder.Services.AddSchoolData(builder.Configuration);
builder.Services.AddScoped<IStudentService, StudentService>();

The factory is a singleton because it holds only a string and creates a new SqlConnection per call. Repositories are scoped — one per request, released with it.

The startup check on the connection string means a misconfigured deployment fails immediately with a message naming the setting, rather than on the first request that touches the database.

Repositories

public sealed class StudentRepository : IStudentRepository
{
    private readonly ISqlConnectionFactory _connectionFactory;

    public StudentRepository(ISqlConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }

    public async Task<PagedResult<Student>> SearchAsync(
        int schoolId, StudentQueryParameters query, CancellationToken ct)
    {
        const string sql = @"
            SELECT COUNT(*)
            FROM   dbo.Student AS s
            WHERE  s.SchoolId = @SchoolId
              AND  s.Status <> 1
              AND  (@Term IS NULL OR s.Name LIKE @Like OR s.RollNumber LIKE @Like)
              AND  (@ClassName IS NULL OR s.ClassName = @ClassName);

            SELECT   s.Id, s.PublicId, s.SchoolId, s.Name, s.RollNumber, s.ClassName,
                     s.Section, s.DateOfBirth, s.ParentName, s.ParentPhone, s.Address, s.Status
            FROM     dbo.Student AS s
            WHERE    s.SchoolId = @SchoolId
              AND    s.Status <> 1
              AND    (@Term IS NULL OR s.Name LIKE @Like OR s.RollNumber LIKE @Like)
              AND    (@ClassName IS NULL OR s.ClassName = @ClassName)
            ORDER BY s.ClassName, s.Section, s.Name, s.Id
            OFFSET   @Skip ROWS FETCH NEXT @PageSize ROWS ONLY;";

        var parameters = new DynamicParameters();
        parameters.Add("@SchoolId", schoolId, DbType.Int32);
        parameters.Add("@Term", query.Term, DbType.String, size: 100);
        parameters.Add("@Like", string.IsNullOrWhiteSpace(query.Term) ? null : $"%{query.Term}%",
                       DbType.String, size: 102);
        parameters.Add("@ClassName", query.ClassName, DbType.String, size: 10);
        parameters.Add("@Skip", (query.Page - 1) * query.PageSize, DbType.Int32);
        parameters.Add("@PageSize", query.PageSize, DbType.Int32);

        using var connection = _connectionFactory.Create();

        using var grid = await connection.QueryMultipleAsync(
            new CommandDefinition(sql, parameters, cancellationToken: ct));

        var totalCount = await grid.ReadSingleAsync<int>();
        var items = (await grid.ReadAsync<Student>()).ToList();

        return new PagedResult<Student>
        {
            Items = items,
            TotalCount = totalCount,
            Page = query.Page,
            PageSize = query.PageSize
        };
    }
}

Four things this gets right:

• SchoolId in both WHERE clauses. Not just the list — every query in the class.
• QueryMultiple returns the count and the page in one round trip.
• A deterministic ORDER BY ending in s.Id. Without a unique tiebreaker, rows repeat across pages while others are never shown.
• CommandDefinition with the token, so an abandoned request stops its database work.

public async Task<Student?> GetByPublicIdAsync(int schoolId, Guid publicId, CancellationToken ct)
{
    const string sql = @"
        SELECT s.Id, s.PublicId, s.SchoolId, s.Name, s.RollNumber, s.ClassName,
               s.Section, s.DateOfBirth, s.ParentName, s.ParentPhone, s.Address, s.Status
        FROM   dbo.Student AS s
        WHERE  s.SchoolId = @SchoolId AND s.PublicId = @PublicId;";

    using var connection = _connectionFactory.Create();

    return await connection.QuerySingleOrDefaultAsync<Student>(
        new CommandDefinition(sql, new { SchoolId = schoolId, PublicId = publicId },
                              cancellationToken: ct));
}

QuerySingleOrDefault rather than QueryFirstOrDefault: a lookup by unique key returning two rows is a data defect, and Single throws so you find out. First would silently pick one.

Services

public sealed class StudentService : IStudentService
{
    private readonly IStudentRepository _repository;
    private readonly ILogger<StudentService> _logger;

    public StudentService(IStudentRepository repository, ILogger<StudentService> logger)
    {
        _repository = repository;
        _logger = logger;
    }

    public async Task<PagedResult<StudentDto>> SearchAsync(
        int schoolId, StudentQueryParameters query, CancellationToken ct)
    {
        var result = await _repository.SearchAsync(schoolId, query, ct);

        return new PagedResult<StudentDto>
        {
            Items = result.Items.Select(MapToDto).ToList(),
            TotalCount = result.TotalCount,
            Page = result.Page,
            PageSize = result.PageSize
        };
    }

    public async Task<StudentDto> CreateAsync(
        int schoolId, StudentCreateRequest request, CancellationToken ct)
    {
        if (await _repository.RollNumberExistsAsync(schoolId, request.RollNumber, null, ct))
        {
            throw new DuplicateRollNumberException(request.RollNumber);
        }

        var student = new Student
        {
            SchoolId = schoolId,                       // from the claim
            Status = StudentStatus.Active,             // server-decided
            Name = request.Name.Trim(),
            RollNumber = request.RollNumber,
            ClassName = request.ClassName,
            Section = request.Section,
            DateOfBirth = request.DateOfBirth!.Value,
            ParentName = request.ParentName.Trim(),
            ParentPhone = request.ParentPhone,
            Address = request.Address?.Trim()
        };

        student.PublicId = await _repository.CreateAsync(student, ct);

        _logger.LogInformation(
            "Student {RollNumber} created for school {SchoolId}", student.RollNumber, schoolId);

        return MapToDto(student);
    }
}

The service maps entity to DTO, applies business rules and decides server-controlled values. The controller never sees a Student, and the repository never sees a StudentDto.

Transaction boundaries

The service owns transactions; the repository does not.

// Wrong — this method cannot be composed with anything else atomically
public async Task<Guid> CreateAsync(Student student, CancellationToken ct)
{
    using var connection = _connectionFactory.Create();
    await connection.OpenAsync(ct);
    using var transaction = await connection.BeginTransactionAsync(ct);
    // ...
}

Enrolling a student means creating the student and their fee account and an audit row, atomically. If each repository opens its own transaction, a failure on the second leaves the first committed.

public interface IStudentRepository
{
    Task<Guid> CreateAsync(Student student, CancellationToken ct);

    Task<Guid> CreateAsync(
        SqlConnection connection, SqlTransaction transaction, Student student, CancellationToken ct);
}

public sealed class EnrolmentService : IEnrolmentService
{
    private readonly ISqlConnectionFactory _connectionFactory;
    private readonly IStudentRepository _studentRepository;
    private readonly IFeeRepository _feeRepository;
    private readonly IAuditRepository _auditRepository;

    public async Task<Guid> EnrolAsync(
        int schoolId, EnrolmentRequest request, string performedBy, CancellationToken ct)
    {
        // Validate BEFORE opening the transaction — no locks taken on a rejected request
        if (await _studentRepository.RollNumberExistsAsync(schoolId, request.RollNumber, null, ct))
        {
            throw new DuplicateRollNumberException(request.RollNumber);
        }

        using var connection = _connectionFactory.Create();
        await connection.OpenAsync(ct);

        using var transaction = (SqlTransaction)await connection.BeginTransactionAsync(ct);

        try
        {
            var student = BuildStudent(schoolId, request);

            var publicId = await _studentRepository.CreateAsync(
                connection, transaction, student, ct);

            await _feeRepository.CreateAccountAsync(
                connection, transaction,
                new FeeAccount
                {
                    SchoolId = schoolId,
                    StudentId = student.Id,
                    AcademicYear = request.AcademicYear,
                    TotalFees = request.TotalFees,
                    DueDate = DateTime.UtcNow.AddMonths(1)
                }, ct);

            await _auditRepository.WriteAsync(
                connection, transaction,
                new AuditLog
                {
                    SchoolId = schoolId,
                    Action = "EnrolStudent",
                    EntityName = nameof(Student),
                    EntityId = student.Id,
                    ChangedBy = performedBy,
                    ChangedAt = DateTime.UtcNow
                }, ct);

            await transaction.CommitAsync(ct);

            return publicId;
        }
        catch
        {
            await transaction.RollbackAsync(ct);
            throw;
        }
    }
}

Pass the transaction to every call inside it. Omitting it on one means that statement runs outside the transaction and is not rolled back — a subtle bug, because the code looks right and only fails when something else throws.

Validation runs before BeginTransaction so a rejected enrolment holds no locks. The database unique constraint still wins any race between two simultaneous callers.

Stored procedures

public async Task<FeePaymentResult> RecordPaymentAsync(
    int schoolId, FeePaymentRequest request, string collectedBy, CancellationToken ct)
{
    var parameters = new DynamicParameters();
    parameters.Add("@SchoolId", schoolId, DbType.Int32);
    parameters.Add("@FeeAccountId", request.FeeAccountId, DbType.Int32);
    parameters.Add("@Amount", request.Amount, DbType.Decimal, precision: 18, scale: 2);
    parameters.Add("@PaymentMode", (byte)request.PaymentMode, DbType.Byte);
    parameters.Add("@CollectedBy", collectedBy, DbType.String, size: 100);

    parameters.Add("@NewPaymentId", dbType: DbType.Int32,
                   direction: ParameterDirection.Output);
    parameters.Add("@ReceiptNumber", dbType: DbType.String, size: 30,
                   direction: ParameterDirection.Output);

    using var connection = _connectionFactory.Create();

    await connection.ExecuteAsync(new CommandDefinition(
        "dbo.usp_RecordFeePayment",
        parameters,
        commandType: CommandType.StoredProcedure,
        cancellationToken: ct));

    return new FeePaymentResult
    {
        PaymentId = parameters.Get<int>("@NewPaymentId"),
        ReceiptNumber = parameters.Get<string>("@ReceiptNumber")
    };
}

Three details:

• commandType: CommandType.StoredProcedure. Omit it and Dapper sends the name as a literal statement, producing "Incorrect syntax near 'usp_RecordFeePayment'".
• precision and scale on the money parameter. Without them 8000.50 can arrive rounded.
• Output parameters are read after the command completes, never before.

The procedure keeps the payment insert and the balance update in one transaction, so atomicity is guaranteed server-side even if a caller forgets.

Async and cancellation

[HttpGet]
public async Task<ActionResult<PagedResult<StudentDto>>> Search(
    [FromQuery] StudentQueryParameters query, CancellationToken ct)
{
    return Ok(await _studentService.SearchAsync(User.GetSchoolId(), query, ct));
}

ASP.NET Core supplies the CancellationToken from HttpContext.RequestAborted automatically — just declare the parameter. Pass it down to the repository and into CommandDefinition.

An abandoned request then stops its database work rather than running to completion for a client that has gone. On a busy API that measurably reduces load.

Async all the way down. .Result or .Wait() on an async call wastes a thread and can deadlock; the correct fix is to make the caller async, not to block.

Async is about throughput, not latency: one query is not faster, but the server handles more concurrent requests because threads are not blocked waiting on I/O.

Translating database errors

public async Task<Guid> CreateAsync(Student student, CancellationToken ct)
{
    student.PublicId = Guid.NewGuid();

    using var connection = _connectionFactory.Create();

    try
    {
        await connection.ExecuteAsync(new CommandDefinition(sql, student, cancellationToken: ct));
    }
    catch (SqlException ex) when (ex.Number is 2627 or 2601)
    {
        throw new DuplicateRollNumberException(student.RollNumber, ex);
    }

    return student.PublicId;
}

Catch by Number, never by parsing the message — message text is localised and changes between versions.

public class DuplicateRollNumberException : Exception
{
    public string RollNumber { get; }

    public DuplicateRollNumberException(string rollNumber, Exception? inner = null)
        : base($"Roll number '{rollNumber}' is already in use.", inner)
    {
        RollNumber = rollNumber;
    }
}

catch (DuplicateRollNumberException ex)
{
    ModelState.AddModelError(nameof(request.RollNumber), ex.Message);
    return ValidationProblem(ModelState);
}

The controller catches a domain exception it understands. A SqlException reaching the controller means the web layer must know SQL Server error numbers — and the next error number nobody handles becomes a 500.

Numbers worth translating: 2627 and 2601 (duplicate) → 409; 547 (foreign key or check) → 400 or 409; 1205 (deadlock) → retry; -2 (timeout) → 503.

Testing

Unit-test the service with a fake repository — no database:

[Fact]
public async Task CreateAsync_DuplicateRollNumber_Throws()
{
    var repository = new FakeStudentRepository(existingRollNumbers: new[] { "NCA-2024-0012" });
    var service = new StudentService(repository, NullLogger<StudentService>.Instance);

    await Assert.ThrowsAsync<DuplicateRollNumberException>(
        () => service.CreateAsync(1, ValidRequest(), CancellationToken.None));
}

Integration-test the repository against a real database. Mapping bugs, missing tenant filters and SQL syntax only appear against SQL Server:

[Fact]
public async Task GetByPublicIdAsync_OtherSchoolsStudent_ReturnsNull()
{
    var publicId = await SeedStudentAsync(schoolId: 2);

    var student = await _repository.GetByPublicIdAsync(schoolId: 1, publicId, CancellationToken.None);

    Assert.Null(student);
}

That test is the most valuable one in the suite. It fails the moment someone writes a query without the tenant filter — and nothing else catches that.

Run each integration test inside a transaction that is rolled back afterwards, so tests do not depend on each other's data.

Errors you will hit

What you see	Cause	Fix
Unable to resolve service for type 'IStudentRepository'	Not registered	AddScoped it
Connection pool exhausted under load	Connections not disposed	using in every method
A user sees another school's data	schoolId taken from the request	Read the claim
Business rules living in the controller	Layers collapsed	Move them to the service
Tests need a database to run	Service depends on the concrete repository	Depend on the interface

schoolId must come from User.FindFirst, never a parameter. An endpoint accepting ?schoolId= returns a 200 with another school's records and logs nothing.

Common mistakes

• SchoolId taken from the request instead of the claim
• The tenant filter missing from one query
• Transactions opened inside repositories, so operations cannot compose
• A repository call inside a transaction not given the transaction
• Validation inside the transaction rather than before it
• SqlException reaching the controller
• Matching on exception message text rather than Number
• Missing commandType: CommandType.StoredProcedure
• No precision and scale on a money parameter
• Reading an output parameter before the command completes
• No CancellationToken, or accepting one and not passing it on
• .Result or .Wait() on an async call
• QueryFirstOrDefault on a unique key, hiding duplicates
• Non-deterministic ORDER BY with paging
• No integration test proving tenant isolation

Practice

The course assignments are build a service layer and add Dapper stored procedure calls.

1. Build ISqlConnectionFactory, IStudentRepository and IStudentService, registered with correct lifetimes.
2. Add the startup connection-string check. Remove the setting and confirm the failure names it.
3. Implement paged search with QueryMultiple. Confirm one round trip in SQL Profiler.
4. Remove the s.Id tiebreaker from ORDER BY. Page through data with duplicate names and find a row that appears twice.
5. Confirm SchoolId is in every query's WHERE. Remove it from one and write a test that now fails.
6. Implement EnrolAsync creating a student, fee account and audit row in one transaction.
7. Force a failure after the student insert. Confirm none of the three rows exist.
8. Omit the transaction argument on the fee-account call only. Repeat step 7 and confirm the fee account survives the rollback.
9. Call usp_RecordFeePayment with output parameters. Omit commandType and record the error.
10. Read an output parameter before ExecuteAsync completes. Record what you get.
11. Insert a duplicate roll number. Confirm SqlException.Number is 2627 and that the controller receives DuplicateRollNumberException.
12. Add a CancellationToken end to end. Cancel a slow query mid-flight and confirm it stops in SQL Profiler.
13. Write the tenant-isolation integration test. Remove the filter and confirm it fails.
14. Write a service unit test with a fake repository, with no database involved.

Exercises 8 and 13 are the two that matter most — a silent partial commit and an untested tenant boundary.

You can now

• Wire controller, service and repository with DI
• Take schoolId from the token in every endpoint
• Keep business rules out of the controller
• Dispose connections correctly under load
• Make the service testable without a database

Review questions

1. Why must SchoolId come from the claim rather than the request?
2. Why should a repository method not open its own transaction?
3. What happens when one call inside a transaction is not given the transaction?
4. Why translate SqlException into a domain exception in the repository?

Next: Error handling and logging