Transactional and Non-Transactional API Request middleware with unit of work

AppExtensions.cs

namespace Customer.WebApi.Extensions
{
    public static class AppExtensions
    {
        public static void UseUnitOfWorkMiddleware(this IApplicationBuilder app)
        {
            app.UseMiddleware<UnitOfWorkMiddleware>();
        }
    }
}

ServiceRegistration.cs

namespace Common.Infrastructure.SqlContext
{
    public static class ServiceRegistration
    {
        public static void AddSharedSqlInfrastructure(this IServiceCollection services, IConfiguration configuration, string configSection = "ConnectionStrings")
        {
            services.Configure<SqlAppSetting>(configuration.GetSection(configSection));
            services.AddTransient<IDbConfiguration, SqlConfiguration>();
            services.AddTransient<IUnitOfWorkContext, UnitOfWorkContext>();
            services.AddTransient<ISqlDatabase, SqlDatabase>();
        }
    }
}

SqlDatabase.cs

namespace Common.Infrastructure.SqlContext.Sql
{
    // Note: In this illustration the InsertAsync and UpdateAsync came from Dapper Contrib, which is not neccessary. 
    // In your case, you can use ADO or other tool that accept _unitOfWork.Transaction (IDbTransaction)
    public class SqlDatabase : ISqlDatabase
    {
        private readonly IUnitOfWork _unitOfWork;

        public SqlDatabase(IUnitOfWorkContext unitOfWorkContext)
        {
            _unitOfWork = unitOfWorkContext.UnitOfWork;
        }

        public async Task<int> InsertAsync<T>(T entity) where T : class
        {
            return await _unitOfWork.Connection.InsertAsync(entity, transaction: _unitOfWork.Transaction);
        }

        public async Task<bool> UpdateAsync<T>(T entity) where T : class
        {
            return await _unitOfWork.Connection.UpdateAsync(entity, transaction: _unitOfWork.Transaction);
        }
   }
}

namespace Common.Infrastructure.SqlContext.Sql
{
    public interface ISqlDatabase
    {
        Task<int> InsertAsync<T>(T entity) where T : class;
        
        Task<bool> UpdateAsync<T>(T entity) where T : class;
    }
}

UnitOfWork.cs

namespace Common.Infrastructure.SqlContext.Uow
{
    public sealed class UnitOfWork : IUnitOfWork
    {
        private readonly IDbConnection _connection = null;
        private IDbTransaction _transaction = null;

        internal UnitOfWork(IDbConnection connection)
        {
            _connection = connection;
        }

        IDbConnection IUnitOfWork.Connection => _connection;

        IDbTransaction IUnitOfWork.Transaction => _transaction;

        public void BeginTransaction()
        {
            if (_connection.State != ConnectionState.Open)
                throw new UnitOfWorkException("DbConnection connection is closed.");

            _transaction = _connection.BeginTransaction();
        }

        public void Commit()
        {
            if (_transaction == null)
                throw new UnitOfWorkException("Transaction is not created.");

            _transaction.Commit();
            Dispose();
        }

        public void Rollback()
        {
            if (_transaction == null)
                throw new UnitOfWorkException("Transaction is not created.");

            _transaction?.Rollback();
            Dispose();
        }

        public void Dispose()
        {
            _transaction?.Dispose();
            _transaction = null;
        }
    }

    public interface IUnitOfWork : IDisposable
    {
        IDbConnection Connection { get; }

        IDbTransaction Transaction { get; }

        void BeginTransaction();

        void Commit();

        void Rollback();
    }
}

UnitOfWorkContext.cs

namespace Common.Infrastructure.SqlContext.Uow
{
    public class UnitOfWorkContext : IUnitOfWorkContext
    {
        private IDbConnection _connection;
        private IUnitOfWork _unitOfWork;
        private readonly IDbConfiguration _dbConfiguration;

        public UnitOfWorkContext(IDbConfiguration dbConfiguration)
        {
            _dbConfiguration = dbConfiguration;

            // Todo: Find a better way to get around this
            CreateUnitOfWork();
        }

        public IUnitOfWork UnitOfWork => _unitOfWork;

        private void CreateUnitOfWork()
        {
            _connection = new SqlConnection(_dbConfiguration.ConnectionString);
            _connection.Open();
            _unitOfWork = new UnitOfWork(_connection);
        }

        public void Dispose()
        {
            _unitOfWork?.Dispose();

            if (_connection?.State == ConnectionState.Open)
                _connection?.Close();

            _connection?.Dispose();
            _connection = null;
            _unitOfWork = null;
        }
    }
}

namespace Common.Infrastructure.SqlContext.Uow
{
    public interface IUnitOfWorkContext : IDisposable
    {
        public IUnitOfWork UnitOfWork { get; }
    }
}

namespace Common.Infrastructure.SqlContext
{
    public interface IDbConfiguration
    {
        public string ConnectionString { get; }
    }
}

namespace Common.Infrastructure.SqlContext.Sql
{
    public class SqlConfiguration : IDbConfiguration
    {
        private readonly SqlAppSetting _sqlAppSettingKey;

        public SqlConfiguration(IOptions<SqlAppSetting> sqlAppSettingKey)
        {
            _sqlAppSettingKey = sqlAppSettingKey.Value;
        }

        public string ConnectionString => _sqlAppSettingKey.SqlConnectionString;
    }
}

namespace Common.Infrastructure.SqlContext.Sql
{
    public class SqlAppSetting
    {
        public string SqlConnectionString { get; set; }
    }
}

UnitOfWorkMiddleware.cs

namespace Customer.WebApi.Middlewares
{
    public class UnitOfWorkMiddleware
    {
        private readonly RequestDelegate _next;

        public UnitOfWorkMiddleware(RequestDelegate next)
        {
            _next = next;
        }

        public async Task Invoke(HttpContext context)
        {
            using (var applicationDbContext = (IUnitOfWorkContext)context.RequestServices.GetService(typeof(IUnitOfWorkContext)))
            {
                // IsReadOnlyRequest is just a custom HttpContext extension with evaluate httpContext.Request.Method == "GET"
                // You can add any condition to categorize your transactional and non-transaction operation
                if (context.IsReadOnlyRequest())
                {
                    await CreateNonTransactionalRequest(applicationDbContext, context);
                }
                else
                {
                    await CreateTransactionalRequest(applicationDbContext, context);
                }
            }
        }

        private async Task CreateTransactionalRequest(IUnitOfWorkContext applicationDbContext, HttpContext context)
        {
            try
            {
                applicationDbContext.UnitOfWork.BeginTransaction();

                await _next(context);

                applicationDbContext.UnitOfWork.Commit();
            }
            catch (Exception ex)
            {
                applicationDbContext.UnitOfWork.Rollback();
                throw ex;
            }
            finally
            {
                applicationDbContext.Dispose();
            }
        }

        private async Task CreateNonTransactionalRequest(IUnitOfWorkContext applicationDbContext, HttpContext context)
        {
            try
            {
                await _next(context);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                applicationDbContext.Dispose();
            }
        }
    }
}