Using TransactionScope class for DB operations
Posted: Wed 12 Jan 2011 23:10
I am deleting several records from a table and I tested 2 different ways: one using the TransactionScope class and one without it. The code below uses the TransactionScope class and it does not work, but if I remove such a class it works just fine. I ran the web project with the debugger and it shows no problem, every instruction executes correctly. Nevertheless, the result is as if nothing had happened, the records are not deleted; it seems that the transaction does not complete. I set the TimeSpan to zero in order to avoid a timeout while debugging. At the end of the using statement, the value of variable "blnError" equals "false" so the Complete method is issued. The code is part of a class that resides in the App_Code folder.
What could be the possible reasons for not getting the result I expect?
I have a similar situation where several records are inserted using the TransactionScope class and here it works fine. The difference is that, while inserting, the code issues several single Insert operations within a loop, one for each record; but when deleting, only one Delete operation is issued which includes all the records to be deleted in the Where condition.
The whole idea is that, one insert operation includes more than one record; and one delete operation includes more than one record too. When the user needs to perform an update, the application issues a delete for all the records and then inserts the correct ones; this way I emulate an update.
Because insert and delete operation are independent classes, I use the TransactionScope class to control the process. The version of dotConnect for PostgreSQL I use is 4.95.180.
With respect,
Jorge Maldonado
What could be the possible reasons for not getting the result I expect?
I have a similar situation where several records are inserted using the TransactionScope class and here it works fine. The difference is that, while inserting, the code issues several single Insert operations within a loop, one for each record; but when deleting, only one Delete operation is issued which includes all the records to be deleted in the Where condition.
The whole idea is that, one insert operation includes more than one record; and one delete operation includes more than one record too. When the user needs to perform an update, the application issues a delete for all the records and then inserts the correct ones; this way I emulate an update.
Because insert and delete operation are independent classes, I use the TransactionScope class to control the process. The version of dotConnect for PostgreSQL I use is 4.95.180.
With respect,
Jorge Maldonado
Code: Select all
public Boolean Borra(ref String strMensaje, String strOrigen)
{
// Variable para indicar si hubo error en el proceso de borrado.
Boolean blnError = false;
// Se define el query.
StringBuilder strQuery = new StringBuilder();
strQuery.Append("DELETE FROM listas_pre_titulos ");
strQuery.Append("WHERE ");
strQuery.Append("lpt_fuente = " + this.Fuente + " AND ");
strQuery.Append("lpt_fecha = '" + ListaPreTitulo.Fecha.ToString("dd/MM/yyyy") + "' AND ");
strQuery.Append("lpt_tipo = " + this.Tipo);
// Se define la conexion.
String strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PgSqlConnection"].ToString();
PgSqlConnection pgConn = new PgSqlConnection(strConn);
PgSqlCommand pgCmd = new PgSqlCommand(Convert.ToString(strQuery), pgConn);
// Se efectua la operacion a la base de datos.
using (TransactionScope trnScope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.Zero))
{
try
{
pgConn.Open();
if (pgCmd.ExecuteNonQuery() != 0)
{
strMensaje += "SE HA BORRADO UN REGISTRO SATISFACTORIAMENTE. \n";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
}
else
{
blnError = true;
strMensaje += "LA OPERACION DE BORRADO NO SE LLEVO A CABO POR CAUSAS DESCONOCIDAS. \n";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
}
}
catch (Exception excParametro)
{
blnError = true;
strMensaje += "SE HA PRODUCIDO UN ERROR AL BORRAR EL REGISTRO. \n";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
strMensaje += excParametro + "\n";
Utilerias.NuevoEventoSistema(strOrigen + " / Clase ListaPreTitulo / Metodo Borra",
"SE HA PRODUCIDO UN ERROR AL BORRAR EL REGISTRO.",
excParametro.Message, excParametro.GetType().Name, excParametro.StackTrace);
}
finally
{
pgConn.Close();
}
//Completo la transaccion si no hubo errores.
if (!blnError)
trnScope.Complete();
}
// Regreso el estatus del proceso.
return blnError;
}