OracleConnection Class

An OracleConnection object represents a connection to an Oracle database.

Class Inheritance

Object

  MarshalByRefObject

    Component

      OracleConnection

Declaration

// 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

---

OracleConnection Members

OracleConnection members are listed in the following tables:

OracleConnection Constructors

OracleConnection constructors are listed in Table 4-19.

OracleConnection Static Methods

OracleConnection static methods are listed in Table 4-20.

OracleConnection Properties

OracleConnection properties are listed in Table 4-21

OracleConnection Public Methods

OracleConnection public methods are listed in Table 4-22.

OracleConnection Events

OracleConnection events are listed in Table 4-23.

OracleConnection Event Delegates

OracleConnection event delegates are listed in Table 4-24.

---

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

• 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

• DataSource = empty string

• ServerVersion = empty string

---

OracleConnection Static Methods

OracleConnection static methods are listed in Table 4-25.

---

OracleConnection Properties

OracleConnection properties are listed in Table 4-26

ConnectionString

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

Declaration

// 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.

The 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.

Supported connection string attributes:

Table 4-27 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
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 void Main()
  {
    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";
 
    // 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(); 
 
    // 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(); 
 
    // 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();
 
    // 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(); 
 
    // 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(); 
 
    // 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 alogrithm 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();
 
    // 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 terminating the request.

Declaration

// 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.

DataSource

This property specifies the Oracle Net Service Name (formerly known as TNS alias) that identifies an Oracle database instance.

Declaration

// C#
public string DataSource {get;}

Property Value

The Oracle Net Service Name.

Remarks

The default value of this property is an empty string

ServerVersion

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

Declaration

// C#
public string ServerVersion {get;}

Property Value

The version of the Oracle database, for example "9.2.0.1.0."

Exceptions

InvalidOperationException - The connection is closed.

Remarks

The default is an empty string.

State

This property specifies the current state of the connection.

Declaration

// 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 4-28.

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.

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.

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

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.

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();
 
    // Close and Dispose OracleConnection object
    clonedCon.Dispose();
  }
}

Close

This method closes the connection to the database.

Declaration

// 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

Exception

InvalidOperationException - The connection is part of a local transaction.

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.

Example

Application:

// C#
 
// This is the class that will utilize the Enterprise Services 
//   component.  This module needs to be built as an executable.
 
using System;
using System.EnterpriseServices;
using ConnectionSample_Comp;
 
class EnlistDistributedTransactionSample_App
{
  static void Main()
  {
    ESComponent comp = new ESComponent();
    comp.DoWork();
  }
}

Component:

// C#
 
// This module needs to be
//   1) built as a component DLL/Library
//   2) built with a strong name 
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
using System.EnterpriseServices;
 
[Transaction(TransactionOption.RequiresNew)]
public class EnlistDistributedTransactionSample_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();	
  }
}

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();
 
    // 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());
 
    // Update session info
    info.DateFormat = "YYYY-MM-DD";
    con.SetSessionInfo(info);
 
    // Execute SQL SELECT again
    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

// 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.

PurgeStatementCache

This method flushes the Statement Cache by closing all open cursors on the server, 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.

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();
 
    // 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());
 
    // Update session info
    info.DateFormat = "YYYY-MM-DD";
    con.SetSessionInfo(info);
 
    // Execute SQL SELECT again
    Console.WriteLine("Hire Date ({0}): {1}", 
      info.DateFormat, cmd.ExecuteScalar());
 
    // Clean up
    cmd.Dispose();
    con.Dispose();	
  }
}

---

OracleConnection Events

OracleConnection events are listed in Table 4-29.

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.

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

// 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.

---

OracleConnection Event Delegates

OracleConnection event delegates are listed in Table 4-30.

OracleFailoverEventHandler

This event delegate handles the Failover event.

OracleInfoMessageEventHandler

This event delegate handles the InfoMessage event.

StateChangeEventHandler

This event delegate handles the StateChange event.