Oracle.DataAccess.Client Namespace, 21 of 30

OracleTransaction Class

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

// C#
//  Starts a transaction and inserts one record. If insert fails, rolls back
//  the transaction. Otherwise, commits the transaction.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
     "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Create an OracleCommand object using the connection object
OracleCommand cmd = new OracleCommand("", con);

// Start a transaction
OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);

try
  {
    cmd.CommandText = "insert into mytable values (99, 'foo')";
    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();
    txn.Commit();
    Console.WriteLine("One record is inserted into the database table.");
  }
    catch(Exception e)
  {
    txn.Rollback();
    Console.WriteLine("No record was inserted into the database table.");
  }
...

Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

See Also:

OracleTransaction Members

OracleTransaction members are listed in the following tables:

OracleTransaction Static Methods

OracleTransaction static methods are listed in Table 4-112.

Table 4-112 OracleTransaction Static Methods

Methods	Description
`Equals`	Inherited from `Object` (Overloaded)

OracleTransaction Properties

OracleTransaction properties are listed in Table 4-113.

Table 4-113 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 4-114.

Table 4-114 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`

See Also:

OracleTransaction Static Methods

OracleTransaction static methods are listed in Table 4-115.

Table 4-115 OracleTransaction Static Methods

Methods	Description
`Equals`	Inherited from `Object` (Overloaded)

See Also:

OracleTransaction Properties

OracleTransaction properties are listed in Table 4-116.

Table 4-116 OracleTransaction Properties

Name	Description
IsolationLevel	Specifies the isolation level for the transaction
Connection	Specifies the connection that is associated with the transaction

See Also:

IsolationLevel

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

See Also:

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.

See Also:

OracleTransaction Public Methods

OracleTransaction public methods are listed in Table 4-117.

Table 4-117 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`

See Also:

Commit

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

// C#
//  Starts a transaction and inserts one record. If insert fails, rolls back
//  the transaction. Otherwise, commits the transaction.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
     "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

//Create an OracleCommand object using the connection object
OracleCommand cmd = new OracleCommand("", con);

// Start a transaction
OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);

try
  {
    cmd.CommandText = "insert into mytable values (99, 'foo')";
    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();
    txn.Commit();
    Console.WriteLine("One record was inserted into the database table.");
  }
    catch(Exception e)
  {
    txn.Rollback();
    Console.WriteLine("No record was inserted into the database table.");
  }
...

See Also:

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.

See Also:

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.
See Also:

Oracle.DataAccess.Client Namespace

OracleTransaction Class

OracleTransaction Members

Rollback()

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

// C#
//  Starts a transaction and inserts one record. Then rolls back the
//  transaction.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
   "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

OracleCommand cmd = Con.CreateCommand();

// Start a transaction
OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);

cmd.CommandText = "insert into mytable values (99, 'foo')";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
txn.Rollback();
Console.WriteLine("Nothing was inserted into the database table.");
...

See Also:

Rollback(string)

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.

See Also:

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

// C#
//  Starts a transaction and inserts two records. Creates a savepoint
//  within the current transaction for the first insert. Then rolls back to 
//  the savepoint to commit the first record.

...
string ConStr = "User Id=myschema;Password=mypassword;" +
     "Data Source=oracle;";
OracleConnection con = new OracleConnection(ConStr);
con.Open();

OracleCommand cmd = Con.CreateCommand();

// Start a transaction
OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);

cmd.CommandText = "insert into mytable values (99, 'foo')";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

//Create a savepoint
txn.Save("MySavePoint");

cmd.CommandText = "insert into mytable values (100, 'bar')";
cmd.ExecuteNonQuery();

//Rollback to the savepoint
txn.Rollback("MySavePoint");

//Commit the first insert
txn.Commit();

See Also: