0

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.

3
  • not 100% sure what your question is though? can you add it at the bottom of your explaination?
    – Ewan
    Commented Nov 25, 2022 at 16:25
  • I have edited my post. Commented Nov 25, 2022 at 16:49
  • These data seem closely related. How did you decide to put these tables in two separate databases? And why do you need to use two different connection strings? Can you not put the two databases on the same server instance, or put all the tables in one database?
    – John Wu
    Commented Aug 22, 2023 at 22:03

1 Answer 1

0

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

4
  • The problem is that i make a generic repository because data of first table of first database are some properties the same and i want objects that inherits from base class with that data. Data that are retrieved from presentation layer shouldn't pass to service layer with DTOs and in service layer I would do mapping and at the end send to specific repository? And for this reason I have done one repository for each entity, because I implement specific mapping from DTO to entity for each entity. Commented Nov 25, 2022 at 19:47
  • Yeah, don't do that stuff. follow my rules. I'm not sure how i can improve upon an answer to such a generic problem. You are programming in a way which i would not recommend and coming up against the problems which are the reasons i don't recommend it. If you follow my easy rules you wont get those problems. Sure maybe you have a special case which prevents you from doing that, but your question doesn't say why. You literally have "db1.table1"?!
    – Ewan
    Commented Nov 25, 2022 at 20:23
  • The fact that i am trying to use three layer architecture and i thought to put in business layer my services with DTOs and this layer refer to only DAL that has entities and repositories, but for managing inheritance for first table i have added even one common project with model base that is used from both BLL and DAL, is a good way to go? Commented Nov 25, 2022 at 20:32
  • shrug, doesn't sounds like it follows my rules. I'll add a new one, 4. no inheritance apart from interfaces
    – Ewan
    Commented Nov 26, 2022 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.