Recursive AI

Execute a SQLite command batch in C#

OneLite is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

A command batch groups SQL commands with their own parameter collections in one ADO.NET object.

Terminal
dotnet add package RecursiveAI.OneLite.Data

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

C#
using RecursiveAI.OneLite.Data;
 
using var connection = new OneLiteConnection(
    "Data Source=:memory:");
connection.Open();
using var batch = connection.CreateBatch();
batch.BatchCommands.Add(new OneLiteBatchCommand(
    "CREATE TABLE items(name TEXT)"));
var insert = new OneLiteBatchCommand(
    "INSERT INTO items VALUES(@name)");
insert.Parameters.AddWithValue("name", "book");
batch.BatchCommands.Add(insert);
Console.WriteLine(batch.ExecuteNonQuery());
Text
1

Unzip the example, then dotnet run.

Full program

The complete program creates an in-memory items(id INTEGER PRIMARY KEY, name TEXT) table. It inserts book and tool through separate parameterized commands, prints the affected-row total, then reads names and a count from two result sets.

C#
using RecursiveAI.OneLite.Data;
 
using var connection = new OneLiteConnection("Data Source=:memory:");
connection.Open();
using var setup = connection.CreateCommand();
setup.CommandText = "CREATE TABLE items(id INTEGER PRIMARY KEY, name TEXT)";
setup.ExecuteNonQuery();
using var batch = connection.CreateBatch();
foreach (string name in new[] { "book", "tool" })
{
    var command = new OneLiteBatchCommand(
        "INSERT INTO items(name) VALUES(@name)");
    command.Parameters.AddWithValue("name", name);
    batch.BatchCommands.Add(command);
}
Console.WriteLine($"Affected: {batch.ExecuteNonQuery()}");
batch.BatchCommands.Clear();
batch.BatchCommands.Add(new OneLiteBatchCommand("SELECT name FROM items ORDER BY id"));
batch.BatchCommands.Add(new OneLiteBatchCommand("SELECT count(*) FROM items"));
using var reader = batch.ExecuteReader();
while (reader.Read()) Console.WriteLine(reader.GetString(0));
reader.NextResult();
while (reader.Read()) Console.WriteLine($"Count: {reader.GetInt64(0)}");
Text
Affected: 2
book
tool
Count: 2

A missing parameter value throws InvalidOperationException; engine failures surface as OneLiteDataException.

Variations

Read a scalar from a separate batch after disposing the reader. Add these lines at the end of the full program.

C#
reader.Dispose();
using var totals = connection.CreateBatch();
totals.BatchCommands.Add(new OneLiteBatchCommand("SELECT count(*) FROM items"));
Console.WriteLine($"Scalar: {totals.ExecuteScalar()}");
Text
Scalar: 2

The short answer

Use the RecursiveAI.OneLite.Data ADO.NET provider. Open a OneLiteConnection, call CreateBatch(), add OneLiteBatchCommand objects and execute the batch. Each command owns its parameters, so values do not need to be concatenated into SQL text. The snippet at the top creates a table and inserts one parameterized value.

Use ExecuteNonQuery() for a write batch and its affected-row total. Use ExecuteReader() when you need result sets. These methods differ in transaction behavior: nonquery and scalar batches execute atomically, while a reader batch does not add a transaction. The provider documentation describes that distinction.

How do I batch SQLite inserts in C#?

Add one insert command per item, each with a placeholder such as @name and a matching parameter. Reusing the same placeholder name in different commands is fine: the collections belong to those commands, not to the batch as a whole. The complete program binds book and tool separately and reports two affected rows.

Parameters carry values, not identifiers or SQL syntax. Keep table names and column names in trusted command text. For a database NULL, bind DBNull.Value; a parameter whose Value is C# null is rejected. A placeholder with no supplied value also throws InvalidOperationException rather than silently binding NULL.

Batching here is a way to organize commands and choose execution semantics. The example makes no throughput claim and does not compare the batch with an insert loop. For a very large import, decide how many records each transaction should contain based on the application’s rollback and resource requirements rather than copying this tiny fixture unchanged.

How do I use a SQLite transaction in C#?

For ExecuteNonQuery and ExecuteScalar, OneLite creates a transaction when the connection has none. The commands apply together or none apply. When a transaction already exists, the batch uses it; the caller remains responsible for committing or rolling it back. Disposing an uncommitted provider transaction rolls it back.

Reader execution is different: ExecuteReader adds no transaction. If several reads and writes must share one atomic unit, start a transaction explicitly before executing that reader batch. Do not infer atomicity just because commands sit inside the same DbBatch object.

A connection is not thread-safe. Keep a batch and its connection within one coordinated operation rather than sharing them between concurrent callers. Closing a reader and disposing commands does not replace an explicit commit when your code owns the transaction.

How do I read multiple SQLite result sets in C#?

Call ExecuteReader(), consume rows with Read(), then call NextResult() for the next result set. The complete program first reads ordered names and then a count. Check the boolean result of NextResult() when the command list or result shape is not fixed.

The reader’s typed getters apply to the current value’s storage class. This example uses GetString for names and GetInt64 for the count. A typed getter on NULL throws; test IsDBNull when the query can return missing values.

Dispose the reader even if you stop consuming results early. Provider readers can execute statements that remain when they are disposed, so stopping the read loop is not a cancellation mechanism. Keep reader batches free of unintended trailing writes, and use separate write batches when that separation makes the application’s behavior clearer.

All onelite recipes