Skip Headers
Oracle® Data Provider for .NET Developer's Guide
11g Release 1 (11.1.0.6.20)

Part Number E10927-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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.0 or later


OracleConnection Members

OracleConnection members are listed in the following tables.

OracleConnection Constructors

OracleConnection constructors are listed in Table 5-17.

Table 5-17 OracleConnection Constructors

Constructor Description

OracleConnection Constructors

Instantiates a new instance of the OracleConnection class (Overloaded)


OracleConnection Static Properties

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

Table 5-18 OracleConnection Static Property

Property Description

IsAvailable

Indicates whether or not the implicit database connection is available for use


OracleConnection Static Methods

The OracleConnection static methods are listed in Table 5-19.

Table 5-19 OracleConnection Static Methods

Method Description

Equals

Inherited from System.Object (Overloaded)

ClearPool

Clears the connection pool that is associated with the provided OracleConnection object.

Not supported in a .NET stored procedure

ClearAllPools

Clears all connections from all the connection pools

Not supported in a .NET stored procedure


OracleConnection Properties

OracleConnection properties are listed in Table 5-20

Table 5-20 OracleConnection Properties

Property Description

ClientId

Specifies the client identifier for the connection

ConnectionString

Specifies connection information used to connect to an Oracle database

ConnectionTimeout

Indicates the maximum amount of time that the Open method can take to obtain a pooled connection before the request is terminated

Container

Inherited from System.ComponentModel.Component

Database

Not Supported

DataSource

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

ServerVersion

Specifies the version number of the Oracle database to which the OracleConnection has established a connection

Site

Inherited from System.ComponentModel.Component

State

Specifies the current state of the connection


OracleConnection Public Methods

OracleConnection public methods are listed in Table 5-21.

Table 5-21 OracleConnection Public Methods

Public Method Description

BeginTransaction

Begins a local transaction (Overloaded)

Not supported in a .NET stored procedure

ChangeDatabase

Not Supported

Clone

Creates a copy of an OracleConnection object

Not supported in a .NET stored procedure

Close

Closes the database connection

CreateCommand

Creates and returns an OracleCommand object associated with the OracleConnection object

CreateObjRef

Inherited from System.MarshalByRefObject

Dispose

Inherited from System.ComponentModel.Component

EnlistDistributedTransaction

Enables applications to explicitly enlist in a specified distributed transaction

Not supported in a .NET stored procedure

EnlistTransaction

Enables applications to enlist in a specified distributed transaction

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

Not supported in a .NET stored procedure

Equals

Inherited from System.Object (Overloaded)

FlushCache

Flushes all updates and deletes made through REF objects retrieved using this connection

GetHashCode

Inherited from System.Object

GetLifetimeService

Inherited from System.MarshalByRefObject

GetSchema

Returns schema information for the data source of the OracleConnection

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

GetSessionInfo

Returns or refreshes the property values of the OracleGlobalization object that represents the globalization settings of the session (Overloaded)

GetType

Inherited from System.Object

InitializeLifetimeService

Inherited from System.MarshalByRefObject

Open

Opens a database connection with the property settings specified by the ConnectionString

OpenWithNewPassword

Opens a new connection with the new password

Not supported in a .NET stored procedure

PurgeStatementCache

Flushes the Statement Cache by closing all open cursors on the database, when statement caching is enabled

SetSessionInfo

Alters the session's globalization settings with the property values provided by the OracleGlobalization object

ToString

Inherited from System.Object


OracleConnection Events

OracleConnection events are listed in Table 5-22.

Table 5-22 OracleConnection Events

Event Name Description

Disposed

Inherited from System.ComponentModel.Component

Failover

An event that is triggered when an Oracle failover occurs

Not supported in a .NET stored procedure

InfoMessage

An event that is triggered for any message or warning sent by the database

StateChange

An event that is triggered when the connection state changes



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.

Declaration

// C#
public OracleConnection();

Remarks

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

OracleConnection(String)

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

Declaration

// C#
public OracleConnection(String connectionString);

Parameters

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:


OracleConnection Static Properties

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

Table 5-23 OracleConnection Static Property

Property Description

IsAvailable

Indicates whether or not the implicit database connection is available for use


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 run time 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 methods are listed in Table 5-24.

Table 5-24 OracleConnection Static Methods

Method Description

Equals

Inherited from System.Object (Overloaded)

ClearPool

Clears the connection pool that is associated with the provided OracleConnection object.

Not supported in a .NET stored procedure

ClearAllPools

Clears all connections from all the connection pools

Not supported in a .NET stored procedure


ClearPool

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

Declaration

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

Remarks

When this method is invoked, all idle connections are closed and freed from the pool. Currently used connections are not discarded until they are returned to the pool.

The ClearPool method should be invoked only when valid connections can be created (that is, the database is up and can be connected to). Otherwise, the ClearPool method may just create invalid connections to a downed database instance. Assuming valid database connections, a ClearPool invocation creates a connection pool with usable connections. 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

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

This method clears all connections from all the connection pools.

Declaration

// C#
public static void ClearAllPools();

Remarks

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

Table 5-25 OracleConnection Properties

Property Description

ClientId

Specifies the client identifier for the connection

ConnectionString

Specifies connection information used to connect to an Oracle database

ConnectionTimeout

Indicates the maximum amount of time that the Open method can take to obtain a pooled connection before the request is terminated

Container

Inherited from System.ComponentModel.Component

Database

Not Supported

DataSource

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

ServerVersion

Specifies the version number of the Oracle database to which the OracleConnection has established a connection

Site

Inherited from System.ComponentModel.Component

State

Specifies the current state of the connection


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.

Starting with ODP.NET 11.1, password and proxy password connection string attribute values are accepted as case-sensitive strings. Thus, they are passed to the database for authentication in the case provided in the connection string. Therefore, if the database is configured to support case-sensitive passwords, passwords must be passed in the correct case.

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.

Table 5-26 Supported Connection String Attributes

Connection String Attribute Description Default Value

Connection Lifetime

Maximum life time (in seconds) of the connection.

This attribute specifies the lifetime of the connection in seconds. Before the Connection is placed back into the pool, the lifetime of the connection is checked. If the lifetime of the connection exceeds this property value, the connection is closed and disposed of. If this property value is 0, the connection lifetime is never checked. Connections that have exceeded their lifetimes are not closed and disposed of, if doing so brings the number of connections in the pool below the Min Pool Size.

0

Connection Timeout

Maximum time (in seconds) to wait for a free connection from the pool.

This attribute specifies the maximum amount of time (in seconds) that the Open() method can take to obtain a pooled connection before it terminates the request. This value comes into effect only if no free connection is available from the connection pool and the Max Pool Size is reached. If a free connection is not available within the specified time, an exception is thrown. Connection Timeout does not limit the time required to open new connections.

This attribute value takes effect for pooled connection requests and not for new connection requests.

(The default value is 0 for the implicit database connection in a .NET stored procedure.)

15

Context Connection

Returns an implicit database connection if set to true.

An implicit database connection can only be obtained from within a .NET stored procedure. Other connection string attributes cannot be used in conjunction with "context connection" when it is set to true.

Supported in a .NET stored procedure only

false

Data Source

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

empty string

DBA Privilege

Administrative privileges SYSDBA or SYSOPER.

This connection string attribute only accepts SYSDBA or SYSOPER as the attribute value. It is case-insensitive.

empty string

Decr Pool Size

Number of connections that are closed when an excessive amount of established connections are unused.

1

 

This connection string attribute controls the maximum number of unused connections that are closed when the pool regulator makes periodic checks. The regulator thread is spawned every 3 minutes and closes up to Decr Pool Size amount of pooled connections if they are not used. The pool regulator never takes the total number of connections below the Min Pool Size by closing pooled connections.

 

Enlist

Controls the enlistment behavior and capabilities of a connection in context of COM+ transactions or System.Transactions.

If this attribute is set to true, the connection is automatically enlisted in the thread's transaction context. If this attribute is false, no enlistments are made. If this attribute is set to dynamic, applications can dynamically enlist in distributed transactions. This attribute can be set to true, false, yes, no, or dynamic.

true

HA Events

Enables ODP.NET connection pool to proactively remove connections from the pool when an Oracle RAC service, service member, or node goes down.

This feature can only used against an Oracle RAC database and only if "pooling=true".

This attribute can be set to true, false, yes, or no.

false

Load Balancing

Enables ODP.NET connection pool to balance work requests across Oracle RAC instances based on the load balancing advisory and service goal.

This feature can only used against an Oracle RAC database and only if "pooling=true".

This attribute can be set to true, false, yes, or no.

false

Incr Pool Size

Number of new connections to be created when all connections in the pool are in use.

This connection string attribute determines the number of new connections that are established when a pooled connection is requested, but no unused connections are available and Max Pool Size is not reached. If new connections have been created for a pool, the regulator thread skips a cycle and does not have an opportunity to close any connections for 6 minutes. Note, however, that some connections can be still be closed during this time if their lifetime has been exceeded.

5

Max Pool Size

Maximum number of connections in a pool.

This attribute specifies the maximum number of connections allowed in the particular pool used by that OracleConnection. Simply changing this attribute in the connection string does not change the Max Pool Size restriction on a currently existing pool. Doing so simply creates a new pool with a different Max Pool Size restriction. This attribute must be set to a value greater than the Min Pool Size. This value is ignored unless Pooling is turned on.

100

Metadata Pooling

Caches metadata information.

This attribute indicates whether or not metadata information for executed queries are cached for improved performance.

True

Min Pool Size

Minimum number of connections in a pool.

This attribute specifies the minimum number of connections to be maintained by the pool during its entire lifetime. Simply changing this attribute in the connection string does not change the Min Pool Size restriction on a currently existing pool. Doing so simply creates a new pool with a different Min Pool Size restriction. This value is ignored unless Pooling is turned on.

1

Password

Password for the user specified by User Id.

This attribute specifies an Oracle user's password. Password is case-sensitive by default for Oracle Database 11g release 1 (11.1) and later.

empty string

Persist Security Info

Retrieval of the password in the connection string.

If this attribute is set to false, the Password value setting is not returned when the application requests the ConnectionString after the connection is successfully opened by the Open() method. This attribute can be set to either true, false, yes, or no.

false

Pooling

Connection pooling.

This attribute specifies whether or not connection pooling is to be used. Pools are created using an attribute value matching algorithm. This means that connection strings which only differ in the number of spaces in the connection string use the same pool. If two connection strings are identical except that one sets an attribute to a default value while the other does not set that attribute, both requests obtain connections from the same pool. This attribute can be set to either true, false, yes, or no.

true

Promotable Transaction

Promotable to distributed transaction or not.

If "promotable" is specified, the first and all subsequent connections opened in the same TransactionScope enlist in the same distributed transaction. If "local" is specified, the first connection opened in the TransactionScope uses a local transaction.

promotable

Proxy User Id

User name of the proxy user.

This connection string attribute specifies the middle-tier user, or the proxy user, who establishes a connection on behalf of a client user specified by the User Id attribute. ODP.NET attempts to establish a proxy connection if either the Proxy User Id or the Proxy Password attribute is set to a non-empty string.

empty string

 

For the proxy user to connect to an Oracle database using operating system authentication, the Proxy User Id must be set to "/". The Proxy Password is ignored in this case. The User Id cannot be set to "/" when establishing proxy connections. The case of this attribute value is preserved.

 

Proxy Password

Password of the proxy user.

This connection string attribute specifies the password of the middle-tier user or the proxy user. This user establishes a connection on behalf of a client user specified by the User Id attribute. ODP.NET attempts to establish a proxy connection if either the Proxy User Id or the Proxy Password attribute is set to a non-empty string.

The case of this attribute value is preserved if it is surrounded by double quotes.

empty string

Statement Cache Purge

Statement cache purged when the connection goes back to the pool.

If statement caching is enabled, setting this attribute to true purges the Statement Cache when the connection goes back to the pool.

false

Statement Cache Size

Statement cache enabled and cache size set size, that is, the maximum number of statements that can be cached.

A value greater than zero enables statement caching and sets the cache size to itself. This value should not be greater than the value of the OPEN_CURSORS parameter set in the init.ora database configuration file.

10

User Id

Oracle user name.

This attribute specifies the Oracle user name. The case of this attribute value is preserved if it is surrounded by double quotes. For the user to connect to an Oracle database using operating system authentication, set the User Id to "/". Any Password attribute setting is ignored in this case.

empty string

Validate Connection

Validation of connections coming from the pool.

Validation causes a round-trip to the database for each connection. Therefore, it should only be used when necessary.

false


ConnectionTimeout

This property indicates 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

This property indicates the connection timeout that has been set using the ConnectionString attribute Connection TimeOut.

This property is read-only.

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.

Table 5-27 OracleConnection Public Methods

Public Method Description

BeginTransaction

Begins a local transaction (Overloaded)

Not supported in a .NET stored procedure

ChangeDatabase

Not Supported

Clone

Creates a copy of an OracleConnection object

Not supported in a .NET stored procedure

Close

Closes the database connection

CreateCommand

Creates and returns an OracleCommand object associated with the OracleConnection object

CreateObjRef

Inherited from System.MarshalByRefObject

Dispose

Inherited from System.ComponentModel.Component

EnlistDistributedTransaction

Enables applications to explicitly enlist in a specified distributed transaction

Not supported in a .NET stored procedure

EnlistTransaction

Enables applications to enlist in a specified distributed transaction

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

Not supported in a .NET stored procedure

Equals

Inherited from System.Object (Overloaded)

FlushCache

Flushes all updates and deletes made through REF objects retrieved using this connection

GetHashCode

Inherited from System.Object

GetLifetimeService

Inherited from System.MarshalByRefObject

GetSchema

Returns schema information for the data source of the OracleConnection

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

GetSessionInfo

Returns or refreshes the property values of the OracleGlobalization object that represents the globalization settings of the session (Overloaded)

GetType

Inherited from System.Object

InitializeLifetimeService

Inherited from System.MarshalByRefObject

Open

Opens a database connection with the property settings specified by the ConnectionString

OpenWithNewPassword

Opens a new connection with the new password

Not supported in a .NET stored procedure

PurgeStatementCache

Flushes the Statement Cache by closing all open cursors on the database, when statement caching is enabled

SetSessionInfo

Alters the session's globalization settings with the property values provided by the OracleGlobalization object

ToString

Inherited from System.Object


BeginTransaction

BeginTransaction methods begin local transactions.

Overload List

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

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 isolation level is supported: IsolationLevel.ReadCommitted.

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

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:

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

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

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.

FlushCache

This method flushes all updates and deletes made through REF objects retrieved using this connection.

Declaration

// c#
public void FlushCache();

Exceptions

InvalidOperationException - The specified connection is not open.

Remarks

Before flushing objects, it is required that the application has explicitly started a transaction by executing the BeginTransaction method on the OracleConnection object. This is because if the object being flushed has not already been locked by the application, an exclusive lock is obtained implicitly for the object. The lock is only released when the transaction commits or rollbacks.

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.

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

Return Value

A DataTable object.

Exception

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.

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

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

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

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.

Table 5-28 OracleConnection Events

Event Name Description

Disposed

Inherited from System.ComponentModel.Component

Failover

An event that is triggered when an Oracle failover occurs

Not supported in a .NET stored procedure

InfoMessage

An event that is triggered for any message or warning sent by the database

StateChange

An event that is triggered when the connection state changes


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.

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.

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.

Remarks

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

See Also: