OracleTransaction Class

An OracleTransaction object represents a local transaction.

Class Inheritance

System.Object

  System.MarshalByRefObject

    System.Data.Common.DbTransaction

      Oracle.DataAccess.Client.OracleTransaction

Declaration

// C#
public sealed class OracleTransaction : DbTransaction
// C#
public sealed class OracleTransaction : MarshalByRefObject, 
   IDbTransaction, IDisposable

Requirements

Provider ODP.NET, Unmanaged Driver ODP.NET, Managed Driver
Assembly Oracle.DataAccess.dll Oracle.ManagedDataAccess.dll
Namespace Oracle.DataAccess.Client Oracle.ManagedDataAccess.Client
.NET Framework 3.5, 4.0, 4.5 4.0, 4.5

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Remarks

The application calls BeginTransaction on the OracleConnection object to create an OracleTransaction object. The OracleTransaction object can be created in Read Committed mode only. Any other mode results in an exception.

The execution of a DDL statement in the context of a transaction is not recommended since it results in an implicit commit that is not reflected in the state of the OracleTransaction object.

All operations related to savepoints pertain to the current local transaction. Operations like commit and rollback performed on the transaction have no effect on data in any existing DataSet.

Example

// Database Setup, if you have not done so yet.
/*
connect scott/tiger@oracle
DROP TABLE MyTable;
CREATE TABLE MyTable (MyColumn NUMBER);
--CREATE TABLE MyTable (MyColumn NUMBER PRIMARY KEY);
 
*/
 
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client;
 
class OracleTransactionSample
{
  static void Main()
  {
    // Drop & Create MyTable as indicated Database Setup, at beginning
    
    // This sample starts a transaction and inserts two records with the same
    // value for MyColumn into MyTable.
    // If MyColumn is not a primary key, the transaction will commit.
    // If MyColumn is a primary key, the second insert will violate the 
    // unique constraint and the transaction will rollback.
 
 
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    OracleCommand cmd = con.CreateCommand();
 
    // Check the number of rows in MyTable before transaction
    cmd.CommandText = "SELECT COUNT(*) FROM MyTable";    
    int myTableCount = int.Parse(cmd.ExecuteScalar().ToString());
 
    // Print the number of rows in MyTable
    Console.WriteLine("myTableCount = " + myTableCount);
 
    // Start a transaction
    OracleTransaction txn = con.BeginTransaction(
      IsolationLevel.ReadCommitted);
 
    try
    {
      // Insert the same row twice into MyTable
      cmd.CommandText = "INSERT INTO MyTable VALUES (1)";
      cmd.ExecuteNonQuery();
      cmd.ExecuteNonQuery(); // This may throw an exception
      txn.Commit();
    }
    catch (Exception e)
    {
      // Print the exception message
      Console.WriteLine("e.Message    =  " + e.Message);
 
      // Rollback the transaction
      txn.Rollback();
    }    
 
    // Check the number of rows in MyTable after transaction    
    cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
    myTableCount = int.Parse(cmd.ExecuteScalar().ToString());
 
    // Prints the number of rows
    // If MyColumn is not a PRIMARY KEY, the value should increase by two.
    // If MyColumn is a PRIMARY KEY, the value should remain same.
    Console.WriteLine("myTableCount = " + myTableCount);
 
    txn.Dispose();
    cmd.Dispose();
 
    con.Close();
    con.Dispose();
  }
}

Not supported in a .NET stored procedure

OracleTransaction Members

OracleTransaction members are listed in the following tables.

OracleTransaction Static Methods

The OracleTransaction static method is listed in Table 6-137.

Table 6-137 OracleTransaction Static Method

Method Description

Equals

Inherited from System.Object (Overloaded)


OracleTransaction Properties

OracleTransaction properties are listed in Table 6-138.

Table 6-138 OracleTransaction Properties

Property Description

IsolationLevel

Specifies the isolation level for the transaction

Connection

Specifies the connection that is associated with the transaction


OracleTransaction Public Methods

OracleTransaction public methods are listed in Table 6-139.

Table 6-139 OracleTransaction Public Methods

Public Method Description

Commit

Commits the database transaction

CreateObjRef

Inherited from System.MarshalByRefObject

Dispose

Frees the resources used by the OracleTransaction object

Equals

Inherited from System.Object (Overloaded)

GetHashCode

Inherited from System.Object

GetLifetimeService

Inherited from System.MarshalByRefObject

GetType

Inherited from System.Object

InitializeLifetimeService

Inherited from System.MarshalByRefObject

Rollback

Rolls back a database transaction (Overloaded)

Save

Creates a savepoint within the current transaction

ToString

Inherited from System.Object


OracleTransaction Static Methods

The OracleTransaction static method is listed in Table 6-140.

Table 6-140 OracleTransaction Static Method

Method Description

Equals

Inherited from System.Object (Overloaded)


OracleTransaction Properties

OracleTransaction properties are listed in Table 6-141.

Table 6-141 OracleTransaction Properties

Property Description

IsolationLevel

Specifies the isolation level for the transaction

Connection

Specifies the connection that is associated with the transaction


IsolationLevel

This property specifies the isolation level for the transaction.

Declaration

// C#
public override IsolationLevel IsolationLevel {get;}

Property Value

IsolationLevel

Implements

IDbTransaction

Exceptions

InvalidOperationException - The transaction has already completed.

Remarks

Default = IsolationLevel.ReadCommitted

Connection

This property specifies the connection that is associated with the transaction.

Declaration

// C#
public OracleConnection Connection {get;}

Property Value

Connection

Implements

IDbTransaction

Remarks

This property indicates the OracleConnection object that is associated with the transaction.

OracleTransaction Public Methods

OracleTransaction public methods are listed in Table 6-142.

Table 6-142 OracleTransaction Public Methods

Public Method Description

Commit

Commits the database transaction

CreateObjRef

Inherited from System.MarshalByRefObject

Dispose

Frees the resources used by the OracleTransaction object

Equals

Inherited from System.Object (Overloaded)

GetHashCode

Inherited from System.Object

GetLifetimeService

Inherited from System.MarshalByRefObject

GetType

Inherited from System.Object

InitializeLifetimeService

Inherited from System.MarshalByRefObject

Rollback

Rolls back a database transaction (Overloaded)

Save

Creates a savepoint within the current transaction

ToString

Inherited from System.Object


Commit

This method commits the database transaction.

Declaration

// C#
public override void Commit();

Implements

IDbTransaction

Exceptions

InvalidOperationException - The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.

Remarks

Upon a successful commit, the transaction enters a completed state.

Example

// Database Setup, if you have not done so yet
/*
connect scott/tiger@oracle
DROP TABLE MyTable;
CREATE TABLE MyTable (MyColumn NUMBER);
--CREATE TABLE MyTable (MyColumn NUMBER PRIMARY KEY);
 
*/
 
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class CommitSample
{
  static void Main()
  {
    // Drop & Create MyTable as indicated in Database Setup, at beginning
    
    // This sample starts a transaction and inserts two records with the same
    // value for MyColumn into MyTable.
    // If MyColumn is not a primary key, the transaction will commit.
    // If MyColumn is a primary key, the second insert will violate the 
    // unique constraint and the transaction will rollback.
 
 
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    OracleCommand cmd = con.CreateCommand();
 
    // Check the number of rows in MyTable before transaction
    cmd.CommandText = "SELECT COUNT(*) FROM MyTable";    
    int myTableCount = int.Parse(cmd.ExecuteScalar().ToString());
 
    // Print the number of rows in MyTable
    Console.WriteLine("myTableCount = " + myTableCount);
 
    // Start a transaction
    OracleTransaction txn = con.BeginTransaction(
      IsolationLevel.ReadCommitted);
 
    try
    {
      // Insert the same row twice into MyTable
      cmd.CommandText = "INSERT INTO MyTable VALUES (1)";
      cmd.ExecuteNonQuery();
      cmd.ExecuteNonQuery(); // This may throw an exception
      txn.Commit();
    }
    catch (Exception e)
    {
      // Print the exception message
      Console.WriteLine("e.Message    =  " + e.Message);
 
      // Rollback the transaction
      txn.Rollback();
    }    
 
    // Check the number of rows in MyTable after transaction    
    cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
    myTableCount = int.Parse(cmd.ExecuteScalar().ToString());
 
    // Prints the number of rows
    // If MyColumn is not a PRIMARY KEY, the value should increase by two.
    // If MyColumn is a PRIMARY KEY, the value should remain same.
    Console.WriteLine("myTableCount = " + myTableCount);
 
    txn.Dispose();
    cmd.Dispose();
 
    con.Close();
    con.Dispose();
  }
}

Dispose

This method frees the resources used by the OracleTransaction object.

Declaration

// C#
public void Dispose();

Implements

IDisposable

Remarks

This method releases both the managed and unmanaged resources held by the OracleTransaction object. If the transaction is not in a completed state, an attempt to rollback the transaction is made.

Rollback

Rollback rolls back a database transaction.

Overload List:

Rollback()

This method rolls back a database transaction.

Declaration

// C#
public override void Rollback();

Implements

IDbTransaction

Exceptions

InvalidOperationException - The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.

Remarks

After a Rollback(), the OracleTransaction object can no longer be used because the Rollback ends the transaction.

Example

// Database Setup, if you have not done so yet.
/*
connect scott/tiger@oracle
DROP TABLE MyTable;
CREATE TABLE MyTable (MyColumn NUMBER);
 
*/
 
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class RollbackSample
{
  static void Main()
  {
    // Drop & Create MyTable as indicated previously in Database Setup
    
    // This sample starts a transaction and inserts one record into MyTable.
    // It then rollsback the transaction, the number of rows remains the same
 
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    OracleCommand cmd = con.CreateCommand();
 
    // Check the number of rows in MyTable before transaction
    cmd.CommandText = "SELECT COUNT(*) FROM MyTable";    
    int myTableCount = int.Parse(cmd.ExecuteScalar().ToString());
 
    // Print the number of rows in MyTable
    Console.WriteLine("myTableCount = " + myTableCount);
 
    // Start a transaction
    OracleTransaction txn = con.BeginTransaction(
      IsolationLevel.ReadCommitted);
 
    // Insert a row into MyTable
    cmd.CommandText = "INSERT INTO MyTable VALUES (1)";
    cmd.ExecuteNonQuery();
 
    // Rollback the transaction
    txn.Rollback();
 
    // Check the number of rows in MyTable after transaction
    cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
    myTableCount = int.Parse(cmd.ExecuteScalar().ToString());
 
    // Prints the number of rows, should remain the same
    Console.WriteLine("myTableCount = " + myTableCount);
 
    txn.Dispose();
    cmd.Dispose();
 
    con.Close();
    con.Dispose();
  }
}

Rollback(string)

This method rolls back a database transaction to a savepoint within the current transaction.

Declaration

// C#
public override void Rollback(string savepointName);

Parameters

  • savepointName

    The name of the savepoint to rollback to, in the current transaction.

Exceptions

InvalidOperationException - The transaction has already been completed successfully, has been rolled back, or the associated connection is closed.

Remarks

After a rollback to a savepoint, the current transaction remains active and can be used for further operations.

The savepointName specified does not have to match the case of the savepointName created using the Save method, since savepoints are created in the database in a case-insensitive manner.

Save

This method creates a savepoint within the current transaction.

Declaration

// C#
public void Save(string savepointName);

Parameters

  • savepointName

    The name of the savepoint being created in the current transaction.

Exceptions

InvalidOperationException - The transaction has already been completed.

Remarks

After creating a savepoint, the transaction does not enter a completed state and can be used for further operations.

The savepointName specified is created in the database in a case-insensitive manner. Calling the Rollback method rolls back to savepointName. This allows portions of a transaction to be rolled back, instead of the entire transaction.

Example

// Database Setup, if you have not done so yet.
/*
connect scott/tiger@oracle
DROP TABLE MyTable;
CREATE TABLE MyTable (MyColumn NUMBER);
 
*/
 
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class SaveSample
{
  static void Main()
  {
    // Drop & Create MyTable as indicated in Database Setup, at beginning
    
    // This sample starts a transaction and creates a savepoint after
    // inserting one record into MyTable.
    // After inserting the second record it rollsback to the savepoint
    // and commits the transaction. Only the first record will be inserted
 
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    OracleCommand cmd = con.CreateCommand();
 
    // Check the number of rows in MyTable before transaction
    cmd.CommandText = "SELECT COUNT(*) FROM MyTable";    
    int myTableCount = int.Parse(cmd.ExecuteScalar().ToString());
 
    // Print the number of rows in MyTable
    Console.WriteLine("myTableCount = " + myTableCount);
 
    // Start a transaction
    OracleTransaction txn = con.BeginTransaction(
      IsolationLevel.ReadCommitted);
 
    // Insert a row into MyTable
    cmd.CommandText = "INSERT INTO MyTable VALUES (1)";
    cmd.ExecuteNonQuery();
 
    // Create a savepoint
    txn.Save("MySavePoint");
 
    // Insert another row into MyTable
    cmd.CommandText = "insert into mytable values (2)";
    cmd.ExecuteNonQuery();
 
    // Rollback to the savepoint
    txn.Rollback("MySavePoint");
 
    // Commit the transaction
    txn.Commit();
 
    // Check the number of rows in MyTable after transaction
    cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
    myTableCount = int.Parse(cmd.ExecuteScalar().ToString());
 
    // Prints the number of rows, should have increased by 1
    Console.WriteLine("myTableCount = " + myTableCount);
 
    txn.Dispose();
    cmd.Dispose();
 
    con.Close();
    con.Dispose();
  }
}