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):

Job	Plain ADO.NET	Open.Database.Extensions
A list of mapped objects	20	5
One row	20	5
A scalar	9	4
A command with no result set	9	4

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 per-call overrides for columns with other names.

Renamed columns

A reusable, declared mapping for columns whose names differ from your properties is being built and will be described here when it ships.

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

Package	What it is
Open.Database.Extensions.Core	The foundation, expressive commands and Map<T>(). Provider-agnostic.
Open.Database.Extensions.Channel	Rows into System.Threading.Channels.
Open.Database.Extensions.Dataflow	Rows into TPL Dataflow blocks.
Open.Database.Extensions.MSSqlClient	SqlConnectionFactory and SQL Server specializations on Microsoft.Data.SqlClient.
Open.Database.Extensions.SqlClient	The same API on the older System.Data.SqlClient. Legacy: prefer MSSqlClient.
Open.Database.Extensions	A meta-package for Core. The last published version is 9.0.3: use Core.

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-preview01. 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.