Link Search Menu Expand Document
RepoDB

DbRepository

This page contains the reference implementation when implementing a repository that inherits the DbRepository class. The consolidated output of this page can be found here.

The DbRepository class is used for implementing a shared repository. Imagine you are working with [dbo].[Customer], [dbo].[Product] and [dbo].[Order] tables.

Cache

Create a custom cache class.

public static class MyCustomCache : MemoryCache
{
    ...
}

Create a factory class.

public static class CacheFactory
{
    private static object _syncLock = new object();
    private static ICache _cache = null;
    
    public static ICache CreateCacher()
    {
        if (_cache == null)
        {
            lock (_syncLock)
            {
                if (_cache == null)
                {
                    _cache = new MyCustomCache();
                }
            }
        }
        return _cache;
    }
}

Or, if you wish to dependency inject.

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();

    // Registration
    services.AddSingleton<ICache, MyCustomCache>();
}

Trace

Create a custom trace class.

public static class MyCustomTrace : ITrace
{
    /* Implement all the methods here */
}

Create a factory class.

public static class TraceFactory
{
    private static object _syncLock = new object();
    private static ITrace _trace = null;
    
    public static ITrace CreateTracer()
    {
        if (_trace == null)
        {
            lock (_syncLock)
            {
                if (_trace == null)
                {
                    _trace = new MyCustomTrace();
                }
            }
        }
        return _trace;
    }
}

Or, if you wish to dependency inject.

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();

    // Registration
    services.AddSingleton<ITrace, MyCustomTrace>();
}

Settings

The settings object must be injected within the constructor of the repository. Please refer to Microsoft documentation.

public class AppSetting
{
    public string ConnectionString { get; set; }
    public int CommandTimeout { get; set; }
    public int CacheItemExpiration { get; set; }
}

Repository

Simply inherit the DbRepository class and pass a generic type for the connection object.

Below is the sample repository implementation.

For the factory classes.

public class NorthwindRepository : DbRepository<SqlConnection>
{
    public NorthwindRepository(IOptions<AppSetting> settings)
        : base(settings.Value.ConnectionString,
            commandTimeout: settings.CommandTimeout,
            connectionPersistency: ConnectionPersistency.PerCall,
            cache: CacheFactory.CreateCacher(),
            cacheItemExpiration: settings.CacheItemExpiration,
            trace: TraceFactory.CreateTracer(),
            statementBuilder: null)
    {}

    ...
}

For the dependency-injected classes.

public class NorthwindRepository : DbRepository<SqlConnection>
{
    public NorthwindRepository(IOptions<AppSetting> settings,
        ICache cache,
        ITrace trace)
        : base(settings.Value.ConnectionString,
            commandTimeout: settings.CommandTimeout,
            connectionPersistency: ConnectionPersistency.PerCall,
            cache: cache,
            cacheItemExpiration: settings.CacheItemExpiration,
            trace: trace,
            statementBuilder: null)
    {}

    ...
}

Methods

Below is the recommended way when exposing a method that returns all the records.

public IEnumerable<Customer> GetCustomers(string cacheKey = null,
    IDbTransaction transaction = null)
{
    return QueryAll<Customer>(cacheKey: cacheKey,
        transaction: transaction);
}

public IEnumerable<Product> GetProducts(string cacheKey = null,
    IDbTransaction transaction = null)
{
    return QueryAll<Product>(cacheKey: cacheKey,
        transaction: transaction);
}

Below is the recommended way when exposing a method that return the records related to a parent entity.

public IEnumerable<Order> GetCustomerOrders(int customerId,
    IDbTransaction transaction = null)
{
    return QueryAll<Order>(o => o.CustomerId == customerId,
        transaction: transaction);
}

Below is the recommended way when exposing a method that returns a single record.

public Customer GetCustomer(int id,
    string cacheKey = null,
    IDbTransaction transaction = null)
{
    return Query<Customer>(id,
        cacheKey: cacheKey,
        transaction: transaction).FirstOrDefault();
}

public Product GetProduct(int id,
    string cacheKey = null,
    IDbTransaction transaction = null)
{
    return Query<Product>(p => p.Name == name,
        cacheKey: cacheKey,
        transaction: transaction).FirstOrDefault();
}

public Order GetOrder(int id,
    IDbTransaction transaction = null)
{
    return Query<Order>(id,
        transaction: transaction).FirstOrDefault();
}

Below is the recommended way when exposing a method that deletes a record.

public int DeleteOrder(int id,
    IDbTransaction transaction = null)
{
    return Delete<Order>(id,
        transaction: transaction);
}

public int DeleteProduct(int id,
    IDbTransaction transaction = null)
{
    return Delete<Product>(id,
        transaction: transaction);
}

Below is the recommended way to deletes a record with children.

public int DeleteCustomer(int id,
    IDbTransaction transaction = null)
{
    // Delete the child orders first
    var deletedOrders = Delete<Order>(o => o.CustomerId == id,
        transaction: transaction);

    // No need to check (if deletedOrders > 0) as some customers does not have orders
    return Delete<Customer>(id,
        transaction: transaction);
}

We suggest that you check the presence of the transaction object.

if (transaction == null)
{
    var connection = CreateConnection();
    try
    {
        using (var implicitTransaction = connection.BeginTransaction())
        {
            return DeleteCustomer(id, implicitTransaction);
        }
    }
    finally
    {
        if (ConnectionPersistency == ConnectionPersistency.PerCall)
        {
            connection.Dispose();
        }
    }
}

Below is the recommended way when exposing a method that merges a record.

public int MergeCustomer(Customer customer,
    IDbTransaction transaction = null)
{
    return Merge<Customer, int>(customer,
        transaction: transaction);
}

public int MergeOrder(Order order,
    IDbTransaction transaction = null)
{
    return Merge<Order, int>(order,
        transaction: transaction);
}

public int MergeProduct(Product product,
    IDbTransaction transaction = null)
{
    return Merge<Product, int>(product,
        transaction: transaction);
}

Below is the recommended way when exposing a method that saves a record.

public int SaveCustomer(Customer customer,
    IDbTransaction transaction = null)
{
    return Insert<Customer, int>(customer,
        transaction: transaction);
}

public int SaveOrder(Order order,
    IDbTransaction transaction = null)
{
    return Insert<Order, int>(order,
        transaction: transaction);
}

public int SaveProduct(Product product,
    IDbTransaction transaction = null)
{
    return Insert<Product, int>(product,
        transaction: transaction);
}

Below is the recommended way when exposing a method that updates a record.

public int UpdateCustomer(Customer customer,
    IDbTransaction transaction = null)
{
    return Update<Customer>(customer,
        transaction: transaction);
}

public int UpdateOrder(Order order,
    IDbTransaction transaction = null)
{
    return Update<Order>(order,
        transaction: transaction);
}

public int UpdateProduct(Product product,
    IDbTransaction transaction = null)
{
    return Update<Product>(product,
        transaction: transaction);
}

Async Methods

Ensure that all the synchronous methods you had created has the corresponding asynchronous methods suffixed by Async keyword. Within these methods, ensure that you are calling the corresponding asynchronous operations of the library.

// Get (Many)

public async Task<IEnumerable<Customer>> GetCustomersyAsync(string cacheKey = null,
    IDbTransaction transaction = null)
{
    return await QueryAllAsync<Customer>(cacheKey: cacheKey,
        transaction: transaction);
}

public async Task<IEnumerable<Product>> GetProductsyAsync(string cacheKey = null,
    IDbTransaction transaction = null)
{
    return await QueryAllAsync<Product>(cacheKey: cacheKey,
        transaction: transaction);
}

public async Task<IEnumerable<Order>> GetCustomerOrdersyAsync(int customerId,
    IDbTransaction transaction = null)
{
    return await QueryAllAsync<Order>(o => o.CustomerId == customerId,
        transaction: transaction);
}

public async Task<Customer> GetCustomeryAsync(int id,
    string cacheKey = null,
    IDbTransaction transaction = null)
{
    return (await QueryAsync<Customer>(id,
        cacheKey: cacheKey,
        transaction: transaction)).FirstOrDefault();
}

// Get

public async Task<Product> GetProductyAsync(int id,
    string cacheKey = null,
    IDbTransaction transaction = null)
{
    return await QueryAsync<Product>(p => p.Name == name,
        cacheKey: cacheKey,
        transaction: transaction).FirstOrDefault();
}

public async Task<Order> GetOrderyAsync(int id,
    IDbTransaction transaction = null)
{
    return await QueryAsync<Order>(id,
        transaction: transaction).FirstOrDefault();
}

// Delete

public async Task<int> DeleteOrderyAsync(int id,
    IDbTransaction transaction = null)
{
    return await DeleteAsync<Order>(id,
        transaction: transaction);
}

public async Task<int> DeleteProductyAsync(int id,
    IDbTransaction transaction = null)
{
    return await DeleteAsync<Product>(id,
        transaction: transaction);
}

public async Task<int> DeleteCustomeryAsync(int id,
    IDbTransaction transaction = null)
{
    // Delete the child orders first
    var deletedOrders = await DeleteAsync<Order>(o => o.CustomerId == id,
        transaction: transaction);

    // No need to check (if deletedOrders > 0) as some customers does not have orders
    return await DeleteAsync<Customer>(id,
        transaction: transaction);
}

// Merge

public async Task<int> MergeCustomeryAsync(Customer customer,
    IDbTransaction transaction = null)
{
    return await MergeAsync<Customer,int>(customer,
        transaction: transaction);
}

public async Task<int> MergeOrderyAsync(Order order,
    IDbTransaction transaction = null)
{
    return await MergeAsync<Order, int>(order,
        transaction: transaction);
}

public async Task<int> MergeProductyAsync(Product product,
    IDbTransaction transaction = null)
{
    return await MergeAsync<Product, int>(product,
        transaction: transaction);
}

// Save

public async Task<int> SaveCustomerAsync(Customer customer,
    IDbTransaction transaction = null)
{
    return await InsertAsync<Customer, int>(customer,
        transaction: transaction);
}

public async Task<int> SaveOrderAsync(Order order,
    IDbTransaction transaction = null)
{
    return await InsertAsync<Order, int>(order,
        transaction: transaction);
}

public async Task<int> SaveProductAsync(Product product,
    IDbTransaction transaction = null)
{
    return await InsertAsync<Product, int>(product,
        transaction: transaction);
}

// Update

public async Task<int> UpdateCustomerAsync(Customer customer,
    IDbTransaction transaction = null)
{
    return await UpdateAsync<Customer>(customer,
        transaction: transaction);
}

public async Task<int> UpdateOrderAsync(Order order,
    IDbTransaction transaction = null)
{
    return await UpdateAsync<Order>(order,
        transaction: transaction);
}

public async Task<int> UpdateProductAsync(Product product,
    IDbTransaction transaction = null)
{
    return await UpdateAsync<Product>(product,
        transaction: transaction);
}

Dependency Injection

Create an interface that contains all the necessary methods. The name must be identitical on the purpose of the repository.

public interface INorthwindRepository
{
    /*** Non-Async ***/

    // Get (Many)

    IEnumerable<Customer> GetCustomers(string cacheKey = null,
        IDbTransaction transaction = null);

    IEnumerable<Product> GetProducts(string cacheKey = null,
        IDbTransaction transaction = null);

    IEnumerable<Order> GetCustomerOrders(int customerId,
        IDbTransaction transaction = null);

    // Get

    Customer GetCustomer(int id,
        string cacheKey = null,
        IDbTransaction transaction = null);

    Product GetProduct(int id,
        string cacheKey = null,
        IDbTransaction transaction = null);

    Order GetOrder(int id,
        IDbTransaction transaction = null);

    // Delete

    int DeleteOrder(int id,
        IDbTransaction transaction = null);

    int DeleteProduct(int id,
        IDbTransaction transaction = null);

    int DeleteCustomer(int id,
        IDbTransaction transaction = null);

    // Merge

    int MergeCustomer(Customer customer,
        IDbTransaction transaction = null);

    int MergeOrder(Order order,
        IDbTransaction transaction = null);

    int MergeProduct(Product product,
        IDbTransaction transaction = null);

    // Save

    int SaveCustomer(Customer customer,
        IDbTransaction transaction = null);

    int SaveOrder(Order order,
        IDbTransaction transaction = null);

    int SaveProduct(Product product,
        IDbTransaction transaction = null);

    // Update

    int UpdateCustomer(Customer customer,
        IDbTransaction transaction = null);

    int UpdateOrder(Order order,
        IDbTransaction transaction = null);

    int UpdateProduct(Product product,
        IDbTransaction transaction = null);

    /*** Async ***/

    // Get (Many)

    Task<IEnumerable<Customer>> GetCustomersyAsync(string cacheKey = null,
        IDbTransaction transaction = null);

    Task<IEnumerable<Product>> GetProductsyAsync(string cacheKey = null,
        IDbTransaction transaction = null);

    Task<IEnumerable<Order>> GetCustomerOrdersyAsync(int customerId,
        IDbTransaction transaction = null);

    Task<Customer> GetCustomeryAsync(int id,
        string cacheKey = null,
        IDbTransaction transaction = null);

    // Get

    Task<Product> GetProductyAsync(int id,
        string cacheKey = null,
        IDbTransaction transaction = null);

    Task<Order> GetOrderyAsync(int id,
        IDbTransaction transaction = null);

    // Delete

    Task<int> DeleteOrderyAsync(int id,
        IDbTransaction transaction = null);

    Task<int> DeleteProductyAsync(int id,
        IDbTransaction transaction = null);

    Task<int> DeleteCustomeryAsync(int id,
        IDbTransaction transaction = null);

    // Merge

    Task<int> MergeCustomeryAsync(Customer customer,
        IDbTransaction transaction = null);

    Task<int> MergeOrderyAsync(Order order,
        IDbTransaction transaction = null);

    Task<int> MergeProductyAsync(Product product,
        IDbTransaction transaction = null);

    // Save

    Task<int> SaveCustomerAsync(Customer customer,
        IDbTransaction transaction = null);

    Task<int> SaveOrderAsync(Order order,
        IDbTransaction transaction = null);

    Task<int> SaveProductAsync(Product product,
        IDbTransaction transaction = null);

    // Update

    Task<int> UpdateCustomerAsync(Customer customer,
        IDbTransaction transaction = null);

    Task<int> UpdateOrderAsync(Order order,
        IDbTransaction transaction = null);

    Task<int> UpdateProductAsync(Product product,
        IDbTransaction transaction = null);
}

Then, implement it on the repository.

public class NorthwindRepository : DbRepository<Customer, SqlConnection>, INorthwindRepository
{
    ...
}

Service Configuration and Registration

Register it as singleton if you…

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();

    // Registration
    services.AddSingleton<INorthwindRepository, NorthwindRepository>();
}

Otherwise, register it as transient.

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();

    // Registration
    services.AddTransient<INorthwindRepository, NorthwindRepository>();
}

Key Take-aways

  • The transaction argument is needed in every method in order for you to enable the Unit of Work (UOW).
  • The cache key argument is needed in the case you need to cache the result.
  • The interface is needed for dependency injection.
  • The singleton registration is needed for caching and connection persistency.
  • The method names would be targeted to the entity.
  • The repository must be short and precise on its purpose.