dotConnect for MySQL Documentation
In This Topic
    Working with Transactions in LinqConnect
    In This Topic

    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.