FractalDataWorks.Data.MsSql 0.4.0-preview.6

FractalDataWorks.Data.MsSql

Microsoft SQL Server data layer providing T-SQL command translators, data type converters, container types, and database path abstractions.

Overview

FractalDataWorks.Data.MsSql provides SQL Server-specific implementations:

• Command Translators - T-SQL translators for Query, Insert, Update, Delete commands
• Data Type Converters - SQL Server to CLR type conversion (29 converters)
• Container Types - Table, View, and StoredProcedure containers
• Database Paths - T-SQL identifier formatting with [Database].[Schema].[Object] support

Target Framework: .NET 10.0

Dependencies: FractalDataWorks.Commands.Data.Abstractions, FractalDataWorks.Data.Abstractions, Microsoft.Data.SqlClient

Key Components

Command Translators

The MsSqlDataCommandTranslators TypeCollection provides T-SQL translators for all data command types.

From MsSqlDataCommandTranslators.cs:37-49:

[TypeCollection(typeof(MsSqlDataCommandTranslatorBase), typeof(IDataCommandTranslator<SqlCommand>), typeof(MsSqlDataCommandTranslators))]
[ExcludeFromCodeCoverage]
public abstract partial class MsSqlDataCommandTranslators :
    TypeCollectionBase<MsSqlDataCommandTranslatorBase, IDataCommandTranslator<SqlCommand>>
{
    // Source generator creates:
    // - Static constructor
    // - Static properties: Query, Insert, Update, Delete, BulkInsert, BatchInsert, CompoundQuery
    // - public static IReadOnlyList<IDataCommandTranslator> All()
    // - public static IDataCommandTranslator ByName(string name)
    // - public static IDataCommandTranslator ById(int id)

}

Query Translation

From MsSqlQueryTranslator.cs:38-47:

[TypeOption(typeof(MsSqlDataCommandTranslators), "Query")]
public sealed class MsSqlQueryTranslator : MsSqlDataCommandTranslatorBase
{
    /// <summary>
    /// Initializes a new instance of the <see cref="MsSqlQueryTranslator"/> class.
    /// </summary>
    public MsSqlQueryTranslator()
        : base("Query")
    {
    }

The translator builds complete SELECT statements with parameterized WHERE, ORDER BY, and OFFSET/FETCH clauses.

From MsSqlQueryTranslator.cs:113-141:

private static SqlCommand BuildSelectStatement(
    IStorageContainer container,
    DatabasePath dbPath,
    IFilterExpression? filter,
    IProjectionExpression? projection,
    IOrderingExpression? ordering,
    IPagingExpression? paging)
{
    var sql = new StringBuilder();

    // SELECT clause
    if (projection?.PropertyNames?.Any() == true)
    {
        var fields = string.Join(", ", projection.PropertyNames.Select(p => $"[{p}]"));
        sql.Append(CultureInfo.InvariantCulture, $"SELECT {fields}");
    }
    else if (container.Schema.Fields.Count > 0)
    {
        var fields = string.Join(", ", container.Schema.Fields.Select(f => $"[{f.Name}]"));
        sql.Append(CultureInfo.InvariantCulture, $"SELECT {fields}");
    }
    else
    {
        sql.Append("SELECT *");
    }

    // FROM clause
    sql.Append(CultureInfo.InvariantCulture, $" FROM {dbPath.QuotedIdentifier}");

Insert Translation

From MsSqlInsertTranslator.cs:33-42:

[TypeOption(typeof(MsSqlDataCommandTranslators), "Insert")]
public sealed class MsSqlInsertTranslator : MsSqlDataCommandTranslatorBase
{
    /// <summary>
    /// Initializes a new instance of the <see cref="MsSqlInsertTranslator"/> class.
    /// </summary>
    public MsSqlInsertTranslator()
        : base("Insert")
    {
    }

Insert statements automatically exclude identity/computed columns and return SCOPE_IDENTITY().

From MsSqlInsertTranslator.cs:94-125:

private static SqlCommand BuildInsertStatement(
    IStorageContainer container,
    DatabasePath dbPath,
    object data)
{
    // Get columns from schema (exclude identity and computed columns)
    var fields = container.Schema.Fields
        .Where(f => !f.IsIdentity && !f.IsComputed)
        .ToList();

    if (fields.Count == 0)
    {
        throw new InvalidOperationException($"Container {container.Name} has no insertable fields");
    }

    var fieldNames = fields.Select(f => f.Name).ToList();

    // Build column list
    var columnList = string.Join(", ", fieldNames.Select(f => $"[{f}]"));

    // Build parameter list
    var paramList = string.Join(", ", fieldNames.Select(f => $"@{f}"));

    // Build INSERT statement with SCOPE_IDENTITY() to return generated ID
    var sql = $"INSERT INTO {dbPath.QuotedIdentifier} ({columnList}) VALUES ({paramList}); SELECT CAST(SCOPE_IDENTITY() AS INT);";

    // Create SqlCommand and add parameters
    var command = CreateSqlCommand(sql);
    AddParametersFromObject(command, data, fieldNames);

    return command;
}

Database Paths

From DatabasePath.cs:19-51:

public sealed class DatabasePath : PathBase, IDataPath<IStorageContainer>
{
    private readonly List<IStorageContainer> _containers;

    /// <summary>
    /// Initializes a new instance of the <see cref="DatabasePath"/> class.
    /// </summary>
    /// <param name="database">The database name.</param>
    /// <param name="schema">The schema name (default: "dbo").</param>
    /// <param name="objectName">The object name (table, view, stored procedure).</param>
    /// <param name="containers">Optional containers at this path.</param>
    public DatabasePath(
        string? database,
        string schema,
        string objectName,
        IEnumerable<IStorageContainer>? containers = null)
        : base(1, "DatabasePath")
    {
        Database = database ?? string.Empty;
        Schema = schema ?? throw new ArgumentNullException(nameof(schema));
        ObjectName = objectName ?? throw new ArgumentNullException(nameof(objectName));
        _containers = containers?.ToList() ?? new List<IStorageContainer>();
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="DatabasePath"/> class with default schema "dbo".
    /// </summary>
    /// <param name="database">The database name.</param>
    /// <param name="objectName">The object name (table, view, stored procedure).</param>
    public DatabasePath(string database, string objectName)
        : this(database, "dbo", objectName)
    {
    }

Paths provide quoted identifier formatting for T-SQL:

From DatabasePath.cs:80-91:

/// <summary>
/// Gets the quoted identifier format for T-SQL.
/// Uses [Database].[Schema].[Object] when database is specified, otherwise [Schema].[Object].
/// </summary>
public string QuotedIdentifier => string.IsNullOrEmpty(Database)
    ? $"[{Schema}].[{ObjectName}]"
    : $"[{Database}].[{Schema}].[{ObjectName}]";

/// <summary>
/// Gets the schema.object format (without database).
/// </summary>
public string SchemaQualifiedName => $"[{Schema}].[{ObjectName}]";

Data Type Converters

The MsSqlConverters TypeCollection provides 29 converters for SQL Server data types.

From MsSqlConverters.cs:13-24:

[TypeOption(typeof(DataTypeConverters), "MsSql")]
[TypeCollection(typeof(DataTypeConverterBase),
                typeof(IDataTypeConverter),
                typeof(MsSqlConverters))]
public abstract partial class MsSqlConverters : DataTypeConverterCollectionBase
{
    /// <summary>
    /// Initializes a new instance of the <see cref="MsSqlConverters"/> class.
    /// </summary>
    protected MsSqlConverters() : base("MsSql", "Microsoft SQL Server")
    {
    }

From MsSqlConverters.cs:39-50:

/// <summary>
/// Gets a converter by SQL source type name (e.g., "int", "nvarchar", "bigint").
/// Returns Empty if not found.
/// </summary>
public static IDataTypeConverter BySourceType(string sourceType)
{
    if (string.IsNullOrWhiteSpace(sourceType))
    {
        return Empty;
    }

    var converter = All().FirstOrDefault(c =>
        c.SourceType.Equals(sourceType, System.StringComparison.Ordinal));

Example converter implementation:

From SqlInt32Converter.cs:13-39:

[TypeOption(typeof(MsSqlConverters), "Int32")]
[ExcludeFromCodeCoverage]
public sealed class MsSqlInt32Converter()
    : DataTypeConverterBase(
        id: 1,
        name: "Int32",
        sourceType: "int",
        targetClrType: typeof(int),
        dbType: DbType.Int32)
{
    /// <inheritdoc/>
    public override object? ToClr(object? dbValue)
    {
        if (dbValue is null or DBNull)
        {
            return null;
        }

        return Convert.ToInt32(dbValue, CultureInfo.InvariantCulture);
    }

    /// <inheritdoc/>
    public override object? ToDb(object? clrValue)
    {
        return clrValue;
    }
}

Container Types

From TableContainer.cs:12-46:

public sealed class TableContainer : ContainerBase
{
    /// <summary>
    /// Initializes a new instance of the <see cref="TableContainer"/> class.
    /// </summary>
    /// <param name="name">The table name.</param>
    /// <param name="path">The database path to the table.</param>
    /// <param name="schema">The container schema with field definitions.</param>
    /// <param name="format">Optional format override (defaults to Tabular).</param>
    public TableContainer(
        string name,
        DatabasePath path,
        IContainerSchema schema,
        IFormatType? format = null)
        : base(1, name)
    {
        Path = path;
        Schema = schema;
        ContainerType = new TableContainerType();
        Format = format ?? FormatTypes.Tabular;
        SupportedOperations = new[] { "Query", "Insert", "Update", "Delete" };
    }

    /// <inheritdoc/>
    public override IContainerType ContainerType { get; }

    /// <inheritdoc/>
    public override IFormatType Format { get; }

    /// <inheritdoc/>
    public override IPath Path { get; }

    /// <inheritdoc/>
    public override IContainerSchema Schema { get; }
}

Usage Examples

Looking Up Converters

From test file MsSqlConverterIntegrationTests.cs:26-45:

// Test lookup for common SQL types
var intConverter = MsSqlConverters.BySourceType("int");
var bigintConverter = MsSqlConverters.BySourceType("bigint");
var nvarcharConverter = MsSqlConverters.BySourceType("nvarchar");
var bitConverter = MsSqlConverters.BySourceType("bit");
var datetimeConverter = MsSqlConverters.BySourceType("datetime");

intConverter.ShouldNotBe(MsSqlConverters.Empty);
bigintConverter.ShouldNotBe(MsSqlConverters.Empty);
nvarcharConverter.ShouldNotBe(MsSqlConverters.Empty);
bitConverter.ShouldNotBe(MsSqlConverters.Empty);
datetimeConverter.ShouldNotBe(MsSqlConverters.Empty);

// Verify correct mappings
intConverter.TargetClrType.ShouldBe(typeof(int));
bigintConverter.TargetClrType.ShouldBe(typeof(long));
nvarcharConverter.TargetClrType.ShouldBe(typeof(string));
bitConverter.TargetClrType.ShouldBe(typeof(bool));

Converting Database Values

From test file MsSqlConverterIntegrationTests.cs:75-96:

var intConverter = MsSqlConverters.BySourceType("int");
var stringConverter = MsSqlConverters.BySourceType("nvarchar");
var boolConverter = MsSqlConverters.BySourceType("bit");

// Test int conversion
var intResult = intConverter.ToClr(42);
intResult.ShouldBe(42);

// Test string conversion
var stringResult = stringConverter.ToClr("test value");
stringResult.ShouldBe("test value");

// Test bool conversion
var boolResult = boolConverter.ToClr(true);
boolResult.ShouldBe(true);

// Test null handling
intConverter.ToClr(System.DBNull.Value).ShouldBeNull();
stringConverter.ToClr(null).ShouldBeNull();

Using Converters with Schema Fields

From test file MsSqlConverterIntegrationTests.cs:48-72:

// Create field with type system properties
var field = new Field
{
    Name = "CustomerId",
    FieldType = new SimpleFieldType { TypeName = "Int32", ClrType = typeof(int) },
    Role = FieldRole.Identity,
    IsNullable = false,
    IsPrimaryKey = true,
    IsIdentity = true,
    IsComputed = false,
    TypeSystemId = "MsSql",
    ConverterTypeId = 1  // MsSqlInt32Converter
};

field.TypeSystemId.ShouldBe("MsSql");
field.ConverterTypeId.ShouldBe(1);

// Verify we can look up the converter
var converter = MsSqlConverters.ById(field.ConverterTypeId!.Value);
converter.ShouldNotBe(MsSqlConverters.Empty);
converter.Name.ShouldBe("Int32");
converter.SourceType.ShouldBe("int");

Best Practices

1. Use TypeCollection lookups - Access translators via MsSqlDataCommandTranslators.ByName("Query") or static properties
2. Use BySourceType for converters - Prefer MsSqlConverters.BySourceType("int") over ById() when SQL type is known
3. Check for Empty results - TypeCollection lookups return Empty instance for unknown names/IDs
4. Use DatabasePath for identifiers - Use QuotedIdentifier property for safe T-SQL formatting
5. Exclude identity columns - Insert translators automatically exclude identity and computed columns

Related Packages

• FractalDataWorks.Data.Abstractions - Core data contracts (IStorageContainer, IDataTypeConverter, IPath)
• FractalDataWorks.Commands.Data.Abstractions - Data command contracts (IQueryCommand, IInsertCommand)
• FractalDataWorks.Services.Connections.MsSql - SQL Server connection factory and configuration