How do I use a transaction in LINQ to SQL using stored procedures?

Question

I have a piece of code that looks something like this (ClearImportTable and InsertPage are stored procedures):

datacontext.ClearImportTable() //Essentially a DELETE FROM table
for (int i = 1; i < MAX_PAGES; ++i){
    datacontext.InsertPage(i); //Inserts data into the table
}

This is a somewhat simplified version of my code, but the idea is that it clears the table before inserting records. The only problem is that if an error occurrs after ClearImportTable, all of the data from the table is wiped. Is there any way to wrap this in a transaction so that if there are any errors, everything will be put back the way it was?

Answer 1

You can do a transaction scope:

 using (var transaction = new TransactionScope())
 {
     // do stuff here...
     transaction.Complete();
 }

If an exception occurs or the using block is left without hitting the transaction.Complete() then everything performed within the using block is rolled back.

You will need to reference the System.Transactions assembly.

Answer 2

Set the Transaction property on the DataContext?

Answer 3

As "Sailing Judo" mentioned above, I've used the TransactionScope blocks with great success when I need to call stored procs. However there's one "gotcha" that I've run into where an exception gets thrown saying that the "transaction is in doubt." To get around this I had to call a nondeferred method on the proc to make it evaluate the result immediately. So instead of

using (var transaction = new TransactionScope())
{
    var db = new dbDataContext();

    db.StoredProc();
    transaction.Complete();
}

I had to call it like this...

using (var transaction = new TransactionScope())
{
    var db = new dbDataContext();

    db.StoredProc().ToList();
    transaction.Complete();
}

In this example, ToList() could be any nondeferred method which causes LINQ to immediately evaluate the result.

I assume this is because LINQs lazy (deferred) nature isn't playing well with the timing of the transaction, but this is only a guess. If anyone could shine more light on this, I'd love to hear about it.