locked
Undoing SubmitChanges() when a transaction fails

    Question

  •  

    Within the scope of a using transactionscope statement, I call many other functions, some of which in turn call SubmitChanges to submit the changes to the database. Is there any way that I can undo all of the changes that have been submitted to the database when a transaction fails, including those submitted with SubmitChanges?

    Any suggestions would be much appreciated!

    Saturday, March 17, 2007 4:01 PM

Answers

  • Because all of this uses a connection to a Sql Server database (one assumes it is a SqlCommand that is used, which auto-enrolls in the transaction), if the transaction fails, and the work was done in a TransactionScope, then none of the changes are going to be committed.

    Hope this helps.

    Sunday, March 18, 2007 6:14 AM

All replies

  • Because all of this uses a connection to a Sql Server database (one assumes it is a SqlCommand that is used, which auto-enrolls in the transaction), if the transaction fails, and the work was done in a TransactionScope, then none of the changes are going to be committed.

    Hope this helps.

    Sunday, March 18, 2007 6:14 AM
  • Hopefully someone will read this year-old post...

     

    My problem is that I have a cyclic situation:

     

    Contacts

      ID (NOT IDENTITY)

      DefaultAddress

     

    Addresses

      ID (IDENTITY)

      ContactID

     

    So it seems I have to call SubmitChanges twice (I can't get it working any other way):

     

    address.ContactID = contact.ID

    db.Addresses.Add(address)

    db.SubmitChanges()

    contact.DefaultAddress = address.ID

    db.Contacts.Add(contact)

    db.SubmitChanges()

     

    The problem is if the first SubmitChanges succeeds and the second one fails, I need to roll back the whole thing.

    I figure MyDataContext.Transaction is the key, but I cannot figure out how to create the DbTransaction item.

     

     

    Tuesday, November 06, 2007 6:00 PM
  • I got it:

     

    db.Connection.Open()

    db.Transaction = db.Connection.BeginTransaction()

    ...

    db.Transaction.Commit()

    (finally) db.Connection.Close()

     

    This works, though I'm curious if the LINQ to SQL classes have an automated way to do it without the transaction.

     

    Tuesday, November 06, 2007 6:29 PM