SQL engine

WitSqlEngine is the layer the ADO.NET provider is built on. Same SQL, same planner, same transactions, without the connection and command objects in front of it.

Two reasons to use it directly. The ceremony is smaller when nothing else expects a DbConnection. And it exposes two things ADO.NET has no place for: schema introspection typed to this engine's own definitions, and direct iterator access to a table or an index.

using OutWit.Database.Core.Builder;
using OutWit.Database.Engine;

var database = new WitDatabaseBuilder()
    .WithFilePath("app.witdb")
    .WithBTree()
    .WithTransactions()
    .Build();

using var engine = new WitSqlEngine(database, ownsStore: true);

ownsStore: true means disposing the engine disposes the database. Pass false when something else owns its lifetime.

Running SQL

var users = engine.Query("SELECT Id, Name FROM Users WHERE IsActive = 1");

foreach (var user in users)
    Console.WriteLine($"{user["Id"].AsInt64()}: {user["Name"].AsString()}");

Parameters go in a dictionary:

var recent = engine.Query(
    "SELECT * FROM Orders WHERE CreatedAt > @since",
    new Dictionary<string, object?> { ["since"] = DateTime.UtcNow.AddDays(-7) });

Values come back as WitSqlValue, which converts on request: AsInt64, AsString, AsDecimal, AsDouble, AsGuid, AsDateTime, and an OrNull variant of each for columns that permit null.

Prepared statements

Parsing and planning happen once, and the statement is then run with different values:

using var statement = engine.Prepare(
    "INSERT INTO Events (Name, At) VALUES (@name, @at)");

foreach (var e in events)
{
    statement.ClearParameters();
    statement.SetParameter("name", e.Name);
    statement.SetParameter("at", e.At);

    using var result = statement.Execute();
}

Parameter names are given without the @, which the statement adds. ClearParameters between rows matters when the sets differ: a value left from the previous row is otherwise still bound.

ExecuteBatch takes a sequence of parameter dictionaries and runs through them, which is the shorter form of the loop above and what BulkInsert uses underneath.

After an insert, LastInsertRowId and LastChangesCount on the statement carry what it did.

Schema introspection

Ask the engine about its own schema, and get back this engine's definitions rather than a DataTable:

var table = engine.GetTable("Orders");
var indexes = engine.GetTableIndexes("Orders");
var index = engine.GetIndex("IX_Orders_CustomerId");

long rows = engine.GetTableRowCount("Orders");

GetTableRowCount reads a counter kept in the catalogue, so it is constant in table size rather than a scan. It answers -1 when the table does not exist or the count is not known, which is a different thing from zero. The same counter is behind SELECT COUNT(*), and it is why counting rows here costs so much less than on an engine that walks them.

Bulk insert

Writing many rows through separate statements pays for parsing each one. The engine prepares once and binds per row:

var columns = new[] { "Name", "Email", "Age" };

var rows = new List<object?[]>
{
    ["Alice", "alice@example.com", 25],
    ["Bob",   "bob@example.com",   30]
};

int inserted = engine.BulkInsert("Orders", columns, rows);

Wrap it in a transaction to pay for one commit rather than one per row.

Iterators

For the cases where SQL is the wrong shape, the engine hands out the iterators the planner would have used. They take names rather than definition objects:

using var scan = engine.CreateTableScan("Orders");

using var seek = engine.CreateIndexSeek("Orders", "IX_Orders_CustomerId", [WitSqlValue.FromInt(42)]);

using var range = engine.CreateIndexRangeScan(
    "Orders", "IX_Orders_CreatedAt",
    startKey: WitSqlValue.FromDateTime(from), startInclusive: true,
    endKey:   WitSqlValue.FromDateTime(to),   endInclusive: false);

Every row carries _rowid as its first column, before the table's own, which is how a row is named when you need to go back to it.

An index whose metadata exists but whose physical index does not falls back to a table scan rather than failing, so a result is always a result.

This is the layer to reach for when writing tooling, or when a fixed access path beats letting the planner choose. It is also the layer where you can get it wrong, since nothing here checks that the index you picked suits the question you are asking.

Transactions

Transaction control is SQL here:

engine.Execute("BEGIN TRANSACTION");

try
{
    engine.Execute("INSERT INTO Users (Name) VALUES ('Alice')");
    engine.Execute("INSERT INTO Audit (Action) VALUES ('user created')");

    engine.Execute("COMMIT");
}
catch
{
    engine.Execute("ROLLBACK");
    throw;
}

SAVEPOINT, ROLLBACK TO and SET TRANSACTION ISOLATION LEVEL work the same way. See transactions.

Where to go next

• WitSQL, the language
• ADO.NET provider, the same engine behind DbConnection
• Core API, the layer below this one
• Indexing, what the planner does with an index