1

I am getting following exception,

"messages": [], "source": "Microsoft.Data.SqlClient.SqlCommand", "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

I tried to implement unitOfWork pattern

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<someClass1> _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();
}

Expected result:

code 200 success; [] challan updated successfully.

SJI
  • 21
  • 4

1 Answers1

0

Following Line was throwing exception:

var challan = _repos.GetByIdAsync(request.Id, cancellationToken);

But I have figured it out. I was trying to open multiple connections with database where as System.Transaction allows only one connection to be opened. but if you really want to open another connection you must close it first in order to complete a Transaction.

SJI
  • 21
  • 4