Rollback context changes in Linq Winforms application.

[Andrus]

I'm creating DbLinq MDI WinForms application.
It allows edit customers list in DataGridView. Every row change is submitted
separately.
As Nicholas recommends, it is reasonable to create database Context object
Db in form constructor and keep it alive in form existence.

I use following command to delete row when user selects delete row command:

Db.Customers.Remove( customer );
try {
Db.SubmitChanges();
} catch (Exception ex) {
// Error => we MUST roll back modifications
UIManager.Error(ex, "Cannot delete a row");
}

In case of delete error (e.q foreign key violation) I need to rollback the
delete operation in Db.

Any idea how to do this ?
Currently deleted customer remains in Db. Invalid delete keeps submitted in
every SubmitChanges() making any future SubmitChanges() impossible.

Andrus.

 

[Marc Gravell]

I'm creating DbLinq

Yet again : you are probably one of the few people on this list using
this third-party LINQ dialect, so I'm not sure how many people *here*
will be able to give you a good answer. Try asking the author?

Anyway, in LINQ-to-SQL, although there is Attach, there is no obvious
Detach mechanism - but the change-conflicts are presented via the data-
context. Another option would be to look at the change-set to
construct a new data-context *just* of the changes, and try submitting
that - and just don't keep adding it if you know it has failed once.

But in reality - at this point your data-context is unreliable. I'd be
very tempted to simply ditch it...

Marc

 

[Nicholas Paldino [.NET/C# MVP]]

Andrus,

In addition to what Marc said, I should add that I never said that you
should create the DataContext in the form constructor. I believe that what
I said is to evaluate the data operations that you are performing, and then
create a DataContext there.

What you will want to do is create a TransactionScope instance (this
assumes that your data provider supports detecting an ambient transaction
and registering a connection with it). You can use that when you begin your
delete operation, and if it succeeds, call Complete, and it will commit all
the work. If Complete is never called, then entire operation is rolled
back.

If it fails, you are going to want to get rid of the DataContext, as you
will have an inconsistency, I am sure.