how to organize MySQL database connection when not using Entity Framework (or similiar)

Question

Based on these two samples

• https://github.com/jasontaylordev/CleanArchitecture
• https://github.com/jasontaylordev/NorthwindTraders

I added an Application and Infrastructure layer to my API project. The important part is that I will only use the MySQL.Data package for the database stuff (no Entity Framework or other helping libraries).

I thought it would be a good practise to define interfaces for repositories in the Application layer

public interface IUsersRepository
{
    Task<IList<User>> GetUsers();
    Task<User> GetUserByUsername(string username);
    // ...
}

and implement them in the Infrastructure layer. So when it comes to the DI container setup via IServiceCollection I can setup those repositories with services.AddTransient(typeof(IUsersRepository), typeof(UsersRepository));. Due to the fact I'm not using an ORM tool I have to setup the connection by myself. That's why I defined an interface in the Application layer

public interface IDatabaseContext
{
    DbConnection DatabaseConnection { get; }
}

and create the connection to the MySQL database in the Infrastructure layer

public class DatabaseContext : IDatabaseContext
{
    public DbConnection DatabaseConnection { get; }

    public DatabaseContext()
    {
        DatabaseConnection = new MySqlConnection("server=127.0.0.1;uid=root;pwd=12345;database=test");
    }
}

To make this injectable I add it to the services collection with services.AddSingleton(typeof(IDatabaseContext), typeof(DatabaseContext));

I think the implementing repositories should only care for their own query because they might get chained for a transaction. Currently they don't take care for the connection

public class UsersRepository : IUsersRepository
{
    private readonly IDatabaseContext databaseContext;

    public UsersRepository(IDatabaseContext databaseContext)
    {
        this.databaseContext = databaseContext;
    }

    public async Task<IList<User>> GetUsers()
    {
        using (DbCommand getUsersCommand = databaseContext.DatabaseConnection.CreateCommand())
        {
            // setup command string, parameters and execute command afterwards 
        }
    }
}

The problem is that now every repository call requires a connection handling before execution in the Application layer. By that I mean I have to wrap the call like so

await databaseContext.DatabaseConnection.OpenAsync();
IList<User> users = await usersRepository.GetUsers();
// ...
await databaseContext.DatabaseConnection.CloseAsync();

so the calling class needs to inject the repository and the IDatabaseContext. I'm also not sure if opening/closing the connection for each query / transaction is a good idea.

Maybe there are some better approaches to enhance the current one. I would like to create a self managing database connection. The application layer shouldn't open/close connections. It should only call the repository methods. The repository methods shouldn't do it neither because they might run in a transaction and only the first query should open it and the last one closes it.

It would be awesome to define new repository methods with the SQL logic only and all the connection stuff is handled once. Any ideas?

Answer 1

First, if you enable connection pooling on the MySql connector then you can skip the CloseAsync call and Dispose the connection each time you have used it, that will allow the pooling mechanism of the connector to reuse connections as needed. To enable it add Pooling=True to your connection string.

Second, to avoid all the extra code you can create a base class for the repositories and implement all the connection handling on it, I would create a function that takes a Func<DbConnection,Task<T>> and some type of static factory to reduce code rewrite:

//static DB factory
public static class DBFactory
{
    public async Task<DBConnection> GetConnection()
    {
        //Create here your connection
        var newCon = //..
        await newCon.OpenAsync();
        return newCon;
    }


    public async Task ExecuteTransaction(Func<DBConnection, MySqlTransaction, Task<bool>> TransactedCode)
    {
        using(var dbConnection = await GetConnection())
        {
            var transact = dbConnection.BeginTransaction();
            try
            {
                if(await TransactedCode(dbConnection, transact))
                    transact.Commit();
                else
                    transact.RollBack();
            }
            catch{  transact.RollBack();  }
        }
    }

}

//Base class for repositories
public abstract class BaseRepository
{
    protected async Task<T> ExecuteResultWithConnection<T>(Func<DBConnection, MySqlTransaction, Task<T>> RepositoryMethod)
    {
        using(var dbCon = await DBFactory.GetConnection())
        {
            return await RepositoryMethod(dbCon, null);
        }
    }

    protected async Task ExecuteWithConnection(Func<DBConnection, MySqlTransaction, Task> RepositoryMethod)
    {
        using(var dbCon = await DBFactory.GetConnection())
        {
            await RepositoryMethod(dbCon, null);
        }
    }


}

//Example of repository
public class TestRepository : BaseRepository
{
    public async Task<IList<TestObject>> GetTestObjects(DBConnection con = null, MysqlTransaction Transact = null)
    {

       if(con != null)
       {
           //execute the code without calling the base function
           //using con as your connection and transact if supplied
           return yourResult;
       }
       else
       {
           return await ExecuteResultWithConnection(async (dbCon, transact) => {

                //Here you have your connection ready to be used as dbCon
                //without transaction
                return yourResult;

           });
       }
    }

    public async Task AddTestObject(TestObject NewObject, DBConnection con = null, MysqlTransaction Transact = null)
    {

       if(con != null)
       {
           //execute the code without calling the base function
           //using con as your connection and transact if supplied
       }
       else
       {
           await ExecuteWithConnection(async (dbCon, transact) => {

               //Here you have your connection ready to be used as dbCon
               //without transaction

           });
       }
    }
}

Now, calling a repository is totally clean:

var repo = new TestRepository();
var objs = await repo.GetTestObjects();
await repo.AddTestObject(new TestObject{ /* whatever */ });

Also, you can create transactions:

await DBFactory.ExecuteTransaction(async (dbCon, transact) => {

    var someObject = repo.GetTestObjects(dbCon, transact);

    await repo.AddTestObject(new TestObject{ /* whatever */ }, dbCon, transact);
    await repo.AddTestObject(new TestObject{ /* whatever */ }, dbCon, transact);
    await repo.AddTestObject(new TestObject{ /* whatever */ }, dbCon, transact);

    return true;

    //If any of the inserts fails with an exception the transaction
    //will be automatically rolled back.
    //You can also return false if the transaction must be rolled back.

});

Remember, this is just an example, in the real world you will have a more complex infrastructure, this only gives you an idea of what you could do.