Edit

Batching

  • Article

SQLite doesn't natively support batching SQL statements together into a single command. But because there's no network involved, it wouldn't really help performance anyway. Microsoft.Data.Sqlite does, however, implement statement batching as a convenience to make it behave more like other ADO.NET providers.

When calling DbCommand.ExecuteReader, statements are executed up to the first one that returns results. Calling DbDataReader.NextResult continues executing statements until the next one that returns results or until it reaches the end of the batch. Calling DbDataReader.Dispose or Close executes any remaining statements that haven't been consumed by NextResult(). If you don't dispose a data reader, the finalizer tries to execute the remaining statements, but any errors it encounters are ignored. Because of this, it's important to dispose DbDataReader objects when using batches.

// Batch two SELECT statements into a single command
var command = connection.CreateCommand();
command.CommandText =
@"
    SELECT *
    FROM blog;

    SELECT *
    FROM post;
";
using (var reader = command.ExecuteReader())
{
    // Read the first result set
    while (reader.Read())
    {
        Console.WriteLine($"Blog {reader["id"]}: {reader["name"]}");
    }

    // Read the second result set
    reader.NextResult();
    while (reader.Read())
    {
        Console.WriteLine($"Post {reader["id"]}: {reader["title"]}");
    }
}

See also