In this article we consider a short description of the LinqConnect transactional models. There are also some simple code listings illustrating different aspects of the transactional model.

LinqConnect has transactions as a part of its architecture.

Implicit Local Transaction. LinqConnect starts a local DbTransaction and uses it to execute generated SQL commands, if there is no open transaction into the LinqConnect connection instance. When all SQL commands have been successfully completed, LinqConnect commits the transaction and returns.

Explicit Local and Distributed Transaction. LinqConnect performs a check whether this call was executed in the scope of the Transaction started by user (local or distributed), when SubmitChanges is called. It is executed in the context of the same transaction, if the DataContext.Transaction property is set to a DbTransaction instance. Please note, that you should either commit or rollback the transaction (LinqConnect does not perform it for you).

        //Opening connection
        context.Connection.Open();  
        DEPT department = context.DEPT.Where(d => d.DEPTNO == 10).First();
        department.LOC = "TEST";
        //Opening transaction. 
        //Transaction property        
        context.Transaction = context.Connection.BeginTransaction();
        //This call participates in the transaction
        context.SubmitChanges(); 
        department.DNAME = "TEST";
        //This call also participates in the transaction 
        context.SubmitChanges(); 
        //Replace the flag condition with the one you need 
        //or remove it and leave only commit part
        if(flag)
        {
          //transaction completed successfully, both calls succeeded 
          context.Transaction.Commit(); 
        }
        else 
        {
          //something is wrong, both calls are rolled back 
          context.Transaction.Rollback();  
        }

        'Opening connection  
	Context.Connection.Open()
	Dim department As Dept = context.Depts.Where(Function(ByVal d As Dept) d.Deptno = 10).First()  
	department.LOC = "TEST"
	'Opening transaction  
	'Transaction property		
	Context.Transaction = Context.Connection.BeginTransaction()  
	'This call participates in the transaction  
	Context.SubmitChanges()  
	department.DNAME = "TEST"  
	'This call also participates in the transaction  
	context.SubmitChanges()
	'Replace the flag condition with the one you need  
	'or remove it and leave only commit part  
	If(flag)  
         'transaction completed successfully, both calls succeeded  
          Context.Transaction.Commit()  
	Else  
         'something is wrong, both calls are rolled back  
          Context.Transaction.Rollback()  
        End If  

Here is an example for DataContext.Transaction usage. In LinqConnect we have a context-level Transaction property.

In Microsoft LINQ to SQL the connection corresponding to the transaction must match the connection used for constructing the DataContext. An exception is thrown, if a different connection is used.

Devart LinqConnect implements this situation a bit differently - we use a new connection for every querying operation for performance reasons. There is a way to obtain the planned behaviour, however. To achieve this, you can use Devart.Data.DataContext.MaxUsedConnections property:

Devart.Data.DataContext.MaxUsedConnections = 1; 

Devart.Data.DataContext.MaxUsedConnections = 1 

Then all queries will be executed using the single instance of the connection. In case of submitting the changes the DataContext.Connection persists.

Distributed Transactions and TransactionScope. Here is a code sample about usage Suppress option of TransactionScope:

        DEPT department = context.DEPT.Where(d => d.DEPTNO == 25).First();
        department.DNAME = "xxx";
        using (TransactionScope tscope = new TransactionScope(TransactionScopeOption.Suppress)) 
        {
	    //a new transaction will be created for this call  			
            context.SubmitChanges();
            tscope.Complete();
         }

        Dim department As Dept = Context.Depts.Where(Function(ByVal d As Dept) d.DEPTNO = 25).First()
	department.Dname = "xxx"
	Using TScope As New TransactionScope(TransactionScopeOption.Suppress)
	    'a new transaction will be created for this call  			
	    Context.SubmitChanges()
	    TScope.Complete()
	End Using

You can find more information about Transactions in MSDN.