Siemens.AspNet.DbProvider 7.7.4

Siemens.AspNet.DbProvider

A powerful, flexible database provider for .NET applications that simplifies database interactions with multiple database systems.

Features

• Multi-Database Support: Works with multiple database providers including:

  • MySQL/MariaDB
  • Snowflake
  • Apache Iceberg tables through DuckDB (Snowflake Horizon catalog), see Iceberg
  • SingleStore (limited support)

• Type-Safe Queries: Strongly-typed query results with automatic mapping between database and .NET types

• Dynamic Data Operations:

  • Create, read, update, and delete table data
  • Dynamic table schema management
  • Copy tables and data between tables

• Transaction Support: Full transaction support across all database providers

• Parameterized Queries: Protection against SQL injection through parameterized queries

• SQL Analytics: Built-in query analytics and performance monitoring

• File Import: Read data from CSV and Excel files with built-in form file readers

• Data Export: Export data and schema definitions to CSV and Excel formats

• Migration Support: Database migration utilities for schema evolution

• Error Handling: Comprehensive error handling with detailed exception information

Installation

Prerequisites

• .NET 10.0 or higher
• Required database client libraries:
  • MySQL: MySql.Data package (included as dependency)
  • Snowflake: Snowflake.Data package (included as dependency)
  • Iceberg: DuckDB.NET.Data.Full package (included as dependency, ships the native DuckDB library)

NuGet Package

dotnet add package Siemens.AspNet.DbProvider

Getting Started

Basic Setup

Register the database provider in your Program.cs or startup class:

// Add base services
builder.Services.AddDbProvider(builder.Configuration);

// Add specific provider(s) you need
builder.Services.AddMySqlProvider(builder.Configuration);
// and/or
builder.Services.AddSnowflakeProvider(builder.Configuration);

// If using MySQL, add connection factory
builder.Services.AddMySqlConnectionFactory();

// If using dynamic data features
builder.Services.AddDatabaseService(builder.Configuration);

// If using form file readers (CSV, Excel)
builder.Services.AddFormFileReaderProvider();

// If using data export features
builder.Services.AddDataExporter();
builder.Services.AddSchemaExporter();

Connection String Configuration

In your appsettings.json file:

{
  "ConnectionStrings": {
    "MySqlConnection": "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;",
    "SnowflakeConnection": "account=myAccount;user=myUser;password=myPassword;DB=myDatabase;SCHEMA=mySchema;"
  }
}

Basic Usage

Executing Queries

public class UserRepository
{
    private readonly IDbQueryProvider _dbQueryProvider;

    public UserRepository(IDbQueryProvider dbQueryProvider)
    {
        _dbQueryProvider = dbQueryProvider;
    }

    public async Task<User> GetUserByIdAsync(int userId)
    {
        const string sql = "SELECT * FROM Users WHERE Id = @UserId";
        var parameters = new { UserId = userId };
        
        return await _dbQueryProvider.QueryFirstOrDefaultAsync<User>(sql, parameters);
    }
    
    public async Task<IEnumerable<User>> GetAllUsersAsync()
    {
        const string sql = "SELECT * FROM Users";
        
        return await _dbQueryProvider.QueryAsync<User>(sql);
    }
    
    public async Task<int> CreateUserAsync(User user)
    {
        const string sql = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email); SELECT LAST_INSERT_ID();";
        var parameters = new { user.Name, user.Email };
        
        return await _dbQueryProvider.ExecuteScalarAsync<int>(sql, parameters);
    }
}

Using Transactions

public async Task TransferFundsAsync(int fromAccountId, int toAccountId, decimal amount)
{
    using var transaction = await _dbQueryProvider.BeginTransactionAsync();
    
    try
    {
        await _dbQueryProvider.ExecuteAsync(
            "UPDATE Accounts SET Balance = Balance - @Amount WHERE Id = @FromId", 
            new { Amount = amount, FromId = fromAccountId },
            transaction);
            
        await _dbQueryProvider.ExecuteAsync(
            "UPDATE Accounts SET Balance = Balance + @Amount WHERE Id = @ToId", 
            new { Amount = amount, ToId = toAccountId },
            transaction);
            
        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

Dynamic Data Features

The Dynamic Data module provides powerful schema-agnostic database operations without writing SQL:

Service Registration

// Register the DatabaseService 
builder.Services.AddDatabaseService(builder.Configuration);

Table Schema Management

public class SchemaService
{
    private readonly IDatabaseService _databaseService;
    private readonly DbConnectionInfo _dbConnectionInfo;

    public SchemaService(IDatabaseService databaseService, IDbTypeProvider dbTypeProvider, IConfiguration configuration)
    {
        _databaseService = databaseService;
        
        // Create connection info
        var dbProvider = dbTypeProvider.GetFor(DatabaseProvider.MySql);
        _dbConnectionInfo = dbProvider.ParseConnectionString(
            configuration.GetConnectionString("MySqlConnection"));
    }

    // Create a new table with specified columns
    public async Task<SimplifiedTableSchema> CreateProductTableAsync()
    {
        // Define columns for the table
        var columns = new List<SimpleColumn>
        {
            new("Id", SimplifiedDbType.Integer) { IsPrimaryKey = true, IsAutoIncrement = true },
            new("ProductName", SimplifiedDbType.String) { MaxLength = 100, IsNullable = false },
            new("Description", SimplifiedDbType.String) { MaxLength = 500 },
            new("Price", SimplifiedDbType.Decimal) { IsNullable = false },
            new("CreatedAt", SimplifiedDbType.DateTime) { IsNullable = false },
            new("IsActive", SimplifiedDbType.Bool) { IsNullable = false, DefaultValue = "1" }
        };

        // Create the table
        return await _databaseService.AddTableAsync(
            "Products", 
            columns.ToImmutableList(), 
            _dbConnectionInfo);
    }
    
    // Add a column to existing table
    public async Task<int> AddCategoryColumnAsync()
    {
        // Get table schema
        var tableSchema = await _databaseService.GetTableSchemaAsync(
            "Products", 
            _dbConnectionInfo);
            
        // Add column
        return await _databaseService.AddColumnToAsync(
            tableSchema,
            new SimpleColumn("Category", SimplifiedDbType.String) { MaxLength = 50 });
    }
    
    // Copy table structure (with or without data)
    public async Task<SimplifiedTableSchema> CreateProductsBackupTableAsync()
    {
        // Get existing table schema
        var existingTable = await _databaseService.GetTableSchemaAsync(
            "Products", 
            _dbConnectionInfo);
            
        // Create copy with data
        return await _databaseService.CopyTableAsync(
            "Products_Backup",
            existingTable,
            copyData: true);
    }
}

Dynamic CRUD Operations

public class ProductRepository
{
    private readonly IDatabaseService _databaseService;
    private readonly SimplifiedTableSchema _tableSchema;

    public ProductRepository(IDatabaseService databaseService)
    {
        _databaseService = databaseService;
        
        // This would be retrieved once and cached/injected in a real app
        _tableSchema = databaseService.GetTableSchemaAsync("Products", connectionInfo).Result;
    }

    // Insert data
    public async Task<InsertDataResult> AddProductsAsync(IEnumerable<Dictionary<string, object>> products)
    {
        // Convert to dynamic data objects
        var dynamicData = new DynamicDataObjectsWithLocation(products);
        
        // Add audit values
        var auditValues = new Dictionary<string, string>
        {
            { "CreatedAt", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss") },
            { "IsActive", "1" }
        }.ToImmutableDictionary();
        
        // Insert data
        return await _databaseService.InsertDataAsync(_tableSchema, dynamicData, auditValues);
    }
    
    // Query data with filtering
    public async Task<IEnumerable<ProductDto>> GetActiveProductsAsync()
    {
        // Create where condition
        var wherePart = new WherePart();
        wherePart.AddEqualCondition("IsActive", "1");
        
        // Define sort order
        var sortOrder = new SortOrder("ProductName", ColumnSortOrder.Asc);
        
        // Get data with specific columns
        return await _databaseService.GetDataAsync<ProductDto>(
            _tableSchema,
            wherePart,
            sortOrder,
            "Id", "ProductName", "Price", "Category");
    }
    
    // Query with pagination
    public async Task<PageInfo<ProductDto>> GetProductsPagedAsync(int pageNumber, int pageSize)
    {
        // Create pagination
        var pagination = new Pagination(pageNumber, pageSize);
        
        // Create sort order
        var sortOrder = new SortOrder("ProductName", ColumnSortOrder.Asc);
        
        // Get paginated data
        return await _databaseService.GetDataPageWiseAsync<ProductDto>(
            _tableSchema,
            pagination,
            new WherePart(),
            sortOrder);
    }
    
    // Update data
    public async Task<UpdateDataResult> UpdateProductPricesAsync(
        IEnumerable<Dictionary<string, object>> productsWithNewPrices)
    {
        // Convert to dynamic data objects
        var dynamicData = new DynamicDataObjectsWithLocation(productsWithNewPrices);
        
        // Add audit values
        var auditValues = new Dictionary<string, string>
        {
            { "ModifiedAt", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss") }
        }.ToImmutableDictionary();
        
        // Update data
        return await _databaseService.UpdateDataAsync(_tableSchema, dynamicData, auditValues);
    }
    
    // Delete data by condition
    public async Task<int> DeleteDiscontinuedProductsAsync(string category)
    {
        var wherePart = new WherePart();
        wherePart.AddEqualCondition("Category", category);
        wherePart.AddEqualCondition("IsActive", "0");
        
        return await _databaseService.DeleteDataAsync(_tableSchema, wherePart);
    }
}

Powerful Data Import and Export

public class DataImportExportService
{
    private readonly IFormFileReaderProvider _fileReaderProvider;
    private readonly IDatabaseService _databaseService;
    private readonly IDbDataExporter _dataExporter;
    private readonly IDbSchemaExporter _schemaExporter;
    private readonly SimplifiedTableSchema _tableSchema;

    public DataImportExportService(
        IFormFileReaderProvider fileReaderProvider,
        IDatabaseService databaseService,
        IDbDataExporter dataExporter,
        IDbSchemaExporter schemaExporter)
    {
        _fileReaderProvider = fileReaderProvider;
        _databaseService = databaseService;
        _dataExporter = dataExporter;
        _schemaExporter = schemaExporter;
        
        // This would be retrieved once and cached/injected in a real app
        _tableSchema = databaseService.GetTableSchemaAsync("Products", connectionInfo).Result;
    }

    // Import products from CSV file
    public async Task<InsertDataResult> ImportProductsFromCsvAsync(IFormFile csvFile)
    {
        // Get CSV reader
        var reader = _fileReaderProvider.GetReader(FormFileReaderInfo.CsvReader);
        
        // Read data as dynamic objects
        var products = await reader.ReadAsDynamicObjectsAsync(csvFile);
        
        // Insert into database
        return await _databaseService.InsertDataAsync(_tableSchema, products);
    }
    
    // Export data to CSV file
    public async Task ExportProductsToCsvAsync(string filePath)
    {
        // Get product data
        var data = await _databaseService.GetDataAsync<Dictionary<string, object>>(
            _tableSchema,
            "*");
            
        // Export to CSV
        await _dataExporter.ExportAsync(
            _tableSchema.TableName,
            data,
            filePath,
            DbDataExporterInfo.CsvExporter);
    }
    
    // Export schema to Excel
    public async Task ExportSchemaToExcelAsync(string filePath)
    {
        await _schemaExporter.ExportAsync(
            _tableSchema,
            filePath,
            DbSchemaExporterInfo.ExcelExporter);
    }
}

Advanced Features

SQL Analytics

The provider includes SQL analytics that can help you monitor and optimize your queries:

// Enable SQL analytics in your configuration
public void ConfigureServices(IServiceCollection services)
{
    services.AddMySqlAnalyticsHandler(Configuration);
    // ... other services
}

// Use analytics-enabled methods
public class PerformanceMonitoringService
{
    private readonly IDbQueryProvider _dbQueryProvider;
    
    public PerformanceMonitoringService(IDbQueryProvider dbQueryProvider)
    {
        _dbQueryProvider = dbQueryProvider;
    }
    
    public async Task<QueryPerformanceInfo> GetUserDataWithPerformanceMetrics(int userId)
    {
        var sql = @"
            SELECT u.*, p.* 
            FROM Users u
            JOIN UserProfiles p ON u.Id = p.UserId
            WHERE u.Id = @UserId";
            
        var (users, metrics) = await _dbQueryProvider.QueryWithAnalyticsAsync<UserWithProfile>(
            sql, 
            new { UserId = userId });
            
        // Return both query results and performance metrics
        return new QueryPerformanceInfo
        {
            Data = users,
            ExecutionTimeMs = metrics.ExecutionTime.TotalMilliseconds,
            RowsAffected = metrics.RowCount,
            SqlExecuted = metrics.FormatedSql
        };
    }
}

Logging and Error Handling

The provider offers comprehensive logging and error handling:

public class DataAccessService
{
    private readonly IDbQueryProvider _dbQueryProvider;
    private readonly ILogger<DataAccessService> _logger;
    
    public DataAccessService(IDbQueryProvider dbQueryProvider, ILogger<DataAccessService> logger)
    {
        _dbQueryProvider = dbQueryProvider;
        _logger = logger;
    }
    
    public async Task<IEnumerable<User>> GetUsersWithLoggingAsync()
    {
        const string sql = "SELECT * FROM Users";
        
        try
        {
            // Execute query with logging
            var (users, logs) = await _dbQueryProvider.QueryWithLogAsync<User>(sql);
            
            // Log query information
            _logger.LogInformation(
                "Query executed successfully: {SqlQuery}, Rows: {RowCount}, Time: {ExecutionTimeMs}ms",
                logs.FormatedSql,
                logs.RowCount,
                logs.ExecutionTime.TotalMilliseconds);
                
            return users;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing query");
            throw;
        }
    }
}

Iceberg through DuckDB and Snowflake Horizon

The Iceberg provider runs the SQL of the Snowflake provider on the Apache Iceberg tables behind Snowflake Horizon. DuckDB attaches Horizon's Iceberg REST catalog and reads and writes the data files on S3 directly - no Snowflake warehouse is involved. Query providers, parameters, transactions, embedded SQL files and the @dbName/@schema/@dbPrefix placeholders work as for Snowflake, so a service switches by swapping the connection parser:

// Before: Snowflake
public sealed class GetConnectionQuery(SnowflakeConnectionParser parser) { ... parser.ParseConnection(secret) ... }

// After: Iceberg - same SnowflakeCertificateConnection secret, same DbConnectionInfo
public sealed class GetConnectionQuery(IcebergConnectionParser parser) { ... parser.ParseConnection(secret) ... }

AddDbProvider registers the Iceberg provider and IcebergConnectionParser. The catalog side comes from the IcebergSettings configuration section; account, user and key pair come from the Snowflake key-pair secret:

The S3 credentials follow AwsSettings__Profile and the region AwsSettings__Region, the AWS settings the rest of the application already runs on; there is no Iceberg setting for the profile. DuckDB only sees the native process environment: AWS_PROFILE set in code or in a test configuration is ignored and DuckDB would use the default profile, which fails with ExpiredToken once its credentials are old. Without an AwsSettings__Profile (Lambda, CI) the credentials come from the execution role or the environment.

How it works:

• One engine per process and connection string. The first connection loads the DuckDB extensions (httpfs, aws, avro, iceberg), creates the S3 secret (AWS credential chain, region pinned) and attaches the catalog. Every further connection is a Duplicate() of that engine.
• Authentication. Horizon expects a key-pair JWT that is valid for a minute. A private OAuth endpoint on 127.0.0.1 answers DuckDB's token requests with a fresh Horizon token exchange, so DuckDB renews tokens itself and the private key never enters DuckDB.
• Freshness. MAX_TABLE_STALENESS '0 seconds': every transaction reads the current snapshot. Without a transaction every statement commits on its own, as with Snowflake; BeginTransactionAsync groups statements, and the commit goes table by table (DISABLE_MULTI_TABLE_COMMIT, Horizon has no multi-table commit). Changes across multiple tables are not atomic: a later table commit can fail after an earlier one succeeded.
• Commit conflicts. Iceberg commits optimistically: when the table changed since it was loaded, the catalog rejects the commit with 409 and the writer has to commit again on the new snapshot. DuckDB also reports this conflict for the next write of a table it committed to itself shortly before, without any concurrent writer. A single statement outside a transaction is therefore repeated up to four times (100-800 ms back-off with jitter); the rejected commit wrote nothing, so this is safe. Statements inside a transaction and SQL texts with several statements are not repeated: the conflict surfaces at the commit, or the earlier statements are already committed. Callers of such units have to repeat them themselves.
• Identifiers. Iceberg tables created from Snowflake use UPPER_SNAKE_CASE columns. With UpperSnake, quoted identifiers are translated ("Project"."ProjectId" becomes "PROJECT"."PROJECT_ID") and UPPER_SNAKE_CASE result columns are returned as PascalCase, so entities map without aliases. String literals and comments are not touched.
• Snowflake dialect. @Name and :Name parameters become DuckDB's $Name outside literals, quoted identifiers and comments. NULL values remain bound parameters. ARRAY_CONSTRUCT, IFF, EQUAL_NULL, PARSE_JSON and CURRENT_TIMESTAMP() are rewritten. Other Snowflake-only functions have to be replaced by their DuckDB counterpart in the SQL. The Snowflake timestamp types are rewritten in casts (CAST(x AS ...), x::...) and column definitions: TIMESTAMP_LTZ, TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP_TZ become TIMESTAMPTZ (DuckDB keeps the instant but no offset and accepts no precision, so (n) is dropped) and TIMESTAMP_NTZ(n) becomes TIMESTAMP(n). TIMESTAMP, TIMESTAMPTZ, TIMESTAMP WITH TIME ZONE and DATETIME are valid DuckDB types and stay. Every connection runs in UTC, so casting a DateTimeOffset to TIMESTAMP_NTZ gives its UTC wall clock; Snowflake keeps the wall clock of the offset instead, so pass UTC values.
• Values. Guid and enum parameters are passed as text (the Iceberg columns are strings). LIST/STRUCT results are returned as JSON text and TIMESTAMP WITH TIME ZONE as DateTimeOffset, like the Snowflake driver does.

The connection string selects the mode per connection. For local development or tests, no Snowflake secret is needed:

const string connectionString = "engine=iceberg;mode=memory;schema=main;threads=2;memory_limit=256MiB;";
var connection = dbTypeProvider.GetFor(connectionString).ParseConnectionString(connectionString);
using var queryProvider = connection.GetQueryProvider();

IcebergSettings.Mode only controls what IcebergConnectionParser writes into the connection string. Both modes start DuckDB with DataSource=:memory: and apply the same thread and memory limits. Catalog mode then attaches Horizon and uses Iceberg data files on S3; memory mode uses ordinary DuckDB tables and does not exercise Iceberg storage, catalog authentication or remote commits. An S3 path alone cannot replace the endpoint, warehouse and authentication settings. Connections with the same string share the local database until the engine registry is disposed; use distinct schemas in the connection strings to isolate tests. Values in generated connection strings use ADO.NET quoting, preserving PEM line breaks and pass phrases containing whitespace, quotes or semicolons.

Limits: the dynamic data services (table schema, copy, bulk import/export) are not supported for Iceberg. DuckDB reads the AWS settings from the native process environment, so AWS_PROFILE/AWS_REGION set with Environment.SetEnvironmentVariable are not visible to it.

Database Types Support

The provider supports various database types:

MySQL Types

• String types: varchar, tinytext, text, mediumtext, longtext, etc.
• Numeric types: int, tinyint, smallint, bigint, decimal, float, double
• Date/time types: datetime, date, timestamp, time, year
• Boolean type
• JSON type

Snowflake Types

• String types: varchar, string, text
• Numeric types: integer, smallint, bigint, decimal, float, double
• Date/time types: datetime, date, time, timestamp
• Boolean type
• JSON type

Iceberg Types

• String types: varchar, string, text
• Numeric types: integer, smallint, bigint, decimal, float, double, real
• Date/time types: timestamp, timestamptz, date, time
• Boolean type
• Binary type: blob
• Complex types (list, struct, map) as JSON text

Simplified Database Types

For cross-database compatibility, the library provides simplified types:

// Simplified types abstract the actual database type
public enum SimplifiedDbType
{
    String,
    Integer,
    Decimal,
    Bool,
    DateTime,
    Date,
    Json
}

// Example using simplified types to create a table
var columns = new List<SimpleColumn>
{
    new("Id", SimplifiedDbType.Integer) { IsPrimaryKey = true, IsAutoIncrement = true },
    new("Name", SimplifiedDbType.String) { MaxLength = 100 },
    new("IsActive", SimplifiedDbType.Bool),
    new("CreatedAt", SimplifiedDbType.DateTime),
    new("Config", SimplifiedDbType.Json)
};

Code rules for DbProvider consumers

The package brings its own code rule pack, DbProviderRules (namespace Siemens.AspNet.DbProvider.CodeRules). It keeps data access on the query provider. If you run code rules (Siemens.AspNet.CodeRules.MSTest, .XUnit, .NUnit or the coderules CLI), register the pack in your ICodeRuleSetup:

public sealed class CodeRuleSetup : ICodeRuleSetup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddCodeRuleEngine("MyRepo.slnx", o => o.AddRulePack<DbProviderRules>());
    }
}

Skip a rule with a reason, e.g. .SkipRule<Never_Route_DbConnections_To_Methods_Of_Type>("Legacy importer, replaced in #1234"). Applications that do not run code rules are not affected, and the pack adds no dependency.

Contributing

Contributions to the Siemens.AspNet.DbProvider are welcome. Please ensure that your code adheres to the existing style and includes appropriate tests.

License

Copyright 2025 (c) Siemens AG. All rights reserved.