Transaction Management with Mediatr and Dapper in Clean Architecture

[Sarmadjavediqbal]

I am trying to implement unitOfWork pattern for Transaction but I am getting following error,

error:

{ "messages": [], "source": "Microsoft.Data.SqlClient.SqlCommand+<>c", "exception": "BeginExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.", "errorId": "6d4329bc-c24f-4852-ba88-35caf4b9ad6d", "supportMessage": "Provide the ErrorId 6d4329bc-c24f-4852-ba88-35caf4b9ad6d to the support team for further analysis.", "statusCode": 500 }

Following is my Code:

using Mapster;
using Microsoft.Data.SqlClient;
using API.Application.Common.Custom.IUnitOfWork;
using API.Domain.MyAPI.Entities;
using System.Data;
using System.Transactions;

namespace API.Application.MyAPI.Challan.Commands.Update;
public class UpdateChallanRequest : IRequest<ChallanDto>
{
public int Id { get; set; }
public decimal Property1 { get; set; }
public DateTime Property2 { get; set; }
}

public class UpdateChallanRequestValidator : CustomValidator
{
public UpdateChallanRequestValidator()
{
}
}

internal class UpdateChallanRequestHandler : IRequestHandler<UpdateChallanRequest, ChallanDto>
{
private readonly IRepositoryWithEvents _repos;
private readonly IDapperRepository _repository;
private readonly IUnitOfWork _unitOfWork;
private IDbTransaction _transaction;
private IStringLocalizer<(someClass1, someClass2)> _localizer;

public UpdateChallanRequestHandler(IDapperRepository repository, IUnitOfWork unitOfWork, IStringLocalizer<(someClass1, someClass2)> localizer, IRepositoryWithEvents<someClass1> repos, IDbTransaction transaction)
{
    _repository = repository;
    _unitOfWork = unitOfWork;
    _localizer = localizer;
    _repos = repos;
    _transaction = transaction;
}

public async Task<ChallanDto> Handle(UpdateChallanRequest request, CancellationToken cancellationToken)
{
    _unitOfWork.BeginTransaction();
    var challan = _repos.GetByIdAsync(request.Id, cancellationToken);

    string query;
    string query1;

    var parameters = new
    {
        ID = request.Id,
        param1 = request.Property1,
        param2 = request.Property2
    };

    query = "BEGIN" +
                        "UPDATE dbo.someTable1" +
                        "SET someColumn1=@param1, Active=0, someColumn2=@param2, flagPaid=true" +
                        "WHERE dbo.someTable1.id = @ID;" +
                    "END";
    var result = await _repository.QueryAsync<someClass1>(query, parameters, cancellationToken: cancellationToken);

    await _unitOfWork.SaveChangesAsync();

    try
    {
        if(challan.Id == null)
        {
            throw new ArgumentNullException();
        }
        else if (request.Id != null && challan.Id != null)
        {
            query1 = "BEGIN" +
                        "INSERT INTO dbo.someTable2" +
                        "(someColumn1, someColumn2, someColumn3)" +
                        "VALUES (" +
                            "SELECT someColumn1 from dbo.someTable1 WHERE dbo.someTable1.id = @ID;," +
                            "SELECT someColumn2 from dbo.someTable1  WHERE dbo.someTable1.id = @ID;," +
                            "SELECT someColumn3 from dbo.someTable1  WHERE dbo.someTable1.id = @ID;," +
                    "END";
            await _repository.QueryAsync<someClass1>(query1, parameters, cancellationToken: cancellationToken);

            await _unitOfWork.SaveChangesAsync();

            _unitOfWork.Commit();
        }
    }
    catch(Exception ex)
    {
        _unitOfWork.Rollback();
        throw new ArgumentNullException("Challan Not Found.");
    }

    return result.Adapt<ChallanDto>();
}
}

UnitOfWork and IUnitOfWork;

public class UnitOfWork : IUnitOfWork
{
    private readonly ApplicationDbContext _context;
    private IDbContextTransaction? _transaction;

    public UnitOfWork(ApplicationDbContext context)
    {
        _context = context;
    }

    public void BeginTransaction()
    {
        if (_transaction != null)
        {
            return;
        }

        _transaction = _context.Database.BeginTransaction();
    }

    public Task<int> SaveChangesAsync()
    {
        return _context.SaveChangesAsync();
    }

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

        _transaction.Commit();
        _transaction.Dispose();
        _transaction = null;
    }

    public async Task SaveAndCommitAsync()
    {
        await SaveChangesAsync();
        Commit();
    }

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

        _transaction.Rollback();
        _transaction.Dispose();
        _transaction = null;
    }

    public void Dispose()
    {
        if (_transaction == null)
        {
            return;
        }

        _transaction.Dispose();
        _transaction = null;
    }
}

public interface IUnitOfWork : IDisposable, ITransientService
{
    void BeginTransaction();
    void Commit();
    void Rollback();
    Task<int> SaveChangesAsync();
    Task SaveAndCommitAsync();
}

I have tried methods from following sources but none of them are working;

https://medium.com/swlh/transaction-management-with-mediator-pipelines-in-asp-net-core-39317a19bb8d https://github.com/iammukeshm/EFCoreAndDapper

[Sarmadjavediqbal]

Please tell me what am I doing wrong?

[Sarmadjavediqbal]

@jbogard

[AdaskoTheBeAsT]

You use EF in unit of work and dapper - that is somewhat strange

I would use TransactionScope provider instead with this in mind https://particular.net/blog/transactionscope-and-async-await-be-one-with-the-flow

[Sarmadjavediqbal]

You use EF in unit of work and dapper - that is somewhat strange

I would use TransactionScope provider instead with this in mind https://particular.net/blog/transactionscope-and-async-await-be-one-with-the-flow

@AdaskoTheBeAst this link does not answer to my problem.

[Sarmadjavediqbal]

Brother Could you be more specific to the exception I am getting?

[Sarmadjavediqbal]

Also could you please correct my code???

[jbogard]

I don't do Clean Architecture, you're better off asking this on Stack Overflow I think.