JMSL.Core.Dal 2.0.1

JMSL.Core.Dal

Lightweight SQL Server database access layer using the Unit of Work and Repository patterns on top of ADO.NET + Dapper.

Package ID: JMSL.Core.Dal Version: 2.0.1 Target Framework: .NET 8.0 License: MIT Maintained by: JMSL IT — Jaykay Marketing Services (Pvt) Limited

Dependencies

Dapper is not a direct dependency — install it separately in your consuming project if you want query helpers.

Core Types

Patterns

Two injection patterns are supported. Choose one consistently per project.

Pattern A — Constructor Injection (recommended for DI)

The IUnitOfWork is resolved from outside (e.g., from a service layer) and injected via the constructor. The repository stores it as _unitOfWork and uses it for all calls.

public class ProductRepository : BaseSQLRepository, IProductRepository
{
    private const string SP_GET_ALL   = "dbo.GetAllProducts";
    private const string SP_GET_BY_ID = "dbo.GetProductById";
    private const string SP_INSERT    = "dbo.InsertProduct";

    public ProductRepository(IUnitOfWork unitOfWork) : base(unitOfWork) { }

    // Uses stored _unitOfWork — no unitOfWork param needed
    public IEnumerable<Product> GetAll() =>
        WithConnection(c => c.Query<Product>(SP_GET_ALL, commandType: CommandType.StoredProcedure));

    public async Task<Product?> GetByIdAsync(int id)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@ProductId", id, DbType.Int32);

        return await WithConnectionAsync(c =>
            c.QuerySingleOrDefaultAsync<Product>(SP_GET_BY_ID, param: parameters,
                commandType: CommandType.StoredProcedure));
    }

    public void Insert(Product product)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@Name",  product.Name,  DbType.String);
        parameters.Add("@Price", product.Price, DbType.Decimal);

        Execute(c => c.Execute(SP_INSERT, param: parameters, commandType: CommandType.StoredProcedure));
    }
}

Caller:

// Read — no transaction
using var session = new DalSession(connectionString);
var repo = new ProductRepository(session.UnitOfWork);
var products = repo.GetAll();

// Write — with transaction
using var session = new DalSession(connectionString, autoBeginTransaction: true);
try
{
    var repo = new ProductRepository(session.UnitOfWork);
    repo.Insert(product);
    session.UnitOfWork.Commit();
}
catch
{
    session.UnitOfWork.Rollback();
    throw;
}

Pattern B — Per-Call Injection

IUnitOfWork is passed explicitly to each repository method. Useful when migrating from older code or when a single repository instance is reused across multiple sessions.

public class ProductRepository : BaseSQLRepository
{
    private const string SP_GET_ALL = "dbo.GetAllProducts";
    private const string SP_INSERT  = "dbo.InsertProduct";

    public ProductRepository() : base() { }

    public IEnumerable<Product> GetAll(IUnitOfWork unitOfWork) =>
        WithConnection(c => c.Query<Product>(SP_GET_ALL, commandType: CommandType.StoredProcedure), unitOfWork);

    public void Insert(Product product, IUnitOfWork unitOfWork)
    {
        var parameters = new DynamicParameters();
        parameters.Add("@Name",  product.Name,  DbType.String);
        parameters.Add("@Price", product.Price, DbType.Decimal);

        Execute(c => c.Execute(SP_INSERT, param: parameters, commandType: CommandType.StoredProcedure), unitOfWork);
    }
}

Caller:

// Read — no transaction
using var session = new DalSession(connectionString);
var products = repo.GetAll(session.UnitOfWork);

// Write — with transaction
using var session = new DalSession(connectionString, autoBeginTransaction: true);
try
{
    repo.Insert(product, session.UnitOfWork);
    session.UnitOfWork.Commit();
}
catch
{
    session.UnitOfWork.Rollback();
    throw;
}

Transaction Behaviour

IUnitOfWork does not expose the underlying IDbTransaction. Transaction enlistment is managed internally. Do not pass transaction: to Dapper calls — omit that parameter entirely.

Helper Methods on BaseSQLRepository

All three connection methods have overloads for both constructor-injected and per-call IUnitOfWork.

© 2025 Jaykay Marketing Services (Pvt) Limited. All rights reserved.