Madhav Sharma

Madhav Sharma

  • 887
  • 899
  • 39.1k

why transaction showing exception with dapper

Jun 14 2021 8:46 PM

System.InvalidOperationException: BeginExecuteNonQuery 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.

public async Task<int> AddAsync(AbnormalCodeMaster entity)
{
    //using (var connection = CreateConnection())
    DynamicParameters param = new DynamicParameters();
    using (var connection = new SqlConnection(DbConnectionString))
    {
        //Begin the transaction
        await connection.OpenAsync();
        var transaction = await connection.BeginTransactionAsync();
        try
        {
            param.Add("@AbnormalCode", entity.AbnormalCode);
            param.Add("@Description", entity.Description);
            param.Add("@IsActive", entity.IsActive);
            string abnormalCodeQuery = MasterQuaries<AbnormalCodeMaster>.InsertUpdateQuaries(SQLTables.AbnormalCodeMaster.ToString(), AuditAction.Insert.ToString());
                    
            //string abnormalCodeQuery = "INSERT INTO AbnormalCodeMaster ([AbnormalCode],[Description],[IsActive]) OUTPUT Inserted.Id VALUES (@Abnormalcode,@Description,@IsActive)";
            //string abnormalCodeQuery = "insert into "
            var abnormalResponse = await connection.ExecuteAsync(abnormalCodeQuery);
            if (abnormalResponse > 0)
            {
                var auditTrail = new AuditTrail()
                {
                    TableId = abnormalResponse,
                    TableName = SQLTables.AbnormalCodeMaster.ToString(),
                    Action = AuditAction.Insert.ToString(),
                    CreatedBy = entity.CreatedBy,
                    CreatedDate = DateTime.Now,
                };
                string auditQuery = MasterQuaries<AuditTrail>.InsertUpdateQuaries(SQLTables.AuditTrail.ToString(), AuditAction.Insert.ToString());
                var auditResponse = await connection.ExecuteAsync(auditQuery, transaction);
                if (auditResponse > 0)
                {
                    await transaction.CommitAsync();
                }
                else
                {
                   await transaction.RollbackAsync();
                   // roll the transaction back
                }
            }
            return abnormalResponse;
        }
        catch (Exception ex)
        {
            //transaction.Rollback();
            throw;
        }
    }
}

Answers (2)