am.kon.packages.services.dac.mssql 0.1.0.8

am.kon.packages.services.dac.mssql

am.kon.packages.services.dac.mssql wraps the raw DataBase from am.kon.packages.dac.mssql and exposes it as an injectable service that manages one or more SQL Server connections. Resolve DatabaseConnectionService from your DI container to run commands, stream data, or execute transactional batches against the configured databases.

Installation

 dotnet add package am.kon.packages.services.dac.mssql

Configuration

Add the DAC configuration blocks to appsettings.json (or equivalent):

{
  "am.kon.dac": {
    "DefaultConnection": "Reporting"
  },
  "ConnectionStrings": {
    "Reporting": "Server=localhost;Database=Reporting;Trusted_Connection=True;",
    "Operational": "Server=localhost;Database=Operational;Trusted_Connection=True;"
  }
}

Wire everything up during service registration:

using am.kon.packages.dac.primitives.Config;
using am.kon.packages.services.dac.mssql;
using am.kon.packages.services.dac.mssql.Config;

services.Configure<DacConfig>(configuration.GetSection(DacConfig.SectionDefaultName));
services.Configure<ConnectionStringsConfig>(configuration.GetSection(ConnectionStringsConfig.SectionDefaultName));
services.AddSingleton<DatabaseConnectionService>();

At runtime the service builds a DataBase instance for each configured connection string, keeps a DefaultDatabase reference, and exposes an indexer to retrieve named databases on demand.

Working with the default database

The service forwards most operations to the default connection. Each overload mirrors the methods on DataBase so you can pick the parameter type that suits your calling code (IDataParameter[], SqlParameter[], DacMsSqlParameters, or the legacy DacSqlParameters). Examples below assume using Microsoft.Data.SqlClient; and using System.Data;.

Executing non-query commands

public async Task<int> SaveAuditAsync(DatabaseConnectionService connections, Guid itemId)
{
    var parameters = new DacMsSqlParameters().AddItem("@ItemId", itemId);

    return await connections.ExecuteNonQueryAsync(
        sql: "dbo.audit_append",
        parameters: parameters.ToArray(),
        commandType: CommandType.StoredProcedure);
}

Fetching scalar values

object count = await connections.ExecuteScalarAsync(
    sql: "SELECT COUNT(1) FROM Sales.Orders WHERE Status = @Status",
    parameters: new[] { new SqlParameter("@Status", OrderStatus.Pending) });

Streaming results

await using var reader = await connections.ExecuteReaderAsync(
    sql: "dbo.GetPendingOrders",
    parameters: Array.Empty<SqlParameter>(),
    commandType: CommandType.StoredProcedure);

while (await reader.ReadAsync())
{
    // hydrate DTOs here
}

Filling existing containers

var buffer = new DataTable();
connections.FillData(
    dataOut: buffer,
    sql: "SELECT * FROM Reports.MonthlySummary",
    parameters: Array.Empty<SqlParameter>(),
    startRecord: 0,
    maxRecords: 100);

FillDataSet, FillDataTable, GetDataSet, and GetDataTable follow the same pattern as the underlying DataBase type—use them when you prefer to materialise tabular structures directly.

Transactional and batch operations

DatabaseConnectionService exposes the underlying DefaultDatabase so you can opt into the batch helpers when required:

var database = connections.DefaultDatabase;

await database.ExecuteTransactionalSQLBatchAsync(async transaction =>
{
    var conn = (SqlConnection)transaction.Connection;
    var tx = (SqlTransaction)transaction;

    using var updateInventory = new SqlCommand("dbo.UpdateInventory", conn, tx)
    {
        CommandType = CommandType.StoredProcedure
    };
    updateInventory.Parameters.AddWithValue("@Sku", sku);
    updateInventory.Parameters.AddWithValue("@Delta", -quantity);
    await updateInventory.ExecuteNonQueryAsync();

    using var log = new SqlCommand("dbo.LogFulfilment", conn, tx)
    {
        CommandType = CommandType.StoredProcedure
    };
    log.Parameters.AddWithValue("@Sku", sku);
    log.Parameters.AddWithValue("@Quantity", quantity);
    await log.ExecuteNonQueryAsync();

    return true;
});

For non-transactional batches, call ExecuteSQLBatchAsync in the same manner. Both helpers support the familiar throwDBException, throwGenericException, and throwSystemException switches.

Coordinating updates across databases

When two or more named connections must be updated as a single logical unit, wrap the work in a TransactionScope. This promotes the underlying SQL connections to a distributed transaction (MSDTC must be enabled when the servers differ).

using System.Transactions;

public async Task<bool> SynchroniseAsync(DatabaseConnectionService connections, Guid jobId)
{
    using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);

    try
    {
        var primary = connections.DefaultDatabase;
        var archival = connections["Archive"];

        await primary.ExecuteTransactionalSQLBatchAsync(async tx =>
        {
            var cmd = (SqlCommand)tx.Connection.CreateCommand();
            cmd.Transaction = (SqlTransaction)tx;
            cmd.CommandText = "UPDATE Jobs SET Processed = 1 WHERE JobId = @JobId";
            cmd.Parameters.AddWithValue("@JobId", jobId);
            await cmd.ExecuteNonQueryAsync();
            return 0;
        });

        await archival.ExecuteTransactionalSQLBatchAsync(async tx =>
        {
            var cmd = (SqlCommand)tx.Connection.CreateCommand();
            cmd.Transaction = (SqlTransaction)tx;
            cmd.CommandText = "INSERT INTO ProcessedJobs(JobId, CompletedAt) VALUES(@JobId, SYSUTCDATETIME())";
            cmd.Parameters.AddWithValue("@JobId", jobId);
            await cmd.ExecuteNonQueryAsync();
            return 0;
        });

        scope.Complete(); // Commit the distributed transaction
        return true;
    }
    catch
    {
        // Omitting scope.Complete causes the TransactionScope to roll back on dispose
        throw;
    }
}

If MSDTC is unavailable, run the operations sequentially and implement compensating actions for failure scenarios instead.

Deriving custom services

When you need to expose domain-specific helpers while keeping DI registration simple, derive from DatabaseConnectionService and add strongly typed methods that leverage the protected members and the DefaultDatabase property.

public sealed class ReportingConnectionService : DatabaseConnectionService
{
    public ReportingConnectionService(
        ILogger<DatabaseConnectionService> logger,
        IConfiguration configuration,
        IOptions<DacConfig> dacConfig,
        IOptions<ConnectionStringsConfig> connectionOptions)
        : base(logger, configuration, dacConfig, connectionOptions) { }

    public Task<DataSet> LoadHeadcountAsync(DateOnly asOf)
    {
        var parameters = new DacMsSqlParameters()
            .AddItem("@AsOf", asOf);

        return DefaultDatabase.GetDataSet(
            sql: "dbo.HR_GetHeadcount",
            parameters: parameters.ToArray(),
            commandType: CommandType.StoredProcedure);
    }

    public Task<int> AppendAuditAsync(Guid itemId)
    {
        var parameters = new DacMsSqlParameters()
            .AddItem("@ItemId", itemId);

        return ExecuteNonQueryAsync(
            sql: "dbo.audit_append",
            parameters: parameters.ToArray(),
            commandType: CommandType.StoredProcedure);
    }
}

Register the derived class in DI (services.AddSingleton<ReportingConnectionService>()) alongside or instead of the base service, depending on your needs.

Managing multiple databases

var reporting = connections.DefaultDatabase;
var operational = connections["Operational"];

DataSet latest = reporting.GetDataSet(
    sql: "dbo.GetLatestMetrics",
    parameters: Array.Empty<SqlParameter>(),
    commandType: CommandType.StoredProcedure);

DataTable syncStatus = operational.GetDataTable(
    sql: "SELECT * FROM SyncStatus",
    parameters: Array.Empty<SqlParameter>());

The indexer returns null when a key is missing; handle that scenario if consumers provide user input.

Lifecycle hooks

• Start() currently returns a completed task and is available for symmetry with hosted services.
• Stop() cancels the shared CancellationTokenSource, signalling any in-flight operations to exit.

Call Stop() during application shutdown or implement IHostedService to delegate to these methods automatically.

For lower-level usage without dependency injection, use am.kon.packages.dac.mssql directly.