---
title: "Execute a SQLite command batch in C#"
url: https://recursiveai.org/onelite/cookbook/execute-sqlite-command-batch/
description: "Run parameterized ADO.NET command batches with OneLite, inspect affected rows and read multiple result sets in a runnable .NET example."
version: "1.0.0"
---

# 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.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite.Data) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/execute-sqlite-command-batch/execute-sqlite-command-batch.zip)

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

```shell
dotnet add package RecursiveAI.OneLite.Data
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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](https://recursiveai.org/onelite/cookbook/execute-sqlite-command-batch/execute-sqlite-command-batch.zip), 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.

```csharp
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.

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

```text
Scalar: 2
```

## Related recipes

- [Inspect a SQLite query plan in C#](https://recursiveai.org/onelite/cookbook/inspect-sqlite-query-plan/)
- [Recover rows from a damaged SQLite file in C#](https://recursiveai.org/onelite/cookbook/recover-rows-from-damaged-sqlite-file/)

## 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](https://recursiveai.org/onelite/docs/data/) 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.
