Expressive commands

An expressive command is a small fluent object: pick the SQL (or stored procedure), add parameters, then call a terminal that runs it.

Two starting points

The factory form (connectionFactory.Command(sql) or connectionFactory.StoredProcedure(name)) and the connection form (connection.Command(sql) or connection.StoredProcedure(name)) look the same after the first call. Side by side, row against row:

Factory form

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

Connection form

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

In the factory form the command creates, opens and disposes a connection for every terminal. In the connection form the connection is yours: the command opens it only when it is closed, closes only what it opened, and never disposes it. See who owns the connection.

A command is not safe to share across threads: one built from a single connection throws InvalidOperationException if two terminals run at once.

Which command type you get

You call it on	You get	Reader type
IDbConnection, IDbTransaction, IDbConnectionFactory	ExpressiveCommand	IDataReader
DbConnection, DbTransaction, IDbConnectionFactory<TConnection>	ExpressiveDbCommand	DbDataReader (async reads)
SqlConnection or a SqlConnectionFactory (MSSqlClient package)	ExpressiveSqlCommand	SqlDataReader

Pass a CommandType as the second argument of Command if you need TableDirect.

Parameters

AddParam(name, value) adds an input parameter. Overloads accept a database type and a ParameterDirection. AddParamIf(condition, name, value) adds the parameter only when the condition is true, which keeps the SQL text constant:

// AddParamIf adds the parameter only when the condition is true: use it when the SQL text varies with the same condition.
string sql = author.HasValue
    ? "select * from posts where author = @author order by id"
    : "select * from posts order by id";

Post[] posts = connectionFactory
    .Command(sql)
    .AddParamIf(author.HasValue, "author", author)
    .Map<Post>()
    .ToArray();

AddReturnParam registers a return value; call ExecuteReturn<T>() to read it.

Terminals

Terminal	Returns
ExecuteNonQuery()	Rows affected
ExecuteScalar(), ExecuteScalar<T>()	The first column of the first row
Execute(command => ...)	Whatever your function returns, given the raw IDbCommand
ExecuteReader(reader => ...)	Whatever your function returns, given the live reader
Map<T>() then a terminal	Mapped objects: see Mapping rows to objects
ToChannel<T>(), ToTargetBlock<T>()	See Channels and Dataflow

ExpressiveDbCommand and ExpressiveSqlCommand add Async twins (for example ExecuteNonQueryAsync()) that honor the command's cancellation token (UseCancellationToken(token)). SetTimeout(seconds) sets the command timeout.

ExecuteScalar<T>() casts the value the provider returns, so T must be that exact type. SQLite returns a long for count(*) and SQL Server returns an int. To convert, pass a function instead:

// ExecuteScalar<T> casts the value the provider returns, so T must be that exact type
// (SQLite counts as long; SQL Server counts as int). Use the Func overload to convert instead.
long count = connectionFactory
    .Command("select count(*) from posts where author = @author")
    .AddParam("author", 42)
    .ExecuteScalar<long>();

int updated = connectionFactory
    .Command("update posts set views = views + 1 where author = @author")
    .AddParam("author", 42)
    .ExecuteNonQuery();

int count = connectionFactory
    .Command("select count(*) from posts")
    .ExecuteScalar(value => Convert.ToInt32(value));