An OracleTransaction
object represents a local transaction.
Class Inheritance
Object
MarshalByRefObject
OracleTransaction
Declaration
// 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 one of the following two modes:
Read Committed (default)
Serializable
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
Comment: Not supported in a .NET stored procedure
OracleTransaction
members are listed in the following tables:
OracleTransaction Static Methods
The OracleTransaction
static method is listed in Table 5-96.
Table 5-96 OracleTransaction Static Method
Method | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleTransaction Properties
OracleTransaction
properties are listed in Table 5-97.
Table 5-97 OracleTransaction Properties
Name | 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 5-98.
Table 5-98 OracleTransaction Public Methods
Public Method | Description |
---|---|
Commit | Commits the database transaction |
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose | Frees the resources used by the OracleTransaction object |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
Rollback | Rolls back a database transaction (Overloaded) |
Save | Creates a savepoint within the current transaction |
ToString |
Inherited from Object |
The OracleTransaction
static method is listed in Table 5-99.
Table 5-99 OracleTransaction Static Method
Method | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleTransaction
properties are listed in Table 5-100.
Table 5-100 OracleTransaction Properties
Name | Description |
---|---|
IsolationLevel | Specifies the isolation level for the transaction |
Connection | Specifies the connection that is associated with the transaction |
This property specifies the isolation level for the transaction.
Declaration
// C# public IsolationLevel IsolationLevel {get;}
Property Value
IsolationLevel
Implements
IDbTransaction
Exceptions
InvalidOperationException
- The transaction has already completed.
Remarks
Default = IsolationLevel.ReadCommitted
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 are listed in Table 5-101.
Table 5-101 OracleTransaction Public Methods
Public Method | Description |
---|---|
Commit | Commits the database transaction |
CreateObjRef |
Inherited from MarshalByRefObject |
Dispose | Frees the resources used by the OracleTransaction object |
Equals |
Inherited from Object (Overloaded) |
GetHashCode |
Inherited from Object |
GetLifetimeService |
Inherited from MarshalByRefObject |
GetType |
Inherited from Object |
InitializeLifetimeService |
Inherited from MarshalByRefObject |
Rollback | Rolls back a database transaction (Overloaded) |
Save | Creates a savepoint within the current transaction |
ToString |
Inherited from Object |
This method commits the database transaction.
Declaration
// C# public 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(); } }
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
rolls back a database transaction.
Overload List:
This method rolls back a database transaction.
This method rolls back a database transaction to a savepoint within the current transaction.
This method rolls back a database transaction.
Declaration
// C# public 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(); } }
This method rolls back a database transaction to a savepoint within the current transaction.
Declaration
// C#
public 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.
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(); } }