Skip to main content

SQL Server Provider

The SQL Server provider offers full relational database support with Dapper-based query execution, connection pooling, health checks, and built-in resilience.

Before You Start​

  • .NET 8.0+ (or .NET 9/10 for latest features)
  • A SQL Server instance (local, Azure SQL, or AWS RDS)
  • Familiarity with data access and IDb interface

Installation​

dotnet add package Excalibur.Data.SqlServer

Dependencies: Excalibur.Data.Abstractions, Microsoft.Data.SqlClient, Dapper

Quick Start​

using Microsoft.Extensions.DependencyInjection;

services.AddSqlServerPersistence(opts =>
{
    opts.ConnectionString = "Server=localhost;Database=MyApp;Trusted_Connection=true;";
});

Registration Methods​

MethodWhat It RegistersKey Options
AddSqlServerPersistence(opts)Core data executors + persistenceConnectionString, CommandTimeout
AddSqlServerPersistenceWithRetry(opts)Persistence + automatic retryResiliency.MaxRetryAttempts
AddSqlServerPersistenceWithEncryption(opts)Persistence + Always EncryptedSecurity.ColumnEncryptionSetting
AddSqlServerPersistenceReadOnly(opts)Read-only replica connectionConnectionString
AddSqlServerPersistenceHighAvailability(opts)Failover supportConnectionString
AddSqlServerDeadLetterStore(opts)IDeadLetterStoreTableName
AddSqlServerProjectionStore<T>(opts)IProjectionStore<T>ConnectionString, TableName
AddSqlServerTransactionScope(...)Transaction scopeIsolationLevel, Timeout

All methods also accept IConfiguration binding: AddSqlServerPersistence(configuration, sectionName: "SqlServerPersistence").

Batch Projection Registration​

Register multiple projections sharing the same connection:

services.AddSqlServerProjections(connectionString, projections =>
{
    projections.Add<OrderSummary>();
    projections.Add<CustomerProfile>(o => o.TableName = "CustomerViews");
    projections.Add<InventoryView>(o => o.TableName = "InventoryViews");
});

Health Checks​

services.AddHealthChecks()
    .AddSqlServerPersistenceHealthCheck();

Data Request Pattern​

Define queries as reusable data request objects:

public class GetOrderByIdRequest : DataRequest<Order?>
{
    public GetOrderByIdRequest(Guid orderId)
    {
        Command = new CommandDefinition(
            "SELECT * FROM Orders WHERE Id = @Id",
            new { Id = orderId });
    }
}

// Execute via persistence provider
var order = await provider.ExecuteAsync(new GetOrderByIdRequest(orderId), cancellationToken);

Batch Operations​

var requests = new[]
{
    new InsertOrderRequest(order1),
    new InsertOrderRequest(order2),
    new InsertOrderRequest(order3)
};

await sqlProvider.ExecuteBatchInTransactionAsync(requests, scope, cancellationToken);

Configuration​

{
  "SqlServerPersistence": {
    "ConnectionString": "Server=localhost;Database=MyApp;Trusted_Connection=true;",
    "CommandTimeout": 30,
    "EnableConnectionResiliency": true,
    "MaxRetryAttempts": 3,
    "RetryDelayMilliseconds": 1000,
    "EnableDetailedLogging": false,
    "EnableMetrics": true
  }
}

Dead Letter Store​

For messages that fail processing:

services.AddSqlServerDeadLetterStore(options =>
{
    options.ConnectionString = connectionString;
    options.TableName = "DeadLetters";
});

See Also​