* The version of PostgreSQL I am using is 9.0.1.
* I sent a backup of the database using your support form, it was performed with pgAdmin III.
* Below you can find more detailed C# code for deletion and insertion operations. Deletion does not work but insertion does.
The following code calls the method that deletes the information:
Code: Select all
protected void BorraLista()
{
ListaPreTitulo objListaPreTitulo = new ListaPreTitulo();
objListaPreTitulo.Fuente = Convert.ToInt64(txtIDFuente.Text);
ListaPreTitulo.Fecha = new PgSqlTimeStamp(dteFechaLista.Date.Year, dteFechaLista.Date.Month, dteFechaLista.Date.Day);
objListaPreTitulo.Tipo = Convert.ToInt32(cboCatTiposMedia.SelectedItem.Value);
objListaPreTitulo.Borra(ref strMensaje, "ListasPreTitulos.aspx");
}
NOTE: The deletion process always deletes more than 1 record.
The following code (without using the TransactionScope class) deletes the records from the table and it works fine:
Code: Select all
public Boolean Borra(ref String strMensaje, String strOrigen)
{
// Variable used to indicate if there is an error in the process.
Boolean blnError = false;
// Query definition.
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);
// Connection definition.
String strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PgSqlConnection"].ToString();
PgSqlConnection pgConn = new PgSqlConnection(strConn);
PgSqlCommand pgCmd = new PgSqlCommand(Convert.ToString(strQuery), pgConn);
// The operation to the DB is performed.
try
{
pgConn.Open();
if (pgCmd.ExecuteNonQuery() != 0)
{
strMensaje += "DELETION SUCCEDED. \n";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
}
else
{
blnError = true;
strMensaje += "DELETION NOT SUCCESSFUL. \n";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
}
}
catch (Exception excParametro)
{
blnError = true;
strMensaje += "AN ERROR HAS OCCURRED. \n";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
strMensaje += excParametro + "\n";
Utilerias.NuevoEventoSistema(strOrigen + " / Clase ListaPreTitulo / Metodo Borra",
"AN ERROR HAS OCCURRED.",
excParametro.Message, excParametro.GetType().Name, excParametro.StackTrace);
}
finally
{
pgConn.Close();
}
// Final result of the operation.
return blnError;
}
The following code (using the TransactionScope class) deletes the records from the table and does not work fine, nothing is deleted:
Code: Select all
public Boolean Borra(ref String strMensaje, String strOrigen)
{
// Variable used to indicate if there is an error in the process.
Boolean blnError = false;
// Query definition.
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);
// Connection definition.
String strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PgSqlConnection"].ToString();
PgSqlConnection pgConn = new PgSqlConnection(strConn);
PgSqlCommand pgCmd = new PgSqlCommand(Convert.ToString(strQuery), pgConn);
// The operation to the DB is performed.
using (TransactionScope trnScope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.Zero))
{
try
{
pgConn.Open();
if (pgCmd.ExecuteNonQuery() != 0)
{
strMensaje += "DELETION SUCCEDED. \n";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
}
else
{
blnError = true;
strMensaje += "DELETION NOT SUCCESSFUL. \n";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
}
}
catch (Exception excParametro)
{
blnError = true;
strMensaje += "AN ERROR HAS OCCURRED. \n";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Borra" + "\n";
strMensaje += excParametro + "\n";
Utilerias.NuevoEventoSistema(strOrigen + " / Clase ListaPreTitulo / Metodo Borra",
"AN ERROR HAS OCCURRED.",
excParametro.Message, excParametro.GetType().Name, excParametro.StackTrace);
}
finally
{
pgConn.Close();
}
// Transaction is completed if no error happened.
if (!blnError)
trnScope.Complete();
}
// Final result of the operation.
return blnError;
}
The following code calls a method that inserts records to the same table:
Code: Select all
protected void InsertaLista()
{
// Valido que no exista una lista preliminar con la misma fuente,
// la misma fecha y el mismo tipo.
ListaPreTitulo objListaPreTitulo = new ListaPreTitulo();
objListaPreTitulo.Fuente = Convert.ToInt64(txtIDFuente.Text);
ListaPreTitulo.Fecha = new PgSqlTimeStamp(dteFechaLista.Date.Year, dteFechaLista.Date.Month, dteFechaLista.Date.Day);
objListaPreTitulo.Tipo = Convert.ToInt32(cboCatTiposMedia.SelectedItem.Value);
if (objListaPreTitulo.ExisteLista(ref strMensaje, "ListasPreTitulo.aspx") > 0)
{
strMensaje += "DUPLICATE INFORMATION.";
}
else
{
objListaPreTitulo.Inserta(ref strMensaje, "ListasPreTitulos.aspx", lstTitulos);
}
}
The following code inserts records to the table. It uses the TransactionScope class and it works fine.
NOTE: The insertion process always inserts more than 1 record.
Code: Select all
public Boolean Inserta(ref String strMensaje, String strOrigen, ASPxListBox lstTitulos)
{
Boolean blnError = false;
// Connection definition.
String strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PgSqlConnection"].ToString();
PgSqlConnection pgConn = new PgSqlConnection(strConn);
pgConn.Open();
PgSqlCommand pgCmd = new PgSqlCommand();
pgCmd.Connection = pgConn;
using (TransactionScope trnScope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.Zero))
{
try
{
// Items to be inserted are in a ListBox.
Int32 intIndice = 0;
StringBuilder strQuery = new StringBuilder();
for (intIndice = 1; intIndice <= lstTitulos.Items.Count; intIndice++)
{
strQuery.Remove(0, strQuery.Length);
strQuery.Append("INSERT INTO listas_pre_titulos (");
strQuery.Append("lpt_fuente, ");
strQuery.Append("lpt_titulo, ");
strQuery.Append("lpt_fecha, ");
strQuery.Append("lpt_tipo, ");
strQuery.Append("lpt_posicion ");
strQuery.Append(") VALUES (");
strQuery.Append(this.Fuente + ", ");
strQuery.Append(Convert.ToInt64(lstTitulos.Items[intIndice - 1].Value) + ", ");
strQuery.Append("'" + ListaPreTitulo.Fecha.ToString("yyyy-MM-dd AD") + "', ");
strQuery.Append(this.Tipo + ", ");
strQuery.Append(Convert.ToInt32(lstTitulos.Items[intIndice - 1].Text.Substring(0, 3)) + ")");
pgCmd.CommandText = Convert.ToString(strQuery);
if (pgCmd.ExecuteNonQuery() == 0)
{
blnError = true;
strMensaje += "AN ERROR HAS OCCURRED.";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Inserta" + "\n";
break;
}
}
// Si no hubo errores se lleva a cabo la transaccion.
if (!blnError)
{
strMensaje += "INSERTION SUCCEEDED";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Inserta" + "\n";
}
}
catch (Exception excParametro)
{
blnError = true;
strMensaje += "INSERTION NOT SUCCESSFUL.";
strMensaje += strOrigen + " / Clase ListaPreTitulo / Metodo Inserta" + "\n";
strMensaje += excParametro + "\n";
Utilerias.NuevoEventoSistema(strOrigen + " / Clase ListaPreTitulo / Metodo Inserta",
"INSERTION NOT SUCCESSFUL.",
excParametro.Message, excParametro.GetType().Name, excParametro.StackTrace);
}
finally
{
pgConn.Close();
}
// Transaction is completed if no error happened.
if (!blnError)
trnScope.Complete();
}
// Final result of the operation.
return blnError;
}
As you can see, insertion and deletion procedures are very similar; insertion works fine but deletion does not.
I know that I could delete records without using the TransactionScope class but I need to include transactions because the user has the option of modifying data which actually is performed by deleting records and then inserting the new ones so I need to be sure the complete process is made successfully.
I hope the information I am giving you is appropriate.
Best regards,
Jorge Maldonado