How to use transactions

[kienxuandaoit89]

Is your feature request related to a problem? Please describe.

How to use transactions with this framework? (example CRUD is used IRepositoryWithEvents)

[Sarmadjavediqbal]

Yes I want to know about this as well. @MikaelHild @iammukeshm could you please suggest how to implement transactionspipeline in dotnet-web-api????

[Sarmadjavediqbal]

Also suggest which nuget package to use for transactions.

[Sarmadjavediqbal]

@kienxuandaoit89 you raised this issue last year. Have you found the solution yet???

[Sarmadjavediqbal]

@iammukeshm

[MikaelHild]

Ardalis specification does not include transactions but can be implemented by using the dbContext, i.e. you could implement your own Repository for the purpose.

See this for an example ardalis/CleanArchitecture#327 (comment)
and read more here: ardalis/CleanArchitecture#327

[Sarmadjavediqbal]

@MikaelHild
@iammukeshm
I tried to implement unitOfWork pattern 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<UpdateChallanRequest>
{
    public UpdateChallanRequestValidator()
    {
    }
}

internal class UpdateChallanRequestHandler : IRequestHandler<UpdateChallanRequest, ChallanDto>
{
    private readonly IRepositoryWithEvents<TrnsChallanGeneration> _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>();
    }
}

[MikaelHild]

Mukesh have written an article on using Dapper with transactions for this purpose.
In the code above I think you're missing the transaction with the repository.

https://codewithmukesh.com/blog/using-entity-framework-core-and-dapper/

[Sarmadjavediqbal]

Thank you @MikaelHild brother but this still does not solve my problem. I am using Mediatr and mukesh's example shows the solution without mediatr.

[MikaelHild]

https://github.com/iammukeshm/EFCoreAndDapper

Checkout the repo above. It's basically the same but you either have to expose the ApplicationDbContext using an interface in the Application project, or handle the Request in the infrastructure project and use the DbContext to handle the transaction.

It could possibly also be solved by using Pipelines in mediator.
https://medium.com/swlh/transaction-management-with-mediator-pipelines-in-asp-net-core-39317a19bb8d

[Sarmadjavediqbal]

@MikaelHild Could you please correct my code and tell me what could be wrong in it????

[Sarmadjavediqbal]

@MikaelHild I have figured it out. I was opening 2 connections with database where as System.Transaction allows only 1 or if you want to check and validate some value from database you must close the connection first before opening another connection with System.Transaction.

[Sarmadjavediqbal]

This issue is resolved so can be closed now.