• Understanding Transactions
• Local Transactions
• Distributed Transactions

Understanding Transactions

A transaction is one or several operations considered as a single unit of work which is completed entirely or have no effect at all ("all-or-nothing"). If a failure occurs at one point in the transaction, all of the updates can be rolled back to their pre-transaction state. A transaction must conform to the ACID properties - atomicity, consistency, isolation, and durability-in order to guarantee data consistency.

If a transaction involves multiple tables in the same database, then explicit transactions in PL/SQL often perform better. You can use COMMIT and ROLLBACK statements in your SQL to fix and discard respectively the previous commands in your current PL/SQL block. For more information, see Oracle PL/SQL documentation.

Otherwise, a transaction with plain SQL can be implemented via special command classes from library/assemblies designed to work with Oracle database. For example, you can use Devart.Data.Universal.UniCommand: start transaction on UniCommand's connection, execute several SQL statements via this UniCommand, and commit/rollback all operations when it is necessary. See the sample from the Local Transaction topic.

This article describes the way to manipulate transactions from your .NET code (without envolving PL/SQL transactions) - this is the most common case of working with transactions. Concerning your task, you can choose the type of transaction to implement - local or distributed. A transaction considered to be a local transaction when it is a single-phase transaction and is handled by the database directly. A distributed transaction is a transaction that affects several resources, it is coordinated by a transaction monitor and uses fail-safe mechanisms (such as two-phase commit) for transaction resolution.

Note: Be aware, promotable transactions used in SQL Server is not supported by Oracle. Also take into account that TransactionScope (distributed transactions) is completed only on its disposing.

Local Transactions

dotConnect Universal has its UniTransaction object for performing local transactions. UniTransaction is used when several operations on one connection object should be performed as one transaction. The application creates a UniTransaction object by calling BeginTransaction on the UniConnection object. All subsequent operations associated with the transaction (for example, committing or aborting the transaction), are performed on the UniTransaction object. The correlation between UniConnection and UniTransaction is always 1:1. As a result, only one UniTransaction can be created for a separate UniConnection in one moment of time.

Example:

The following example creates an UniConnection and an UniTransaction. It also demonstrates how to use the BeginTransaction, Commit, and Rollback methods.

public void RunUniTransaction(string myConnString) 
{ 
  UniConnection myConnection = new UniConnection(myConnString); 
  myConnection.Open(); 
  UniCommand myCommand = new UniCommand(); 
  UniTransaction myTrans; 
  // Start a local transaction 
  myTrans = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); 
  // Assign transaction object for a pending local transaction 
  myCommand.Transaction = myTrans; 
  myCommand.Connection = myConnection; 
  try 
  { 
    myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(50, 'DEVELOPMENT')"; 
    myCommand.ExecuteNonQuery(); 
    myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(60, 'PRODUCTION')"; 
    myCommand.ExecuteNonQuery(); 
    myTrans.Commit(); 
    Console.WriteLine("Both records are written to database."); 
  } 
  catch(Exception e) 
  { 
    myTrans.Rollback(); 
    Console.WriteLine(e.ToString()); 
    Console.WriteLine("Neither record was written to database."); 
  } 
  finally 
  { 
    myConnection.Close(); 
  } 
} 

Public Sub RunUniTransaction(ByVal myConnString As String)
  Dim myConnection As New UniConnection(myConnString)
  myConnection.Open()
  Dim myCommand As New UniCommand
  Dim myTrans As UniTransaction
  ' Start a local transaction
  myTrans = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)
  ' Assign transaction object for a pending local transaction
  myCommand.Transaction = myTrans
  myCommand.Connection = myConnection
  Try
    myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(50, 'DEVELOPMENT')"
    myCommand.ExecuteNonQuery()
    myCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(60, 'PRODUCTION')"
    myCommand.ExecuteNonQuery()
    myTrans.Commit()
    Console.WriteLine("Both records are written to database.")
  Catch e As Exception
    myTrans.Rollback()
    Console.WriteLine(e.ToString())
    Console.WriteLine("Neither record was written to database.")
  Finally
    myConnection.Close()
  End Try
End Sub

Distributed Transactions

If you want to put operations that are executed on several connection objects in the same distributed transaction, it is necessary to enlist them in TransactionScope. It can be done with the Enlist parameter of connection string or with the EnlistTransaction method of UniConnection class.

The System.Transactions.TransactionScope class makes a code block transactional by implicitly enlisting connections in a distributed transaction. You must call the Complete method at the end of the code block marked by the TransactionScope. The Dispose method is invoked when program execution leaves a code block, causing the transaction to be discontinued if the Complete method is not called. If an exception has been thrown that causes the code to leave scope, the transaction is considered to be discontinued.

It is recommended that you employ a using block to ensure that the Dispose method is called on the TransactionScope object when the using block is exited. Failure to commit or roll back pending transactions can seriously degrade performance because the default time-out for the TransactionScope is one minute. If you do not use a using statement, you must perform all work in a Try block and explicitly call the Dispose method in the Finally block.

If an exception occurs within the TransactionScope, the transaction is marked as inconsistent and is abandoned. It is rolled back when the TransactionScope is disposed. If no exception occurs, participating transactions commit.

Example:

The following example demonstrates the usage of TransactionScope and a distributed transaction between SQL Server and Oracle databases. It is necessary to add reference to the System.Transactions.dll assembly to use the System.Transactions namespace.

using System;
using Devart.Data.Universal;
using System.Transactions;

namespace UniTransaction
{
  class Program
  {
    static void Main(string[] args)
    {
      using (TransactionScope transScope = new TransactionScope())
      {
        try
        {
          using (UniConnection sqlConn = new UniConnection("provider=sql server;Data Source=xxx;Integrated Security=False;User ID=xxx; initial catalog=xxx;Enlist=true"))
          {
            sqlConn.Open();
            UniCommand sqlComm = new UniCommand("update dept set dname='test' where deptno=30",
            sqlConn);
            sqlComm.ExecuteNonQuery();
            sqlConn.Close();
          }

          using (UniConnection oraConn = new UniConnection("provider=Oracle;Data
          Source=xxx;uid=xxx;pwd=xxx;Enlist=true"))
          {
            oraConn.Open();
            UniCommand oraComm = new UniCommand("update dept set dname='test' where deptno=30",
            oraConn);
            oraComm.ExecuteNonQuery();
            oraConn.Close();
          }
          transScope.Complete();
        }
        catch(Exception)
        {
          Console.WriteLine("Transaction is aborted");
        }
      }
    }
  }
}

Imports System
Imports Devart.Data.Universal
Imports System.Transactions

Namespace UniTransaction
  Class Program
    Private Shared Sub Main(args As String())
      Using transScope As New TransactionScope()
        Try
          Using sqlConn As New UniConnection("provider=sql server;Data Source=xxx;Integrated Security=False;User ID=xxx; initial catalog=xxx;Enlist=true")
            sqlConn.Open()
            Dim sqlComm As New UniCommand("update dept set dname='test' where deptno=30", sqlConn)
            sqlComm.ExecuteNonQuery()
            sqlConn.Close()
          End Using

          Using oraConn As New UniConnection("provider=Oracle;Data Source=xxx;uid=xxx;pwd=xxx;Enlist=true")
            oraConn.Open()
            Dim oraComm As New UniCommand("update dept set dname='test' where deptno=30", oraConn)
            oraComm.ExecuteNonQuery()
            oraConn.Close()
          End Using
          transScope.Complete()
        Catch generatedExceptionName As Exception
          Console.WriteLine("Transaction is aborted")
        End Try
      End Using
    End Sub
  End Class
End Namespace