OracleTransaction Class

An OracleTransaction object represents a local transaction.

Class Inheritance

System.Object

  System.MarshalByRefObject

    System.Data.Common.DbTransaction (ADO.NET 2.0 only)

      Oracle.DataAccess.Client.OracleTransaction

Declaration

// ADO.NET 2.0: C#
public sealed class OracleTransaction : DbTransaction

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

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();
  }
}

Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

ODP.NET Version: ODP.NET for .NET Framework 2.0 or ODP.NET for .NET Framework 4

Comment: 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 5-115.

OracleTransaction Properties

OracleTransaction properties are listed in Table 5-116.

OracleTransaction Public Methods

OracleTransaction public methods are listed in Table 5-117.

---

OracleTransaction Static Methods

The OracleTransaction static method is listed in Table 5-118.

---

OracleTransaction Properties

OracleTransaction properties are listed in Table 5-119.

IsolationLevel

This property specifies the isolation level for the transaction.

Declaration

// ADO.NET 2.0: 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 5-120.

Commit

This method commits the database transaction.

Declaration

// ADO.NET 2.0: 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.

• Rollback(string)

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

Rollback()

This method rolls back a database transaction.

Declaration

// ADO.NET 2.0: 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

// ADO.NET 2.0: 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();
  }
}