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!

Answer 1

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.

Answer 2

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.

 

 

Answer 3

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.