18

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?

RoastBeast
  • 1,059
  • 2
  • 22
  • 38
user107534
  • 201
  • 1
  • 2
  • 4

5 Answers5

20

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.

leppie
  • 115,091
  • 17
  • 196
  • 297
  • 5
    You should mention that your sample _always_ reverts its work, or am I wrong and dc.Transaction.Rollback() does no harm after dc.SubmitChanges()? – VVS May 15 '09 at 09:12
  • -1: Should have a using block for the transaction rather than try/finally to ensure cleanup. – Richard May 15 '09 at 09:31
  • 4
    @Richard: I am waiting on you for a snippet using a 'using' block that is semantically the same as mine. – leppie May 15 '09 at 09:49
  • Wont ALWAYS rollback - what if there is a power outage between `SubmitChanges` and `Rollback`? – Yaakov Ellis May 17 '11 at 06:00
  • 1
    @Yaakov Ellis: Your understanding of transactions are incorrect. Data only gets committed when Commit is called. IF there was a power failure, no data would be committed and the transaction with just go in limbo. – leppie May 17 '11 at 11:37
  • leppie - you're right, I overlooked the fact that there was no commit – Yaakov Ellis May 17 '11 at 11:48
  • 2
    Is there any harm to opening the DataContext's connection? What if it's already open - won't it throw an Exception? What about leaving it open - could this be wasteful, leaving a connection open indefinitely? – Chris Moschini Jul 12 '11 at 17:33
  • 1
    @Chris Moschini: Yes, opening an already open connection will throw an error, and the code would still be wrong for closing the connection if it was already open. The framework uses an SqlConnectionManager instance to check whether it's closed and needs opened, whether to automatically close it when done, and handles dealing with any ambient transactions or other transactions assigned to the DataContext. I think it's much easier to use a transaction scope, because they can be nested easily, and the framework handles the connection opening and closing logic correctly. – Triynko Nov 07 '11 at 20:14
  • 1
    Note that if you need to share the transaction to another instance of the `DataContext`, you need to use the same connection. `new MyDataContext(dc.Connection)`. – Gabriel GM Jan 04 '16 at 20:14
15

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.

Richard
  • 106,783
  • 21
  • 203
  • 265
  • 2
    +1 for including the IsolationLevel change for SQL Server. Explained here: http://blogs.msdn.com/b/dbrowne/archive/2010/05/21/using-new-transactionscope-considered-harmful.aspx Why the EnterpriseServicesInteropOption.Automatic? – Chris Moschini Jul 12 '11 at 17:40
  • where does allOK come from? My intelli-type isn't picking it up. – Since_2008 Aug 24 '11 at 17:28
  • @Fritos: The code running in the transaction (ie. in the replacement for the comment). – Richard Aug 24 '11 at 17:29
  • 2
    Would you normally have the datacontext already when creating the TransactionScope? Or is it necessary to create the datacontext _inside_ the 'using (var trans)'? – Bjarke Ebert Mar 12 '13 at 10:43
  • I've just rolled back the edit to this. Using fully namespace qualified names is not idiomatic C#; having one name fully qualified but not others is inconsistent. – Richard Jan 18 '14 at 09:45
14

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.

Damien
  • 1,463
  • 1
  • 18
  • 30
10

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
}
Philippe
  • 3,945
  • 3
  • 38
  • 56
  • 1
    This is the best answer so far, IMO. Transaction scope is the way to go. This also specifies a ConflictMode and at least suggests some appropriate form of error handling. Nice job. – Triynko Nov 07 '11 at 20:13
0

Is something like this:

using (YourDatacontext m_DB = new YourDatacontext())
using (TransactionScope tran = new TransactionScope())
{
   try
   {
      //make here the changes
      m_DB.SubmitChanges();
      tran.Complete();
   }
   catch (Exception ex)
   {
       Transaction.Current.Rollback();
   }
}
Nițu Alexandru
  • 714
  • 1
  • 11
  • 33