c# – Dapper UOW with Multiple Databases

Scenarios / Requirements:

I am working on to convert the existing WebAPI Data Layer to Dapper from EF. The following are the major scenarios / my aim during the UoW implementation.

  1. Transaction handling for one database (database transaction).
  2. Transaction handling between database tables (Transaction Scope).
  3. Service methods calls another service methods to do some functionalities.
  4. Proper connection / UoW disposal.
  5. Need to allow DI for Repositories also.
  6. Connection creation should be hide from the service layer

My aim of this question is to :

  • Please look into my implementation and suggest any improvements or better practice
  • Any code smell or issues

I think it will be useful for others also

My Implementation

I searched for Dapper UoW and got this stack overflow thread. I liked the answers of Amit Joshi and Nathan Cooper. I just start from the answers and mix or extend both answers according to my needs.

Thank you both of you for the helpful answers and also to pim which was also nice.

Here I created a sample WebAPI with 2 databases(Production and Purchase). We need to use Transaction Scope for transactions between database, we have to initialize the connection inside the Transaction Scope. So started the same way @amit-joshi implemented but the class name (Dal Session) I used from @nathan-cooper. In order to inject the repositories, I passed UOw context as parameter to the repositories as explained by @nathan-cooper with some changes.

In order to handle multiple databases, I created 2 different UOw context and inject thorugh DI.Please find my code below;

Models:

    //Database Name : Production
public class Product
{
    public int ProductID { get; set; }
    public string Name { get; set; }
    public int ProductCategoryId { get; set; }
}

public class ProductCategory
{
    public int ProductCategoryID { get; set; }
    public string Name { get; set; }

    public List<Product> Products { get; set; }
}

//Database Name : Purchase
public class Vendor
{
    public int VendorID { get; set; }
    public string Name { get; set; }
    public int CreditRating { get; set; }
    public List<Product> Products { get; set; }
}

DatabaseName Enum:

public enum DatabaseConnectionName
{
    Production,
    Purchase
}

Unit Of Work pattern:

public interface IUnitOfWorkContext
{
    UnitOfWork Create();
    IDbConnection Connection { get; }
    IDbTransaction Transaction { get; }

}
public interface IProductionUnitOfWorkContext : IUnitOfWorkContext
{

}

public interface IPurchaseUnitOfWorkContext : IUnitOfWorkContext
{

}
    public abstract class UnitOfWorkContextBase : IUnitOfWorkContext, IDisposable
{
    private readonly IDbConnection _connection = null;
    private UnitOfWork _unitOfWork;
    private bool IsUnitOfWorkOpen => !(_unitOfWork == null || _unitOfWork.IsDisposed);
    public UnitOfWorkContextBase(DatabaseConnectionName connectionName)
    {
        _connection = new SqlConnection(Constants.Get(connectionName));
    }

    public IDbConnection Connection {
        get
        {
            if (!IsUnitOfWorkOpen)
            {
                throw new InvalidOperationException(
                    "There is not current unit of work from which to get a connection. Call BeginTransaction first");
            }
            return _unitOfWork.Connection;
        }
    }
    public IDbTransaction Transaction {
        get
        {
            if (!IsUnitOfWorkOpen)
            {
                throw new InvalidOperationException(
                    "There is not current unit of work from which to get a connection. Call BeginTransaction first");
            }
            return _unitOfWork.Transaction;
        }
    }

    public UnitOfWork Create()
    {
        if (IsUnitOfWorkOpen)
        {
            throw new InvalidOperationException(
                "Cannot begin a transaction before the unit of work from the last one is disposed");
        }
        _connection.Open();
        _unitOfWork = new UnitOfWork(_connection);
        return _unitOfWork;
    }

    public void Dispose()
    {
        _unitOfWork.Dispose();
        _connection.Dispose();
    }
}
public class ProductionUnitOfWorkContext : UnitOfWorkContextBase, IProductionUnitOfWorkContext
{
    public ProductionUnitOfWorkContext()
        :base(DatabaseConnectionName.Production)
    {
    }

}
public class PurchaseUnitOfWorkContext : UnitOfWorkContextBase, IPurchaseUnitOfWorkContext
{
    public PurchaseUnitOfWorkContext()
        : base(DatabaseConnectionName.Purchase)
    {
    }
}
    public sealed class UnitOfWork : IDisposable
{
    public IDbConnection Connection { get; } = null;
    public IDbTransaction Transaction { get; private set; } = null;
    public bool IsDisposed { get; private set; } = false;

    public UnitOfWork(IDbConnection connection)
    {
        Connection = connection;
    }
    public void Begin()
    {
        Transaction = Connection.BeginTransaction();
    }
    public void Commit()
    {
        Transaction.Commit();
        Dispose();
    }
    public void RollBack()
    {
        Transaction.Rollback();
        Dispose();
    }
    public void Dispose()
    {
        Transaction?.Dispose();
        Transaction = null;
        IsDisposed = true;
    }
}

Autofac DI Registration:

            builder.RegisterType<GeneralService>()
               .As<IGeneralService>();

        builder.RegisterType<ProductionUnitOfWorkContext>()
           .As<IProductionUnitOfWorkContext>().InstancePerLifetimeScope();
        builder.RegisterType<PurchaseUnitOfWorkContext>()
           .As<IPurchaseUnitOfWorkContext>().InstancePerLifetimeScope();

        builder.RegisterType<ProductCategoryRepository>()
               .As<IProductCategoryRepository>().InstancePerLifetimeScope();
        builder.RegisterType<ProductRepository>()
               .As<IProductRepository>().InstancePerLifetimeScope();
        builder.RegisterType<VendorRepository>()
               .As<IVendorRepository>().InstancePerLifetimeScope();

Service Methods:

public class GeneralService : IGeneralService
{
    private readonly IPurchaseUnitOfWorkContext _purchaseUoWContext;
    private readonly IProductionUnitOfWorkContext _productionUoWContext;
    private IVendorRepository _vendorRepo;
    private IProductRepository _ProductRepo;
    private IProductCategoryRepository _ProductCategoryRepo;

    public GeneralService(IPurchaseUnitOfWorkContext purchaseUoWContext, IProductionUnitOfWorkContext productionUowContext, 
        IVendorRepository vendorRepository, IProductRepository productRepository, IProductCategoryRepository productCategoryRepo, 
        IProductService productService)
    {
        _purchaseUoWContext = purchaseUoWContext;
        _productionUoWContext = productionUowContext;
        _vendorRepo = vendorRepository;
        _ProductRepo = productRepository;
    }

    //Simple insert without Transaction
    public void AddProductCategory(ProductCategory entity)
    {
        var unitOfWork = _productionUoWContext.Create();
        try
        {
            var id = _ProductCategoryRepo.Add(entity);
        }
        catch (Exception)
        {
            throw;
        }
    }
    //Insert Products along with Category- Tranaction in Single Database (Production)
    public void AddProductCategoryAndProducts(ProductCategory entity)
    {
        var unitOfWork = _productionUoWContext.Create();
        unitOfWork.Begin();
        try
        {
            var categoryId = _ProductCategoryRepo.Add(entity);
            foreach (var product in entity.Products)
            {
                product.ProductCategoryId = categoryId;
                _ProductRepo.Add(product);
            }
            unitOfWork.Commit();
        }
        catch (Exception)
        {
            unitOfWork.RollBack();
            throw;
        }
    }
    //Insert 'Products along with Vendor - Transaction between databases
    public void AddVendor(Vendor entity)
    {
        try
        {
            using (TransactionScope scope = new TransactionScope())
            {
                var purchaseUow = _purchaseUoWContext.Create();
                var productionUoW = _productionUoWContext.Create();

                var vendorId = _vendorRepo.Add(entity);
                foreach (var product in entity.Products)
                {
                    product.ProductCategoryId = 1;
                    var productId = _ProductRepo.Add(product);
                }
                scope.Complete();
            }
        }
        catch (Exception ex)
        {
            throw;
        }
    }
}

Repositories

Skipping repositories as it already in the original answers. Currently dapper implemented as independent repository classes

Demerits Found:

  1. In Transaction Scope scenario, no option to restrict developer to start transaction in the UoW.
  2. There is an UoW Creation issue, if the service calls to another service with in the same Database UoW context as the UOW is already created. Ex. Production Category service calls AddProduct method from Product Service (instead of product repository) it will fail as the ProductionUowContext already created UOW in the category service.

Please share your views or improvement points