Coming from Dapper

Open.Database.Extensions is a set of extensions on ADO.NET, not a Dapper clone, and plenty of code uses both. This page is for readers who know Dapper. The biggest difference is not the query syntax: it is who handles the connection. For speed and memory, see Performance.

The steps, side by side

Each comparison lists the same four steps in the same order, row against row: (1) get the connection, (2) the command or query, (3) the parameters, (4) the terminal and the mapping. A row that one side does not need says "nothing". The SQL is predefined once, so the lines stay short:

public const string ListSql = "select * from posts where author = @author order by id";
public const string OneSql = "select * from posts where id = @id";
public const string CountSql = "select count(*) from posts where author = @author";
public const string TouchSql = "update posts set views = views where author = @author";
public const string InsertSql = "insert into posts (id, author, title, body, views) values (@id, 1, 'Temporary', null, 0)";

Dapper needs no setup. It needs the connection string and the provider type in scope at every call site. It opens a closed connection for the call and closes it again afterwards, so connection.Open() is not required; add it only if you want to control when the connection opens.

Open.Database.Extensions needs a connection factory, created or registered once per application (see Registering the factory once):

var connectionFactory = new DbConnectionFactory<SqliteConnection>(() => new SqliteConnection(connectionString));

After that, no call site writes any connection code. The counts below do not include this one line of one-time setup.

A list of rows

Dapper

using var connection = new SqliteConnection(connectionString);
List<Post> posts = connection.Query<Post>(ListSql,
    new { author = 42 })
    .AsList();

Open.Database.Extensions (factory)

// nothing: the factory creates, opens and disposes the connection
List<Post> posts = connectionFactory.Command(ListSql)
    .AddParam("author", 42)
    .Map<Post>().ToList();

Lines you write per call: Dapper 4, Open.Database.Extensions 3 (plus the one-time setup).

What is different:

The connection. With Dapper you create it and dispose it, and Dapper opens it if it is closed and closes it when the call finishes. With a factory, the factory creates it for each execution, the command opens it only if it is closed and disposes it when the terminal returns, so it is open only for the duration of the call.
Rows. Dapper's Query<T> buffers by default (buffered = true) and returns an IEnumerable<T>, turned into a list here by AsList(). ToList() reads every row into a list during the call. Read(handler) offers a lazy sequence that is only valid inside the handler.
Mapping. Both match columns to members by name, ignoring case. Dapper can also bind constructor parameters and fields and, if you turn on MatchNamesWithUnderscores, match first_name to FirstName. The extensions set public settable properties only and do no naming-convention matching: a renamed column needs an override (see Renamed columns).
Null parameters. Both convert a null parameter value to DBNull.
Mapping errors. Dapper wraps them in a DataException ("Error parsing column ..."). The extensions throw an InvalidOperationException that names the property and the column.

One row

Dapper

using var connection = new SqliteConnection(connectionString);
Post? post = connection.QueryFirstOrDefault<Post>(OneSql,
    new { id = 3 });
// nothing: "first or default" and Post are in the method name above

Open.Database.Extensions (factory)

// nothing: the factory creates, opens and disposes the connection
Post? post = connectionFactory.Command(OneSql)
    .AddParam("id", 3)
    .Map<Post>().FirstOrDefault();

Lines you write per call: Dapper 3, Open.Database.Extensions 3 (plus the one-time setup).

What is different:

No rows, or several. Dapper's QueryFirstOrDefault returns default for no rows and the first row for several. QuerySingleOrDefault returns default for none and throws for more than one. QueryFirst and QuerySingle throw when there is no row. The extensions' FirstOrDefault() returns null for no rows and the first row for several; SingleOrDefault() returns null for none and throws InvalidOperationException for more than one. There is no throwing First or Single.

A scalar

Dapper

using var connection = new SqliteConnection(connectionString);
long count = connection.ExecuteScalar<long>(CountSql,
    new { author = 42 });
// nothing: the terminal is the method name above

Open.Database.Extensions (factory)

// nothing: the factory creates, opens and disposes the connection
long count = connectionFactory.Command(CountSql)
    .AddParam("author", 42)
    .ExecuteScalar<long>();

Lines you write per call: Dapper 3, Open.Database.Extensions 3 (plus the one-time setup).

What is different:

The value's type. Dapper converts the provider's value to T where it can (it uses Convert.ChangeType) and returns default for NULL. The extensions' ExecuteScalar<T>() casts the value exactly as the provider returns it, so T must be that type (SQLite counts as long, SQL Server as int). To convert, pass a function: ExecuteScalar(value => ...).

A command with no result set

Dapper

using var connection = new SqliteConnection(connectionString);
int updated = connection.Execute(TouchSql,
    new { author = 42 });
// nothing: the terminal is the method name above

Open.Database.Extensions (factory)

// nothing: the factory creates, opens and disposes the connection
int updated = connectionFactory.Command(TouchSql)
    .AddParam("author", 42)
    .ExecuteNonQuery();

Lines you write per call: Dapper 3, Open.Database.Extensions 3 (plus the one-time setup).

What is different:

The result. Both return the number of rows affected, as an int.

A transaction

Two facts are both true, and mixing them up causes the "Dapper needs Open()" belief. Dapper opens a closed connection for plain queries and closes it again afterwards. ADO.NET requires an open connection to begin a transaction, so any code that begins one opens the connection first. Dapper does not begin, commit or roll back transactions: its methods take an IDbTransaction that you created from an open connection.

Dapper

using var connection = new SqliteConnection(connectionString);
connection.Open();
using var transaction = connection.BeginTransaction();
try
{
    connection.Execute(InsertSql, new { id = 100 }, transaction);
    connection.Execute(InsertSql, new { id = 101 }, transaction);
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Open.Database.Extensions (factory)

connectionFactory.Using(connection =>
    // nothing: ExecuteTransaction opens the connection if it is closed
    // nothing: ExecuteTransaction begins the transaction
    connection.ExecuteTransaction(transaction =>
    {
        transaction.Command(InsertSql).AddParam("id", 100).ExecuteNonQuery();
        transaction.Command(InsertSql).AddParam("id", 101).ExecuteNonQuery();
        // nothing: it commits when the action returns
    }));
// nothing: it rolls back if the action throws
// nothing: Using disposes the connection

Lines you write per call: Dapper 14, Open.Database.Extensions 6 (plus the one-time setup).

What is different:

Who opens the connection. Dapper: you, with Open(), because BeginTransaction() needs it. Microsoft's SqlConnection.BeginTransaction examples open the connection first, and Microsoft.Data.Sqlite reports that the call "can only be called when the connection is open". The extensions' ExecuteTransaction opens the connection if it is closed.
Begin, commit, roll back. Dapper: you write all three, usually with a try/catch, and pass the transaction to every call. The extensions begin the transaction, run your action, commit when it returns without throwing, and roll back (and rethrow) when it throws. ExecuteTransactionConditional commits only when your action returns true; otherwise it rolls back.
A connection that was already open. The extensions close only a connection they opened; an open connection stays open.
Where the helpers live. They are extension methods on DbConnection, with async forms (ExecuteTransactionAsync, ExecuteTransactionConditionalAsync), an optional isolation level and a cancellation token. A factory reaches them through Using.

A connection you own

If you would rather own the connection, the extensions work on a connection the way Dapper does: the same first row, then the same rows as the factory form.

Dapper

using var connection = new SqliteConnection(connectionString);
List<Post> posts = connection.Query<Post>(ListSql,
    new { author = 42 })
    .AsList();

Open.Database.Extensions (your connection)

using var connection = new SqliteConnection(connectionString);
List<Post> posts = connection.Command(ListSql)
    .AddParam("author", 42)
    .Map<Post>().ToList();

Lines you write per call: Dapper 4, Open.Database.Extensions 4.

What is different:

Nothing about the connection. You create it and dispose it in both. Each opens a closed connection for the call and closes only what it opened; neither needs Open(). The extensions never dispose a connection you gave them.
Concurrency. A command built from one connection throws InvalidOperationException if two terminals run at once.

The total

Across the four query calls above Dapper takes 13 lines and the extensions 12, plus one line of one-time setup. The counts are close: the difference is what the lines are. None of the extensions' per-call lines creates, owns or disposes a connection, so there is nothing to forget, to leak or to keep open too long, and connection handling lives in one place instead of at every call site.

Who owns what

	Dapper	Extensions with a factory	Extensions with your connection
Creates the connection	You, at each call site	The factory, for each execution	You
Needs the connection string and provider type in scope	At each call site	Once, where the factory is created	You, where you create it
Opens it	Dapper, if it is closed	The command, if it is closed	The command, only if it is closed
Closes it	Dapper, if it opened it	The command disposes it	The command, only if it opened it
Disposes it	You	The command	You. Never the command

Registering the factory once

IDbConnectionFactory<TConnection> is the abstraction, DbConnectionFactory<TConnection> wraps any Func<TConnection>, and SqlConnectionFactory (in the MSSqlClient package) takes a connection string. The library has no dependency-injection extension of its own: register the factory with whatever container you use. With Microsoft.Extensions.DependencyInjection:

var services = new ServiceCollection();

// A singleton is fine: the factory holds no connection, it only creates them.
services.AddSingleton<IDbConnectionFactory<SqliteConnection>>(
    new DbConnectionFactory<SqliteConnection>(() => new SqliteConnection(Db.ConnectionString)));
services.AddTransient<PostService>();

using ServiceProvider provider = services.BuildServiceProvider();
var service = provider.GetRequiredService<PostService>();

Consumers take the factory as a constructor parameter and never see a connection string:

public class PostService(IDbConnectionFactory<SqliteConnection> connectionFactory)
{
    public List<Post> ByAuthor(int author) => connectionFactory.Command(ListSql)
        .AddParam("author", author)
        .Map<Post>().ToList();
}

For SQL Server, register SqlConnectionFactory as IDbConnectionFactory<SqlConnection>:

services.AddSingleton<IDbConnectionFactory<SqlConnection>>(new SqlConnectionFactory(connectionString));

A singleton is fine because the factory holds no connection: Create() returns a new connection object each time.

Renamed columns

Dapper can match underscored names (MatchNamesWithUnderscores) or you can alias the columns in SQL. With the extensions, name the column for each property that needs it. nameof keeps the property side checked by the compiler:

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

Async and cancellation

Dapper's QueryAsync<T> returns a Task<IEnumerable<T>>, and a cancellation token travels in a CommandDefinition. The extensions' terminals have Async twins that take a CancellationToken and return a ValueTask, for example ToArrayAsync(cancellationToken); a token set on the command with UseCancellationToken(token) is used when a terminal is not given one.

Where Dapper does more

Dapper has features this library does not offer:

Constructor binding. Positional records work with Dapper. Map<T>() needs new T() and settable properties.
Multi-mapping and QueryMultiple. Dapper maps several types from one row and reads several result sets from one call.
Type handlers and custom type maps. SqlMapper.AddTypeHandler and SqlMapper.SetTypeMap.
Dynamic results. Query(sql) without a type returns dynamic rows.

// Dapper binds constructor parameters, so a positional record works.
// Map<PostRecord>() does not compile: it requires new T() and settable properties.
List<PostRecord> posts = connection.Query<PostRecord>("select id, author, title, body from posts order by id").AsList();

With the extensions, use Map(selector) or the reader helpers for those jobs.

Migrating a call site

Create the connection factory once and register or inject it.
Replace using var connection = ... and connection.Query<T>(sql, new { ... }) with connectionFactory.Command(sql).AddParam(...).Map<T>().ToList() (or ToArray()).
If a model uses constructor binding, give it a parameterless constructor and settable properties, or write a Map(selector).
If column names differ from property names, add overrides.

Table of Contents

Coming from Dapper

The steps, side by side

A list of rows

One row

A scalar

A command with no result set

A transaction

A connection you own

The total

Who owns what

Registering the factory once

Renamed columns

Async and cancellation

Where Dapper does more

Migrating a call site