Wednesday, August 31, 2011

Using TransactionScope or DbTransaction in LINQ to SQL

LINQ to SQL supports SQL transaction and in many cases calling SubmitChanges() method is enough to finalize the batch transaction. However, there are cases when you need explicit transaction handling.

Note: the following examples assume that we're connecting to remote SQL Server because when connecting to remote SQL, we sometimes have to consider MSDTC involvement for distributed transactions.

Example (1) below is the case you need explicit transaction. For testing purpose, I added if-statement in the middle and threw the exception. When an exception is thrown, if you check SQL Server table, you will notice that data is removed by sp_delete() method (this is a LINQ to SQL method that calls SQL Stored Procedure). So even if we didn't call SubmitChanges() method, data transaction can occur, so need to have explicit transaction.

So for the explicit or manual transaction, we can use TransactionScope or ADO.NET DbTransaction. The Example (2) shows how to use TransactionScope. TransactionScope is simple and easy to use. If running this program, the whole transaction will be rolled back because of if-statement exception. If commenting out testing if-statement, the transaction is successful.

The reason is when you have multiple DataContexts, MSDTC is involved so you need to start MSDTC service on remote SQL Server machine. (if you are connecting to local SQL Server, you won't have the error)

Basically if you have multiple data contexts, you need to use MSDTC.
However, the restriction that you have to start MSDTC service on your remote SQL Server might not be possible in some cases. If you cannot (or don't want to) start MSDTC inevitably, you can try a kind of trick using manual transaction with DbTransaction. Here is how to. First, get DbTransaction object from Connection.BeginTransaction() and use Commit at the bottom of try block and Rollback method in the catch block. If db2 (DataClasses1DataContext object) throws an exception due to an error, catch block will rollback previously-ran db transaction.

2 comments:

hiI am working on transactions in multiple data context, getting same error, MSDTC is not configured and due to current setup I can't do that, you have provided a solution here but I am confused that transaction is opened for datacontext1 and how it would handle context2, even transaction object is not assigned to datacontext 2.

Shafaqat Ali : Right, the statement was not clear. So I have updated sentence right above Example (4). For multiple data context, basically DTC should be involved. The example 4 is just showing a workaround if you can't have DTC for whatever reason.