Solución: Trabajar con la transaccionalidad que trae .Net.
Es obvio que las validaciones para las transacciones de escritura a la base de datos debe hacerse dentro del mismo SP, pero cuando se trabaja con varios SPs en el front-end, es decir, desde la capa del negocio, se debe realizar una validación adicional para controlar cualquier fallo que pudiera suceder desde la aplicación.
En realidad, validar las transacciones es fácil, solo se necesita añadir un SqlTransaction a la conexión que invoca al SP, pero en mi caso, que son varios SPs que realizan muchas escrituras a la base de datos, se debe tener un estricto control de la transaccionalidad.
Un ejemplo.
Tengo los siguientes SPs: sp_ingresar_cabecera, sp_ingresar_detalle, sp_registrar_historial, a los cuales debo mantener la secuencia estricta de grabación, es decir, si falla sp_registrar_historial, no debe grabarse sp_ingresar_cabecera ni sp_ingresar_detalle
Esto se logra realizando una opción general, que invoque a los 3 SPs y manteniendo la transacción dentro del mismo.
Private Sub Grabar() Using connection As SqlConnection = New SqlConnection(args) connection.Open() 'Abre la conexion Dim transaction As SqlTransaction 'Crea una variable de transaccion transaction = connection.BeginTransaction() 'Inicia la transaccion 'Paso a la funcion la transaccion y la conexion if sp_ingresar_cabecera(transaction, connection) = False Then transaction.Rollback() 'Si existe un error en la funcion que graba en el SP, se reversa la transaccion. Exit Sub End If if sp_ingresar_detalle(transaction, connection) = False Then transaction.Rollback() Exit Sub End If if sp_ingresar_detalle(transaction, connection) = False Then transaction.Rollback() Exit Sub End If transaction.Commit() 'Si todo esta correcto, la transaccion se graba en la base de datos. connection.Close() End Using End Sub
Notese que las funciones que se llaman igual a los SPs de grabación se les envía como parámetro la transacción y la conexión, ésto sirve para que no se cree una conexion nueva en cada SP sino que se utilice la misma para todos los SPs, ayudando a que si existe una falla, todos los SPs se reversen.
Para probar que la transaccionalidad está funcionando se puede realizar el siguiente ejemplo.
En Microsoft Visual Studio, en modo de depuración, poner un punto de interrupción en alguno de los SPs que graban información, en el momento en que la información debería estar en la base de datos, como en la linea command.ExecuteNonQuery() o adapter.Fill(DataTable), o simplemente antes de la línea transaction.Commit()
Luego realizar una consulta a la base de datos en el motor de su preferencia, en mi caso es SQL Server y para ello utilizo el Microsoft SQL Server Management Studio y realizo un select sencillo a cualquiera de las tablas que estoy afectando directamente con el SP del Visual Studio.
Si la transaccionalidad está correcta, el select no mostrará ningún valor, y en el peor de los casos, la consulta se quedará congelada indefinidamente.
¿Cómo ésto puede ser bueno?, ¡Mi tabla se encuentra bloqueada! ¡No puedo acceder a ella!
Esta es una de las "bellezas" de la transaccionalidad, la tabla se "bloqueará" cuando se encuentre con una sentencia BeginTransaction y se "liberará" cuando se ejecute Rollback o Commit.
Es por eso que la transaccionalidad es una arma de doble filo, ayuda a que las acciones nunca se realicen si hay un problema, pero la tabla se bloquea hasta que la transacción termine.
Como recomendación, solo se debe usar la transaccionalidad en SPs o consultas que realicen modificación de datos, es decir, si hay Insert, Update o Delete, y no se debe utilizar para otras consultas.
La transaccionalidad ayuda a muchos problemas al momento de modificar los datos, porque, como en mi caso, se necesita que todos los datos sean coherentes para que existan en la base de datos, caso contrario, que ninguna información se grabe.