How to 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 slightly 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 occurs after ClearImportTable, all data from the table is erased. Is there a way to wrap this in a transaction so that if there are any errors, everything will be returned as it was?

Answer 1

You can make transaction volume:

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

.Complete(), , , .

System.Transactions.

Answer 2

" ", TransactionScope , procs. "", , , , " ". , nonferferred proc, .

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

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

...

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

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

ToList() , LINQ .

, , () LINQs , . - , .

Answer 3

Set Transaction property in DataContext?