OracleConnection Class

An OracleConnection object represents a connection to an Oracle database.

Class Inheritance

System.Object

  System.MarshalByRefObject

    System.ComponentModel.Component

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

        Oracle.DataAccess.Client.OracleConnection

Declaration

// ADO.NET 2.0: C#
public sealed class OracleConnection : DbConnection, IDbConnection, ICloneable

// ADO.NET 1.x: C#
public sealed class OracleConnection : Component, IdbConnection, ICloneable

Thread Safety

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

Example

// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class OracleConnectionSample
{
  static void Main()
  {  
    // Connect
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    // Execute a SQL SELECT
    OracleCommand cmd = con.CreateCommand();
    cmd.CommandText = "select * from emp";
    OracleDataReader reader = cmd.ExecuteReader();
 
    // Print all employee numbers
    while (reader.Read())
      Console.WriteLine(reader.GetInt32(0));
 
    // Clean up
    reader.Dispose();
    cmd.Dispose();
    con.Dispose();
  }
}

Requirements

Namespace: Oracle.DataAccess.Client

Assembly: Oracle.DataAccess.dll

Microsoft .NET Framework Version: 1.x or 2.0

---

OracleConnection Members

OracleConnection members are listed in the following tables:

OracleConnection Constructors

OracleConnection constructors are listed in Table 5-17.

OracleConnection Static Properties

The OracleConnection static property is listed in Table 5-19.

OracleConnection Static Methods

The OracleConnection static method is listed in Table 5-19.

OracleConnection Properties

OracleConnection properties are listed in Table 5-20

OracleConnection Public Methods

OracleConnection public methods are listed in Table 5-21.

OracleConnection Events

OracleConnection events are listed in Table 5-22.

---

OracleConnection Constructors

OracleConnection constructors instantiate new instances of the OracleConnection class.

Overload List:

• OracleConnection()

  This constructor instantiates a new instance of the OracleConnection class using default property values.

• OracleConnection(String)

  This constructor instantiates a new instance of the OracleConnection class with the provided connection string.

OracleConnection()

This constructor instantiates a new instance of the OracleConnection class using default property values.

Declaration

// C#
public OracleConnection();

Remarks

The properties for OracleConnection are set to the following default values:

• ConnectionString = empty string

• ConnectionTimeout = 15 (default value of 0 is used for the implicit database connection)

• DataSource = empty string

• ServerVersion = empty string

OracleConnection(String)

This constructor instantiates a new instance of the OracleConnection class with the provided connection string.

Declaration

// C#
public OracleConnection(String connectionString);

Parameters

• connectionString

  The connection information used to connect to the Oracle database.

Remarks

The ConnectionString property is set to the supplied connectionString. The ConnectionString property is parsed and an exception is thrown if it contains invalid connection string attributes or attribute values.

The properties of the OracleConnection object default to the following values unless they are set by the connection string:

• ConnectionString = empty string

• ConnectionTimeout = 15 (default value of 0 is used for the implicit database connection)

• DataSource = empty string

• ServerVersion = empty string

---

OracleConnection Static Properties

The OracleConnection static property is listed in Table 5-23.

IsAvailable

This property indicates whether or the implicit database connection is available for use.

Declaration

// C#
public static bool IsAvailable {get;}

Property Value

Returns true if the implicit database connection is available for use.

Remarks

The availability of the implicit database connection can be checked at runtime through this static property. When Oracle Data Provider for .NET is used within a .NET stored procedure, this property always returns true. Otherwise, false is returned.

To obtain an OracleConnection object in a .NET stored procedure that represents the implicit database connection, set the ConnectionString property of the OracleConnection object to "context connection=true" and invoke the Open method.

Note that not all features that are available for an explicit user connection are available for an implicit database connection. See "Implicit Database Connection" for details.

Example

// C# (Library/DLL)
using System;
using Oracle.DataAccess.Client;
 
public class IsAvailableSample
{
  static void MyStoredProcedure()
  {
    OracleConnection con = new OracleConnection();
    if (OracleConnection.IsAvailable)
    {
      // This function is invoked as a stored procedure
      // Obtain the implicit database connection by setting
      //   "context connection=true" in the connection string
      con.ConnectionString = "context connection=true";
    }
    else
    {
      // This function is not invoked as a stored procedure
      // Set the connection string for a normal client connection
      con.ConnectionString = "user id=scott;password=tiger;data source=oracle";
    }
 
    con.Open();
    Console.WriteLine("connected!");
  }
}

---

OracleConnection Static Methods

The OracleConnection static method is listed in Table 5-24.

ClearPool

This method clears the connection pool that is associated with the provided OracleConnection object.

Declaration

// C#
public static void ClearPool(OracleConnection connection);

Remarks (Oracle.DataAccess.Client only)

When this method is invoked, all idle connections are closed and freed from the pool. Currently used connections remain usable and will be placed back into the pool when the user invokes Close() on the OracleConnection object.

The connection pool is usable once this method is invoked. Therefore, connection requests succeed even after the invocation of this method. Connections created after this method invocation are not cleared unless another invocation is made.

This method can be invoked with an OracleConnection object before opening the connection as well as after, provided the ConnectionString is properly set.

Exceptions

InvalidOperationException – Either the connection pool cannot be found or the provided connection string is invalid.

Example (Oracle.DataAccess.Client only)

// C#
// Sample demonstrating the use of ClearPool API in OracleConnection class
 
using System;
using Oracle.DataAccess.Client;
 
class ClearPoolSample
{
  static void Main()
  {
    Console.WriteLine("Running ClearPool sample..." );
    // Set the connection string
    string strConn = "User Id=scott;Password=tiger;Data Source=oracle;" +
                     "Min pool size=5;";
    OracleConnection conn = new OracleConnection(strConn);
 
    // Open the connection
    conn.Open();
  
    // Clears the connection pool associated with connection 'conn'
    OracleConnection.ClearPool (conn);
 
    // This connection will be placed back into the pool
    conn.Close ();
 
    // Open the connection again to create additional connections in the pool
    conn.Open();
 
    // Create a new connection object
    OracleConnection connNew = new OracleConnection(strConn);
 
    // Clears the pool associated with Connection 'connNew'
    // Since the same connection string is set for both the connections,
    // connNew and conn, they will be part of the same connection pool.
    // We need not do an Open() on the connection object before calling
    // ClearPool
    OracleConnection.ClearPool (connNew);
 
    // cleanup
    conn.Close();
    Console.WriteLine("Done!");
  }
}

ClearAllPools

add this back into the tables, include supported only by client

This method clears all connections from all the connection pools.

Declaration

// C#
public static void ClearAllPools();

Remarks (Oracle.DataAccess.Client only)

This call is analogous to calling ClearPool for all the connection pools that are created for the application.

Exceptions

InvalidOperationException – No connection pool could be found for the application.

Example

// C#
// Sample demonstrating the use of ClearAllPools API in OracleConnection class
 
using System;
using Oracle.DataAccess.Client;
 
class ClearAllPoolsSample
{
  static void Main()
  {
    Console.WriteLine("Running ClearAllPools sample..." );
    // Set the connection string
    string strConn = "User Id=scott;Password=tiger;Data Source=oracle;" +
           "Min pool size=5;";
    OracleConnection conn = new OracleConnection(strConn);
    
    // Create another connection object with a different connection string
    string strConnNew = "User Id=scott;Password=tiger;Data Source=oracle;";
    OracleConnection connNew = new OracleConnection(strConnNew);
 
    // Open the connections. Separate pools are created for conn and connNew
    conn.Open();
    connNew.Open();
    
    // Clears the pools associated with conn and connNew
    OracleConnection.ClearAllPools ();
 
    // cleanup
    conn.Close();
    connNew.Close();
    Console.WriteLine("Done!");
  }
}

---

OracleConnection Properties

OracleConnection properties are listed in Table 5-25

ClientId

This property specifies the client identifier for the connection.

Declaration

// C#
public string ClientId {set;}

Property Value

The string to be used as the client identifier.

Remarks

The default value is null.

Setting ClientId to null resets the client identifier for the connection. Setting ClientId to an empty string sets the client identifier for the connection to an empty string. ClientId is set to null when the Close method is called on the OracleConnection object.

Using the ClientId property allows the application to set the client identifier in the application context for every database session using ODP.NET. This enables ODP.NET developers to configure the Oracle Virtual Private Database (VPD) more easily.

ConnectionString

This property specifies connection information used to connect to an Oracle database.

Declaration

// ADO.NET 2.0: C#
public override string ConnectionString{get; set;}
 

// ADO.NET: 1.x C#
public string ConnectionString{get; set;}
 

Property Value

If the connection string is supplied through the constructor, this property is set to that string.

Implements

IDbConnection

Exceptions

ArgumentException - An invalid syntax is specified for the connection string.

InvalidOperationException - ConnectionString is being set while the connection is open.

Remarks

The default value is an empty string.

ConnectionString must be a string of attribute name and value pairings, separated by a semi-colon, for example:

"User Id=scott;password=tiger;data source=oracle"

If the ConnectionString is not in a proper format, an exception is thrown. All spaces are ignored unless they are within double quotes.

When the ConnectionString property is set, the OracleConnection object immediately parses the string for errors. An ArgumentException is thrown if the ConnectionString contains invalid attributes or invalid values. Attribute values for User Id, Password, Proxy User Id, Proxy Password, and Data Source (if provided) are not validated until the Open method is called.

The connection must be closed to set the ConnectionString property. When the ConnectionString property is reset, all previously set values are reinitialized to their default values before the new values are applied.

Oracle database supports case-sensitive user names. To connect as a user whose name is of mixed case, for example, "MySchema", the User Id attribute value must be surrounded by double quotes, as follows:

"User Id=\"MySchema\";Password=MYPASSWORD;Data Source=oracle"

However, if the Oracle user name is all upper case, the User Id connection string attribute can be set to that user name without the use of the double quotes since User Ids that are not doubled-quoted are converted to all upper case when connecting. Single quotes are not supported.

For a complete example, see "Example".

If a connection string attribute is set more than once, the last setting takes effect and no exceptions are thrown.

Boolean connection string attributes can be set to either true, false, yes, or no.

Remarks (.NET Stored Procedure)

To obtain an OracleConnection object in a .NET stored procedure that represents the implicit database connection, set the ConnectionString property of the OracleConnection object to "context connection=true" and invoke the Open method. Other connection string attributes cannot be used in conjunction with "context connection" when it is set to true.

Supported Connection String Attributes

Table 5-26 lists the supported connection string attributes.

Example

This code example shows that the case of the User Id attribute value is not preserved unless it is surrounded by double quotes. The example also demonstrates when connection pools are created and when connections are drawn from the connection pool.

/* Database Setup: Log on as SYS or SYSTEM that has CREATE USER privilege.
grant connect, resource to "MYSCHEMA" identified by MYPWD;
grant connect, resource to "MySchema" identified by MYPWD;
*/
 
// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class ConnectionStringSample
{
  static long GetSID(OracleConnection con)
  {
    OracleCommand cmd = new OracleCommand();
    cmd.Connection = con;
    cmd.CommandText = "select SYS_CONTEXT('USERENV','SID') from dual";
    object sid = cmd.ExecuteScalar();
    return Convert.ToInt32(sid);
  }
 
  static void Main()
  {
    // To demonstrate whether a connection is obtained from the same 
    // connection pool or not, we check the SID (session id).  If the 
    // session IDs of two connections are the same, that would indicate
    // that the connection is the same and hence the connection was 
    // obtained from the same connection pool.  
    // If the session IDs are different, it could potentially mean that a
    // different connection was obtained from the same connection pool or
    // a different connection is obtained from a different connection pool. 
    // However, since the sample always places the connection
    // back into the pool by disposing it before requesting another
    // connection, a connection with the same ID will be obtained again
    // if a connection is requested from the same connection pool.
 
    string constr1 = "User Id=myschema;Password=mypwd;Data Source=oracle";
    string constr2 = "User Id=MySchema;Password=MyPwd;Data Source=oracle";
    string constr3 = "User Id=\"MYSCHEMA\";Password=MYPWD;Data Source=oracle";
    string constr4 = "User Id=\"MySchema\";Password=MYPWD;Data Source=oracle";
    string constr5 = " User Id=myschema;Password=mypwd;Data Source=oracle; ";
    string constr6 = 
      "User Id=myschema;Password=mypwd;Data Source=oracle;pooling=true";
    long sid0, sid1;
 
    // Connect as "MYSCHEMA/MYPWD"
    // NOTE: the password is case insensitive
    // A new connection and a new connection pool X is created
    OracleConnection con1 = new OracleConnection(constr1);
    con1.Open(); 
    sid0 = GetSID(con1); 
 
    // Place connection back into connection pool X
    con1.Dispose(); 
 
    // Connect as "MYSCHEMA/MYPWD"
    // The connection from pool X is obtained.  No new connection created.
    OracleConnection con2 = new OracleConnection(constr2);
    con2.Open(); 
    sid1 = GetSID(con2);
    if (sid1 == sid0)
      Console.WriteLine("con1 and con2 are from the same connection pool");
    else
      Console.WriteLine("con1 and con2 are from different connection pools");

    // Place connection back into connection pool X
    con2.Dispose();
 
    // Connect as "MYSCHEMA/MYPWD"
    // The connection from pool X is obtained.  No new connection created.
    OracleConnection con3 = new OracleConnection(constr3);
    con3.Open();
    sid1 = GetSID(con3); 
    if (sid1 == sid0)
      Console.WriteLine("con1 and con3 are from the same connection pool");
    else
      Console.WriteLine("con1 and con3 are from different connection pools");

    // Place connection back into connection pool X
    con3.Dispose();
 
    // Connect as "MySchema/MYPWD"
    // A new connection and connection pool Y is created
    OracleConnection con4 = new OracleConnection(constr4);
    con4.Open(); 
    sid1 = GetSID(con4); 
    if (sid1 == sid0)
      Console.WriteLine("con1 and con4 are from the same connection pool");
    else
      Console.WriteLine("con1 and con4 are from different connection pools");
    
    // Place connection back into connection pool Y
    con4.Dispose();
 
    // Connect as "MYSCHEMA/MYPWD"
    // The connection from pool X is obtained
    // Extra spaces or semi-colons in the connection string do not force 
    //   new pools to be created
    OracleConnection con5 = new OracleConnection(constr5);
    con5.Open();
    sid1 = GetSID(con5); 
    if (sid1 == sid0)
      Console.WriteLine("con1 and con5 are from the same connection pool");
    else
      Console.WriteLine("con1 and con5 are from different connection pools");   
  
    // Place connection back into connection pool X
    con5.Dispose();
 
    // Connect as "MYSCHEMA/MYPWD"
    // A connection is obtained from Connection Pool X. 
    // It's important to note that different connection strings do
    // not necessarily mean that ODP.NET will create different 
    // connection pools for them.  In other words, ODP.NET does not
    // use exact string matching algorithm to determine whether 
    // a new connection pool needs to be created or not.
    // Instead, it creates connection pools based on the uniqueness
    // of attribute values settings in the connection string.
    OracleConnection con6 = new OracleConnection(constr6);
    con6.Open();
    sid1 = GetSID(con6); 
    if (sid1 == sid0)
      Console.WriteLine("con1 and con6 are from the same connection pool");
    else
      Console.WriteLine("con1 and con6 are from different connection pools");   
  
    // Place connection back into connection pool X
    con6.Dispose();
  }
}

ConnectionTimeout

This property specifies the maximum amount of time that the Open method can take to obtain a pooled connection before the request is terminated.

Declaration

// ADO.NET 2.0: C#
public override int ConnectionTimeout {get;}

// ADO.NET 1.x: C#
public int ConnectionTimeout {get;}

Property Value

The maximum time allowed for a pooled connection request, in seconds.

Implements

IDbConnection

Remarks

The default value is 15.

Setting this property to 0 allows the pooled connection request to wait for a free connection without a time limit. The timeout takes effect only for pooled connection requests and not for new connection requests.

Remarks (.NET Stored Procedure)

There is no connection string specified by the application and a connection on the implicit database is always available, therefore, this property is set to 0.

Database

This property is not supported.

Declaration

// ADO.NET 2.0: C#
public override string Database {get;}

// ADO.NET 1.x: C#
public string Database {get;}

Property Value

A string.

Implements

IDbConnection.Database

Remarks

This property is not supported. It always returns an empty string.

DataSource

This property specifies the Oracle Net Services Name, Connect Descriptor, or an easy connect naming that identifies the database to which to connect

Declaration

// ADO.NET 2.0: C#
public override string DataSource {get;}

// ADO.NET 1.x: C#
public string DataSource {get;}

Property Value

Oracle Net Services Name, Connect Descriptor, or an easy connect naming that identifies the database to which to connect.

Remarks (.NET Stored Procedure)

The value of this property is always an empty string for the implicit database connection.

ServerVersion

This property specifies the version number of the Oracle database to which the OracleConnection has established a connection.

Declaration

// ADO.NET 2.0: C#
public override string ServerVersion {get;}

// ADO.NET 1.x: C#
public string ServerVersion {get;}

Property Value

The version of the Oracle database.

Exceptions

InvalidOperationException - The connection is closed.

Remarks

The default is an empty string.

State

This property specifies the current state of the connection.

Declaration

// ADO.NET 2.0: C#
public override ConnectionState State {get;}

// ADO.NET 1.x: C#
public ConnectionState State {get;}

Property Value

The ConnectionState of the connection.

Implements

IDbConnection

Remarks

ODP.NET supports ConnectionState.Closed and ConnectionState.Open for this property. The default value is ConnectionState.Closed.

---

OracleConnection Public Methods

OracleConnection public methods are listed in Table 5-27.

BeginTransaction

BeginTransaction methods begin local transactions.

Overload List

• BeginTransaction()

  This method begins a local transaction.

• BeginTransaction(IsolationLevel)

  This method begins a local transaction with the specified isolation level.

BeginTransaction()

This method begins a local transaction.

Declaration

// C#
public OracleTransaction BeginTransaction();

Return Value

An OracleTransaction object representing the new transaction.

Implements

IDbConnection

Exceptions

InvalidOperationException - A transaction has already been started.

Remarks

The transaction is created with its isolation level set to its default value of IsolationLevel.ReadCommitted. All further operations related to the transaction must be performed on the returned OracleTransaction object.

Remarks (.NET Stored Procedure)

Using this method causes a Not Supported exception.

BeginTransaction(IsolationLevel)

This method begins a local transaction with the specified isolation level.

Declaration

// C#
public OracleTransaction BeginTransaction(IsolationLevel isolationLevel);

Parameters

• isolationLevel

  The isolation level for the new transaction.

Return Value

An OracleTransaction object representing the new transaction.

Implements

IDbConnection

Exceptions

InvalidOperationException - A transaction has already been started.

ArgumentException - The isolationLevel specified is invalid.

Remarks

The following two isolation levels are supported:

• IsolationLevel.ReadCommitted

• IsolationLevel.Serializable

Requesting other isolation levels causes an exception.

Remarks (.NET Stored Procedure)

Using this method causes a Not Supported exception.

Example

// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class BeginTransactionSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    // Create an OracleCommand object using the connection object
    OracleCommand cmd = con.CreateCommand();
 
    // Start a transaction
    OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);
 
    // Update EMP table
    cmd.CommandText = "update emp set sal = sal + 100";
    cmd.ExecuteNonQuery();
 
    // Rollback transaction
    txn.Rollback();
    Console.WriteLine("Transaction rolledback");
 
    // Clean up
    txn.Dispose();
    cmd.Dispose();
    con.Dispose();
  }
}

ChangeDatabase

This method is not supported.

Declaration

// ADO.NET 2.0: C#
public override void ChangeDatabase(string databaseName);

// ADO.NET 1.x: C#
public void ChangeDatabase(string databaseName);

Parameters

• databaseName

  The name of the database that replaces the current database name.

Implements

IDbConnection.ChangeDatabase

Exceptions

NotSupportedException - Method not supported.

Remarks

This method is not supported and throws a NotSupportedException if invoked.

Clone

This method creates a copy of an OracleConnection object.

Declaration

// C#
public object Clone();

Return Value

An OracleConnection object.

Implements

ICloneable

Remarks

The cloned object has the same property values as that of the object being cloned.

Remarks (.NET Stored Procedure)

This method is not supported for an implicit database connection.

Example

// C#
 
using System;
using Oracle.DataAccess.Client; 
 
class CloneSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    // Need a proper casting for the return value when cloned
    OracleConnection clonedCon = (OracleConnection)con.Clone();
 
    // Cloned connection is always closed, regardless of its source,
    //   But the connection string should be identical
    clonedCon.Open();
    if (clonedCon.ConnectionString.Equals(con.ConnectionString)) 
      Console.WriteLine("The connection strings are the same.");
    else
      Console.WriteLine("The connection strings are different.");
 
    // Close and Dispose OracleConnection object
    clonedCon.Dispose();
  }
}

Close

This method closes the connection to the database.

Declaration

// ADO.NET 2.0: C#
public override void Close();

// ADO.NET 1.x: C#
public void Close();

Implements

IDbConnection

Remarks

Performs the following:

• Rolls back any pending transactions.

• Places the connection to the connection pool if connection pooling is enabled. Even if connection pooling is enabled, the connection can be closed if it exceeds the connection lifetime specified in the connection string. If connection pooling is disabled, the connection is closed.

• Closes the connection to the database.

The connection can be reopened using Open().

CreateCommand

This method creates and returns an OracleCommand object associated with the OracleConnection object.

Declaration

// C#
public OracleCommand CreateCommand();

Return Value

The OracleCommand object.

Implements

IDbConnection

Example

// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class CreateCommandSample
{
  static void Main()
  {  
    // Connect
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    // Execute a SQL SELECT
    OracleCommand cmd = con.CreateCommand();
    cmd.CommandText = "select * from emp";
    OracleDataReader reader = cmd.ExecuteReader();
 
    // Print all employee numbers
    while (reader.Read())
      Console.WriteLine(reader.GetInt32(0));
 
    // Clean up
    reader.Dispose();
    cmd.Dispose();
    con.Dispose();
  }
}

EnlistDistributedTransaction

This method enables applications to explicitly enlist in a specific distributed transaction after a connection has been opened.

Declaration

// C#
public void EnlistDistributedTransaction(ITransaction transaction);

Parameters

• transaction

  An ITransaction interface.

Exceptions

InvalidOperationException - The connection is part of a local transaction or the connection is closed.

Remarks

EnlistDistributedTransaction enables objects to enlist in a specific transaction that is passed to the method. The ITransaction interface can be obtained by applying an (ITransaction) cast to the ContexUtil.Transaction property within the component that started the distributed transaction.

The connection must be open before calling this method or an InvalidOperationException is thrown.

If a connection is part of a local transaction that was started implicitly or explicitly while attempting to enlist in a distributed transaction, the local transaction is rolled back and an exception is thrown.

By default, distributed transactions roll back, unless the method-level AutoComplete declaration is set.

Invoking the commit on the ITranasction raises an exception.

Invoking the rollback on the ITransaction method and calling ContextUtil.SetComplete on the same distributed transaction raises an exception.

Remarks (.NET Stored Procedure)

Using this method causes a Not Supported exception.

Example

Application:

// C#
 
/* This is the class that will utilize the Enterprise Services 
   component.  This module needs to be built as an executable.
   
   The Enterprise Services Component DLL must be built first 
   before building this module. 
   In addition, the DLL needs to be referenced appropriately 
   when building this application. 
*/
 
using System;
using System.EnterpriseServices;
using DistribTxnSample;
 
class DistribTxnSample_App
{
  static void Main()
  {
    DistribTxnSample_Comp comp = new DistribTxnSample_Comp();
    comp.DoWork();
  }
}

Component:

// C#
 
/* This module needs to be
   1) built as a component DLL/Library
   2) built with a strong name
   
  This library must be built first before the application is built.
*/ 
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
using System.EnterpriseServices;
 
namespace DistribTxnSample
{
  [Transaction(TransactionOption.RequiresNew)]
  public class DistribTxnSample_Comp : ServicedComponent
  {
    public void DoWork()
    {   
      string constr = 
        "User Id=scott;Password=tiger;Data Source=oracle;enlist=false";
      OracleConnection con = new OracleConnection(constr);
      con.Open();
 
      // Enlist in a distrubuted transaction
      con.EnlistDistributedTransaction((ITransaction)ContextUtil.Transaction);
 
      // Update EMP table
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "UPDATE emp set sal = sal + .01";
      cmd.ExecuteNonQuery();
 
      // Commit
      ContextUtil.SetComplete();
 
      // Dispose OracleConnection object
      con.Dispose();
    }
  }
}

EnlistTransaction

This method enlists the connection to the specified transaction.

Supported Only in ADO.NET 2.0-Compliant ODP.NET

Declaration

// C#
public override void EnlistTransaction(Transaction transaction)
 

Parameters

• transaction

  A System.Transactions.Transaction object.

Exceptions

InvalidOperationException - The connection is part of a local transaction or the connection is closed.

Remarks

Invocation of this method immediately enlists the connection to a distributed transaction that is specified by the provided transaction parameter.

If OracleConnection is still associated with a distributed transaction that has not completed from a previous EnlistTransaction method invocation, calling this method will cause an exception to be thrown.

In general, for distributed transaction enlistments to succeed, the "enlist" connection string attribute must be set to either "true" or "dynamic" before invoking the Open method. Setting the "enlist" connection string attribute to "true" will implicitly enlist the connection when the Open method is called, if the connection is within a transaction context. Setting it to "dynamic" allows the connection to dynamically enlist in distributed transactions when an EnlistTransaction or EnlistDistributedTransaction method is called. The "enlist" attribute should be set to "false" only if the connection will never enlist in a distributed transaction.

GetSchema

GetSchema methods return schema information for the data source of the OracleConnection.

Supported Only in ADO.NET 2.0-Compliant ODP.NET

Overload List

• GetSchema()

  This method returns schema information for the data source of the OracleConnection.

• GetSchema (string collectionName)

  This method returns schema information for the data source of the OracleConnection using the specified string for the collection name.

• GetSchema (string collectionName, string[] restrictions)

  This method returns schema information for the data source of the OracleConnection using the specified string for the collection name and the specified string array for the restriction values.

GetSchema()

This method returns schema information for the data source of the OracleConnection.

Declaration

// ADO.NET 2.0: C#
public override DataTable GetSchema();
 

Return Value

A DataTable object.

Exceptions

InvalidOperationException – The connection is closed.

Remarks

This method returns a DataTable object that contains a row for each metadata collection available from the database.

The method is equivalent to specifying the String value "MetaDataCollections" when using the GetSchema(String) method.

Example

// C#
 
using System;
using System.Data;
using System.Data.Common;
using Oracle.DataAccess.Client;
 
class GetSchemaSample
{
  static void Main(string[] args)
  {
    string constr = "User Id=scott; Password=tiger; Data Source=oracle;";
    string ProviderName = "Oracle.DataAccess.Client";
 
    DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
 
    using (DbConnection conn = factory.CreateConnection())
    {
      try
      {
        conn.ConnectionString = constr;
        conn.Open();
 
        //Get all the schema collections and write to an XML file. 
        //The XML file name is Oracle.DataAccess.Client_Schema.xml
        DataTable dtSchema = conn.GetSchema();
        dtSchema.WriteXml(ProviderName + "_Schema.xml");
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
        Console.WriteLine(ex.StackTrace);
      }
    }
  }
}

GetSchema (string collectionName)

This method returns schema information for the data source of the OracleConnection using the specified string for the collection name.

Declaration

// ADO.NET 2.0: C#
public override DataTable GetSchema (string collectionName);

Parameters

collectionName

Name of the collection for which metadata is required.

Return Value

A DataTable object.

Exceptions

ArgumentException – The requested collection is not defined.

InvalidOperationException – The connection is closed.

InvalidOperationException – The requested collection is not supported by current version of Oracle database.

InvalidOperationException – No population string is specified for requested collection.

Example

// C#
 
using System;
using System.Data;
using System.Data.Common;
using Oracle.DataAccess.Client;
 
class GetSchemaSample
{
  static void Main(string[] args)
  {
    string constr = "User Id=scott; Password=tiger; Data Source=oracle;";
    string ProviderName = "Oracle.DataAccess.Client";
 
    DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
 
    using (DbConnection conn = factory.CreateConnection())
    {
      try
      {
        conn.ConnectionString = constr;
        conn.Open();
 
        //Get MetaDataCollections and write to an XML file.
        //This is equivalent to GetSchema()
        DataTable dtMetadata =
          conn.GetSchema(DbMetaDataCollectionNames.MetaDataCollections);
        dtMetadata.WriteXml(ProviderName + "_MetaDataCollections.xml");
 
        //Get Restrictions and write to an XML file.
        DataTable dtRestrictions =
          conn.GetSchema(DbMetaDataCollectionNames.Restrictions);
        dtRestrictions.WriteXml(ProviderName + "_Restrictions.xml");
 
        //Get DataSourceInformation and write to an XML file.
        DataTable dtDataSrcInfo =
          conn.GetSchema(DbMetaDataCollectionNames.DataSourceInformation);
        dtDataSrcInfo.WriteXml(ProviderName + "_DataSourceInformation.xml");
 
        //data types and write to an XML file.
        DataTable dtDataTypes =
          conn.GetSchema(DbMetaDataCollectionNames.DataTypes);
        dtDataTypes.WriteXml(ProviderName + "_DataTypes.xml");
 
        //Get ReservedWords and write to an XML file.
        DataTable dtReservedWords =
          conn.GetSchema(DbMetaDataCollectionNames.ReservedWords);
        dtReservedWords.WriteXml(ProviderName + "_ReservedWords.xml");
 
        //Get all the tables and write to an XML file.
        DataTable dtTables = conn.GetSchema("Tables");
        dtTables.WriteXml(ProviderName + "_Tables.xml");
 
        //Get all the views and write to an XML file.
        DataTable dtViews = conn.GetSchema("Views");
        dtViews.WriteXml(ProviderName + "_Views.xml");
 
        //Get all the columns and write to an XML file.
        DataTable dtColumns = conn.GetSchema("Columns");
        dtColumns.WriteXml(ProviderName + "_Columns.xml");
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
        Console.WriteLine(ex.StackTrace);
      }
    }
  }
}

GetSchema (string collectionName, string[] restrictions)

This method returns schema information for the data source of the OracleConnection using the specified string for the collection name and the specified string array for the restriction values.

Declaration

// ADO.NET 2.0: C#
public override DataTable GetSchema (string collectionName, 
    string[] restrictions);
 

Parameters

• collectionName

  The name of the collection of metadata being retrieved.

• restrictions

  An array of restrictions that apply to the metadata being retrieved.

Return Value

A DataTable object.

Exception

• ArgumentException – The requested collection is not defined.

• InvalidOperationException – One of the following conditions exist:

  • The connection is closed.

  • The requested collection is not supported by the current version of Oracle database.

  • More restrictions were provided than the requested collection supports.

  • No population string is specified for requested collection.

Remarks

This method takes the name of a metadata collection and an array of String values that specify the restrictions for filtering the rows in the returned DataTable. This returns a DataTable that contains only rows from the specified metadata collection that match the specified restrictions.

For example, if the Columns collection has three restrictions (owner, tablename, and columnname), to retrieve all the columns for the EMP table regardless of schema, the GetSchema method must pass in at least these values: null, EMP.

If no restriction value is passed in, default values are used for that restriction, which is the same as passing in null. This differs from passing in an empty string for the parameter value. In this case, the empty string ("") is considered the value for the specified parameter.

collectionName is not case-sensitive, but restrictions (string values) are.

Example

// C#
 
using System;
using System.Data;
using System.Data.Common;
using Oracle.DataAccess.Client;
 
class GetSchemaSample
{
  static void Main(string[] args)
  {
    string constr = "User Id=scott; Password=tiger; Data Source=oracle;";
    string ProviderName = "Oracle.DataAccess.Client";
 
    DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
 
    using (DbConnection conn = factory.CreateConnection())
    {
      try
      {
        conn.ConnectionString = constr;
        conn.Open();
 
        //Get Restrictions
        DataTable dtRestrictions =
          conn.GetSchema(DbMetaDataCollectionNames.Restrictions);
        
        DataView dv = dtRestrictions.DefaultView;
 
        dv.RowFilter = "CollectionName = 'Columns'";
        dv.Sort = "RestrictionNumber";
 
        for (int i = 0; i < dv.Count; i++)
          Console.WriteLine("{0} (default) {1}" , 
                            dtRestrictions.Rows[i]["RestrictionName"], 
                            dtRestrictions.Rows[i]["RestrictionDefault"]);
 
        //Set restriction string array
        string[] restrictions = new string[3];
 
        //Get all columns from all tables owned by "SCOTT"
        restrictions[0] = "SCOTT";
        DataTable dtAllScottCols = conn.GetSchema("Columns", restrictions);
 
        // clear collection
        for (int i = 0; i < 3; i++)
          restrictions[i] = null;
 
        //Get all columns from all tables named "EMP" owned by any 
        //owner/schema
        restrictions[1] = "EMP";
        DataTable dtAllEmpCols = conn.GetSchema("Columns", restrictions);
 
        // clear collection
        for (int i = 0; i < 3; i++)
          restrictions[i] = null;
 
        //Get columns named "EMPNO" from tables named "EMP", 
        //owned by any owner/schema
        restrictions[1] = "EMP";
        restrictions[2] = "EMPNO";
        DataTable dtAllScottEmpCols = conn.GetSchema("Columns", restrictions);
 
        // clear collection
        for (int i = 0; i < 3; i++)
          restrictions[i] = null;
 
        //Get columns named "EMPNO" from all
        //tables, owned by any owner/schema
        restrictions[2] = "EMPNO";
        DataTable dtAllEmpNoCols = conn.GetSchema("Columns", restrictions);
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
        Console.WriteLine(ex.Source);
      }
    }
  }
}

GetSessionInfo

GetSessionInfo returns or refreshes an OracleGlobalization object that represents the globalization settings of the session.

Overload List:

• GetSessionInfo()

  This method returns a new instance of the OracleGlobalization object that represents the globalization settings of the session.

• GetSessionInfo(OracleGlobalization)

  This method refreshes the provided OracleGlobalization object with the globalization settings of the session.

GetSessionInfo()

This method returns a new instance of the OracleGlobalization object that represents the globalization settings of the session.

Declaration

// C#
public OracleGlobalization GetSessionInfo();

Return Value

The newly created OracleGlobalization object.

Example

// C#
 
using System;
using Oracle.DataAccess.Client;
 
class GetSessionInfoSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    // Get session info from connection object
    OracleGlobalization info = con.GetSessionInfo();
 
    // Update session info
    info.DateFormat = "YYYY-MM-DD";
    con.SetSessionInfo(info);
 
    // Execute SQL SELECT
    OracleCommand cmd = con.CreateCommand();
    cmd.CommandText = "select TO_CHAR(hiredate) from emp";
    Console.WriteLine("Hire Date ({0}): {1}",
      info.DateFormat, cmd.ExecuteScalar());
 
    // Clean up
    cmd.Dispose();
    con.Dispose();
  }
}

GetSessionInfo(OracleGlobalization)

This method refreshes the provided OracleGlobalization object with the globalization settings of the session.

Declaration

// C#
public void GetSessionInfo(OracleGlobalization oraGlob);

Parameters

• oraGlob

  The OracleGlobalization object to be updated.

Open

This method opens a connection to an Oracle database.

Declaration

// ADO.NET 2.0: C#
public overide void Open();

// ADO.NET 1.x: C#
public void Open();

Implements

IDbConnection

Exceptions

ObjectDisposedException - The object is already disposed.

InvalidOperationException - The connection is already opened or the connection string is null or empty.

Remarks

The connection is obtained from the pool if connection pooling is enabled. Otherwise, a new connection is established.

It is possible that the pool does not contain any unused connections when the Open() method is invoked. In this case, a new connection is established.

If no connections are available within the specified connection timeout value, when the Max Pool Size is reached, an OracleException is thrown.

OpenWithNewPassword

This method opens a new connection with the new password.

Declaration

// C#
public void OpenWithNewPassword(string newPassword);

Parameters

• newPassword

  A string that contains the new password.

Remarks

This method uses the ConnectionString property settings to establish a new connection. The old password must be provided in the connection string as the Password attribute value.

This method can only be called on an OracleConnection in the closed state.

Remarks (.NET Stored Procedure)

This method is not supported with an implicit database connection.

PurgeStatementCache

This method flushes the statement cache by closing all open cursors on the database, when statement caching is enabled.

Declaration

// C#
public void PurgeStatementCache();

Remarks

Flushing the statement cache repetitively results in decreased performance and may negate the performance benefit gained by enabling the statement cache.

Statement caching remains enabled after the call to PurgeStatementCache.

Invocation of this method purges the cached cursors that are associated with the OracleConnection. It does not purge all the cached cursors in the database.

Example

// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class PurgeStatementCacheSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle;" +
      "Statement Cache Size=20";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    OracleCommand cmd = new OracleCommand("select * from emp", con);
    cmd.CommandType = CommandType.Text;
    OracleDataReader reader = cmd.ExecuteReader();
 
    // Purge Statement Cache
    con.PurgeStatementCache();
 
    // Close and Dispose OracleConnection object
    Console.WriteLine("Statement Cache Flushed");
    con.Close();
    con.Dispose();
  }
}

SetSessionInfo

This method alters the session's globalization settings with all the property values specified in the provided OracleGlobalization object.

Declaration

// C#
public void SetSessionInfo(OracleGlobalization oraGlob);

Parameters

• oraGlob

An OracleGlobalization object.

Remarks

Calling this method is equivalent to calling an ALTER SESSION SQL on the session.

Example

// C#
 
using System;
using Oracle.DataAccess.Client;
 
class SetSessionInfoSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    // Get session info from connection object
    OracleGlobalization info = con.GetSessionInfo();
 
    // Update session info
    info.DateFormat = "YYYY-MM-DD";
    con.SetSessionInfo(info);
 
    // Execute SQL SELECT
    OracleCommand cmd = con.CreateCommand();
    cmd.CommandText = "select TO_CHAR(hiredate) from emp";
    Console.WriteLine("Hire Date ({0}): {1}",
      info.DateFormat, cmd.ExecuteScalar());
 
    // Clean up
    cmd.Dispose();
    con.Dispose();
  }
}

---

OracleConnection Events

OracleConnection events are listed in Table 5-28.

Failover

This event is triggered when an Oracle failover occurs.

Declaration

// C#
public event OracleFailoverEventHandler Failover;

Event Data

The event handler receives an OracleFailoverEventArgs object which exposes the following properties containing information about the event.

• FailoverType

  Indicates the type of the failover.

• FailoverEvent

  Indicates the state of the failover.

Remarks

The Failover event is raised when a connection to an Oracle instance is unexpectedly severed. The client should create an OracleFailoverEventHandler delegate to listen to this event.

InfoMessage

This event is triggered for any message or warning sent by the database.

Declaration

// C#
public event OracleInfoMessageEventHandler InfoMessage;

Event Data

The event handler receives an OracleInfoMessageEventArgs object which exposes the following properties containing information about the event.

• Errors

  The collection of errors generated by the data source.

• Message

  The error text generated by the data source.

• Source

  The name of the object that generated the error.

Remarks

In order to respond to warnings and messages from the database, the client should create an OracleInfoMessageEventHandler delegate to listen to this event.

StateChange

This event is triggered when the connection state changes.

Declaration

// ADO.NET 2.0: C#
public override event StateChangeEventHandler StateChange;

// ADO.NET 1.x: C#
public event StateChangeEventHandler StateChange;

Event Data

The event handler receives a StateChangeEventArgs object which exposes the following properties containing information about the event.

• CurrentState

  The new state of the connection.

• OriginalState

  The original state of the connection.

Remarks

The StateChange event is raised after a connection changes state, whenever an explicit call is made to Open, Close or Dispose.