OracleTransaction Class

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.

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.

• 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

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