Table of Contents

Running queries

Define a command once, run it however you need. Each block below is a complete example.

Define once, run

A QueryCommand is built from a SQL string and reused for the life of the app. Execution methods sit directly on it.

using RinkuLib.Queries;
using RinkuLib.Commands;

public record Track(int Id, string Name, decimal UnitPrice);

static readonly QueryCommand GetTracks = new(
    "SELECT TrackId AS Id, Name, UnitPrice FROM tracks WHERE AlbumId = @albumId");

using DbConnection cnn = GetConnection();

List<Track> tracks = GetTracks.Query<List<Track>>(cnn, new { albumId = 1 });

The command holds no per-call state and is safe to share across threads. Per-call values travel in the arguments.

The type argument picks the shape

Same command, different T, different result.

List<Track> all       = GetTracks.Query<List<Track>>(cnn, new { albumId = 1 });        // buffered
IEnumerable<Track> it = GetTracks.Query<IEnumerable<Track>>(cnn, new { albumId = 1 }); // streamed
Track track           = GetTrackById.Query<Track>(cnn, new { id = 10 });               // one, throws if absent
Optional<Track> maybe = GetTrackById.Query<Optional<Track>>(cnn, new { id = 99 });     // one or empty

Every shape and its zero-row behavior is on result shapes.

Parameters

Pass an object whose members match the parameter names, case-insensitive. Anonymous objects, records, and DTOs all work, and unmatched members are ignored.

static readonly QueryCommand ByComposer = new(
    "SELECT TrackId AS Id, Name, UnitPrice FROM tracks WHERE Composer = @composer AND UnitPrice >= @minPrice");

var tracks = ByComposer.Query<List<Track>>(cnn, new { composer = "AC/DC", minPrice = 0.99m });

When C# logic should set the values instead, use a builder. Both roads are covered on supplying values.

var b = ByComposer.StartBuilder();
b.Use("@composer", "AC/DC");
b.Use("@minPrice", 0.99m);
var tracks = b.Query<List<Track>>(cnn);

Writes and scalars

Execute returns the affected-row count, ExecuteScalar<T> a single value.

static readonly QueryCommand UpdatePrice = new(
    "UPDATE tracks SET UnitPrice = @price WHERE TrackId = @id");
static readonly QueryCommand CountTracks = new("SELECT COUNT(*) FROM tracks");

int affected = UpdatePrice.Execute(cnn, new { id = 10, price = 1.29m });
int total    = CountTracks.ExecuteScalar<int>(cnn);

Async

Every method has an async version. StreamQueryAsync returns an IAsyncEnumerable<T>.

List<Track> tracks = await GetTracks.QueryAsync<List<Track>>(cnn, new { albumId = 1 }, ct: token);

await foreach (Track t in GetTracks.StreamQueryAsync<Track>(cnn, new { albumId = 1 }, ct: token))
    Process(t);

Transactions, timeouts, cancellation

The optional context arguments come after the parameter object.

using var trans = cnn.BeginTransaction();
UpdatePrice.Execute(cnn, new { id = 10, price = 1.29m }, transaction: trans);
trans.Commit();

var slow = GetTracks.Query<List<Track>>(cnn, new { albumId = 1 }, timeout: 60);

var rows = await GetTracks.QueryAsync<List<Track>>(cnn, new { albumId = 1 }, ct: token);

Reuse one DbCommand across a batch

Bind a builder to a single DbCommand and a loop stops rebuilding it each pass.

static readonly QueryCommand Insert = new("INSERT INTO playlists (Name) VALUES (@name)");

using var sqlCmd = new SqlCommand();
var batch = Insert.StartBuilder(sqlCmd);
foreach (var name in names) {
    batch.Use("@name", name);
    batch.Execute(cnn);
}

Several result sets

One command, several selects, read in order.

static readonly QueryCommand Dashboard = new(
    "SELECT * FROM artists WHERE ArtistId = @id; SELECT * FROM albums WHERE ArtistId = @id");

using var multi = Dashboard.ExecuteMultiReader(cnn, out DbCommand cmd, new { id = 1 });
using (cmd) {
    Artist artist      = multi.Query<Artist>();
    List<Album> albums = multi.Query<List<Album>>();
}

See multiple result sets.

The SQL string on the connection

Skip declaring a QueryCommand and hand the SQL to the connection. It caches the command by the string. More on the SQL string.

List<Track> tracks = cnn.Query<List<Track>>(
    "SELECT TrackId AS Id, Name, UnitPrice FROM tracks WHERE AlbumId = @albumId",
    new { albumId = 1 });

A DbCommand you already have

The mapping side also runs on a command you built yourself.

static readonly CachedTypeParser<Track> Tracks = new();

using var cmd = cnn.CreateCommand();
cmd.CommandText = "SELECT TrackId AS Id, Name, UnitPrice FROM tracks WHERE TrackId = @id";
cmd.Parameters.Add(new SqlParameter("@id", 10));

Track track = Tracks.Query(cmd);

See any DbCommand.

One command that adapts to its input

The template can mark parts optional, so the values you pass decide the SQL. ?@ marks a variable optional, _X spreads a collection.

static readonly QueryCommand Search = new(
    "SELECT TrackId AS Id, Name, UnitPrice FROM tracks WHERE AlbumId = ?@albumId AND GenreId IN (?@genreIds_X)");

Search.Query<List<Track>>(cnn, new { albumId = 1 });
// SELECT TrackId AS Id, Name, UnitPrice FROM tracks WHERE AlbumId = @albumId

Search.Query<List<Track>>(cnn, new { genreIds = new[] { 1, 2, 3 } });
// SELECT TrackId AS Id, Name, UnitPrice FROM tracks WHERE GenreId IN (@genreIds_1, @genreIds_2, @genreIds_3)

static readonly QueryCommand UpdateTrack = new(
    "UPDATE tracks SET Name = ?@name, UnitPrice = ?@price WHERE TrackId = @trackId");

UpdateTrack.Execute(cnn, new { trackId = 10, name = "Remastered" });
// UPDATE tracks SET Name = @name WHERE TrackId = @trackId

The ?@ toggle is a structural rule the engine applies to every keyword section alike, the WHERE and the SET list above, and just as well a projected column, a join, a group-by, or an order-by. When several queries are really one with parts switched on and off, one command replaces them all. The template syntax is its own section, conditional SQL.

Cheatsheet

Goal Method Sync return Async return
Insert / Update / Delete Execute int Task<int>
Single value ExecuteScalar<T> T Task<T>
One row (throws if none) Query<T> T Task<T>
One row or empty Query<Optional<T>> Optional<T> Task<Optional<T>>
Exactly one row Query<Single<T>> Single<T> Task<Single<T>>
Many (buffered) Query<List<T>> List<T> Task<List<T>>
Many (streamed) Query<IEnumerable<T>> IEnumerable<T> Task<IEnumerable<T>>
Many (async stream) StreamQueryAsync<T> n/a IAsyncEnumerable<T>
A raw reader ExecuteReader DbDataReader Task<DbDataReader>
Several result sets ExecuteMultiReader MultiReader Task<MultiReader>

The T in Query<T> is open, not a fixed menu. New result shapes plug in the same way the built-in ones do.

Edit this page