Open.Database.Extensions.MSSqlClient 11.0.0-preview02

Open.Database.Extensions

You write the SQL. This library writes the other twenty lines.

Open.Database.Extensions turns the ADO.NET boilerplate around your own SQL into one fluent command: create the connection, add the parameters, run it, map the rows, dispose everything, the way ADO.NET best practice says. It works with any ADO.NET provider, is built for short connection times and low allocations, and sits on the standard types you already use.

Documentation · Getting started · API reference · Benchmarks

Before and after

Fetch one author's posts, with plain ADO.NET:

var posts = new List<Post>();

using var connection = new SqliteConnection(Db.ConnectionString);
connection.Open();

using var command = connection.CreateCommand();
command.CommandText = "select id, author, title, body from posts where author = @author order by id";
var parameter = command.CreateParameter();
parameter.ParameterName = "@author";
parameter.Value = 42;
command.Parameters.Add(parameter);

using var reader = command.ExecuteReader();
while (reader.Read())
{
    posts.Add(new Post
    {
        Id = reader.GetInt32(0),
        Author = reader.GetInt32(1),
        Title = reader.GetString(2),
        Body = reader.IsDBNull(3) ? null : reader.GetString(3),
    });
}

And with Open.Database.Extensions:

Post[] posts = await connectionFactory
    .Command("select * from posts where author = @author order by id")
    .AddParam("author", 42)
    .Map<Post>()
    .ToArrayAsync();

The same four jobs, counted from the compiled snippets in docsite/samples/BeforeAfter.cs (lines of code, not counting blank lines):

What you no longer write: opening and closing the connection, creating the command and setting its text, creating and adding every parameter, the read loop, copying columns into properties (ordinals, IsDBNull), and disposing all of it.

Why it is fast

• Measured against Dapper 2.1.89 on an in-memory provider: Map<T>() is roughly 2 to 3 times faster at 100 or more rows, with about a third of the allocations (0.33 to 0.38 of Dapper's on multi-row results).
• With a real database the database dominates: against SQL Server LocalDB it is on par to a few percent faster (ratios 0.89 to 1.08, mostly ties) with about 0.55 to 0.6 times the allocations on multi-row results.
• Built to hold a connection briefly: the row mapping is compiled once per result shape and cached, values are read with typed getters (nothing is boxed), Skip advances without mapping, and a command built from a factory opens its connection when it runs and disposes it afterwards.

These figures come from the 11.0.0-preview01 build, re-run independently on a noisy machine, so read the timing ratios as indicative. The SQLite results are published but were not independently re-run, and a re-baseline is pending. Channels, Dataflow and Retrieve().DequeueAs<T>() are conveniences, not speed-ups. See Performance.

Expressive commands

Start from a connection factory and the command opens, runs and disposes its own connection:

// The command takes a connection from the factory when a terminal runs, opens it, and disposes it afterwards.
Post[] posts = await connectionFactory
    .Command("select * from posts where author = @author order by id")
    .AddParam("author", 42)
    .Map<Post>()
    .ToArrayAsync();

Or start from a connection you already have. You own it; the command opens it only if it is closed and closes only what it opened:

// You own the connection. The command opens it only if it is closed, and closes only what it opened.
Post[] posts = connection
    .Command("select * from posts where author = @author order by id")
    .AddParam("author", 42)
    .Map<Post>()
    .ToArray();

Stored procedures are StoredProcedure(name), and the terminals include ExecuteNonQuery, ExecuteScalar, Map<T>() and their async twins. See Expressive commands.

Row mapping without configuration

Map<T>() matches columns to public settable properties by name, ignoring case, and ignores the rest:

// Columns are matched to public settable properties by name, ignoring case.
// The extra "views" column has no property, so it is ignored.
Post[] posts = connection
    .Command("select * from posts order by id")
    .Map<Post>()
    .ToArray();

Paging reads only what it needs, and skipped rows are never mapped:

// Skipped rows are advanced without being mapped, and reading stops once the take is satisfied.
Post[] page = connection
    .Command("select * from posts order by id")
    .Map<Post>()
    .Skip(1)
    .Take(2)
    .ToArray();

Models need new T() and settable properties: there is no constructor binding. See Mapping rows to objects for the rules and limits, including the RowMap<T> for columns with other names.

Renamed columns

When your columns are named differently from your properties, declare the mapping once as a RowMap<T> and reuse it. The map is made from key/value pairs of property name and column name; write the property name with nameof so the compiler catches a rename. Create it on first use with field ??= (C# 14), so only the maps you use are built. It is checked when it is created, and passing it costs nothing per call:

static class PersonMaps
{
    public static RowMap<Person> PersonMap => field ??= new([
        new(nameof(Person.Id), "person_id"),
        new(nameof(Person.FirstName), "first_name"),
        new(nameof(Person.LastName), "last_name"),
    ]);
}

Person[] people = connection
    .Command("select person_id, first_name, last_name from people order by person_id")
    .Map<Person>(PersonMaps.PersonMap)
    .ToArray();

Declare a map once and reuse it: a new RowMap<T>([...]) or an ImmutableArray of pairs passed to the constructor at the call site builds a new map, and compiles the row reader, on every call. Older compilers have no field keyword; use an explicit backing field.

A null column means "do not map this property". It is not a no-op: it stops the by-name match, so the property keeps its default value even if the result set has a column with that name. The property must exist; the column need not:

// LastName is not read at all, although the result set has a last_name column.
public static RowMap<Person> PersonNamesOnlyMap => field ??= new([
    new(nameof(Person.Id), "person_id"),
    new(nameof(Person.FirstName), "first_name"),
    new(nameof(Person.LastName), null),
]);

More in Columns named differently.

Transactions in one expression

// The factory opens a connection and starts a transaction. Returning true commits; false (or a throw) rolls back.
bool committed = connectionFactory.Using(connection =>
    connection.ExecuteTransactionConditional(transaction =>
    {
        transaction
            .Command("insert into posts (id, author, title, body, views) values (100, 1, 'Temporary', null, 0)")
            .ExecuteNonQuery();

        return shouldCommit;
    }));

And more

• Channels and Dataflow: stream rows into a Channel<T> or a dataflow block. Guide
• SQL Server: SqlConnectionFactory and stored procedures on Microsoft.Data.SqlClient. Guide
• Coming from Dapper: the same jobs side by side. Guide

Install

dotnet add package Open.Database.Extensions.Core --prerelease

Targets: netstandard2.0, netstandard2.1 and net10.0 (MSSqlClient: net472 and net10.0). Version 11 is a preview.

How it fits together

Status of version 11

This is 11.0.0-preview02. What changed, the breaking changes and the known limitations are in What's new in 11; earlier versions are in the release notes. Please try it and tell us what you find.

Contributing and license

Documentation lives next to the code and is rebuilt and published from master automatically; see where each kind of documentation lives. Released under the MIT license.