Connections

See how NextORM acquires, reuses and disposes the database connection, and how the connection-lifecycle role differs from the context itself.

Prerequisites: Quickstart · Dependency injection · Query reuse.

Overview

The SQL providers (SqliteDataContext, SqlServerDataContext, PostgresDataContext) all derive from DataContext, which implements both IDataContext and IConnectionManager. A context is built either from a connection string (the context creates and owns the connection) or from an already-built DbConnection (the caller owns it). The in-memory provider implements only IDataContext - it has no connection at all.

Connection string vs supplied connection

Every SQL provider exposes two constructors, and the Use… builder extensions wrap them:

// A: the context creates and owns the connection from the string.
using var owned = new SqliteDataContext("Data Source=app.db", new DataContextBuilder());

// B: the caller owns the connection; the context reuses this exact instance.
using var supplied = new SqliteConnection("Data Source=app.db");
using var reused = new SqliteDataContext(supplied, new DataContextBuilder());

// Equivalent through the builder:
var builderA = new DataContextBuilder().UseSqlite("app.db");
var builderB = new DataContextBuilder().UseSqlite(supplied);

ConnectionString returns the string the context was built with, or the supplied connection's own ConnectionString when the context was built from a connection:

using var ctx = new SqliteDataContext("Data Source=:memory:", new DataContextBuilder());
ctx.ConnectionString; // "Data Source=:memory:"

using var supplied = new SqliteConnection("Data Source=:memory:");
using var ctx2 = new SqliteDataContext(supplied, new DataContextBuilder());
ctx2.ConnectionString; // supplied.ConnectionString

Connection reuse

GetConnection creates the connection on first access and returns the same instance on every subsequent call. EnsureConnectionOpen opens it if it is closed; EnsureConnectionOpenAsync(CancellationToken) is the cancellable asynchronous form.

using var ctx = new SqliteDataContext("Data Source=app.db", new DataContextBuilder());

ctx.EnsureConnectionOpen();

bool same = ReferenceEquals(ctx.GetConnection(), ctx.GetConnection()); // true

Because the connection is reused, a supplied connection is the way to keep a per-connection database alive. A SQLite :memory: database is scoped to its connection, so a table created through a supplied connection is visible to the context only because the context runs on that very connection:

using var supplied = new SqliteConnection("Data Source=:memory:");
supplied.Open();

using (var setup = supplied.CreateCommand())
{
    setup.CommandText = "create table simple_entity (id integer primary key);" +
                        "insert into simple_entity (id) values (42);";
    setup.ExecuteNonQuery();
}

using var ctx = new SqliteDataContext(supplied, new DataContextBuilder());
var rows = ctx.From<ISimpleEntity>().Select(x => x.Id).ToList(); // [42]

Disposal rules

An owned connection (created from a connection string) is disposed by Dispose() / DisposeAsync(). Cached prepared commands that were bound to it are reset so they rebind on the next use.
A supplied connection is never disposed by the context - it is left in whatever state it was in. The caller owns it.

var owned = new SqliteDataContext("Data Source=:memory:", new DataContextBuilder());
var ownedConn = owned.GetConnection();
owned.EnsureConnectionOpen();
owned.Dispose();
// ownedConn.State == ConnectionState.Closed

using var supplied = new SqliteConnection("Data Source=:memory:");
supplied.Open();
using (var ctx = new SqliteDataContext(supplied, new DataContextBuilder()))
{
    ctx.EnsureConnectionOpen();
}
// supplied.State == ConnectionState.Open

Because IDataContext is registered as scoped in DI and implements IDisposable/IAsyncDisposable, disposing the scope disposes an owned connection automatically. See Dependency injection.

`IConnectionManager`

Connection lifecycle is a separate role, deliberately not part of IDataContext, so a provider without a connection (the in-memory context) is not forced to implement a no-op:

public interface IConnectionManager
{
    DbConnection GetConnection();
    void EnsureConnectionOpen();
    Task EnsureConnectionOpenAsync(CancellationToken cancellationToken = default);
}

DataContext implements it, so casting a context yields the same connection as the public GetConnection:

using var ctx = new SqliteDataContext("Data Source=app.db", new DataContextBuilder());
bool same = ReferenceEquals(((IConnectionManager)ctx).GetConnection(), ctx.GetConnection()); // true

Transactions

A transaction is a separate role too, so a context without a connection (the in-memory one) does not implement it. DataContext implements ITransactionManager: start a transaction with BeginTransaction*, or enlist in one owned by EF Core, Dapper or raw ADO.NET with UseTransaction. Savepoints (CreateSavepoint*/RollbackToSavepoint*/ReleaseSavepoint*) run on the active transaction or on one enlisted with UseTransaction, and never change its ownership. SQLite, PostgreSQL, SQL Server and MySQL/MariaDB support it; ClickHouse rejects it and the in-memory provider does not implement it. See Transactions for the full guide.

Command timeout

Every command nextorm executes exposes the ADO.NET DbCommand.CommandTimeout (seconds). The default is the provider's: nextorm does not touch the property unless you configure a timeout.

Set a context-wide default with UseCommandTimeout; it applies to every SELECT, DML and ExecuteScalar command of the contexts the builder creates:

using var ctx = new DataContextBuilder()
    .UseSqlite("app.db")
    .UseCommandTimeout(15)
    .CreateDataContext();

Override it for a single query with WithCommandTimeout(seconds) on the builder or on the built command (EntityBuilder<TEntity>, EntityBuilder and QueryCommand<TResult>):

var ids = ctx.From<ISimpleEntity>()
    .WithCommandTimeout(5)          // this query only
    .Select(x => x.Id)
    .ToList();

var slowIds = ctx.From<ISimpleEntity>()
    .Select(x => x.Id)
    .WithCommandTimeout(120)        // on QueryCommand<T> too
    .ToList();

Notes:

No call (null) and a value of zero or less keep the provider default; the property is not changed, so there is no cost when the setting is unused. WithCommandTimeout(0) clears the per-query override and inherits the context default.
The resolved value is part of the plan-cache key, so two otherwise identical queries with different timeouts do not share a cached command and the timeout never leaks from one query to the next. Contexts with different UseCommandTimeout values do not share a cached plan either.
The per-query override is available on read queries; DML commands and batches always use the context default.
The in-memory context has no command and ignores the setting.
Provider caveat: ClickHouse.Driver 1.4.0 exposes the inherited property, but an HTTP request is bounded by the connection Timeout (default two minutes), not CommandTimeout; the value has no effect there. See Limitations.

Provider differences

Provider	Connection type	Notes
SQLite	`SqliteConnection`	`OnConnectionCreated` registers the custom aggregate functions (`stdev`, `stdevp`, `var`, `varp`) for both created and supplied connections.
SQL Server	`SqlConnection`	No connection callback; a null parameter value is sent as `DBNull`.
PostgreSQL	`NpgsqlConnection`	No connection callback; a null parameter value is sent as `DBNull`.
MySQL	`MySqlConnection`	No connection callback; a null parameter value is sent as `DBNull`.
MariaDB	`MySqlConnection`	Uses the MySQL driver and dialect; a null parameter value is sent as `DBNull`.
ClickHouse	`ClickHouseConnection`	Parameters are sent as HTTP query parameters; a null value cannot have its ClickHouse type inferred, so set an explicit driver-level parameter type.
In-memory	none	`InMemoryDataContext` implements `IDataContext` only and does not expose `GetConnection`/`IConnectionManager`.

Table of Contents