CG.Infrastructure.Data 3.10.9

CG.Infrastructure.Data

A comprehensive data access library that provides database migration, data access patterns, and repository interfaces using DbUp and Dapper for .NET applications.

Overview

CG.Infrastructure.Data is a specialized library that provides robust data access capabilities, database migration management, and repository pattern interfaces. It leverages DbUp for database schema management and Dapper for high-performance data access, offering a foundation for data persistence in .NET applications.

Note: This library provides interfaces and infrastructure components. Concrete implementations of repositories, database seeding, and connection management must be provided by consuming applications.

Features

• Database Migration Management: Automated database schema updates using DbUp
• High-Performance Data Access: Lightweight ORM capabilities with Dapper
• Repository Pattern Interfaces: Generic repository interface for common CRUD operations
• Transaction Support: Full transaction management with rollback capabilities
• Connection Management: Flexible connection string handling with named connections
• Database Creation: Automatic database creation and configuration
• Script Management: Multiple script source options (file system, assemblies, etc.)
• Environment Awareness: Docker and local environment support
• Pagination Support: Built-in pagination with customizable query generation
• Column Protection: SQL injection protection for column names
• Service Registration: Automatic service registration with dependency injection

Requirements

• .NET 9.0 or later
• CG.Infrastructure.Core 3.10.8+
• CG.Infrastructure.Configuration 3.10.1+
• Dapper 2.1.66+
• dbup-core 6.0.4+
• dbup-sqlserver 6.0.0+
• Microsoft.Data.SqlClient 6.1.1+
• Microsoft.Extensions.Configuration.Abstractions 9.0.8+
• Microsoft.Extensions.DependencyInjection.Abstractions 9.0.8+
• Microsoft.Extensions.Logging.Abstractions 9.0.8+

Installation

dotnet add package CG.Infrastructure.Data

Or add the following to your .csproj file:

<PackageReference Include="CG.Infrastructure.Data" Version="3.10.8" />

Quick Start

1. Configure Database Settings

Add configuration to your appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=MyApp;Trusted_Connection=true;",
    "ReadOnlyConnection": "Server=localhost;Database=MyAppReadOnly;Trusted_Connection=true;"
  },
  "DbUpOptions": {
    "UseDocker": false,
    "DbSchema": "dbo",
    "Drop": false,
    "Ensure": true,
    "Journal": "__SchemaVersions",
    "ScriptPath": "C:\\Scripts\\Database",
    "UseLocal": false,
    "DbPath": "C:\\Databases",
    "DbInitialSize": "10MB",
    "DbMaxSize": "100MB",
    "DbFileGrowth": "10MB"
  },
  "DbOptions": {
    "CommandTimeout": 3000,
    "GetLastInsertId": "SELECT @@IDENTITY;"
  }
}

2. Register Data Services

In your Program.cs or Startup.cs:

using Infrastructure.Data.Extensions;
using Microsoft.Extensions.DependencyInjection;

var builder = WebApplication.CreateBuilder(args);

// Register data services
builder.Services.RegisterInfraDataServices(builder.Configuration);

var app = builder.Build();

3. Use Data Access Services

[ApiController]
[Route("[controller]")]
public class UsersController : ControllerBase
{
    private readonly IDataAccess _dataAccess;

    public UsersController(IDataAccess dataAccess)
    {
        _dataAccess = dataAccess;
    }

    [HttpGet("custom")]
    public async Task<ActionResult<IEnumerable<User>>> GetUsersCustom()
    {
        var query = "SELECT * FROM Users WHERE IsActive = @IsActive";
        var parameters = new { IsActive = true };
        
        var users = await _dataAccess.LoadData<User, object>(
            query, parameters, "DefaultConnection");
        
        return Ok(users);
    }
}

Core Functionality

Database Migration with DbUp

The library provides comprehensive database migration capabilities:

// Get migration service
var migrationService = serviceProvider.GetRequiredService<IDbMigrationService>();

// Configure scripts to run
var scripts = new List<ScriptConfig>
{
    new ScriptConfig
    {
        Folder = "Initial",
        Scripts = ScriptEnum.FileSystem,
        AlwaysRun = true
    },
    new ScriptConfig
    {
        Folder = "Updates",
        Scripts = ScriptEnum.ExecutingAssembly,
        AlwaysRun = false
    }
};

// Run migrations
var success = migrationService.UpgradeDatabase(scripts);

Data Access with Dapper

Direct SQL execution with full transaction support:

// Load data
var users = await _dataAccess.LoadData<User, object>(
    "SELECT * FROM Users WHERE Department = @Department",
    new { Department = "IT" },
    "DefaultConnection"
);

// Load single record
var user = await _dataAccess.LoadSingle<User, object>(
    "SELECT * FROM Users WHERE Id = @Id",
    new { Id = 1 },
    "DefaultConnection"
);

// Execute commands
var affectedRows = await _dataAccess.SaveData(
    "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)",
    new { Name = "John Doe", Email = "john@example.com" },
    "DefaultConnection"
);

// Get scalar values
var userId = await _dataAccess.SaveScalar(
    "INSERT INTO Users (Name, Email) VALUES (@Name, @Email); SELECT @@IDENTITY;",
    new { Name = "Jane Doe", Email = "jane@example.com" },
    "DefaultConnection"
);

Transaction Management

Full transaction support for complex operations:

// Start transaction
using var transaction = _dataAccess.StartTransaction("DefaultConnection");

try
{
    // Execute multiple operations in transaction
    var userResult = await _dataAccess.SaveDataInTransaction(
        "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)",
        new { Name = "John Doe", Email = "john@example.com" },
        transaction
    );

    var profileResult = await _dataAccess.SaveDataInTransaction(
        "INSERT INTO UserProfiles (UserId, Bio) VALUES (@UserId, @Bio)",
        new { UserId = userResult, Bio = "Software Developer" },
        transaction
    );

    // Commit transaction
    _dataAccess.CommitTransaction(transaction);
}
catch
{
    // Rollback on error
    _dataAccess.RollbackTransaction(transaction);
    throw;
}

Repository Pattern Interface

The library provides the IRepository<T> interface. You must implement concrete repository classes:

// Example implementation - you must create this class
public class UserRepository : IRepository<User>
{
    private readonly IDataAccess _dataAccess;

    public UserRepository(IDataAccess dataAccess)
    {
        _dataAccess = dataAccess;
    }

    public async Task<IEnumerable<User>> GetAllAsync()
    {
        return await _dataAccess.LoadData<User, object>(
            "SELECT * FROM Users", null, "DefaultConnection");
    }

    public async Task<User?> GetByIdAsync(object id)
    {
        return await _dataAccess.LoadSingle<User, object>(
            "SELECT * FROM Users WHERE Id = @Id",
            new { Id = id },
            "DefaultConnection"
        );
    }

    public async Task<Guid> CreateAsync(User entity)
    {
        var result = await _dataAccess.SaveScalar(
            "INSERT INTO Users (Name, Email) VALUES (@Name, @Email); SELECT @@IDENTITY;",
            entity,
            "DefaultConnection"
        );
        
        return new Guid(result.ToString());
    }

    public async Task<bool> UpdateAsync(User entity)
    {
        var affected = await _dataAccess.SaveData(
            "UPDATE Users SET Name = @Name, Email = @Email WHERE Id = @Id",
            entity,
            "DefaultConnection"
        );
        
        return affected > 0;
    }

    public async Task<bool> DeleteByIdAsync(object id)
    {
        var affected = await _dataAccess.SaveData(
            "DELETE FROM Users WHERE Id = @Id",
            new { Id = id },
            "DefaultConnection"
        );
        
        return affected > 0;
    }

    // Implement other interface methods...
    public async Task<IEnumerable<User>> FindAsync(Expression<Func<User, bool>> predicate)
    {
        // Implementation depends on your requirements
        throw new NotImplementedException("Implement based on your needs");
    }

    public async Task<bool> ExistsAsync(Expression<Func<User, bool>> predicate)
    {
        // Implementation depends on your requirements
        throw new NotImplementedException("Implement based on your needs");
    }

    public async Task<bool> DeleteAsync(User entity)
    {
        return await DeleteByIdAsync(entity.Id);
    }
}

Pagination Support

Built-in pagination with customizable query generation:

// Configure pagination options
services.RegisterDbOptions(configuration, options =>
{
    options.GetPageQuery = (input, pageIndex, pageSize, totalCount, orderBy, pairs) =>
    {
        var offset = (pageIndex - 1) * pageSize;
        return $"{input} {orderBy} OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY";
    };
});

// Use pagination
public async Task<PagedResult<User>> GetUsersPaged(int page, int pageSize)
{
    var offset = (page - 1) * pageSize;
    
    var users = await _dataAccess.LoadData<User, object>(
        "SELECT * FROM Users ORDER BY Name OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY",
        new { Offset = offset, PageSize = pageSize },
        "DefaultConnection"
    );

    var totalCount = await _dataAccess.LoadSingle<int, object>(
        "SELECT COUNT(*) FROM Users",
        null,
        "DefaultConnection"
    );

    return new PagedResult<User>(users, totalCount, page, pageSize);
}

Configuration Options

DbUpOptions

DbOptions

Script Management

The library supports multiple script sources through ScriptEnum:

• FileSystem: Scripts from file system paths
• ExecutingAssembly: Scripts embedded in executing assembly
• EntryAssembly: Scripts embedded in entry assembly
• SpecificAssembly: Scripts from a specific assembly
• ExecutingAssemblies: Scripts from multiple executing assemblies

Script Configuration

var scripts = new List<ScriptConfig>
{
    new ScriptConfig
    {
        Folder = "Initial",
        Scripts = ScriptEnum.FileSystem,
        AlwaysRun = true,
        Assembly = null
    },
    new ScriptConfig
    {
        Folder = "Migrations",
        Scripts = ScriptEnum.ExecutingAssembly,
        AlwaysRun = false,
        Assembly = "MyApp.Data"
    }
};

Service Registration

The library provides extension methods for easy service registration:

RegisterInfraDataServices

Registers all data services with configuration:

services.RegisterInfraDataServices(configuration);

This method:

1. Registers DbUpOptions with configuration binding
2. Registers DbOptions with configuration binding
3. Registers repository interfaces (note: implementations must be provided separately)
4. Registers data access services
5. Registers migration services

Important: Repository implementations are not automatically registered. You must register them manually or implement the RegisterServices method to scan for implementations.

Manual Registration

You can also register individual options manually:

// Register DbUp options
services.RegisterDbUpOptions(configuration, options =>
{
    options.UseDocker = true;
    options.ScriptPath = "/app/scripts";
});

// Register database options
services.RegisterDbOptions(configuration, options =>
{
    options.CommandTimeout = 5000;
    options.GetPageQuery = CustomPaginationQuery;
});

Implementation Requirements

What You Must Implement

1. Repository Classes: Implement IRepository<T> for your entities
2. Database Seeding: Implement IDatabaseSeedService if needed
3. Connection Factory: Ensure IConnectionFactory is properly implemented
4. Entity Models: Define your entity classes

Example Repository Registration

// Register your repository implementations
services.AddScoped<IUserRepository, UserRepository>();
services.AddScoped<IProductRepository, ProductRepository>();
// ... other repositories

Advanced Usage

Custom Pagination Query

services.RegisterDbOptions(configuration, options =>
{
    options.GetPageQuery = (input, pageIndex, pageSize, totalCount, orderBy, pairs) =>
    {
        var offset = (pageIndex - 1) * pageSize;
        var limit = pageSize;
        
        // Custom pagination logic
        if (pageSize > totalCount)
        {
            limit = totalCount;
        }
        
        return $"{input} {orderBy} LIMIT {limit} OFFSET {offset}";
    };
});

Database Seeding

Note: You must implement IDatabaseSeedService yourself:

public class DatabaseSeedService : IDatabaseSeedService
{
    private readonly IDataAccess _dataAccess;

    public DatabaseSeedService(IDataAccess dataAccess)
    {
        _dataAccess = dataAccess;
    }

    public async Task ApplySeeding()
    {
        // Check if seeding is needed
        var userCount = await _dataAccess.LoadSingle<int, object>(
            "SELECT COUNT(*) FROM Users", null, "DefaultConnection");

        if (userCount == 0)
        {
            // Apply initial seed data
            await _dataAccess.SaveData(
                "INSERT INTO Users (Name, Email, IsActive) VALUES (@Name, @Email, @IsActive)",
                new { Name = "Admin", Email = "admin@example.com", IsActive = true },
                "DefaultConnection"
            );
        }
    }
}

// Register the service
services.AddScoped<IDatabaseSeedService, DatabaseSeedService>();

Environment-Specific Configuration

// appsettings.Development.json
{
  "DbUpOptions": {
    "UseDocker": false,
    "ScriptPath": "C:\\DevScripts\\Database"
  }
}

// appsettings.Production.json
{
  "DbUpOptions": {
    "UseDocker": true,
    "ScriptPath": "/app/scripts"
  }
}

Dependencies

• CG.Infrastructure.Core: Provides the base service infrastructure and dependency injection support
• CG.Infrastructure.Configuration: Configuration management and connection options
• Dapper: High-performance micro ORM for data access
• dbup-core: Database migration framework
• dbup-sqlserver: SQL Server support for DbUp
• Microsoft.Data.SqlClient: SQL Server data provider
• Microsoft.Extensions.Configuration.Abstractions: Configuration system integration
• Microsoft.Extensions.DependencyInjection.Abstractions: Dependency injection support
• Microsoft.Extensions.Logging.Abstractions: Logging support

Integration with Infrastructure Suite

This library integrates with the broader infrastructure suite by:

1. Implementing IService: Integrates with the automatic service registration system
2. Configuration Integration: Uses the configuration library for connection management
3. Service Extensions: Provides extension methods for easy service registration
4. Dependency Injection: Seamlessly integrates with the DI container
5. Logging Integration: Uses the logging infrastructure for migration tracking

Best Practices

1. Script Organization: Organize scripts by version or feature in separate folders
2. Transaction Management: Always use transactions for multi-step operations
3. Connection Management: Use named connections for different database contexts
4. Error Handling: Implement proper error handling and rollback mechanisms
5. Performance: Use appropriate indexes and optimize SQL queries
6. Security: Use parameterized queries to prevent SQL injection
7. Testing: Test migrations in isolated environments before production
8. Repository Implementation: Implement repositories for each entity type
9. Service Registration: Manually register repository implementations or use assembly scanning

Important Notes

• This library provides interfaces and infrastructure, not complete implementations
• Repository classes must be implemented by consuming applications
• Database seeding service must be implemented by consuming applications
• Connection factory implementation must be provided
• Service registration for repositories may require manual configuration

Contributing

This library is part of the CG Infrastructure suite. For contributions, please follow the established patterns and ensure all tests pass.

License

This project is licensed under the terms specified in the LICENSE file.

Version History

• 3.10.8: Current stable release
• Supports .NET 9.0
• Includes comprehensive data access and migration capabilities
• Provides DbUp integration for database schema management
• Integrates with the infrastructure service registration system
• Note: Repository implementations must be provided by consumers

Support

For issues, questions, or contributions, please refer to the project repository or contact the maintainers.