How to use transactions with a datacontext

Question

Can i use transactions with a datacontext, so i can rollback the state of the context after an error. And so yes, how does it work?

Answer 1

I use them in testing all the time :)

try
{
  dc.Connection.Open();
  dc.Transaction = dc.Connection.BeginTransaction();

  dc.SubmitChanges();
}
finally
{
  dc.Transaction.Rollback();
}

UPDATE

This will ALWAYS rollback after the fact. I use this in testing.

Answer 2

Something like this, probably:

try
{
    using (TransactionScope scope = new TransactionScope())
    {
        //Do some stuff

        //Submit changes, use ConflictMode to specify what to do
        context.SubmitChanges(ConflictMode.ContinueOnConflict);

        scope.Complete();
    }
}
catch (ChangeConflictException cce)
{
        //Exception, as the scope was not completed it will rollback
}

Answer 3

A DataContext will pick up an ambient transaction by default, so it is just a matter of ensuring there is a Transaction in scope. The details become the main issue:

• What options do you need (e.g. isolation level)
• Do you want a new transaction or reuse an existing transaction (e.g. an audit/logging operation might require a new transaction so it can be committed even if the overall business operation fails and thus the outer transaction is rolled back).

This is simplified some prototype code, the real code uses helpers to create the transactions with policy driven options (one of the purposes of the prototype was to examine the impact of these options).

using (var trans = new TransactionScope(
                           TransactionScopeOption.Required,
                           new TransactionOptions {
                               IsolationLevel = IsolationLevel.ReadCommitted
                           },
                           EnterpriseServicesInteropOption.Automatic)) {
    // Perform operations using your DC, including submitting changes

    if (allOK) {
        trans.Complete();
    }
}

If Complete() is not called then the transaction will be rolled back. If there is a containing transaction scope then both the inner and outer transactions need to Complete for the changes on the database to be committed.

Answer 4

Thanx for the answers.

Last week i have been trying to implement my code with transactions. But after i rolled back the transaction, when i call GetChangeSet, the set contains all the changes i had made????

Answer 5

Hello, This topic is very useful for me. I have used this way but it’s sucess. I am trying to do some other wrong calculator to it rollback….but db can’t rollback. My code:

DataContext db = new DataContext(); db.Connection.Open(); DbTransaction trans = db.Connection.BeginTransaction(); try { account acc1= new account (); acc.username = “demo”; acc.password = “demo”; db.AddToaccount(acc1); db.SaveChanges(); // some other calculator….. trans.Commit(); Label1.Text = “DB is saved success!”; } catch { trans.Rollback(); Label1.Text = “DB is rollback”; }

please help me.