Dapper Transactions — Coordinating Multiple Operations

Clinical operations are often multi-step:
  1. Insert the new prescription
  2. Deactivate the previous prescription
  3. Write an audit log entry
  4. Update the patient's medication count

If step 3 fails after step 1 and 2, the prescription is active
  but there is no audit trail. The database is in an inconsistent state.

A transaction makes all four steps atomic:
  All succeed → COMMIT: all changes are visible
  Any step fails → ROLLBACK: all changes are undone, as if nothing happened

public async Task CreatePrescriptionAsync(
    Prescription prescription, CancellationToken ct)
{
    using var connection = _connectionFactory.CreateConnection();
    using var transaction = connection.BeginTransaction();

    try
    {
        // Step 1: deactivate previous prescription
        await connection.ExecuteAsync(
            "UPDATE prescriptions SET is_active = 0, updated_at = GETUTCDATE() WHERE patient_id = @PatientId AND is_active = 1",
            new { prescription.PatientId },
            transaction: transaction);

        // Step 2: insert new prescription
        await connection.ExecuteAsync(
            @"INSERT INTO prescriptions (id, patient_id, medication, dose_amount, dose_unit, prescribed_at, is_active)
              VALUES (@Id, @PatientId, @Medication, @DoseAmount, @DoseUnit, @PrescribedAt, 1)",
            new
            {
                Id            = prescription.Id,
                PatientId     = prescription.PatientId,
                Medication    = prescription.MedicationName,
                DoseAmount    = prescription.Dose.Amount,
                DoseUnit      = prescription.Dose.Unit,
                PrescribedAt  = prescription.PrescribedAt,
            },
            transaction: transaction);

        // Step 3: audit log
        await connection.ExecuteAsync(
            "INSERT INTO audit_logs (id, entity_type, entity_id, action, user_id, timestamp) VALUES (@Id, 'Prescription', @EntityId, 'Created', @UserId, GETUTCDATE())",
            new { Id = Guid.NewGuid(), EntityId = prescription.Id, UserId = _currentUser.UserId },
            transaction: transaction);

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

// SqlConnection in .NET supports async transactions properly
using var connection  = _connectionFactory.CreateConnection() as SqlConnection;
await connection!.OpenAsync(ct);
await using var transaction = await connection.BeginTransactionAsync(ct);

try
{
    await connection.ExecuteAsync(sql1, params1, transaction: transaction);
    await connection.ExecuteAsync(sql2, params2, transaction: transaction);
    await connection.ExecuteAsync(sql3, params3, transaction: transaction);

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

// IUnitOfWork wraps the connection and transaction
public interface IUnitOfWork : IDisposable
{
    IDbConnection  Connection  { get; }
    IDbTransaction Transaction { get; }
    Task CommitAsync(CancellationToken ct = default);
    Task RollbackAsync(CancellationToken ct = default);
}

public sealed class DapperUnitOfWork : IUnitOfWork
{
    private readonly IDbConnection  _connection;
    private readonly IDbTransaction _transaction;

    public DapperUnitOfWork(ISqlConnectionFactory factory)
    {
        _connection  = factory.CreateConnection();
        _transaction = _connection.BeginTransaction();
    }

    public IDbConnection  Connection  => _connection;
    public IDbTransaction Transaction => _transaction;

    public async Task CommitAsync(CancellationToken ct = default)
    {
        if (_transaction is SqlTransaction sqlTx)
            await sqlTx.CommitAsync(ct);
        else
            _transaction.Commit();
    }

    public async Task RollbackAsync(CancellationToken ct = default)
    {
        if (_transaction is SqlTransaction sqlTx)
            await sqlTx.RollbackAsync(ct);
        else
            _transaction.Rollback();
    }

    public void Dispose()
    {
        _transaction.Dispose();
        _connection.Dispose();
    }
}

// Repository usage: inject IUnitOfWork, use its Connection + Transaction
public sealed class PrescriptionRepository : IPrescriptionRepository
{
    private readonly IUnitOfWork _uow;

    public Task InsertAsync(Prescription p, CancellationToken ct) =>
        _uow.Connection.ExecuteAsync(insertSql, p, _uow.Transaction);
}

// Savepoints: rollback part of a transaction, not all of it
using var connection  = (SqlConnection)_connectionFactory.CreateConnection();
await connection.OpenAsync(ct);
var transaction = connection.BeginTransaction();

try
{
    await connection.ExecuteAsync(step1Sql, params1, transaction);

    // Create a savepoint before optional operation
    transaction.Save("BeforeOptionalStep");

    try
    {
        await connection.ExecuteAsync(optionalSql, params2, transaction);
    }
    catch (SqlException ex) when (ex.Number == 2627)  // duplicate key — optional step failed
    {
        // Roll back only to the savepoint — step 1 is preserved
        transaction.Rollback("BeforeOptionalStep");
    }

    await connection.ExecuteAsync(step3Sql, params3, transaction);
    transaction.Commit();
}
catch
{
    transaction.Rollback();  // full rollback including step 1
    throw;
}

// Dapper and EF Core can share a connection and transaction
await using var dbContextTransaction = await _db.Database.BeginTransactionAsync(ct);
var connection   = _db.Database.GetDbConnection();
var sqlTransaction = dbContextTransaction.GetDbTransaction();

// EF Core operation
_db.Patients.Add(patient);
await _db.SaveChangesAsync(ct);

// Dapper operation on the same connection+transaction
await connection.ExecuteAsync(
    "INSERT INTO audit_logs (id, action, timestamp) VALUES (@Id, 'PatientAdmitted', GETUTCDATE())",
    new { Id = Guid.NewGuid() },
    transaction: sqlTransaction);

await dbContextTransaction.CommitAsync(ct);
// Both EF Core and Dapper operations commit or rollback together