Linger.DataAccess 2.0.0-preview.1

Linger.DataAccess

Migration guide for 2.0

Provider-neutral ADO.NET execution, connection/transaction lifetime management, and common query semantics.

Target frameworks

• .NET 10
• .NET 9
• .NET 8
• .NET Framework 4.7.2

BeginTransAsync, CommitAsync, RollbackAsync, and ExecuteTransactionAsync are available on .NET 8 and later. Use their synchronous counterparts on .NET Framework 4.7.2 instead of wrapping synchronous transactions as asynchronous work.

Architecture

• BaseDatabase: commands, connections, transactions, cancellation, and parameter ownership.
• Database: datasets, tables, entity/list mapping, counts, batch queries, and parameterized batch transactions.
• Provider helpers: only provider-specific capabilities.

Database accepts a standard DbProviderFactory; there is no custom provider abstraction.

using Linger.DataAccess;
using Microsoft.Data.SqlClient;

using var database = new Database(SqlClientFactory.Instance, connectionString);

Queries

DataSet allResults = database.Query("SELECT * FROM Users; SELECT * FROM Roles");
DataTable users = database.QueryTable(
    "SELECT * FROM Users WHERE Active = @active",
    new SqlParameter("@active", true));

List<User> mapped = database.FindListBySql<User>("SELECT Id, Name FROM Users");
List<User> asyncMapped = await database.FindListBySqlAsync(
    "SELECT Id, Name FROM Users",
    record => new User(record.GetInt32(0), record.GetString(1)),
    cancellationToken: cancellationToken);

using var connection = new SqlConnection(connectionString);
await connection.OpenAsync(cancellationToken);

using SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT Name FROM Users WHERE Active = @active";
command.Parameters.Add(new SqlParameter("@active", true));

using SqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken);

var streamedNames = new List<string>();
while (await reader.ReadAsync(cancellationToken))
{
    streamedNames.Add(reader.GetString(0));
}

DataSet and DataTable materialization is intentionally synchronous because the BCL fill APIs are synchronous. For asynchronous materialization, use FindListBySqlAsync. For streaming, use the provider's native ADO.NET API so the connection, command, and reader all have explicit caller-owned lifetimes. Linger.DataAccess does not expose an API that creates hidden command and connection resources while returning only a raw DbDataReader.

Existence and counts

bool hasRow = database.HasRows(
    "SELECT 1 FROM Users WHERE Email = @email",
    new SqlParameter("@email", email));

int count = await database.FindCountBySqlAsync(
    "SELECT COUNT(*) FROM Users WHERE Active = @active",
    [new SqlParameter("@active", true)],
    cancellationToken);

Use HasRows for row existence and FindCountBySql when the numeric count is needed.

Parameterized batch transactions

var statements = new[]
{
    new SqlStatement("UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id",
        new SqlParameter("@amount", 100), new SqlParameter("@id", 1)),
    new SqlStatement("UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id",
        new SqlParameter("@amount", 100), new SqlParameter("@id", 2)),
};

int[] affected = await database.ExecuteTransactionAsync(statements, cancellationToken);

ExecuteTransactionAsync is available on .NET 8 and later. Use ExecuteTransaction on .NET Framework 4.7.2.

The transaction is committed only when every statement succeeds. Failure rolls back and rethrows the original error. Do not call this API while an ambient transaction started by BeginTrans is active.

Large IN queries

DataTable result = database.QueryInBatches(
    "SELECT * FROM Orders WHERE OrderId IN ({0})",
    orderIds,
    batchSize: 500);

Values are parameterized and split into batches. The first batch defines the returned DataTable schema. Use this API only for row queries whose batch results can be concatenated directly. It does not preserve global ordering, paging, aggregation, or distinct semantics.

Optional capabilities

Provider-specific features are exposed as capability interfaces. SQL Server implements IBulkInsert:

if (database is IBulkInsert bulkInsert)
{
    await bulkInsert.BulkInsertAsync(table, "dbo.Users", cancellationToken: cancellationToken);
}