Cirreum.Persistence.SqlServer 1.1.4

Cirreum.Persistence.SqlServer

SQL Server provider for Cirreum.Persistence.Sql with Azure Entra ID authentication support

Overview

Cirreum.Persistence.SqlServer is a SQL Server-specific implementation of the ISqlConnectionFactory interface from the Cirreum.Persistence.Sql abstraction layer. It provides:

• SQL Server connection factory with Dapper integration
• Entra (Azure AD) token authentication, with credential selection via the instance's Credential block and tenant pinning via its Identifier
• Health check support for service monitoring
• Integration with the Cirreum Service Provider framework

For query extensions, pagination, transaction chaining, and constraint handling, see the Cirreum.Persistence.Sql documentation.

Installation

dotnet add package Cirreum.Persistence.SqlServer

Quick Start

Basic Registration

builder.AddSqlServer("default", "Server=localhost;Database=MyDb;Trusted_Connection=True;");

With Azure Entra ID Authentication

builder.AddSqlServer("default", settings =>
{
    settings.ConnectionString = "Server=myserver.database.windows.net;Database=MyDb;";
    settings.UseAzureAuthentication = true;
});

With Full Configuration

builder.AddSqlServer("default", settings =>
{
    settings.ConnectionString = configuration.GetConnectionString("SqlServer")!;
    settings.UseAzureAuthentication = true;
    settings.CommandTimeoutSeconds = 60;
}, healthOptions =>
{
    healthOptions.Query = "SELECT 1";
    healthOptions.Timeout = TimeSpan.FromSeconds(5);
});

Configuration Options

SqlServerInstanceSettings

SqlServerHealthCheckOptions

Usage

Inject ISqlConnectionFactory and use the query/command extensions from Cirreum.Persistence.Sql:

public class OrderRepository(ISqlConnectionFactory db)
{
    public async Task<Result<OrderDto>> GetOrderAsync(Guid orderId, CancellationToken ct)
    {
        await using var conn = await db.CreateConnectionAsync(ct);

        return await conn.GetAsync<OrderDto>(
            "SELECT * FROM Orders WHERE OrderId = @Id",
            new { Id = orderId },
            key: orderId,
            ct);
    }

    public async Task<Result<Guid>> CreateOrderAsync(CreateOrder cmd, CancellationToken ct)
    {
        await using var conn = await db.CreateConnectionAsync(ct);
        var orderId = Guid.CreateVersion7();

        return await conn.InsertAndReturnAsync(
            """
            INSERT INTO Orders (OrderId, CustomerId, Amount, CreatedAt)
            VALUES (@OrderId, @CustomerId, @Amount, @CreatedAt)
            """,
            new { OrderId = orderId, cmd.CustomerId, cmd.Amount, CreatedAt = DateTime.UtcNow },
            () => orderId,
            uniqueConstraintMessage: "Order already exists",
            ct: ct);
    }
}

Multiple Instances

Register multiple SQL Server instances with different keys:

builder.AddSqlServer("primary", primaryConnectionString);
builder.AddSqlServer("reporting", reportingConnectionString);

// Inject with [FromKeyedServices]
public class ReportService([FromKeyedServices("reporting")] ISqlConnectionFactory db)
{
    // ...
}

DateOnly and TimeOnly Support

This package includes built-in Dapper type handlers for DateOnly and TimeOnly, allowing you to use these types directly in your models and queries:

public record Appointment(int Id, DateOnly Date, TimeOnly StartTime, TimeOnly EndTime);

// Query with DateOnly/TimeOnly parameters
var appointments = await conn.QueryAsync<Appointment>(
    "SELECT * FROM Appointments WHERE Date = @Date AND StartTime > @MinTime",
    new { Date = new DateOnly(2026, 1, 15), MinTime = new TimeOnly(9, 0) });

// Insert with DateOnly/TimeOnly values
await conn.ExecuteAsync(
    "INSERT INTO Appointments (Date, StartTime, EndTime) VALUES (@Date, @StartTime, @EndTime)",
    new { Date = DateOnly.FromDateTime(DateTime.Today), StartTime = new TimeOnly(9, 30), EndTime = new TimeOnly(10, 0) });

The type handlers are registered automatically when the package is loaded.

Entra (Azure AD) Authentication

When UseAzureAuthentication is enabled, the connection factory acquires tokens for https://database.windows.net/.default using the identity selected by the instance's Credential block:

The instance's Identifier names the Entra tenant to authenticate against; leave it unset to use the credential's default tenant.

{
  "ConnectionString": "Server=myserver.database.windows.net;Database=MyDb;",
  "UseAzureAuthentication": true,
  "Identifier": "00000000-0000-0000-0000-000000000000", // Entra tenant
  "Credential": {
    "Mode": "ManagedIdentity",
    "IdentityId": "11111111-1111-1111-1111-111111111111" // user-assigned MI client id
  }
}

A Credential block on an instance with UseAzureAuthentication = false is rejected at registration — the block selects a token identity, so on a connection-string-authenticated instance it could only ever bind and silently do nothing.

Ensure your Azure SQL Database is configured for Entra authentication and the selected identity has been granted access.

Related Packages

License

This project is licensed under the MIT License - see the LICENSE file for details.

Cirreum Foundation Framework Layered simplicity for modern .NET