Generic Repositories with different IDbConnections

Question

I have two different connection strings for two different databases.

My first database has 2 relational tables: -First table has some default fields,among which average on UI, and some additional fields based on second database that are even displayed in UI and that change based on second database. -Second table has all numbers used to calculate the final average in first table.

My second database has one table: -contain additional fields that are in first table of first database and the final average saved in first table of first database calculated in UI. Additional fields can change if the database source is changed, the average field is always in database for default.

My goal is to save in same transaction data for all two tables of first database and save data for second database, and allow operations in different IDbConnections with same generic repistory base.

My idea is to have one repository for every entity, in this case are 3 entities for all three tables, and only one service class for first database and another service for second database.

public interface IRepository<T> where T:class
{
  // one method for semplicity
  Task<T> GetByIdAsync(int id);
}


     //repository for first table of first database. 
     //FirstDatabaseSource contains properties that are for default in that table and they must be there, this repository accept entities that inherits from that, so in this way i can pass class that hase defaults properties and additional properties of second database table
 public interface IFirstTableFirstDatabaseRepository<T> : IRepository<T> where T : FirstDatabaseFirstEntityBase
{
}

 public interface ISecondTableFirstDatabaseRepository<T> : IRepository<T> where T : FirstDatabaseSecondEntityBase
{
}




public class FirstTableFirstDatabaseRepositorySQL:IFirstTableFirstDatabaseRepository<FirstTableEntity>
    {
        DbDataAccess dataAccess;
        public FirstTableFirstDatabaseRepositorySQL()
        {
           dataAccess = new DbDataAccess(new SqlConnection());
        }
             
            public async Task GetByIdAsync(int id)
           {
                 // I injected the IDbConnection because query parameters syntax(for example in ms access is ?, in sql is @ ecc...) change based on that I haven't found another way to avoid this
                 string sql="..";
                 dataAccess.GetByIdAsync(query,new{Id=id},"MyConnectionName");
            }

        }

    //but in future would be other repository implementations for  entity of first table of first database but with oracle ecc..

  public class SecondTableFirstDatabaseRepositorySQL:ISecondTableFirstDatabaseRepository<SecondTableEntity>
    {
        DbDataAccess dataAccess;
        public SecondTableFirstDatabaseRepositorySQL()
        {
           dataAccess = new DbDataAccess(new SqlConnection());
        }
         
        public async Task GetByIdAsync(int id){
             // I injected the IDbConnection because query parameters syntax(for example in ms access is ?, in sql is @ ecc...) change based on that I haven't found another way to avoid this
             string sql="..";
             dataAccess.GetByIdAsync(query,new{Id=id},"MyConnectionName");
        }

    }
    
    //but in future would be  other repository implementations for  entity of second table of first database but with oracle ecc..

   //class that use dapper to interact directly with database
 public class DbDataAccess
    {
        private IDbConnection _connection;
        private IDbTransaction _transaction;
        private bool IsClosed=false;

        public DbDataAccess(IDbConnection connection)
        {
            _connection = connection;
        }

        public string GetConnectionString(string name)
        {
            var path = ConfigurationManager.ConnectionStrings[name].ConnectionString;

            return path;
        }

        public async Task<List<T>> LoadDataAsync<T, U>(string query, U parameters, string connectionStringName)
        {
            using (_connection)
            {
                _connection.ConnectionString = GetConnectionString(connectionStringName);
                List<T> rows = (await _connection.QueryAsync<T>(query, parameters)).ToList();

                return rows;
            }
        }

        public async Task SaveDataAsync<T>(string query, T parameters, string connectionStringName)
        {
            using (_connection)
            {
                _connection.ConnectionString = GetConnectionString(connectionStringName);
                await _connection.ExecuteAsync(query, parameters);
            }
        }

        public void StartTransaction(string connectionStringName)
        {
            _connection.ConnectionString = GetConnectionString(connectionStringName);
            _connection.Open();

            _transaction = _connection.BeginTransaction();

            IsClosed = false;
        }

        public async Task<List<T>> LoadDataInTransactionAsync<T, U>(string query, U parameters)
        {
            List<T> rows = (await _connection.QueryAsync<T>(query, parameters, transaction: _transaction)).ToList();

            return rows;
        }

        public async Task SaveDataInTransactionAsync<T>(string query, T parameters)
        {
            await _connection.ExecuteAsync(query, parameters, transaction: _transaction);
        }

        public void CommitTransaction()
        {
            _transaction?.Commit();
            _connection?.Close();

            IsClosed = true;
        }

        public void RollBackTransaction()
        {
            _transaction?.Rollback();
            _connection?.Close();

            IsClosed = true;
        }

        public void Dispose()
        {
            if (!IsClosed)
            {
                try
                {
                    CommitTransaction();
                }
                catch
                {
                    throw;
                }
            }

            _transaction = null;
            _connection = null;
        }

Now if i want to do one service for first database that save at the same time in all two tables of first database i would inject IFirstTableFirstDatabaseRepository and ISecondTableFirstDatabaseRepository but they would be generics and dependency injection with generics doesn't seems to me good, maybe I am wrong.

So my abstractions would be different IDbConnections for same entity and first table first repository entity has always same base properties but additional properties that depends on second database that can be different.

I'm not sure if this implementation is good for repositories, should I use dependency injection or factories?

Should I use DTOs from service to repository and viceversa?

Any idea how to improve?

I have to use Dapper.

Sorry for the long text, but I want to try to explain my case as best as possible. I hope I was clear.

Thanks in advance.

Answer 1

OK so here are my repository rules

1. Don't make generic repositories.

   The problem with them is, as you demonstrate,

   • It makes it hard to manage unit of work/transactions
   • You end up duplicating methods from your underlying data access library, such as open, close, transaction etc

2. One repository per database.

   Tables in databases can have relationships, to populate an object you are going to be hitting multiple tables. Although possible its not a great idea to link tables across databases. So a one to one relationship with repositories makes sense.

3. No DTOs

   A repository should be giving calling code a fully populated object. That's its responsibility. Storing and retrieving objects not data.

If you follow these rules for your very generic example. Assuming you are forced to use two databases and need to span the transaction it can be done, but you need an extra service class and a distributed transaction

class MyService
{
    SaveToBothDatabases(Object1 one, Object2 two)
    {
       using var scope = new TransactionScope()
       try
       {
           this.repo1.Save(one); //this uses dapper to save the object to both tables in db1
           this.repo2.Save(two); //this uses dapper to save the object to both tables in db2
           scope.Commit() //complete the transactions on both dbs
       }
       catch
       {
           scope.RollBack() //rollback the transactions on both dbs
       }
    }
}

However, this assumes that your underlying database supports distributed transactions. You are far better off putting all the data in a single database and having a single repo for that database