Suggested method for rollback in database

Question

I'm using linq to entities (C# Winforms) and my database has the following structure: enter image description here

Fist of all, I insert new record on table 'creditos' since all tables need to know the PK of this table. I use a method with something like this

Credito cred = new Credito();
cred.Producto = credito.producto;
cred.Cantidad = credito.monto_prestamo;
cred.TasaInteres = credito.tasa_interes;

and then

context.creditos.AddObject(cred);
context.SaveChanges();
//Get the ID of the inserted record
credito.idCredito = cred.IDCredito;

With the obtained PK of table 'creditos', I insert this as a FK in the other tables using similar methods. So the question here is: how do I make a rollback if one of the insertions fails? Suppose I have already inserted records in two tables but it fails to insert in the third one, how do I delete all changes?

score 2 · Accepted Answer · answered Oct 12 '11 at 02:09

You can wrap all your database operations in a transaction. If anything 'happens', you simply don't commit the transaction and everything will be rolled back. Entity Framework database operations will participate in the transaction and are not committed until you call the transaction's commit method.

Alternatively, if you have your entities mapped correctly then you can specify the relationship in code and the framework will resolve the foreign keys for you. This means that you can write

cred.Persons.Add(person);
cred.Addresses.Add(address);

The framework understands that these object relationships map to database relationships, which involve mapped foreign keys. The dependency will be inserted first (in your case, the creditos table), then identity value will be retrieved, and then the related tables will be updated using that relationship.

You can do this all in a single SaveChanges call.

If all entities are mapped correctly (like you explain) and I call a single SaveChanges, but some of the insertions fail, will the SaveChanges method handle the rollback operation? — Jorge Zapata, Oct 12 '11 at 02:51
By default it will create a transaction, which means it will rollback on fail. Check this question for more: http://stackoverflow.com/questions/815586/entity-framework-using-transactions-or-savechangesfalse-and-acceptallchanges — Kirk Broadhurst, Oct 12 '11 at 03:09

score 1 · Answer 2 · answered Oct 12 '11 at 02:04

To achieve transaction scope functionality, I believe what you want is the TransactionScope. It comes with a few caveats http://simpleverse.wordpress.com/2008/08/05/using-transactionscope-for-handling-transactions/

try
    {
        using (System.Transactions.TransactionScope scop = new System.Transactions.TransactionScope())
        {
            using (NorthwindEntities entity = new NorthwindEntities())
            {
                foreach (Order order in orders)
                {
                    entity.AddToOrders(order);
                }
                entity.SaveChanges();
            }
            scop.Complete();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }

Suggested method for rollback in database

2 Answers2