As we were discussing concurrency options in my previous articles here , we noted that updating the database with SubmitChanges could update a single record or any number of records (even across multiple tables). If we run into conflicts, we can decide how to handle the conflict. However, we didn’t point out previously that if some effort is not made to roll back changes, any records that were successfully saved prior to the exception will be committed to the database. This could leave the database in an invalid state if some records are saved and others are not.
LINQ to SQL offers three main mechanisms to manage transactions. In the first option, used by default, the DataContext will create and put that in a transaction when SubmitChanges is called. This will roll back changes automatically depending on the selected ConflictMode option. If we wish to manually maintain the transaction, the DataContext also offers the ability to use the transaction on the connection already maintained by the DataContext. In this case, we call BeginTransaction on DataContext.Connection before we try to submit the changes. After we submit the changes, we can either commit them or roll them back. The below code demonstrates this.
The downside of managing the transactions directly through the DataContext is that it cannot span multiple connections or multiple DataContext objects. As a third option, the System.Transactions.TransactionScope object that was introduced with the .NET 2.0 Framework was specifically designed to seamlessly span connections. To use it, add a reference to the System. Transactions library. This object will automatically scale the transaction based on the objects that it covers. If the scope only covers a single database call, it will use a simple database transaction. If it spans multiple classes with multiple connections, it will automatically scale up to an enterprise transaction. Additionally, the TransactionScope doesn’t require us to explicitly begin the transaction or roll it back. The only thing you need to do is complete it as shown below.
Unlike the other transaction mechanisms, we don’t need to wrap the code in a try catch block solely to roll the transaction back. With the TransactionScope, the transaction will automatically get rolled back unless we call the Complete method. If an exception is thrown in SubmitChanges, the exception will bypass the Complete method. We don’t need to explicitly roll the transaction back. It still needs to be wrapped in an exception-handling block, but the exception handling can be done closer to the user interface.The true advantage of the TransactionScope object is that it automatically scales based on the given context. It works equally well with local transactions an with heterogeneous sources. Because of the flexibility and scalability, using the TransactionScope object is the preferred method of handling transactions with LINQ to SQL.