Why does the transaction continue even though there is an error in a method?

Question

I have three methods that are part of a single transaction, part of a transaction to register a purchase. I am testing if the transaction works. First, I deleted the Kardex table, and yes, it worked correctly, the changes are rolled back. Now, when intentionally passing a zero value, it doesn't enter the foreach loop, but it still commits, which should not happen because the Kardex data is not persisted in the database. I will show you the code.

public class BD_RegistroCompraDetalle_Kardex
{
    public void RegistrarCompraMaestroCompraDetalleKardexDetalle(EN_MaestroCompra maestro, List < EN_DetalleCompra > detalleCompra, List < EN_KardexDetalle > kardexDetalle)
    {
        BDConexion bdConexion = new BDConexion();
        SqlTransaction transaction = null;
        try
        {
            using(SqlConnection connection = bdConexion.AbrirConexion())
            {
                transaction = connection.BeginTransaction();
                // Registrar el Maestro y obtener el IdMaestro    
                int maestroId = RegistrarMaestroCompra(maestro, connection, transaction);
                // Asignar el IdMaestro a cada detalle                 
                foreach(var detail in detalleCompra)
                    {
                        detail.IdMaestroCompra = maestroId;
                    }
                    // Registrar los Detalles              
                RegistrarDetalleCompra(detalleCompra, connection, transaction);
                // Registrar los Detalles del Kardex                 
                BD_RegistrarDetalleKardex(kardexDetalle, connection, transaction);
                // Confirmar la transacción                 
                transaction.Commit();
            }
        }
        catch(Exception ex)
        {
            //// Manejar la excepción y deshacer la transacción si ocurre un error    
            //if (transaction != null && transaction.Connection != null)             
            //{             transaction.Rollback();             //}             
            throw new Exception(ex.Message);
        }
    }
    public int RegistrarMaestroCompra(EN_MaestroCompra maestro, SqlConnection connection, SqlTransaction transaction)
    { // Output parameter to obtain the IdCompra         
        SqlParameter outputParameter = new SqlParameter("@IdMaestroCompra", SqlDbType.Int)
        {
            Direction = ParameterDirection.Output
        };
        using(SqlCommand command = new SqlCommand("sp_RegistrarMaestroCompra", connection, transaction))
        {
            command.CommandTimeout = 20;
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@NumeroCompra", maestro.NumeroCompra);
            command.Parameters.AddWithValue("@NroDocumento", maestro.NroDocumento);
            command.Parameters.AddWithValue("@FechaCompra", maestro.FechaCompra);
            command.Parameters.AddWithValue("@FechaRegistro", maestro.FechaRegistro);
            command.Parameters.AddWithValue("@IdProveedor", maestro.IdProveedor);
            command.Parameters.AddWithValue("@SubTotalCompra", maestro.SubTotalCompra);
            command.Parameters.AddWithValue("@TotalCompra", maestro.TotalCompra);
            command.Parameters.AddWithValue("@ModalidadPago", maestro.ModalidadPago);
            command.Parameters.AddWithValue("@RecibiConforme", maestro.RecibiConforme);
            command.Parameters.AddWithValue("@DatosAdicional", maestro.DatosAdicional);
            command.Parameters.AddWithValue("@TipoDocCompra", maestro.TipoDocCompra);
            command.Parameters.AddWithValue("@IdUsuario", maestro.IdUsuario);
            // Add output parameter          
            command.Parameters.Add(outputParameter);
            // Execute the stored procedure            
            command.ExecuteNonQuery();
            // Obtain the MaestroId from the output parameter             
            return Convert.ToInt32(outputParameter.Value);
            //return outputParameter.Value.ToString();         
        }
    }
    public void RegistrarDetalleCompra(List < EN_DetalleCompra > detalle, SqlConnection connection, SqlTransaction transaction)
    {
        foreach(var filaDetalle in detalle)
        {
            using(SqlCommand command = new SqlCommand("sp_RegistrarDetalleCompra", connection, transaction))
            {
                command.CommandTimeout = 20;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@IdMaestroCompra", filaDetalle.IdMaestroCompra);
                command.Parameters.AddWithValue("@IdProducto", filaDetalle.IdProducto);
                command.Parameters.AddWithValue("@PrecioUnitario", filaDetalle.PrecioUnitario);
                command.Parameters.AddWithValue("@Cantidad", filaDetalle.Cantidad);
                command.Parameters.AddWithValue("@Importe", filaDetalle.Importe);
                command.ExecuteNonQuery();
            }
        }
    }
    private void BD_RegistrarDetalleKardex(List < EN_KardexDetalle > kardexDetalle, SqlConnection connection, SqlTransaction transaction)
    {
        try
        {
            foreach(var filaDetalle in kardexDetalle)
            {
                using(SqlCommand command = new SqlCommand("sp_CrearKardexDetalle", connection, transaction))
                {
                    command.CommandTimeout = 20;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@IdKardex", filaDetalle.IdKardex);
                    command.Parameters.AddWithValue("@Item", filaDetalle.Item);
                    command.Parameters.AddWithValue("@NumeroDocumento", filaDetalle.NumeroDocumento);
                    command.Parameters.AddWithValue("@DetalleOperacion", filaDetalle.DetalleOperacion);
                    command.Parameters.AddWithValue("@CantidadIngreso", filaDetalle.CantidadIngreso);
                    command.Parameters.AddWithValue("@PrecioIngreso", filaDetalle.PrecioIngreso);
                    command.Parameters.AddWithValue("@TotalIngreso", filaDetalle.TotalIngreso);
                    command.Parameters.AddWithValue("@CantidadSalida", filaDetalle.CantidadSalida);
                    command.Parameters.AddWithValue("@PrecioSalida", filaDetalle.PrecioSalida);
                    command.Parameters.AddWithValue("@TotalSalida", filaDetalle.TotalSalida);
                    command.Parameters.AddWithValue("@CantidadSaldo", filaDetalle.CantidadSaldo);
                    command.Parameters.AddWithValue("@Promedio", filaDetalle.Promedio);
                    command.Parameters.AddWithValue("@Costo_Total_Saldo", filaDetalle.Costo_Total_Saldo);
                    //command.Parameters.AddWithValue("@IdProducto", detalle.IdProducto);                    
                    Console.WriteLine("Pasó por aquí");
                    command.ExecuteNonQuery();
                }
            }
        }
        catch(NullReferenceException e)
        {
            throw new NullReferenceException("Parameter index is out of range.", e);
        }
    }

"The code above is part of a single transaction, but it doesn't work because even without saving data in the Kardex table, it still commits."
"For example, when zero (0) is passed to the foreach loop (foreach var filaDetalle in kardexDetalle), specifically when kardexDetalle = 0, it does not enter the body of the foreach loop, but the code still continues its execution."
"For the same reason, when kardexDetalle is set to null, and using the try-catch block, the application crashes, or the execution terminates, and it does not respect the use of try."
"What I have tried to do: I have attempted to use an if block ('if kardexDetalle == null') to prevent further execution, but it still proceeds to the Commit(). Please, how can I ensure that the transaction performs 'transaction.Rollback();'? Thank you very much for your assistance."

Accepted Answer

Hai Roberto Carlos Melgar Dorado

I think thiss issue is related to how exceptions are handled within the try-catch block, and it might not be effectively triggering the rollback in all situations.

Instead of catching a general Exception, catch specific exceptions that might occur in your code. like for example, you could catch SqlException separately from other exceptions to handle database-related errors more accurately.

catch (SqlException sqlEx)
{     
// Handle SQL-related exceptions     
transaction.Rollback();     
throw new Exception("SQL Exception occurred.", sqlEx); 
} 
catch (Exception ex)
{     
// Handle other exceptions     
transaction.Rollback();     
throw new Exception("An unexpected error occurred.", ex); 
}

And fir the e exception typemske sure ensure that the transaction.Rollback() statement is executed within the catch block.

If this helps kindly accept the answer thanks much.

Share via

Why does the transaction continue even though there is an error in a method?

0 additional answers