jgauffin's coding den

Thoughts and ideas about architecture and programming

· Jonas Gauffin

ADO.NET, the right way (reloaded)

In 2013 I wrote ADO.NET, the right way. The point was that plain ADO.NET, with a couple of extension methods, is a fine alternative to an ORM. I still think so. But the code in that post was written for a human who runs the app, steps through it in a debugger and remembers how the pieces fit.

Today, much of my data access code is written by a coding agent. A coding agent reads files and greps names. It rarely runs the app, and it never watches the console. Everything it learns comes from the code it opened, the compiler and the test run. So I went back to the old article and asked one question of every piece: when the coding agent gets this wrong, who tells it?

This article is about the foundation: the part of a Data project everything else builds on. How to run SQL, read results, own the transaction, where the abstractions go and how to test it. Queries versus commands, and where repositories fit, come in the next articles.

You don't need to know ADO.NET to follow along. The comments in the code explain its quirks as they come up.

ADO.NET in one paragraph

ADO.NET is the database API built into .NET. Every database has a provider (Npgsql for PostgreSQL, Microsoft.Data.SqlClient for SQL Server, and so on) that implements the same abstract classes from System.Data.Common: a DbConnection to the database, a DbTransaction on it, a DbCommand holding SQL and its parameters, and a DbDataReader to walk through the rows a query returns. Write your code against those base classes, and only the line that picks the provider knows which database you use. That's all of ADO.NET this article needs.

The Data project

MyApp.Domain/           business rules, and the interfaces they need from storage (next article)
MyApp.Data/
    Foundation/         the extensions and the request transaction (this article)
    Users/              the SQL for users, one folder per feature
MyApp.Data.Tests/       tests against a real database
MyApp.Web/              picks the provider, registers everything, commits the request

Three guidelines decide what goes where.

The Data project references no provider and no ASP.NET Core. It uses System.Data.Common, which is part of .NET, and the dependency injection abstractions. The web project picks the provider and owns the commit filters. The C# stays provider-neutral; the SQL doesn't, and there's no point pretending otherwise.

No abstractions over ADO.NET. No IDbConnectionFactory, no IUnitOfWork, no generic IRepository<T>. DbTransaction and DbCommand are already abstract classes, so wrapping them in interfaces adds a layer the coding agent must open without adding anything it can check. The seam for unit tests is somewhere else: the interfaces the domain declares for what it needs from storage, implemented in the Data project. Business logic is unit tested against fakes of those. The Data project itself is tested against a real database.

Organize by feature, not by layer. Users/ holds every SQL statement about users. Foundation/ is the only folder every feature uses.

The API

Eight extension methods. That's the whole foundation:

// Commands
DbCommand CreateCommand(this DbTransaction transaction, string sql)
void AddParameter(this DbCommand command, string name, object? value)

// Columns
T Get<T>(this DbDataReader reader, string column)
T Get<T>(this DbDataReader reader, int ordinal)

// Rows
Task<List<T>> ToList<T>(this DbDataReader reader, Func<DbDataReader, T> map)
IAsyncEnumerable<T> ToEnumerable<T>(this DbDataReader reader, Func<DbDataReader, T> map)
Task<T?> FirstOrDefault<T>(this DbDataReader reader, Func<DbDataReader, T> map)

// The request's transaction
IServiceCollection AddRequestTransaction(this IServiceCollection services)

It is small on purpose. The entire API fits in the coding agent's context next to the code it is changing. Dapper or EF Core can't say that, and a coding agent that knows every method it may call doesn't invent one.

Running the SQL is still plain ADO.NET: ExecuteReaderAsync for rows, ExecuteNonQueryAsync for changes, ExecuteScalarAsync for a single value. Nothing new to learn there.

Using it

This class shows the foundation in use. Whether it should be a query, a repository or something else is the topic of the next article.

public sealed class Users(DbTransaction transaction)
{
    // Map reads by position, so every query it reads selects exactly these columns, in this order.
    private const string Columns = "id, company_id, first_name, age";

    public async Task<User?> Get(int id)
    {
        // The SQL refers to the parameter as @id. Never paste a value into the SQL string:
        // a parameter is sent separately from the SQL, so it can't change what the SQL does.
        await using var command = transaction.CreateCommand($"SELECT {Columns} FROM users WHERE id = @id");
        command.AddParameter("id", id);

        // A reader keeps the connection busy until it's disposed: one open reader per connection.
        await using var reader = await command.ExecuteReaderAsync();
        return await reader.FirstOrDefault(Map);
    }

    public async Task<List<User>> FindByCompany(int companyId, string firstName)
    {
        await using var command = transaction.CreateCommand(
            $"SELECT {Columns} FROM users WHERE company_id = @company_id AND first_name LIKE @first_name ORDER BY first_name");
        command.AddParameter("company_id", companyId);

        // The wildcard belongs in the value, not in the SQL.
        command.AddParameter("first_name", firstName + "%");

        await using var reader = await command.ExecuteReaderAsync();
        return await reader.ToList(Map);
    }

    public async Task<int> Add(int companyId, string firstName, int? age)
    {
        // RETURNING is PostgreSQL; SQL Server writes OUTPUT INSERTED.id. The SQL is per database.
        await using var command = transaction.CreateCommand(
            "INSERT INTO users (company_id, first_name, age) VALUES (@company_id, @first_name, @age) RETURNING id");
        command.AddParameter("company_id", companyId);
        command.AddParameter("first_name", firstName);
        command.AddParameter("age", age);

        // ExecuteScalarAsync returns the first column of the first row, as an object.
        return (int)(await command.ExecuteScalarAsync())!;
    }

    public async Task Update(User user)
    {
        await using var command = transaction.CreateCommand(
            "UPDATE users SET company_id = @company_id, first_name = @first_name, age = @age WHERE id = @id");
        command.AddParameter("company_id", user.CompanyId);
        command.AddParameter("first_name", user.FirstName);
        command.AddParameter("age", user.Age);
        command.AddParameter("id", user.Id);

        // Returns the number of rows changed, which is how you notice an id that doesn't exist.
        await command.ExecuteNonQueryAsync();
    }

    private static User Map(DbDataReader reader) => new(
        reader.Get<int>(0),
        reader.Get<int>(1),
        reader.Get<string>(2),
        reader.Get<int?>(3));
}

(Columns is a constant, which is why it may be interpolated into the SQL. Values never are. The column names are lower case with underscores because PostgreSQL folds unquoted names to lower case. That style works in every database.)

Read it as the coding agent does:

  • Grep first_name and you land on the column list, the WHERE, the INSERT, the UPDATE and the parameters. Every place the column is read or written.
  • Add a property to User and its constructor changes, so Map stops compiling. A mapper that sets properties simply forgets the new one.
  • No base class. Everything the class does is in this file.
  • The transaction is a constructor parameter. A command can't be created outside it, and there is no Commit to forget.

Commands

public static class DbCommandExtensions
{
    public static DbCommand CreateCommand(this DbTransaction transaction, string sql)
    {
        // A transaction forgets its connection once committed or rolled back.
        var connection = transaction.Connection
            ?? throw new InvalidOperationException("The transaction has already been committed or rolled back.");

        var command = connection.CreateCommand();

        // A command doesn't join the connection's transaction by itself. SqlClient throws when this
        // is missing, Npgsql doesn't need it. Always set it, and the code works with both.
        command.Transaction = transaction;
        command.CommandText = sql;
        return command;
    }

    public static void AddParameter(this DbCommand command, string name, object? value)
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = name;

        // ADO.NET doesn't send null as NULL. A database NULL is DBNull.Value, both ways.
        parameter.Value = value ?? DBNull.Value;
        command.Parameters.Add(parameter);
    }
}

CreateCommand lives on the transaction, not on the connection. That makes forgetting command.Transaction impossible, which matters because SQL Server and PostgreSQL disagree on what happens when you do.

Reading columns

Get<T> replaces the casts like (int)reader["age"] that ADO.NET code is full of, and is where the coding agent most needs a good error.

public static class DbDataReaderExtensions
{
    public static T Get<T>(this DbDataReader reader, string column)
    {
        int ordinal;
        try
        {
            // Looks the name up among the result's columns, on every call.
            ordinal = reader.GetOrdinal(column);
        }
        catch (IndexOutOfRangeException ex)
        {
            throw new InvalidOperationException($"The result has no column '{column}'. Its columns are: {ColumnsOf(reader)}.", ex);
        }

        return reader.Get<T>(ordinal);
    }

    public static T Get<T>(this DbDataReader reader, int ordinal)
    {
        // A NULL column reads as DBNull.Value, not null, so ask before reading.
        if (reader.IsDBNull(ordinal))
        {
            // Nullable value types and reference types come back as null.
            if (default(T) is null)
                return default!;

            throw new InvalidOperationException($"Column '{reader.GetName(ordinal)}' is NULL. Read it as Get<{NameOf(typeof(T))}?>.");
        }

        // The value comes in the type the provider picked: a PostgreSQL bigint is a long, never an int.
        var value = reader.GetValue(ordinal);
        if (value is T typed)
            return typed;

        throw new InvalidCastException($"Column '{reader.GetName(ordinal)}' is {value.GetType().Name}, not {NameOf(typeof(T))}.");
    }

    private static string ColumnsOf(DbDataReader reader) =>
        string.Join(", ", Enumerable.Range(0, reader.FieldCount).Select(reader.GetName));

    private static string NameOf(Type type) =>
        Nullable.GetUnderlyingType(type) is { } underlying ? underlying.Name + "?" : type.Name;
}

Every failure names the column. A misspelled name lists the columns that do exist. A wrong type says which type the database returned. A NULL in a non-nullable read says exactly what to write instead. The coding agent reads the message and fixes the right line, instead of guessing.

NULL is decided by the type you ask for: Get<int?> and Get<string> return null, Get<int> throws. One method, no GetNullable beside it. The price: string and string? are the same type at runtime, so Get<string> can't refuse a NULL. Let the constructor of your entity refuse it, if that matters.

By name or by position? Reading by position skips the name lookup for every column of every row, which shows in tight loops. The risk is that position couples Map to the order of the SELECT. That's what the Columns constant is for: the order lives in one place, right above the mapper, and every query Map reads uses it. A coding agent reordering a column sees both on the same screen. And when a position is wrong anyway, the error still names the column, through GetName.

Reading rows

Three extension methods take the mapper and do the loop:

public static async Task<List<T>> ToList<T>(this DbDataReader reader, Func<DbDataReader, T> map)
{
    var items = new List<T>();

    // A reader starts before the first row. ReadAsync moves to the next one, and returns false after the last.
    while (await reader.ReadAsync())
        items.Add(map(reader));

    return items;
}

public static async Task<T?> FirstOrDefault<T>(this DbDataReader reader, Func<DbDataReader, T> map)
{
    return await reader.ReadAsync() ? map(reader) : default;
}

public static async IAsyncEnumerable<T> ToEnumerable<T>(this DbDataReader reader, Func<DbDataReader, T> map)
{
    while (await reader.ReadAsync())
        yield return map(reader);
}

ToList is the default. ToEnumerable streams, for results you don't want in memory, and it has a trap the compiler won't catch:

// Compiles. Fails at runtime: the reader is disposed before anyone enumerates.
public async Task<IAsyncEnumerable<User>> All()
{
    await using var command = transaction.CreateCommand($"SELECT {Columns} FROM users");
    await using var reader = await command.ExecuteReaderAsync();
    return reader.ToEnumerable(Map);
}

The method has to be an iterator itself, so the reader lives as long as the enumeration:

public async IAsyncEnumerable<User> All()
{
    await using var command = transaction.CreateCommand($"SELECT {Columns} FROM users");
    await using var reader = await command.ExecuteReaderAsync();
    await foreach (var user in reader.ToEnumerable(Map))
        yield return user;
}

There is a second trap. All commands in a request share one connection, and an open reader blocks it. Run another command while you enumerate, and the provider refuses. (SQL Server allows it only with MARS switched on, PostgreSQL not at all.) So: stream to the response, and do nothing else with the database until you're done.

One transaction per request

Every web request gets one transaction. It's created the first time something asks for it, committed when the request succeeded, and rolled back otherwise.

public sealed class RequestTransaction(DbDataSource dataSource) : IAsyncDisposable
{
    private DbConnection? _connection;
    private DbTransaction? _transaction;
    private bool _committed;

    public DbTransaction Begin()
    {
        if (_transaction != null)
            return _transaction;

        // Connections are pooled: opening one usually takes an idle connection from the pool,
        // and disposing it hands it back. A DI factory can't await, so this is the one synchronous open.
        _connection = dataSource.OpenConnection();
        _transaction = _connection.BeginTransaction();
        return _transaction;
    }

    public async Task Commit()
    {
        if (_transaction == null || _committed)
            return;

        await _transaction.CommitAsync();
        _committed = true;
    }

    public async ValueTask DisposeAsync()
    {
        if (_transaction == null)
            return;

        if (!_committed)
            await _transaction.RollbackAsync();

        await _transaction.DisposeAsync();
        await _connection!.DisposeAsync();
    }
}

public static class RequestTransactionRegistration
{
    public static IServiceCollection AddRequestTransaction(this IServiceCollection services)
    {
        services.AddScoped<RequestTransaction>();
        services.AddScoped<DbTransaction>(provider => provider.GetRequiredService<RequestTransaction>().Begin());
        return services;
    }
}

Classes in the Data project ask for DbTransaction, a type every .NET developer and every model already knows. RequestTransaction is only there so the commit step can ask "did this request touch the database?" without opening a connection to find out. A request that never asks for a DbTransaction costs nothing.

DbDataSource is where connections come from. It's the base class of NpgsqlDataSource, and a provider without its own data source gets one from its factory, such as SqlClientFactory.Instance.CreateDataSource(connectionString). The provider is chosen by a line of code you can grep, not by a name in a config file.

Committing

The rule: commit when the handler finished without an exception, the model state is valid and the response isn't an error. Anything else rolls back when the request's services are disposed.

When to commit matters more than it looks. Middleware that commits after await next() is too late: the response may already be on its way, and a client can see success for a commit that then fails. Commit after the handler, before the result is written. That's a filter, and filters belong to the web project.

For minimal APIs, an endpoint filter:

public sealed class CommitRequestTransaction(RequestTransaction transaction) : IEndpointFilter
{
    public async ValueTask<object?> InvokeAsync(EndpointFilterInvocationContext context, EndpointFilterDelegate next)
    {
        // An exception from the handler passes straight through, so nothing is committed.
        var result = await next(context);
        if (result is not IStatusCodeHttpResult { StatusCode: >= 400 })
            await transaction.Commit();

        return result;
    }
}

For controllers, an action filter:

public sealed class CommitRequestTransactionFilter(RequestTransaction transaction) : IAsyncActionFilter
{
    public async Task OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next)
    {
        var executed = await next();
        if (executed.Exception != null || !executed.ModelState.IsValid)
            return;

        if (executed.Result is not IStatusCodeActionResult { StatusCode: >= 400 })
            await transaction.Commit();
    }
}

Invalid model state doesn't commit. A form that fails validation is usually shown again with a 200, so the status code alone would let a half-done change through. And an exception that a filter has handled still doesn't commit. Something went wrong, and the data shouldn't pretend otherwise.

Minimal APIs have no model state. Validation that fails there answers with a 400 problem result, which the status check already catches.

Wiring it up

builder.Services.AddSingleton<DbDataSource>(NpgsqlDataSource.Create(builder.Configuration.GetConnectionString("MyApp")!));
builder.Services.AddRequestTransaction();
builder.Services.AddScoped<Users>();

// Minimal APIs
builder.Services.AddScoped<CommitRequestTransaction>();
var api = app.MapGroup("/api").AddEndpointFilter<CommitRequestTransaction>();

// Controllers
builder.Services.AddControllers(options => options.Filters.Add<CommitRequestTransactionFilter>());

"Isn't that invisible magic?" The commit isn't in the code that changes the data, true. But nothing happens behind the scenes: you registered it, on a line you can grep, and the rule fits in a sentence. Compare that with a SaveChanges() call in every service method, where the failure mode is a forgotten call that compiles, passes review and loses data.

Outside a request (a background job, a message handler), there is no filter. Create a scope, do the work, and call Commit() yourself:

await using var scope = services.CreateAsyncScope();
var users = scope.ServiceProvider.GetRequiredService<Users>();
// ...
await scope.ServiceProvider.GetRequiredService<RequestTransaction>().Commit();

Testing it

The Data project is tested against a real database, never against fakes of DbCommand or DbDataReader. A fake reader proves that the fake works. It can't tell you that PostgreSQL returns a long, that your SQL has a typo, or that a column is NULL.

A database for the tests

The test database runs in a container on your machine and in the build. It doesn't have to survive a crash, so switch off everything that makes a database wait for the disk:

docker run -d --name myapp-test-db -p 5433:5432 \
    -e POSTGRES_PASSWORD=test -e POSTGRES_DB=myapp_test \
    --tmpfs /var/lib/postgresql/data \
    postgres:17 -c fsync=off -c synchronous_commit=off -c full_page_writes=off
  • --tmpfs keeps the data in memory. Nothing touches the disk, and restarting the container gives you an empty database.
  • fsync=off, synchronous_commit=off and full_page_writes=off stop PostgreSQL from waiting for its log to reach the disk on every commit. In production that would lose data on a crash. In a test database, losing data is fine.

None of this goes in the connection string. How hard a database works to keep its transaction log safe is a setting of the server (PostgreSQL) or of the database (SQL Server), not of the connection. SQL Server can't switch its log off at all, but two settings come close:

docker run -d --name myapp-test-db -p 1434:1433 \
    -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD='Test-1234!' \
    mcr.microsoft.com/mssql/server:2022-latest

CREATE DATABASE myapp_test;
-- Simple recovery: the log is reused instead of kept for backups, so it doesn't grow.
ALTER DATABASE myapp_test SET RECOVERY SIMPLE;
-- A commit returns before its log record is on disk.
ALTER DATABASE myapp_test SET DELAYED_DURABILITY = FORCED;

The tests find the database through an environment variable, and skip when it isn't set, so the suite still runs on a laptop without Docker:

MYAPP_TEST_DB="Host=localhost;Port=5433;Database=myapp_test;Username=postgres;Password=test;Include Error Detail=true"

Include Error Detail makes Npgsql put the offending values in its error messages. Leave it off in production, where those values can be personal data. Turn it on in tests, where it's exactly what the coding agent needs to see.

public static class TestDatabase
{
    private static readonly string? ConnectionString = Environment.GetEnvironmentVariable("MYAPP_TEST_DB");

    // One data source for the whole run, so every test reuses the same pool of open connections.
    private static readonly DbDataSource? Source = ConnectionString == null ? null : NpgsqlDataSource.Create(ConnectionString);

    // Never committed: everything a test writes is rolled back when it's disposed.
    public static RequestTransaction Begin()
    {
        Assert.SkipWhen(Source == null, "Set MYAPP_TEST_DB to run the database tests.");
        return new RequestTransaction(Source!);
    }
}

That rollback is the cleanup. Each test runs in its own transaction and never commits, so tests don't see each other's rows and the database never fills up with test data.

[Fact]
public async Task Missing_column_is_reported_with_the_columns_that_exist()
{
    await using var request = TestDatabase.Begin();
    await using var command = request.Begin().CreateCommand("SELECT 1 AS id, 'Jonas' AS first_name");
    await using var reader = await command.ExecuteReaderAsync();
    await reader.ReadAsync();

    var error = Assert.Throws<InvalidOperationException>(() => reader.Get<string>("last_name"));

    Assert.Contains("id, first_name", error.Message);
}

[Fact]
public async Task Updated_user_is_read_back_with_the_new_name()
{
    await using var request = TestDatabase.Begin();
    var users = new Users(request.Begin());
    var id = await users.Add(1, "Jonas", null);

    await users.Update((await users.Get(id))! with { FirstName = "Changed" });

    Assert.Equal("Changed", (await users.Get(id))!.FirstName);
}

Keeping the tests fast

A database test should take milliseconds. When the suite gets slow, it's almost always one of these:

  • A database per test. Starting a container or creating a database takes seconds. Start one container, and leave it running between runs.
  • Building the schema per test. Run the migrations once per test run, from the same scripts production uses. That also proves the migrations work.
  • Cleaning up by deleting. Truncating tables between tests costs more than the tests. The rollback does it for free.
  • A new pool per test. Creating a data source per test opens new connections every time. Keep one, as TestDatabase does.
  • Running in sequence. Every test has its own transaction, so they can run in parallel. The catch: two tests that change the same row wait for each other's transaction. So each test inserts the rows it changes, instead of sharing seeded ones.

What to test, and where

  • The foundation, once, in MyApp.Data.Tests/Foundation. Each Get<T> error (missing column, NULL into a non-nullable type, wrong type). NULL into int? and string. ToList, FirstOrDefault and ToEnumerable on zero, one and many rows. RequestTransaction: a commit is visible from another connection, a dispose without commit leaves nothing behind.
  • Every SQL statement, in its feature's folder. Write, then read back. A statement no test runs is a statement nobody has seen work.
  • The commit rule, through the web host. Start the app in memory, against the test database, and check that a successful request commits, and that an exception, a 400 and an invalid form roll back. These tests have to commit, so they use rows nobody else touches and remove them afterwards.
  • Business logic, not here. It's tested in the domain's tests, against fakes of the domain's own interfaces.

Why not EF Core?

EF Core is a good library, and models have seen a lot of it. But the things that make it productive for a human are the things a coding agent can't see.

  • The SQL is generated at runtime. A LINQ query that compiles can still fail with "could not be translated" when it runs. Or it runs fine, and fetches the whole table to filter it in memory. The coding agent sees C#; the database sees something else. Here, the SQL in the file is the SQL that runs.
  • Change tracking is state somewhere else. SaveChanges() writes whatever the context tracked, including an entity loaded three calls away. Reading one method doesn't tell you what it will write. Here, every write is an ExecuteNonQueryAsync in the method that does it.
  • Mapping is convention. Table and column names come from class and property names, attributes, OnModelCreating and the conventions in between. Grep a column name and you may find nothing. Here, the column is written out next to the code that reads it.
  • Loading is implicit. Whether a related entity is there depends on an Include, a lazy-loading proxy or what the context happens to track. An N+1 query compiles, passes the tests and shows up in production.
  • The API is huge, and comes in versions. EF6 and EF Core share names and differ in behavior, and models were trained on both. That's where a coding agent calls a method that existed ten years ago.

The honest counterweight: EF Core gets you running faster, and a coding agent writes plausible EF Core code from training alone. If your data access is simple CRUD and stays that way, that can be worth it. The cost shows when something goes wrong, and then it's the coding agent that has to work out why, with nothing but the files to go on.

What I've learned since 2013

The old version worked, and I shipped code like it for years. But I've learned a lot since, and much of it is about what a reader can't see. Every piece below made sense to me back then; every one of them hides something from a coding agent.

(int)record["Age"]. If the column is a bigint, you get InvalidCastException: Unable to cast object of type 'System.Int64' to type 'System.Int32'. Which column? Which query? The coding agent has no idea, so it starts changing casts until the test goes green.

SELECT * and a Map(record, user) that fills properties. Add a property to User and forget the mapper, and the new property is silently empty. Nothing fails. And SELECT * means a grep for a column name never lands on the query that reads it.

A repository base class. Repository<TEntity> with an abstract Map and a ToList that calls it. One more file to open to understand the one you're editing, for a loop of five lines.

AdoNetContext and its units of work. A command joined the first unit of work in a linked list behind a lock, or no transaction at all when the list was empty. Whether a command ran inside a transaction was decided at runtime by state somewhere else. The file you were reading couldn't tell you.

uow.SaveChanges() in every service method. Forget it, and the change is rolled back without a word.

command.Page(...) rewriting CommandText. The SQL you read was not the SQL that ran.

DbProviderFactories and a provider name in app.config. The driver was chosen by configuration, through a lookup no grep can follow.

The common thread: each of these moves information off the page, or lets a mistake compile and fail later. A coding agent pays for both.

Telling the coding agent

The compiler enforces most of this. What it can't goes in a skill the coding agent loads when it works in the Data project:

# Data access

The Data project uses plain ADO.NET with the extensions in its `Foundation/` folder. No ORM, no
interfaces over ADO.NET.

## Setup

- The Data project references no database provider and no ASP.NET Core. SQL lives in one folder per feature.
- The web project registers the provider as a `DbDataSource` singleton, calls `AddRequestTransaction()`,
  registers each Data class as scoped, and adds `CommitRequestTransaction` (minimal APIs) or
  `CommitRequestTransactionFilter` (controllers).
- Outside a web request, create a scope and call `RequestTransaction.Commit()` when the work succeeded.

## Writing SQL

- A Data class takes `DbTransaction` in its constructor and creates commands with
  `transaction.CreateCommand(sql)`. It never commits; the request does.
- Values always go through `command.AddParameter(name, value)`, never into the SQL string.
- Each class has a `Columns` constant and one `Map` method that reads by position with `Get<T>`.
  Every query that `Map` reads selects `Columns`, nothing else and in that order.
- A nullable column is read as a nullable type: `Get<int?>`, `Get<string?>`.
- Read rows with `reader.ToList(Map)` or `reader.FirstOrDefault(Map)`. Use `reader.ToEnumerable(Map)`
  only inside an `async IAsyncEnumerable` method, and run no other command while it is enumerated.
- Write paging, sorting and filtering in the SQL.

## Test database

- The tests run against a database in a local container, started once and left running. It trades
  durability for speed: PostgreSQL runs with its data on tmpfs and `fsync`, `synchronous_commit` and
  `full_page_writes` off; a SQL Server test database uses simple recovery and forced delayed
  durability. Never point the tests at a database whose data matters.
- The schema is created once per test run, by the same migrations production uses.
- The test connection string turns on the provider's detailed errors (Npgsql: `Include Error Detail=true`).

## Testing

- Every SQL statement has a test in the Data project's test project, in the feature's folder, against
  the real database. Never fake `DbCommand` or `DbDataReader`.
- Start each test with `TestDatabase.Begin()` and never commit: the rollback is the cleanup. The tests
  skip when no test database is configured.
- Each test inserts the rows it changes. Tests run in parallel, and a shared row makes them wait for
  each other.
- A write is tested by reading it back.
- Business logic is tested in the domain's tests against fakes of the domain's interfaces, not here.

The skill is short, because the rest is in the types.

Conclusion

The 2013 version optimized for a developer who knew the codebase. This one optimizes for a reader who only knows what's on the page: the SQL, the mapper and the transaction are all in the file being edited, a wrong mapping doesn't compile, a wrong column fails with a message that names it, and every statement has a test against the database it runs on.

It's also less code than the old one. No context, no unit of work, no base class, no connection factory. Eight methods, one class to own the transaction, and two filters you pick from.

Next: queries versus commands, and where repositories fit.