Transactions (Rollback issue)

Topics: Data Access Application Block
Sep 14, 2011 at 3:00 PM

I'm working with EntLib 5, also I’m using the same code example, but I have a little problem with my code, the rollback is not working.
My code execute 2 stored Procedures, one is to insert a row in a parent table, the second inserts a row in a child table
The first stored procedure returns a value that is used in the second SP
I have deleted the second SP in the database because I want that an error occurs so the rollback executes.
But the problem is that the firs SP executes and makes an insert in the parent table then the second SP tries to execute but an exception occurs so the code inside the catch is execute which is the rollback instruction but the rollback never occurs. When I check the data base the parent table has a new row


Does anyone have any suggestions?

 public void CrearJerarquia(string nombre, string descripcion)
        {
            Database db = DatabaseFactory.CreateDatabase();            

            DbCommand dbJerarquiasCommand = db.GetStoredProcCommand("ADM_CrearJerarquias", nombre, descripcion, null);
            //DbCommand dbElementosCommand = db.GetStoredProcCommand("ADM_CrearJerarquiaElemento", "Raiz" + nombre, "Nodo Raiz " + nombre, idJerarquia);
            DbCommand dbElementosCommand = db.GetStoredProcCommand("ADM_CrearJerarquiaElemento");

            using (DbConnection dbConnection = db.CreateConnection())
            {
                dbConnection.Open();
                DbTransaction dbTransaction = dbConnection.BeginTransaction();
                try
                {
                    //Returned value from the firs SP
                    int idJerarquia = (int)db.ExecuteScalar(dbJerarquiasCommand);

                    db.AddInParameter(dbElementosCommand, "Nombre", DbType.String, "Raiz" + nombre);
                    db.AddInParameter(dbElementosCommand, "Descripcion", DbType.String, "Nodo Raiz " + nombre);
                    db.AddInParameter(dbElementosCommand, "IdJerarquia", DbType.Int32, idJerarquia);
                    db.ExecuteNonQuery(dbElementosCommand);

                    dbTransaction.Commit();
                }
                catch (Exception ex)
                {
                    dbTransaction.Rollback();
                }
                dbConnection.Close();
            }
        }
Sep 14, 2011 at 3:24 PM

Hi,

At which line of code does the error occur? Accoring to this article in MSDN (http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbtransaction.rollback.aspx), the transaction can only be rolled back from a pending state; that is, after BeginTransaction is called but before Commit is called; otherwise the Rollback() method wll not do anything because the transaction is not active,

 

Hope this helps :-)

Sep 15, 2011 at 5:20 AM
Edited Sep 15, 2011 at 6:07 AM

Hi,

Have you checked if your stored proc also contains a transaction and you are committting it inside? Also try using the Transaction Scope class and see if this works.

 

Noel Angelo Bolasoc
Avanade Software
Avanade, Inc.
Contact Us