Command Batches

To execute multiple SQL statements in one database call, which can improve performance for databases with significant latency, chain multiple calls to Command and/or CommandFormat. MuchAdo uses ADO.NET provider support for DbBatch to efficiently execute multiple commands.

var newWidgetId = connector
    .CommandFormat(
        $"insert into widgets (name, height) values ({name}, {height})")
    .Command("select last_insert_id()")
    .QuerySingle<long>();

If your provider does not support DbBatch, you may be able to execute multiple statements in a single command by separating the SQL statements with semicolons.

var nextWidgetId = connector
    .CommandFormat($"""
        insert into widgets (name, height) values ({name}, {height});
        select last_insert_id();
        """)
    .QuerySingle<long>();

tip

Batching commands with embedded databases like SQLite may actually hurt performance. Consider executing each command separately instead.

Note that any timeout specified by WithTimeout applies to the entire command batch, not an individual command.

Reading result sets

If only one of the SQL statements returns data records, or if all of the statements return the same kind of record, you can read the data as usual with one of the Query or Enumerate methods.

If each statement returns a different kind of data record, call QueryMultipleAsync to get a disposable result set reader. For each statement that returns records, call one of the Read or Enumerate methods.

await using (var reader = await connector
    .Command("select name from widgets where height < 5")
    .Command("select id from widgets where height >= 5")
    .QueryMultipleAsync())
{
    shortWidgetNames.AddRange(await reader.ReadAsync<string>());
    longWidgetIds.AddRange(await reader.ReadAsync<long>());
}

QueryMultipleAsync also has an overload with a delegate parameter that automatically disposes the result set reader.

var (moreWidgetNames, moreWidgetIds) = await connector
    .Command("select name from widgets where height < 5")
    .Command("select id from widgets where height >= 5")
    .QueryMultipleAsync(async reader =>
        (ShortWidgetNames: await reader.ReadAsync<string>(),
            LongWidgetIds: await reader.ReadAsync<long>()));

Empty command batch

When building a command batch in a loop, it may be simpler to call CreateCommandBatch and start with an empty command batch.

var batch = connector.CreateCommandBatch();
foreach (var widget in widgetsToCreate)
{
    batch.CommandFormat($"""
        insert into widgets (name, height)
            values ({widget.Name}, {widget.Height})
        """);
    batch.Command("select last_insert_id()");
}
var newWidgetIds = await batch.QueryAsync<long>();

ADO.NET access

While a command batch is being executed, the ActiveBatch property of the connector will be set to the corresponding DbBatch.