Flowsy.Db.Repository.Sql 1.0.0

Flowsy Db Repository Sql

This package is a wrapper for Flowsy.Db.Conventions and a flexible and easy to use implementation of Flowsy.Db.Repository.Abstractions.

DbRepository

This class implements the IRepository interface using the conventions provided by the Flowsy.Db.Conventions package.

The DbRepository class is abstract and is meant to be the base class for all repositories in your application. Any derived class must call one of the base constructors provided by the DbRepository class.

For example, you could create the following interface in your domain layer:

using Flowsy.Db.Repository.Abstractions;

namespace Example.Domain;

public interface IStudentRepository : IRepository
{
    // Define methods to interact with Student entities
}

And then create the following class in your persistence layer:

using Example.Domain;
using Flowsy.Db.Repository.Sql;

namespace Example.Persistence;

public sealed class DbStudentRepository : DbRepository, IStudentRepository
{
    public DbStudentRepository(IDbConnectionFactory connectionFactory, ILogger? logger = null) : base(connectionFactory, logger)
    {
    }
  
    public DbStudentRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
    {
    }
  
    // Implement IStudentRepository methods to interact with Student entities.
    // Methods of DbStudentRepository can invoke all methods inherited from DbRepository, which are
    // wrappers for the extension methods on IDbConnection provided by the Flowsy.Db.Conventions package.
}

By using the first constructor, your repository will get connections from a connection factory, which will handle disposal of connections when going out of scope. On the other hand, by using the second constructor, your repository will work with a connection provided by a unit of work, so all operations will be executed under the context of a transaction.

DbRepository Methods

The DbRepository class provides several protected and virtual methods that are wrappers for the extension methods on IDbConnection provided by the Flowsy.Db.Conventions package. The benefit of using these methods instead of calling the extension methods directly is that DbRepository always uses its associated connection, transaction and conventions. In other words, you don't have to worry about connection, transaction and convention handling once you set it all up when your application starts.

• Execute: Executes a query and returns the number of affected rows.
• ExecuteAsync: Asynchronously executes a query and returns the number of affected rows.
• Query: Executes a query and returns a collection of objects of the specified type.
• QueryAsync: Asynchronously executes a query and returns a collection of objects of the specified type.
• QueryFirst: Executes a query and returns the first result or throws an exception if none is found. The result is mapped to the specified type.
• QueryFirstAsync: Asynchronously executes a query and returns the first result or throws an exception if none is found. The result is mapped to the specified type.
• QueryFirstOrDefault: Executes a query and returns the first result or the default value if none is found. The result is mapped to the specified type.
• QueryFirstOrDefaultAsync: Asynchronously executes a query and returns the first result or the default value if none is found. The result is mapped to the specified type.
• QueryMultiple: Executes a query and returns multiple result sets.
• QueryMultipleAsync: Asynchronously executes a query and returns multiple result sets.
• QuerySingle: Executes a query and returns a single result or throws an exception if none or more than one is found. The result is mapped to the specified type.
• QuerySingleAsync: Asynchronously executes a query and returns a single result or throws an exception if none or more than one is found. The result is mapped to the specified type.
• QuerySingleOrDefault: Executes a query and returns a single result or the default value if none or more than one is found. The result is mapped to the specified type.
• QuerySingleOrDefaultAsync: Asynchronously executes a query and returns a single result or the default value if none or more than one is found. The result is mapped to the specified type.

DbUnitOfWork

This class implements the IUnitOfWork interface and allows consumers to perform operations which will be automatically undone if an exception is thrown before calling the SaveWork or SaveWorkAsync methods.

This way, when you're working in the domain and application layers, you don't need to think in terms of database transactions, instead you can think in terms of a higher level of abstraction: a unit of work or set of operations that will be fully saved or fully undone.

For example, you could create the following interface in your domain layer:

using Flowsy.Db.Repository.Abstractions;

namespace Example.Domain;

public interface IAcademicAssessmentUnitOfWork : IUnitOfWork
{
    // Repositories involved in the academic assessment context
    IStudentRepository StudentRepository { get; }
    ICourseRepository CourseRepository { get; }
}

And then create the following class in your persistence layer:

using Example.Domain;
using Flowsy.Db.Repository.Sql;

namespace Example.Persistence;

public class DbAcademicAssessmentUnitOfWork : DbUnitOfWork, IAcademicAssessmentUnitOfWork
{
    public DbAcademicAssessmentUnitOfWork(IDbConnection connection, ILogger? logger = null) : base(connection, logger)
    {
    }
  
    private IStudentRepository? _studentRepository;
    public IStudentRepository StudentRepository => _studentRepository ??= CreateRepository<IStudentRepository, DbStudentRepository>();
  
    private ICourseRepository? _courseRepository;
    public ICourseRepository CourseRepository => _courseRepository ??= CreateRepository<ICourseRepository, DbCourseRepository>();
}

This way, all operations performed by the IStudentRepository and ICourseRepository objects will be executed under the same transaction, and if an exception is thrown before calling the SaveWork or SaveWorkAsync methods, all operations will be undone.

DbUnitOfWork Methods

• BeginWork: Starts a new transaction.
• SaveWork: Commits the transaction.
• SaveWorkAsync: Asynchronously commits the transaction.
• DiscardWork: Rolls back the transaction.
• DiscardWorkAsync: Asynchronously rolls back the transaction.
• OnWorkBegun (protected virtual): Called when a transaction is started.
• OnWorkSaved (protected virtual): Called when a transaction is committed.
• OnWorkDiscarded (protected virtual): Called when a transaction is rolled back.

DbUnitOfWork Events

• WorkBegun: Event that is raised when a transaction is started.
• WorkSaved: Event that is raised when a transaction is committed.
• WorkDiscarded: Event that is raised when a transaction is rolled back.

Dependency Injection

For non-trivial applications, the recommended way of using repositories and units of work is by registering them in the dependency injection container. This package provides extension methods for IServiceCollection to register repositories and units of work.

In any application that uses the Microsoft.Extensions.DependencyInjection package (like Web APIs), you can register repositories and units of work as follows:

appsettings.json

Configure connections for every database required by your application in the appsettings.json file.

{
  "Databases": {
    "MyDatabase": {
      "ProviderInvariantName": "Npgsql",
      "ConnectionString": "Server=localhost;Port=5432;Database=my_database;User Id=my_user;Password=mysecret;Include Error Detail=True;"
    }
  }
}

Program.cs

var builder = WebApplication.CreateBuilder(args);

// ...
// Register services
// ...

// Register the required database providers.
// Flowsy.Db.Conventions and Flowsy.Db.Repository.Sql won't perform any registration of database providers,
// to be decoupled from a specific database provider and let your application decide which one to use.
// This example uses Npgsql as the database provider.
DbProviderFactories.RegisterFactory("Npgsql", NpgsqlFactory.Instance);

// Register the IDbConnectionFactory (from Flowsy.Db.Conventions)
builder.Services.AddConnectionFactory((options, serviceProvider) =>
        {
            var connectionOptions = new Dictionary<string, DbConnectionOptions>();
            
            var config = serviceProvider.GetRequiredService<IConfiguration>();
            var connectionConfigurations = config
                .GetSection("Databases")
                .GetChildren();
            
            foreach (var connectionConfiguration in connectionConfigurations)
            {
                var connectionKey = connectionConfiguration.Key;
                var providerInvariantName = connectionConfiguration["ProviderInvariantName"];
                var connectionString = connectionConfiguration["ConnectionString"];
                
                var provider = providerInvariantName switch
                {
                    "Npgsql" => DbProvider.PostgreSql,
                    _ => throw new NotSupportedException($"The provider '{providerInvariantName}' is not supported.")
                };;
                
                connectionOptions[connectionKey] = new DbConnectionOptions(provider, connectionString);
            }
            
            options.ConnectionOptions = connectionOptions;
        });

// Register the default conventions
builder.Services
    .AddRepositories(options => 
    {
        // Log level for SQL commands executed by all repositories
        options.LogLevel = LogLevel.Debug;
        
        // Resolve the types of the objects that need to be mapped using the conventions
        var types = Assembly
            .GetExecutingAssembly()
            .GetTypes()
            .Where(t => t.Namespace?.EndsWith(".Domain") ?? false)
            .ToArray();
        
        options.Conventions
            .ForConnections("MyDatabase") // Configuration key from appsettings.json
            .ForSchemas("my_schema")
            .ForTables(CaseStyle.LowerSnakeCase)
            .ForColumns(CaseStyle.LowerSnakeCase, types)
            .ForRoutines(DbRoutineType.StoredFunction, CaseStyle.LowerSnakeCase)
            .ForParameters(CaseStyle.LowerSnakeCase, "p_", useNamedParameters: true)
            .ForEnums(DbEnumFormat.Name, CaseStyle.PascalCase, customTypeMapping: new Dictionary<Type, string>
            {
                { typeof(MyEnum), "my_schema.my_enum" }
            });
    })
    .UsingRepository<IStudentRepository, DbStudentRepository>(options => {
        // Optionally configure options for each repository
        options.Conventions.ForSchema("another_schema");
    })
    .UsingRepository<ICourseRepository, DbCourseRepository>(); // Use the default conventions.

// The UsingRepository method will look for a constructor  that
// receives an IDbConnectionFactory instance to create the repository. 

// Register the unit of work
// IAcademicAssessmentUnitOfWork must inherit from IUnitOfWork.
// DbAcademicAssessmentUnitOfWork must inherit from DbUnitOfWork
// and have a constructor that receives an IDbConnection instance.
// The connection name must match the name of the corresponding section in the appsettings.json file.
builder.Services.AddUnitOfWork<IAcademicAssessmentUnitOfWork, DbAcademicAssessmentUnitOfWork>(
    "MyDatabase",
    (connection, serviceProvider) => 
    {
        var logger = serviceProvider.GetRequiredService<ILogger<DbAcademicAssessmentUnitOfWork>>();
        return new DbAcademicAssessmentUnitOfWork(connection, logger);
    });

var app = builder.Build();

// ...

app.Run();

Both IDbConnectionFactory and IUnitOfWork will be registered as scoped services, so their associated connections will live only within the corresponding scope, for instance the scope of a web request.

Using the Services

You can use your repositories and units of work just by injecting them into the constructor of your services.

using Example.Domain;

namespace Example.Application;

public class EvaluateStudentCommandHandler
{
    private readonly IAcademicAssessmentUnitOfWork _unitOfWork;

    public EvaluateStudentCommandHandler(IAcademicAssessmentUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
    }

    public async Task HandleAsync(EvaluateStudentCommand command, CancellationToken cancellationToken)
    {
        var student = new Student();;
  
        // Perform operations on the student entity
        // using values provided by the command
        // ...
  
        _unitOfWork.BeginWork();
  
        // Fictitious method that will save operations performed on the student entity
        await _unitOfWork.StudentRepository.SaveAsync(student, cancellationToken);
  
        // Call methods on other repositories of the unit of work
        // await _unitOfWork.CourseRepository.SomeOperationAsync(...);
  
        await _unitOfWork.SaveWorkAsync();
  
        // If an exception is thrown before calling SaveWorkAsync, all operations will be automatically undone
    }
}