DbContext.cs

using System;
using System.Data;

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using MySql.Data.MySqlClient;

namespace NorthwindApi.Model.Context
{
    public interface IDbContext : IDisposable
    {
        IDbConnection Conn { get; }
        IDbTransaction Transaction { get; }

        void BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted);
        void Commit();
        void Rollback();
    }

    public class DbContext : IDbContext
    {
        private IDbConnection _conn;
        private IDbTransaction _transaction;
        private readonly ILogger _logger;        

        private readonly string _connectionString;

        public DbContext(IConfiguration config, ILoggerFactory loggerFactory)
        {
            _logger = loggerFactory.CreateLogger<DbContext>();

            var dbConfig = config.GetSection("ConnectionSettings");

            var server = dbConfig.GetValue<string>("Server");
            var dbName = dbConfig.GetValue<string>("DbName");
            var dbUser = dbConfig.GetValue<string>("DbUser");
            var dbUserPass = dbConfig.GetValue<string>("DbPassword");

            _connectionString = $"SERVER={server};DATABASE={dbName};UID={dbUser};PASSWORD={dbUserPass};Port=3306";            
        }

        private IDbConnection GetOpenConnection(string connectionString)
        {
            IDbConnection conn = null;

            try
            {
                conn = new MySqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();
            }
            catch (Exception ex)
            {
                if (this._logger != null) _logger.LogError(ex, "Exception");
            }

            return conn;
        }

        public IDbConnection Conn
        {
            get { return _conn ?? (_conn = GetOpenConnection(_connectionString)); }
        }

        public IDbTransaction Transaction
        {
            get { return _transaction; }
        }

        public void BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            if (_transaction == null)
            {
                if (_conn == null) _conn = GetOpenConnection(_connectionString);

                _transaction = _conn.BeginTransaction(isolationLevel);
            }
        }

        public void Commit()
        {
            if (_transaction != null)
            {
                _transaction.Commit();
                _transaction = null;
            }
        }

        public void Rollback()
        {
            if (_transaction != null)
            {
                _transaction.Rollback();
                _transaction = null;
            }
        }

        public void Dispose()
        {
            if (_conn != null)
            {
                try
                {
                    if (_conn.State != ConnectionState.Closed) _conn.Close();
                }
                finally
                {
                    _conn.Dispose();
                }
            }

            GC.SuppressFinalize(this);
        }
    }
}