Open.Database.Extensions.MSSqlClient 11.0.0-preview04

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();

connectionFactory is created once per application, for example new DbConnectionFactory<SqliteConnection>(() => new SqliteConnection(connectionString)); see Registering the factory once.

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 call that runs the command (ToArrayAsync() here) is called the terminal:

// 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). Terminals include ExecuteNonQuery, ExecuteScalar and, after Map<T>(), ToArray, ToList and FirstOrDefault. On a DbConnection or an IDbConnectionFactory<TConnection> each has an Async twin. See Expressive commands.

Row mapping without configuration

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

// Columns are matched to public settable properties by name, ignoring case and underscores.
// The extra "views" column has no property, so it is ignored.
Post[] posts = connection
    .Command("select * from posts order by id")
    .Map<Post>()
    .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. A declared map adds nothing per call beyond a check of the result-set layout:

static class PersonMaps
{
    public static RowMap<Person> PersonMap => field ??= [
        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 map built at the call site is rebuilt, and its row reader recompiled, on every call. The constructor takes one pair or several (new RowMap<T>(new("A", "b"))), and an ImmutableArray or any IEnumerable of pairs also works. A null column in a pair means "do not map this property": it stops the by-name match too, so the property keeps the value new T() gave it. Older compilers have no field keyword; use an explicit backing field.

More in Columns named differently.

Transactions in one expression

Using gives your action a connection from the factory and disposes it afterwards.

// The factory creates the connection; ExecuteTransactionConditional opens it if it is closed and starts the 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 (11.0.0-preview04): see What's new in 11 for the changes, the breaking changes and the known limitations, and the release notes for earlier versions. Please try it and tell us what you find.

How it fits together

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.