FractalDataWorks.Services.Connections.MsSql 0.4.0-preview.6

FractalDataWorks.Services.Connections.MsSql

SQL Server connection implementation for the FractalDataWorks framework.

Overview

This package provides a SQL Server connection implementation using the Authentication Processor Pattern. The factory creates SqlConnection objects directly, passes them to MsSqlConnection, and the connection string goes out of scope immediately for security.

Target Framework: net10.0

Key Components

MsSqlConnection

SQL Server data connection that inherits from ConnectionBase which handles IDataCommand to SqlCommand translation.

public sealed class MsSqlConnection : ConnectionBase<SqlCommand, MsSqlConnectionConfiguration, MsSqlConnection>
{
    private readonly SqlConnection _sqlConnection;
    private readonly ILogger<MsSqlConnection> _logger;

    public MsSqlConnection(
        ILogger<MsSqlConnection> logger,
        MsSqlConnectionConfiguration configuration,
        SqlConnection sqlConnection)
        : base(logger, configuration)
    {
        _sqlConnection = sqlConnection;
        _logger = logger;
    }
}

Key Points:

• Takes MsSqlConnectionConfiguration for metadata (Server, Database, timeouts, etc.)
• Takes SqlConnection created by the factory - the connection string is NOT stored
• The factory creates SqlConnection with the connection string, then the string goes out of scope

MsSqlConnectionFactory

Factory for creating SQL Server connection instances. The factory:

1. Gets the appropriate authentication processor
2. Builds the connection string using the processor
3. Creates SqlConnection with that string (string goes out of scope)
4. Creates MsSqlConnection with the configuration and SqlConnection

public sealed class MsSqlConnectionFactory : IMsSqlConnectionFactory
{
    // Bootstrap-friendly overload - accepts ISecretManager directly
    public async Task<IGenericResult<IGenericConnection>> Create(
        MsSqlConnectionConfiguration configuration,
        ISecretManager? secretManager,
        ILoggerFactory loggerFactory,
        CancellationToken cancellationToken = default)
    {
        // Resolve password from secret manager if needed
        string? resolvedPassword = null;
        if (!string.IsNullOrEmpty(configuration.Authentication?.SecretKeyName))
        {
            // Get password from secret manager...
        }

        // Build connection string using processor
        var connectionStringResult = BuildConnectionString(configuration, resolvedPassword);

        // Create SqlConnection - connection string goes out of scope after this
        var sqlConnection = new SqlConnection(connectionStringResult.Value);

        // Create the connection instance
        var connection = new MsSqlConnection(connectionLogger, configuration, sqlConnection);

        return GenericResult<IGenericConnection>.Success(connection);
    }
}

Authentication Processors

Connection string building is delegated to authentication processors (TypeCollection pattern):

// Get processor for auth type
var processor = MsSqlAuthenticationProcessors.ByName(authTypeName);

// Processor builds the connection string
var connectionString = processor.Process(builder, context);

Supported authentication types:

• SqlAuth / SQL - SQL Server authentication (username/password from SecretManager)
• WindowsAuth / Windows / Integrated - Windows Integrated Authentication
• AzureAD / AAD - Azure Active Directory authentication
• ManagedIdentity / MSI - Azure Managed Identity

MsSqlConnectionConfiguration

Configuration class with [ManagedConfiguration] attribute for database-driven configuration.

[ManagedConfiguration(
    Schema = "cfg",
    TableName = "MsSqlConnection",
    Parent = nameof(ConnectionConfigurationBase<MsSqlConnectionConfiguration>),
    ServiceCategory = "Connection",
    ServiceType = "MsSql")]
public partial class MsSqlConnectionConfiguration : ConnectionConfigurationBase<MsSqlConnectionConfiguration>
{
    public string Server { get; set; } = "localhost";
    public string Database { get; set; } = string.Empty;
    public int Port { get; set; } = 1433;
    public MsSqlAuthenticationSettings Authentication { get; set; } = new();
    // ... other properties
}

MsSqlConnectionType

ServiceTypeOption that registers with ConnectionTypes:

[ServiceTypeOption(typeof(ConnectionTypes), "MsSql")]
public sealed class MsSqlConnectionType : ConnectionTypeBase<IGenericConnection, IMsSqlConnectionFactory, MsSqlConnectionConfiguration>
{
    public MsSqlConnectionType() : base(
        name: "MsSql",
        sectionName: "MsSql",
        displayName: "SQL Server",
        description: "Microsoft SQL Server database connection",
        category: "Database")
    {
    }
}

Bootstrap Usage

For bootstrap scenarios (creating ConfigurationDb connection before DI is available):

// Create factories directly
var loggerFactory = LoggerFactory.Create(b => b.AddConsole());
var secretManagerFactory = new EnvironmentVariableSecretManagerFactory(
    loggerFactory.CreateLogger<EnvironmentVariableSecretManagerFactory>());

// Create secret manager
var secretManagerResult = await secretManagerFactory.Create(new EnvironmentVariableConfiguration());
var secretManager = secretManagerResult.Value;

// Create connection factory
var connectionFactory = new MsSqlConnectionFactory(
    loggerFactory.CreateLogger<MsSqlConnectionFactory>(),
    loggerFactory.CreateLogger<MsSqlConnection>());

// Load config from appsettings
var configDbConfig = configuration.GetSection("ConfigurationDb").Get<MsSqlConnectionConfiguration>();

// Create connection - factory accepts ISecretManager directly
var connectionResult = await connectionFactory.Create(configDbConfig, secretManager, loggerFactory);

Three-Phase Registration

After bootstrap, use three-phase registration:

// Phase 1a: Configure (bind IOptions)
ConnectionTypes.Configure(services, configuration, loggerFactory);

// Phase 1b: Register (register factories)
ConnectionTypes.Register(services, loggerFactory);

var app = builder.Build();

// Phase 2: Initialize (eager resolve, fail-fast)
ConnectionTypes.Initialize(app.Services, loggerFactory);

Cross-Assembly Registration

Since MsSqlConnectionType is in a separate NuGet package, it uses module initializers for automatic registration:

// Generated in your application assembly
[ModuleInitializer]
internal static void RegisterServiceTypeOptions()
{
    ConnectionTypes.RegisterMember(new MsSqlConnectionType());
}

This runs before Main(), ensuring MsSql is registered before ConnectionTypes.Configure() is called.

Configuration Tables

Configuration is loaded from two database tables:

Dependencies

Project References

• FractalDataWorks.Data.MsSql - SQL Server translators
• FractalDataWorks.Services.Connections - Connection base classes
• FractalDataWorks.Services.SecretManagers - Secret management

Package References

• Microsoft.Data.SqlClient - SQL Server connectivity
• Microsoft.Extensions.DependencyInjection.Abstractions
• Microsoft.Extensions.Logging.Abstractions

Related Packages

• FractalDataWorks.Services.Connections - Connection base classes
• FractalDataWorks.Services.Connections.Abstractions - Interfaces
• FractalDataWorks.Data.MsSql - SQL Server translators