How can I enable Transaction my codes with linqto SQL?

Question

if writing below codes: Error returns. I do like advised:
http://stackoverflow.com/questions/794364/how-do-i-use-transactionscope-in-c

But only error change:

The partner transaction manager has disabled its support for remote/network transactions Exception from HRESULT: 0x8004D025

I am using Windows Server 2003.

 using (var stockMovementCtx = new StockMovementCtxDataContext())
            {
                using (var scope = new TransactionScope())
                {

// do something....
}
scope.Complete();
}

but if I changed my codes ; every thing is ok:

using (var stockMovementCtx = new StockMovementCtxDataContext())
            {
              
// do something....

}

How can I solve the above error?

Answer 1

DataContext by default wraps all operations within a Transaction, so you don't need to explicitly do Transaction while working with DataContext. Read this.

http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx

using (var stockMovementCtx = new StockMovementCtxDataContext())
{
     // do something....


     //everything until this point is within the transaction.
     stockMovementCtx.SubmitChange();
}

Why we need TransactionScope ?

TransactionScope enables you to perform transactions beyond dataabse. Say you have series of operations as below and all are atomic and they need be performed within a transaction.

1.) Add record in Table 1
2.) Add record in Table 2
3.) Write a NEW file on the Disk
4.) Rename file X on the disk
5.) Update file record in Table 3

If you use SqlTransaction then only the operaration 1,3 and 5 can participate in the transaction but 3 and 4 cannot because they do not relate database at all. In such cases TrasanctionScope can help you. TransactionScope leverages the MSDTC (Distributed Trasanction co-coordinator) that can help you do transactions beyond database context. You can wrap all five operations within a TransactionScope transaction execute them atomically. It is also worth noting that TransactionScope supports nested transactions, that means one block of transaction can contain multiple set of transactions.