Davasorus.Utility.DotNet.SQL 2026.2.3.2

Davasorus.Utility.DotNet.SQL

Davasorus.Utility.DotNet.SQL provides utilities for executing SQL queries and managing database operations (MSSQL and SQLite) in .NET 8+ applications. It is designed for use with dependency injection (DI) and follows a strict service/client separation: end users should only interact with the Service interfaces; the Service classes handle all client interactions, error handling, and logging.

Features

• Execute SQL queries and stored procedures (MSSQL, SQLite)
• Retrieve results as DataTable, scalar values, or multi-column lists
• CRUD operations for SQLite (Get, Insert, Update, Delete)
• Strongly-typed connection options (SqlConnectionOptions) supporting Integrated Security, SQL auth, and Azure AD
• Strongly-typed SQLite connection options (SqliteConnectionOptions) supporting disk-backed and in-memory databases, ReadOnly/ReadWrite modes, foreign-key enforcement
• Fluent DI configuration with configurable timeout, batch size, and streaming reads
• Robust error handling and logging (SQS/Trello integration)
• Built-in distributed tracing with OpenTelemetry
• Configurable telemetry recording (SQL statements, connection details)
• Designed for DI: Service = Scoped, Client = Transient

Dependency Injection Setup

Recommended: Extension Methods

using Davasorus.Utility.DotNet.SQL.Configuration;

// Register with defaults (30s timeout, streaming enabled)
services.AddSqlServices();

// Or with fluent SqlOptions configuration
services.AddSqlServices(sql => sql
 .WithTimeout(60)
 .WithMaxBatchSize(500));

// Or from appsettings.json
services.AddSqlServices(configuration.GetSection("SqlOptions"));

// Or hybrid (appsettings + fluent overrides)
services.AddSqlServices(
 configuration.GetSection("SqlOptions"),
 sql => sql.WithTimeout(90));

// Register both SqlOptions and a default SqlConnectionOptions (recommended for single-DB consumers)
services.AddSqlServices(
 sql => sql.WithTimeout(60),
 conn => conn
 .WithServer("myserver")
 .WithCatalog("mydb")
 .WithTrustServerCertificate(true));

// Or register both from configuration sections
services.AddSqlServices(
 configuration.GetSection("SqlOptions"),
 configuration.GetSection("SqlConnectionOptions"));

When SqlConnectionOptions is registered via DI, every IMSSQLService method can be called without a per-call connection argument. Multi-DB consumers can pass a per-call SqlConnectionOptions to override the default for that one call.

Manual Registration

// MSSQL
services.AddScoped<IMSSQLService, MSSQLService>();
services.AddTransient<IMSSQLClient, MSSQLClient>();

// SQLite
services.AddScoped<ISqliteService, SqliteService>();
services.AddTransient<ISqliteClient, SqliteClient>();

Note: Only use the Service interfaces (IMSSQLService, ISqliteService) in your application code. The Service classes manage all communication with their respective Client classes internally.

Example Usage

Define a SqlConnectionOptions

using Davasorus.Utility.DotNet.SQL.Configuration;
using Microsoft.Data.SqlClient;

var connection = new SqlConnectionOptions
{
 DbServer = "myserver",
 DbCatalog = "mydb",
 AuthMethod = SqlAuthMethod.IntegratedSecurity, // or SqlAuth, AzureActiveDirectoryDefault
 TrustServerCertificate = true,
};

If you registered a default SqlConnectionOptions via DI (see above), you can omit the connection argument from every call below. The examples include it explicitly to show the hybrid model.

Azure AD / Entra authentication

SqlAuthMethod.AzureActiveDirectoryDefault is enabled out of the box — this package references Microsoft.Data.SqlClient.Extensions.Azure, which auto-registers the AAD authentication providers at module load. Consumers do not need any startup registration code:

var connection = new SqlConnectionOptions
{
 DbServer = "myserver.database.windows.net",
 DbCatalog = "mydb",
 AuthMethod = SqlAuthMethod.AzureActiveDirectoryDefault,
 Encrypt = true,
 TrustServerCertificate = false,
};

For pre-fetched bearer tokens, use SqlAuthMethod.AzureActiveDirectoryToken and set the token via SqlConnectionOptions.AccessToken:

using Azure.Core;
using Azure.Identity;

// Acquire a token from Entra (consumer-side; not part of this package).
var credential = new DefaultAzureCredential();
var tokenContext = new TokenRequestContext(new[] { "https://database.windows.net/.default" });
var aadToken = await credential.GetTokenAsync(tokenContext, cancellationToken);

var connection = new SqlConnectionOptions
{
 DbServer = "myserver.database.windows.net",
 DbCatalog = "mydb",
 AuthMethod = SqlAuthMethod.AzureActiveDirectoryToken,
 AccessToken = aadToken.Token,
 Encrypt = true,
 TrustServerCertificate = false,
};

await sqlService.ExecuteSqlQueryNoReturn("SELECT 1", connection: connection, cancellationToken: cancellationToken);

The package sets SqlConnection.AccessToken directly on the connection instance before opening — the token is not embedded in the connection string. The token's lifetime is the responsibility of the consumer; if it expires between calls, fetch a fresh one and construct a new SqlConnectionOptions. Azure.Identity is not a direct dependency of this package; consumers add it themselves.

Execute a SQL Query (MSSQL)

public class MyDataService
{
 private readonly IMSSQLService _mssqlService;
 public MyDataService(IMSSQLService mssqlService) => _mssqlService = mssqlService;

 public Task<DataTable> GetDataAsync(string query, SqlConnectionOptions connection, CancellationToken cancellationToken) =>
 _mssqlService.ExecuteSqlQueryReturnFullTable(query, connection: connection, cancellationToken: cancellationToken);
}

Execute a Non-Returning SQL Query (MSSQL)

public class MyDataWriter
{
 private readonly IMSSQLService _mssqlService;
 public MyDataWriter(IMSSQLService mssqlService) => _mssqlService = mssqlService;

 // Returns rows-affected (int). Throws on failure.
 public Task<int> ExecuteNonQueryAsync(string query, SqlConnectionOptions connection, CancellationToken cancellationToken) =>
 _mssqlService.ExecuteSqlQueryNoReturn(query, connection: connection, cancellationToken: cancellationToken);
}

Execute a Scalar Query (MSSQL)

// Returns a single scalar string, or null if no rows / DBNull. Throws on failure.
string? result = await _mssqlService.ExecuteSqlQueryWithReturn(
 query,
 connection: connection,
 cancellationToken: cancellationToken);

// Returns a list of strings from a specified column. Throws on failure.
List<string> results = await _mssqlService.ExecuteSqlQueryWithMultiColumnReturn(
 query,
 dataRowStr: "Name",
 connection: connection,
 cancellationToken: cancellationToken);

Execute Parameterized Queries

Pass IReadOnlyList<SqlParameter> for parameterized commands. Each SqlParameter controls its own type/size/direction (no nvarchar(4000) plan-cache poisoning).

var parameters = new SqlParameter[]
{
 new("@id", SqlDbType.Int) { Value = 42 },
 new("@status", SqlDbType.NVarChar, 50) { Value = "Active" },
};

string? name = await _mssqlService.ExecuteSqlQueryWithReturn(
 "SELECT Name FROM Users WHERE Id = @id AND Status = @status",
 parameters: parameters,
 connection: connection,
 cancellationToken: cancellationToken);

Execute Stored Procedures (MSSQL)

// Non-returning stored procedure
await _mssqlService.ExecuteNonReturningStoredProcedure(
 "usp_UpdateRecord",
 parameters: new SqlParameter[]
 {
 new("@Id", SqlDbType.Int) { Value = 1 },
 new("@Name", SqlDbType.NVarChar, 100) { Value = "Updated" },
 },
 connection: connection,
 cancellationToken: cancellationToken);

// Stored procedure returning a DataTable
DataTable result = await _mssqlService.ExecuteReturningStoredProcedure(
 "usp_GetRecords",
 parameters: new SqlParameter[]
 {
 new("@Status", SqlDbType.NVarChar, 20) { Value = "Active" },
 },
 connection: connection,
 cancellationToken: cancellationToken);

Error handling

All IMSSQLService methods throw on failure (no sentinel-error returns). The telemetry helper records OpenTelemetry metrics + sends a single SQS error message before re-throwing the original exception (with stack preserved via ExceptionDispatchInfo). Wrap calls in try/catch if you need to handle SQL errors locally.

SQLite Operations

Breaking change (slice G): The SQLite types were renamed from SqlLite* to Sqlite* (SqliteService, ISqliteService, SqliteClient, ISqliteClient) to match the .NET ecosystem convention (Microsoft.Data.Sqlite, EF Core's SQLite provider). The method EnsureSqlLiteDBExists was renamed to EnsureDatabaseExists. Consumers must update their usings and recompile; no deprecation aliases are provided.

using Davasorus.Utility.DotNet.SQL.Configuration;
using Microsoft.Data.Sqlite;

var sqliteConnection = new SqliteConnectionOptions
{
 DataSource = Path.Combine("/var/data", "app.db"),
 OpenMode = SqliteOpenMode.ReadWriteCreate,
 ForeignKeys = true,
};

public class MySqliteDataService
{
 private readonly ISqliteService _sqliteService;
 public MySqliteDataService(ISqliteService sqliteService) => _sqliteService = sqliteService;

 // Ensure the SQLite file exists (no-op if already present). Throws NotSupportedException
 // for :memory: connections — file existence is meaningless for in-process databases.
 public Task<bool> EnsureDbAsync(SqliteConnectionOptions connection, CancellationToken cancellationToken) =>
 _sqliteService.EnsureDatabaseExists(connection, cancellationToken);

 // Get data — IReadOnlyList<SqliteParameter> for parameterized queries
 public Task<DataTable> GetDataAsync(
 string command,
 IReadOnlyList<SqliteParameter>? parameters,
 SqliteConnectionOptions connection,
 CancellationToken cancellationToken
 ) => _sqliteService.GetDataInDB(command, parameters, connection, cancellationToken);

 // Insert / update / delete — all throw on failure (no swallowed-error returns)
 public Task InsertAsync(
 string command,
 IReadOnlyList<SqliteParameter>? parameters,
 SqliteConnectionOptions connection,
 CancellationToken cancellationToken
 ) => _sqliteService.InsertDataIntoDB(command, parameters, connection, cancellationToken);

 public Task UpdateAsync(
 string command,
 IReadOnlyList<SqliteParameter>? parameters,
 SqliteConnectionOptions connection,
 CancellationToken cancellationToken
 ) => _sqliteService.UpdateDataInDB(command, parameters, connection, cancellationToken);

 public Task DeleteAsync(
 string command,
 IReadOnlyList<SqliteParameter>? parameters,
 SqliteConnectionOptions connection,
 CancellationToken cancellationToken
 ) => _sqliteService.DeleteDataInDB(command, parameters, connection, cancellationToken);
}

For typical single-DB consumers, register the connection options once via DI:

services.AddSqlServicesWithSqlite(
 opts => opts.WithTimeout(30),
 sqlite => sqlite
 .WithDataSource("/var/data/app.db")
 .WithForeignKeys(true));

Then every call can omit the connection argument:

var rows = await _sqliteService.GetDataInDB(
 "SELECT * FROM Users WHERE Id = @id",
 new SqliteParameter[] { new("@id", SqliteType.Integer) { Value = 1 } });

In-memory databases are supported via SqliteOpenMode.Memory:

var memOptions = new SqliteConnectionOptions
{
 DataSource = ":memory:",
 OpenMode = SqliteOpenMode.Memory,
 Cache = SqliteCacheMode.Shared,
};

Note: EnsureDatabaseExists throws NotSupportedException for in-memory connections — file-existence checks have no meaning for in-process databases. Just open the connection directly.

Each SqliteParameter is cloned by the Client before binding, so callers may safely cache and reuse a parameter list across calls without hitting "already contained by another SqliteParameterCollection" errors.

Configuration Options

The SqlOptions class provides configurable behavior:

services.AddSqlServices(sql => sql
 .WithTimeout(60) // Command timeout in seconds (default: 30)
 .WithMaxBatchSize(500) // Max batch size for batch operations (default: 100)
 .WithStreamingReads() // Use SqlDataReader for better memory (default: enabled)
 .WithValidation() // Validate connection strings before queries (default: disabled)
 .WithSqlStatementRecording() // Record SQL text in telemetry (default: disabled, security risk)
 .WithConnectionDetailsRecording()); // Record server/db names in telemetry (default: enabled)

Or via appsettings.json:

{
 "SqlOptions": {
 "CommandTimeoutSeconds": 60,
 "ValidateConnectionString": false,
 "MaxBatchSize": 100,
 "UseStreamingReads": true,
 "RecordSqlStatements": false,
 "RecordConnectionDetails": true
 }
}

Retry behavior

Every IMSSQLService and ISqliteService method automatically retries on transient database failures. The default policy uses exponential backoff with decorrelated jitter and a curated list of transient error codes.

Defaults

• Max attempts: 3 (1 initial + 2 retries)
• Base delay: 200ms
• Max delay: 2s per attempt

Total worst-case retry wall time for an exhausted chain: ~4s of delay plus the actual operation times.

Transient error classification

The default policy retries on:

SQL Server (per Microsoft's transient-error guidance):

SQLite: SQLITE_BUSY (5), SQLITE_LOCKED (6).

Other exceptions (InvalidOperationException for misconfiguration, ArgumentException, etc.) are NOT retried — they're programmer errors, not transient failures.

Note: SQL error -2 (client command timeout) is deliberately excluded from the default transient set. A client-side timeout does not prove the server-side statement was rolled back — the statement may have committed before the response read timed out. Automatic retry of non-idempotent writes (INSERT/UPDATE/EXEC stored-proc) under -2 would silently duplicate rows, which is worse than the original failure. Consumers who genuinely need timeout retry (typically for idempotent read-only workloads) can add -2 via the transientSqlErrorNumbers constructor override on ExponentialBackoffRetryPolicy, or subclass and override IsTransient.

Configuration

services.AddSqlServices(sql => sql
 .WithRetry(retry =>
 {
 retry.MaxAttempts = 5;
 retry.BaseDelay = TimeSpan.FromMilliseconds(500);
 retry.MaxDelay = TimeSpan.FromSeconds(10);
 }));

Set MaxAttempts = 1 to disable retry entirely:

services.AddSqlServices(sql => sql.WithRetry(r => r.MaxAttempts = 1));

Custom policy

For unusual needs (e.g., Polly integration, custom transient classification, circuit breakers), register your own IRetryPolicy BEFORE calling AddSqlServices:

services.AddSingleton<IRetryPolicy>(new MyCustomRetryPolicy(...));
services.AddSqlServices(...);
// AddSqlServices' TryAddSingleton no-ops; MyCustomRetryPolicy is used.

Or subclass ExponentialBackoffRetryPolicy to add custom transient codes:

public class MyExtendedRetryPolicy : ExponentialBackoffRetryPolicy
{
 public MyExtendedRetryPolicy(RetryOptions options) : base(options) { }

 protected override bool IsTransient(Exception ex)
 {
 if (base.IsTransient(ex)) return true;
 // Add custom rules, e.g. timeouts originating from a specific upstream
 return ex is MyUpstreamTransientException;
 }
}

Telemetry

Each retry attempt adds an ActivityEvent named "db.client.retry" to the operation's OpenTelemetry span, with tags attempt, delay_ms, and transient_error. After all retries complete, summary tags db.client.retries and db.client.retry.last_error are added to the span. Retries also log at Warning level.

Transactions

Both IMSSQLService and ISqliteService expose transaction support via two complementary forms.

Lambda form (safe default)

await service.WithTransactionAsync(async tx =>
{
 await tx.ExecuteSqlQueryNoReturn(
 "INSERT INTO Orders (CustomerId, Amount) VALUES (@cid, @amt)",
 parameters: new SqlParameter[]
 {
 new("@cid", SqlDbType.Int) { Value = customerId },
 new("@amt", SqlDbType.Decimal) { Value = amount },
 });

 await tx.ExecuteSqlQueryNoReturn(
 "UPDATE Customers SET LastOrderAt = SYSUTCDATETIME() WHERE Id = @cid",
 parameters: new SqlParameter[] { new("@cid", SqlDbType.Int) { Value = customerId } });
});

• Both INSERTs commit together; or both roll back if anything throws.
• Transient errors (deadlock 1205, connection-reset, Azure SQL throttling) retry the WHOLE transaction by re-running the lambda from scratch.
• A value-returning overload is available: await service.WithTransactionAsync<Order>(async tx => { ...; return order; }).

Lambda re-execution safety: under whole-transaction retry, the lambda may be invoked multiple times. All SQL operations inside the lambda are part of the transaction and are correctly rolled back before retry, but side effects OUTSIDE the transaction (sending external messages, publishing events, mutating non-transactional state) will be repeated. Keep lambda bodies SQL-only or idempotent.

Disposable form (explicit lifetime)

await using var tx = await service.BeginTransactionAsync();
try
{
 await tx.ExecuteSqlQueryNoReturn("INSERT INTO ...");
 await tx.ExecuteSqlQueryNoReturn("UPDATE ...");
 await tx.CommitAsync();
}
catch
{
 await tx.RollbackAsync();
 throw;
}

• No auto-retry — the consumer manages lifetime and error handling explicitly.
• await using ensures the connection is disposed even if the consumer forgets to call Commit/Rollback. The default in that case is rollback (defensive — catches forgotten commits).
• Double-commit / commit-after-rollback throw InvalidOperationException with a clear message.

Isolation level

Both forms accept an optional IsolationLevel? parameter (System.Data.IsolationLevel BCL enum):

await service.WithTransactionAsync(
 async tx => { /* ... */ },
 isolationLevel: IsolationLevel.Serializable);

Null defers to the driver's default: ReadCommitted for SqlClient, Deferred BEGIN for Microsoft.Data.Sqlite.

Out of scope

• Nested transactions / savepoints. The Transaction interfaces deliberately don't expose BeginTransactionAsync / WithTransactionAsync. If a future need emerges, savepoints could be added as a separate slice.
• Distributed transactions (TransactionScope, MSDTC). Single-connection scope only.
• Cross-Service transactions spanning both MSSQL AND SQLite. Each provider has its own connection scope; the package doesn't coordinate cross-provider atomicity.

Telemetry

WithTransactionAsync creates a parent OpenTelemetry span (BEGIN TRANSACTION <catalog> or <dbname>) with db.client.transaction.isolation_level and db.client.transaction.outcome (committed or rolled_back) tags. Per-operation child spans inside the transaction are tagged db.client.in_transaction = true for trace-filtering. BeginTransactionAsync has no parent span (lifetime is consumer-managed), but per-operation tags are still applied.

Bulk Insert

Both providers expose a BulkInsertAsync(targetTable, DataTable, options?, connection?, cancellationToken) method on their Service:

var dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Amount", typeof(int));
foreach (var order in orders)
{
 dt.Rows.Add(order.Name, order.Amount);
}

await service.BulkInsertAsync("dbo.Orders", dt);

• MSSQL: uses SqlBulkCopy. Column mapping is by name; DataTable.Columns[i].ColumnName must match a target column. Mismatches throw ArgumentException before any wire I/O.
• SQLite: uses chunked multi-row INSERT inside an auto-managed transaction. Default batch size is 500 rows.
• No auto-retry. Bulk insert is non-idempotent; a transient-error retry could double-insert rows. Wrap in WithTransactionAsync for atomicity-with-retry.

Tuning:

await service.BulkInsertAsync(
 "dbo.Orders",
 dt,
 options: new BulkInsertOptions(BatchSize: 1000, CommandTimeoutSeconds: 60));

Streaming Reads

For large result sets, StreamSqlQueryAsync yields rows one at a time as IReadOnlyDictionary<string, object?> — no DataTable materialization:

await foreach (var row in service.StreamSqlQueryAsync(
 "SELECT Id, Name FROM dbo.LargeTable WHERE Amount > @threshold",
 parameters: new SqlParameter[] { new("@threshold", SqlDbType.Int) { Value = 1000 } }))
{
 var name = (string)row["Name"]!;
 Process(name);
}

• Each row is a dictionary keyed by column name; DBNull is mapped to C# null.
• Connection-open + initial reader-open are retried per the configured retry policy. Once rows start flowing, retry stops — a mid-stream failure propagates immediately.
• Cancellation honored at both call time and per-iteration (via [EnumeratorCancellation]).
• Available on both IMSSQLService / ISqliteService AND inside transactions via IMSSQLTransaction / ISqliteTransaction. The tx form streams against the open transaction's connection; tag db.client.in_transaction = true is set on the span.

Telemetry and Distributed Tracing

This package includes built-in support for OpenTelemetry distributed tracing. All SQL operations (queries, stored procedures, inserts, updates, deletes) are automatically instrumented with activities/spans.

Activity Sources

The package uses the following activity sources:

• Tyler.Utility.SQL.MSSQL - MSSQL Service operations
• Tyler.Utility.SQL.MSSQL.Client - MSSQL Client operations
• Tyler.Utility.SQL.SQLite - SQLite Service operations
• Tyler.Utility.SQL.SQLite.Client - SQLite Client operations

Telemetry Tags

Database operations include the following tags:

• db.server.address - Database server address (MSSQL only)
• db.path - Database file path (SQLite only)
• db.result.rows - Number of rows returned/affected
• db.result.columns - Number of columns in result
• db.result.count - Result count for multi-column queries
• db.result.length - Result string length for scalar queries
• db.parameters.count - Number of query parameters
• db.error.code - SQL error number (on failure, MSSQL only)
• db.error.severity - SQL error severity (on failure, MSSQL only)

Enabling Telemetry (Optional)

Telemetry is optional and backward compatible. The package works without any telemetry configuration. To enable telemetry in your application:

using Tyler.Utility.DotNet.Telemetry.Configuration;

// In Program.cs or Startup.cs
builder.Services.AddTylerTelemetry(telemetry => telemetry
 .WithServiceName("MyApplication")
 .WithServiceVersion("1.0.0")
 .WithOtlpExporter() // Defaults to Tyler's Seq instance
);

For more details on telemetry configuration, see the .

Benefits

• Distributed Tracing: Track SQL operations across service boundaries
• Performance Monitoring: Measure query execution times
• Error Tracking: Automatically capture exceptions with full context
• Correlated Logs: TraceId and SpanId automatically added to logs
• Backward Compatible: Works without telemetry configured (no-ops)

Error Handling

• All errors are logged using ILogger and SQS/Trello.
• Service methods handle exceptions and provide diagnostics.

Requirements

• .NET 8.0 or later

License

MIT License