How to use transactions with a datacontext

Question

Can I use transactions with a datacontext, so that I can rollback the state of the context after an error? And if so, how does that work?

Solution 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.

Solution 2

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.

Solution 3

It's not as simple as the TransactionScope method but, as I understand it, this is the "correct" way to do it for LINQ-to-SQL. It doesn't require any reference to System.Transactions.

dataContext.Connection.Open();
using (dataContext.Transaction = dataContext.Connection.BeginTransaction())
{
    dataContext.SubmitChanges();

    if (allOK)
    {
        dataContext.Transaction.Commit();
    }
    else
    {
        dataContext.Transaction.RollBack();
    }
}

Of course, the RollBack is only required if you intend to do further data operations within the using, otherwise changes will be automatically discarded.

Solution 4

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
}

View more solutions