Persistence with Dapper

A repository owns the SQL and connection lifetime. Application code asks it to find or store a customer without passing an open connection around. Dapper maps parameters and rows; the database still enforces storage constraints.

This example uses the Customer model from validation, Dapper and SQL Server through Microsoft.Data.SqlClient. Reference those packages in the .NET host project. import Global.Dapper brings its extension methods into lookup.

Database and parameter model

Provision the table through the application's normal migration process:

CREATE TABLE dbo.Customers
(
    Id uniqueidentifier NOT NULL PRIMARY KEY,
    Name nvarchar(100) NOT NULL,
    Email nvarchar(320) NOT NULL,
    IsActive bit NOT NULL
);

Name length agrees with the input rule. Email storage length is a separate database constraint; adjust the application's input policy if it must guarantee that every validated address fits. The primary key rejects repeated identifiers. This example does not equate customer identity with an email address.

Dapper's parameter object is a normal class. CustomerStorageException gives the HTTP boundary a specific infrastructure failure to handle.

import Global.System

public class CustomerKey
    public readonly Id as Guid

    public constructor(id as Guid)
        self.Id = arguments.id
    end constructor
end class

public class CustomerStorageException
    inherits Exception

    public constructor(message as String, cause as Exception)
        calls base(message, cause)
    end constructor
end class

Finding and storing customers

The connection factory returns a new, unopened DbConnection on each invocation. The repository owns each returned connection. Its dependency on the factory is borrowed; it does not own a connection supplied elsewhere or a DI container.

import Global.System
import Global.System.Data.Common
import Global.Dapper

public class CustomerRepository
    private readonly connectionFactory as Func <of DbConnection>

    public constructor(factory as Func <of DbConnection>)
        require factory is not nothing
        connectionFactory = factory
    end constructor

    public async action FindAsync(id as Guid)
        returns Customer
        require id <> Guid.Empty
        Cancellation.ThrowIfCanceled()

        try
            using connection = connectionFactory.Invoke()
                await connection.OpenAsync(Cancellation.Token)
                let command = new CommandDefinition with
                    commandText: "SELECT Id, Name, Email, IsActive FROM dbo.Customers WHERE Id = @Id"
                    parameters: new CustomerKey(id)
                    cancellationToken: Cancellation.Token
                end with
                return await connection.QuerySingleOrDefaultAsync <of Customer>(command)
            end using
        catch DbException
            throw new CustomerStorageException("Customer lookup failed", ex)
        end try
    end action

    public async action AddAsync(customer as Customer)
        require customer is not nothing
        require customer.Id <> Guid.Empty
        Cancellation.ThrowIfCanceled()

        try
            using connection = connectionFactory.Invoke()
                await connection.OpenAsync(Cancellation.Token)
                let command = new CommandDefinition with
                    commandText: "INSERT INTO dbo.Customers (Id, Name, Email, IsActive) VALUES (@Id, @Name, @Email, @IsActive)"
                    parameters: customer
                    cancellationToken: Cancellation.Token
                end with
                let affected = await connection.ExecuteAsync(command)
                if affected <> 1 then
                    throw new InvalidOperationException("Insert did not affect exactly one customer")
                end if
            end using
        catch DbException
            throw new CustomerStorageException("Customer insertion failed", ex)
        end try
    end action
end class

The explicit async declarations establish asynchronous CLR contracts. Imported OpenAsync, QuerySingleOrDefaultAsync and ExecuteAsync keep their familiar names. Ordinary async calls here are awaited in sequence: open first, then execute, then dispose. The logical FindAsync result is a Customer or nothing when no row matches.

The command's with block supplies constructor inputs. It is not an object initializer that mutates an already constructed command. Dapper receives the token explicitly, so overload selection does not have to infer which cancellation-aware API is intended.

A cancellation exception is allowed to propagate. The repository wraps database exceptions and retains their cause; it does not turn a cancelled request into a missing customer. A SQL constraint failure is also a database exception. The HTTP boundary should not assume that every CustomerStorageException is transient or automatically retryable.

The bounded using owns the new connection. In this async action, the static DbConnection contract exposes IAsyncDisposable, so cleanup is awaited before leaving the scope; a deliberately IDisposable-only view would instead use synchronous cleanup. The returned customer is an independent mapped object. Do not return a live reader or deferred query whose connection has already been disposed.

A single INSERT has its normal database statement atomicity. A future operation that updates several tables must introduce an explicit transaction and a failure policy; concatenating more calls into this action does not establish a transaction.

Supplying the factory

DirectSharp can supply the ordinary provider API directly. This fragment belongs to the application's composition action, with connectionString supplied by its configuration:

import Global.System
import Global.System.Data.Common
import Global.Microsoft.Data.SqlClient

action OpenConnection()
    returns DbConnection
    return new SqlConnection(connectionString)
end action

let connectionFactory as Func <of DbConnection> = action OpenConnection
let repository = new CustomerRepository(connectionFactory)

Read connectionString from normal host configuration and secrets storage. The host retains the factory; the repository creates and disposes connections per operation. With SQL Server's normal pooling, disposal returns a healthy connection to its pool rather than keeping one shared connection in the repository.

For tests, supply a controlled connection factory or run against an isolated SQL Server test database. Check missing rows, round trips, duplicate identifiers, cancellation and failed opens. A successful Dapper mapping check is separate from testing the SQL Server schema and provider.

Continue to the web API for request cancellation, DI registration and error responses, or .NET integration for the corresponding EF Core approach.

Previous: the application service. Next: the HTTP host.