Unit of Work with Dapper + SQL Server: Stop partial writes and make transactions boring

 When you build APIs with Dapper, you get performance and control — but you also take responsibility for things that full ORMs often hide, especially transaction management. The result in many codebases is the same: multi-step operations that sometimes fail halfway through, leaving partial data in the database.

The Unit of Work (UoW) pattern is a simple, practical fix. It gives your application an explicit, repeatable way to say:

These changes belong together. Commit them as one, or roll them all back.

This article explains what Unit of Work is, why it’s valuable with Dapper, common anti-patterns, and a complete example you can adapt.

What is Unit of Work?

Unit of Work is a design pattern that:

• Defines a transaction boundary around a business operation
• Ensures multiple database writes across repositories are atomic
• Centralizes Begin/Commit/Rollback so you don’t duplicate transaction code everywhere

With Dapper, “Unit of Work” usually means: a single shared SqlConnection + SqlTransaction that multiple repositories use for the duration of one use-case.

Why it matters (especially with Dapper)

Imagine a “checkout” workflow:

• Insert order
• Reserve inventory
• Insert audit record

If the inventory reservation fails, do you want an “order” row sitting in the database with no reservation? Usually: no.

Unit of Work gives you:

• Atomicity: all writes succeed or none do
• Consistency: repositories share the same connection + transaction
• Cleaner code: transaction handling is in one place
• Better testability: you can test business behavior without HTTP plumbing

The common anti-pattern (what goes wrong)

A frequent Dapper mistake looks like this:

• Each repository method opens its own connection
• Each method executes SQL independently
• No transaction spans the whole operation

What can happen:

• Step 1 succeeds and writes to DB
• Step 2 fails (constraint, timeout, network hiccup)
• The API returns an error, but the database now contains half the story

Even worse: teams try to patch it by sprinkling BeginTransaction() in random repositories — leading to “transaction soup”, nested transaction confusion, and hard-to-debug behavior.

Principle: repositories should execute SQL; the service/use-case should own the business transaction boundary.

A clean Unit of Work implementation for SQL Server

Below is a complete, publishable example using Dapper + SQL Server with Microsoft.Data.SqlClient.

1) The Unit of Work contract

using System.Data;

public interface IUnitOfWork : IDisposable
{
    IDbConnection Connection { get; }
    IDbTransaction Transaction { get; }

    void Begin();
    void Commit();
    void Rollback();
}

2) SQL Server Unit of Work implementation

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

public sealed class SqlUnitOfWork : IUnitOfWork
{
    private readonly string _connectionString;
    private IDbConnection? _connection;
    private IDbTransaction? _transaction;

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

    public IDbConnection Connection =>
        _connection ?? throw new InvalidOperationException("UnitOfWork has not begun. Call Begin().");

    public IDbTransaction Transaction =>
        _transaction ?? throw new InvalidOperationException("Transaction has not begun. Call Begin().");

    public void Begin()
    {
        if (_transaction != null)
            throw new InvalidOperationException("Transaction already started.");

        _connection = new SqlConnection(_connectionString);
        _connection.Open();
        _transaction = _connection.BeginTransaction();
    }

    public void Commit()
    {
        if (_transaction == null)
            throw new InvalidOperationException("No transaction to commit.");

        _transaction.Commit();
        Dispose();
    }

    public void Rollback()
    {
        if (_transaction == null)
            return;

        _transaction.Rollback();
        Dispose();
    }

    public void Dispose()
    {
        _transaction?.Dispose();
        _transaction = null;

        _connection?.Dispose();
        _connection = null;
    }
}

Repositories that participate in the Unit of Work

Key rule: every Dapper call that must be in the transaction passes the shared Transaction.

Orders repository

using Dapper;

public sealed class OrdersRepository
{
    private readonly IUnitOfWork _uow;

    public OrdersRepository(IUnitOfWork uow) => _uow = uow;

    public Task<long> CreateAsync(long customerId, decimal total)
    {
        const string sql = @"
insert into Orders(CustomerId, Total, Status)
values (@CustomerId, @Total, 'Pending');
select cast(scope_identity() as bigint);";

        return _uow.Connection.ExecuteScalarAsync<long>(
            sql,
            new { CustomerId = customerId, Total = total },
            _uow.Transaction
        );
    }
}

Inventory repository (with a safety check)

using Dapper;

public sealed class InventoryRepository
{
    private readonly IUnitOfWork _uow;

    public InventoryRepository(IUnitOfWork uow) => _uow = uow;

    public async Task ReserveAsync(long productId, int qty)
    {
        const string sql = @"
update Inventory
set Reserved = Reserved + @Qty
where ProductId = @ProductId
  and (InStock - Reserved) >= @Qty;";

        var rows = await _uow.Connection.ExecuteAsync(
            sql,
            new { ProductId = productId, Qty = qty },
            _uow.Transaction
        );

        if (rows == 0)
            throw new InvalidOperationException("Not enough inventory to reserve.");
    }
}

Audit repository

using Dapper;

public sealed class AuditRepository
{
    private readonly IUnitOfWork _uow;

    public AuditRepository(IUnitOfWork uow) => _uow = uow;

    public Task WriteAsync(string eventName, string payloadJson)
    {
        const string sql = @"
insert into AuditLog(EventName, PayloadJson, CreatedUtc)
values (@EventName, @PayloadJson, sysutcdatetime());";

        return _uow.Connection.ExecuteAsync(
            sql,
            new { EventName = eventName, PayloadJson = payloadJson },
            _uow.Transaction
        );
    }
}

The use-case/service layer: the transaction boundary lives here

This is where Unit of Work shines. Your service does multiple steps, and commits once at the end.

public sealed class CheckoutService
{
    private readonly IUnitOfWork _uow;
    private readonly OrdersRepository _orders;
    private readonly InventoryRepository _inventory;
    private readonly AuditRepository _audit;

    public CheckoutService(
        IUnitOfWork uow,
        OrdersRepository orders,
        InventoryRepository inventory,
        AuditRepository audit)
    {
        _uow = uow;
        _orders = orders;
        _inventory = inventory;
        _audit = audit;
    }

    public async Task<long> PlaceOrderAsync(long customerId, long productId, int qty, decimal total)
    {
        _uow.Begin();

        try
        {
            var orderId = await _orders.CreateAsync(customerId, total);
            await _inventory.ReserveAsync(productId, qty);
            await _audit.WriteAsync("OrderPlaced", $$"""{"orderId":{{orderId}}}""");

            _uow.Commit();
            return orderId;
        }
        catch
        {
            _uow.Rollback();
            throw;
        }
    }
}

Outcome: if inventory reservation fails, the order insert is rolled back. No partial saves.

Keeping controllers thin (example endpoint)

Controllers should orchestrate HTTP concerns, not transactions.

using Microsoft.AspNetCore.Mvc;

[ApiController]
[Route("api/checkout")]
public sealed class CheckoutController : ControllerBase
{
    private readonly CheckoutService _checkout;

    public CheckoutController(CheckoutService checkout) => _checkout = checkout;

    [HttpPost("place-order")]
    public async Task<ActionResult<long>> PlaceOrder(PlaceOrderDto dto)
    {
        var orderId = await _checkout.PlaceOrderAsync(dto.CustomerId, dto.ProductId, dto.Qty, dto.Total);
        return Ok(orderId);
    }
}

public sealed record PlaceOrderDto(long CustomerId, long ProductId, int Qty, decimal Total);

Dependency Injection: correct lifetime and registration

With ASP.NET Core’s built-in container, register UoW as Scoped (one per request):

// Program.cs (or Startup.cs ConfigureServices)

var cs = builder.Configuration.GetConnectionString("AppDb");

builder.Services.AddScoped<IUnitOfWork>(_ => new SqlUnitOfWork(cs));

builder.Services.AddScoped<OrdersRepository>();
builder.Services.AddScoped<InventoryRepository>();
builder.Services.AddScoped<AuditRepository>();
builder.Services.AddScoped<CheckoutService>();

Why Scoped? It ensures every repository used during a request can share the same UoW instance (and therefore the same transaction) when the service begins it.

When NOT to use Unit of Work

Unit of Work is powerful, but don’t force it everywhere.

• Read-only endpoints: if an endpoint is just SELECT, you don’t need a transaction boundary. (SQL Server can still use snapshot semantics depending on configuration, but UoW doesn’t add value here.)
• Single-statement writes: if the entire operation is a single UPDATE or INSERT and nothing else depends on it, UoW may be overkill.
• When you already have a natural transactional wrapper: for example, if you use stored procedures that encapsulate all writes internally (and handle their own transactions), UoW at the app layer might duplicate work.

That said, the moment you have “write to A, then write to B”, UoW becomes valuable again.

Practical tips (SQL Server + Dapper)

• Use Microsoft.Data.SqlClient for SQL Server connections.
• Always pass the transaction to Dapper calls that must be atomic.
• Avoid starting transactions in repositories. Keep transaction ownership in the service/use-case layer.
• Prefer short transactions. Do not call external services (HTTP calls, message brokers) inside an open SQL transaction unless you really understand the latency/locking consequences.
• Consider adding isolation level explicitly if your domain needs it (BeginTransaction(IsolationLevel.ReadCommitted) etc.).

Summary

Using Dapper doesn’t mean giving up clean architecture. The Unit of Work pattern is one of the simplest ways to:

• prevent partial saves,
• keep your business operations consistent,
• and make transaction handling predictable across the codebase.

Hope you like the article. Happy Programming.