CSharpDB.Data 4.6.2

CSharpDB.Data

ADO.NET provider for the CSharpDB embedded database engine. Standard DbConnection, DbCommand, and DbDataReader with parameterized queries and transactions.

Overview

CSharpDB.Data provides a standard System.Data.Common (ADO.NET) data provider for CSharpDB. Use familiar DbConnection/DbCommand/DbDataReader patterns to query and modify your database. Supports parameterized queries, transactions, prepared statements, prepared-template caching, schema introspection, embedded file-backed and in-memory connection modes, and daemon-backed remote connections over the CSharpDB.Daemon gRPC host.

Key Types

SQL Types and ADO.NET Values

CSharpDB keeps a declared logical SQL type separately from its compact physical record carrier. CSharpDbDataReader.GetDataTypeName() returns the canonical logical spelling (including facets and ROWVERSION), while GetFieldType() and GetValue() materialize the logical CLR value:

See the complete SQL data type reference for declarations, aliases, and facets. Metadata from databases that predate declared types falls back to its physical compatibility view: Integer becomes long, Real becomes double, Decimal becomes decimal, Text becomes string, and Blob becomes byte[].

Usage

using CSharpDB.Data;

// Open a connection
await using var connection = new CSharpDbConnection("Data Source=myapp.db");
await connection.OpenAsync();

// Create a table
await using var cmd = connection.CreateCommand();
cmd.CommandText = """
    CREATE TABLE products (
        id INTEGER PRIMARY KEY,
        name TEXT,
        price REAL
    )
    """;
await cmd.ExecuteNonQueryAsync();

// Insert with parameters
cmd.CommandText = "INSERT INTO products VALUES (@id, @name, @price)";
cmd.Parameters.AddWithValue("@id", 1);
cmd.Parameters.AddWithValue("@name", "Widget");
cmd.Parameters.AddWithValue("@price", 9.99);
await cmd.ExecuteNonQueryAsync();

// Query with a data reader
cmd.CommandText = "SELECT name, price FROM products WHERE price < @max";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@max", 50.0);

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine($"{reader.GetString(0)}: ${reader.GetDouble(1):F2}");
}

// Transactions
await using var tx = await connection.BeginTransactionAsync();
cmd.Transaction = (CSharpDbTransaction)tx;
cmd.CommandText = "INSERT INTO products VALUES (2, 'Gadget', 19.99)";
await cmd.ExecuteNonQueryAsync();
await tx.CommitAsync();

// Save an in-memory connection back to disk
await connection.SaveToFileAsync("products.db");

// Schema introspection
var csConn = (CSharpDbConnection)connection;
var tables = csConn.GetTableNames();
var schema = csConn.GetTableSchema("products");

Using DbProviderFactory

var factory = CSharpDbFactory.Instance;
await using var conn = factory.CreateConnection();
conn.ConnectionString = "Data Source=myapp.db";
await conn.OpenAsync();

Runtime Diagnostics

An open CSharpDbConnection implements the optional ICSharpDbObservabilityClient capability without adding members to the ADO.NET base classes or to ICSharpDbClient:

var diagnostics = (ICSharpDbObservabilityClient)connection;
var runtime = await diagnostics.GetRuntimeDiagnosticsAsync(ct);
var sessions = await diagnostics.GetSessionsAsync(100, ct);

Embedded diagnostics use the connection's exact physical owner: the direct session itself, its owning pool, or its named shared-memory host. They do not search a global alias registry, so unrelated databases or pools with the same alias cannot leak into the result. Disabled diagnostics retain one stable lazy identity per physical owner without creating a runtime diagnostics component; an enabled owner whose exact state is unavailable reports Unavailable instead of inventing data. Remote connections delegate to the daemon's optional capability. Calling diagnostics never opens a closed connection or creates a new database session as a side effect.

Runtime, active-query, recent-query, plan, and session results never expose raw data-source paths, transaction bearer ids, connection strings, credentials, parameters, or row values. Captured SQL is returned only by the separate query detail method and is still governed by the configured SQL-capture mode and remote host authorization.

Cancellation and query control

Async command and diagnostics cancellation tokens are forwarded to supported operations, but cancellation is cooperative. Parsing, planning, synchronous fast paths, or an operation already completing may not observe it immediately. CSharpDbCommand.CommandTimeout is not currently enforced, CSharpDbCommand.Cancel() is a no-op, and the provider has no query/session kill API. Runtime diagnostics provide visibility, not termination control.

Daemon-Backed Connections

Use a remote transport connection string when you want ADO.NET to talk to a running CSharpDB.Daemon host instead of opening the database file directly:

await using var connection = new CSharpDbConnection(
    "Transport=Grpc;Endpoint=http://localhost:5000");
await connection.OpenAsync();

await using var command = connection.CreateCommand();
command.CommandText = "SELECT COUNT(*) FROM products";
var count = (long)(await command.ExecuteScalarAsync() ?? 0L);

Connection-string rules:

• Embedded/direct mode: use Data Source=...
• Daemon-backed mode: use Transport=Grpc;Endpoint=http://...
• Data Source, Load From, and connection pooling are embedded-only options
• Endpoint requires an explicit Transport

For a daemon host, Grpc is the primary transport today:

Transport=Grpc;Endpoint=http://localhost:5000

That gives you standard ADO.NET commands and transactions against the daemon-managed database while keeping the existing embedded connection-string shape unchanged.

Transport guidance:

• Direct with Data Source=... is still faster than any network hop when the caller can open the database locally
• Grpc is the fastest supported network transport in the current CSharpDB stack
• NamedPipes is not implemented end to end today, so do not target it yet for daemon-backed ADO.NET
• for daemon-backed workloads, reuse open connections instead of reconnecting for each command

In-Memory Connection Strings

Data Source=:memory:

Creates a private in-memory database scoped to a single connection.

Data Source=:memory:shared-cache

Creates or attaches to a named shared in-memory database within the current process.

Data Source=:memory:shared-cache;Load From=seed.db

Seeds an in-memory database from seed.db on first open. For named shared memory, later opens must either omit Load From or use the same source path.

Named shared in-memory connections allow multiple live connections at once. One connection may own an explicit transaction at a time; other connections can still run reads against the last committed snapshot while that transaction is active.

Embedded Storage Tuning

You can now push engine-level embedded tuning through the ADO.NET surface without dropping down to Database.OpenAsync(...).

Direct engine options:

using CSharpDB.Data;
using CSharpDB.Engine;

var directOptions = new DatabaseOptions()
    .ConfigureStorageEngine(builder => builder.UseWriteOptimizedPreset());

await using var connection = new CSharpDbConnection("Data Source=ingest.db", directOptions);
await connection.OpenAsync();

Hybrid embedded mode:

await using var connection = new CSharpDbConnection("Data Source=app.db")
{
    DirectDatabaseOptions = new DatabaseOptions()
        .ConfigureStorageEngine(builder => builder.UseDirectLookupOptimizedPreset()),
    HybridDatabaseOptions = new HybridDatabaseOptions
    {
        PersistenceMode = HybridPersistenceMode.IncrementalDurable,
    },
};

await connection.OpenAsync();

Convenience connection-string keywords:

Data Source=app.db;Storage Preset=WriteOptimized
Data Source=app.db;Storage Preset=DirectColdFileLookup;Embedded Open Mode=Direct
Data Source=app.db;Storage Preset=WriteOptimized;Embedded Open Mode=HybridIncrementalDurable

Supported Storage Preset values:

• DirectLookupOptimized
• DirectColdFileLookup
• HybridFileCache
• WriteOptimized
• LowLatencyDurableWrite

Supported Embedded Open Mode values:

• Direct
• HybridIncrementalDurable
• HybridSnapshot

Configuration rules:

• explicit DirectDatabaseOptions override Storage Preset
• explicit HybridDatabaseOptions override Embedded Open Mode
• connection-string keywords fill gaps only when the corresponding explicit options object is absent
• remote transports and named shared-memory databases reject embedded tuning
• private :memory: supports direct tuning, but not hybrid open modes

Pooling note:

• file-backed pooling is now options-aware
• distinct explicit options object instances do not share a pool in v1
• one warm embedded engine is multiplexed across pooled logical sessions
• Max Pool Size limits simultaneous logical sessions over that engine
• repeated opens on one connection reuse its validated embedded configuration; short-lived connections that reuse the same live connection-string instance can also share a weak prepared plan for an absolute pooled file target
• checkout from an existing healthy pool avoids registry-wide coordination
• logical close skips reader and temporary-state cleanup only when the session is proven clean; engine-observed temporary contexts, including trigger-created or failed-operation state, remain conservatively cleaned before reuse
• persistent queries stream from committed WAL snapshots, so a reader does not block data-only writes and does not observe their uncommitted changes
• persistent schema changes report the database as busy while a snapshot reader is active; after a session attempts schema DDL inside an explicit transaction, other sessions cannot start reads until that transaction commits or rolls back
• a logical session that owns temporary-table state reports the database as busy while another session owns an explicit write transaction, because that temporary state cannot be combined safely with the committed persistent snapshot
• opening the same file with an incompatible pooled configuration replaces an idle pool and is rejected while connections from the prior configuration remain open
• pooled and explicitly non-pooled physical ownership cannot overlap for the same file; a non-pooled open retires an idle pool and is rejected while pooled logical sessions remain open
• ClearPool(connectionString) clears all pooled entries for the normalized file-backed target

Connection Pooling (Opt-In)

Connection pooling is disabled by default for standalone ADO.NET callers. Enable it explicitly in the connection string:

Data Source=myapp.db;Pooling=true;Max Pool Size=16

Provider-created EF Core file connections enable pooling by default. Add Pooling=false to the EF connection string when a physical close after each operation is required.

For the lowest ADO.NET lifecycle overhead, keep the connection string in a reused variable instead of rebuilding an equivalent string for every connection. Reusing one CSharpDbConnection is the cheapest shape, while short-lived connection objects using that same string also reuse the prepared absolute-file plan. Relative file targets are deliberately resolved on every open when the process working directory changes, and connections with explicit DatabaseOptions or HybridDatabaseOptions retain options-identity isolation.

To force-release pooled physical connections (for example before deleting database files):

CSharpDbConnection.ClearPool("Data Source=myapp.db;Pooling=true;Max Pool Size=16");
CSharpDbConnection.ClearAllPools();

ClearPool and ClearAllPools also clear named shared in-memory hosts.

Installation

dotnet add package CSharpDB.Data

For the recommended all-in-one package:

dotnet add package CSharpDB

Dependencies

• CSharpDB.Engine - embedded database engine

Related Packages

License

MIT - see LICENSE for details.