Siemens.AspNet.DbProvider 0.1.0-alpha.165

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
  • 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 9.0 or higher
• Required database client libraries:
  • MySQL: MySql.Data package (included as dependency)
  • Snowflake: Snowflake.Data package (included as dependency)

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;
        }
    }
}

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

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)
};

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.