Skip Headers
Oracle® Data Provider for .NET Developer's Guide
10g Release 2 (10.2)

B14307-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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 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 method is listed in Table 5-19.

Table 5-19 OracleConnection Static Method

Method Description
Equals Inherited from Object (Overloaded)

OracleConnection Properties

OracleConnection properties are listed in Table 5-20

Table 5-20 OracleConnection Properties

Name Description
ClientId Specifies the client identifier for the connection
ConnectionString Specifies connection information used to connect to an Oracle database
ConnectionTimeout Specifies the maximum amount of time that the Open method can take to obtain a pooled connection before the request is terminated
Container Inherited from 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
IsAvailable Indicates whether or not the implicit database connection is available for use.
ServerVersion Specifies the version number of the Oracle database to which the OracleConnection has established a connection
Site Inherited from 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 MarshalByRefObject
Dispose Inherited from Component
EnlistDistributedTransaction Enables applications to explicitly enlist in a specified distributed transaction

Not supported in a .NET stored procedure

Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetSessionInfo Returns or refreshes the property values of the OracleGlobalization object that represents the globalization settings of the session (Overloaded)
GetType Inherited from Object
InitializeLifetimeService Inherited from 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 with an implicit database connection

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 Object

OracleConnection Events

OracleConnection events are listed in Table 5-22.

Table 5-22 OracleConnection Events

Event Name Description
Disposed Inherited from 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 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.

Table 5-24 OracleConnection Static Method

Method Description
Equals Inherited from Object (Overloaded)


OracleConnection Properties

OracleConnection properties are listed in Table 5-25

Table 5-25 OracleConnection Properties

Name Description
ClientId Specifies the client identifier for the connection
ConnectionString Specifies connection information used to connect to an Oracle database
ConnectionTimeout Specifies the maximum amount of time that the Open method can take to obtain a pooled connection before the request is terminated
Container Inherited from 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 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

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

Table 5-26 Supported Connection String Attributes

Connection String Attribute Default Value Description
Connection Lifetime 0 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.

Connection Timeout 15 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)

Context Connection false 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

Data Source empty string Oracle Net Services Name, Connect Descriptor, or an easy connect naming that identifies the database to which to connect.
DBA Privilege empty string Administrative privileges SYSDBA or SYSOPER.

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

Decr Pool Size 1 Number of connections that are closed when an excessive amount of established connections are unused.
    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 true Serviced components automatically enlist in distributed 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. This attribute can be set to either true, false, yes, or no.

HA Events false Enables ODP.NET connection pool to proactively remove connections from the pool when a RAC service, service member, or node goes down.

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

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

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

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

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

Incr Pool Size 5 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.

Max Pool Size 100 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.

Min Pool Size 1 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.

Password empty string Password for the user specified by User Id.

This attribute specifies an Oracle user's password. Password is case insensitive.

Persist Security Info false 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.

Pooling true 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.

Proxy User Id empty string 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.

    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 if it is surrounded by double quotes.
Proxy Password empty string 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.

Statement Cache Purge false 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.

Statement Cache Size 0 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.

User Id empty string 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.

Validate Connection false 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.


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

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

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

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

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

// 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 MarshalByRefObject
Dispose Inherited from Component
EnlistDistributedTransaction Enables applications to explicitly enlist in a specified distributed transaction

Not supported in a .NET stored procedure

Equals Inherited from Object (Overloaded)
GetHashCode Inherited from Object
GetLifetimeService Inherited from MarshalByRefObject
GetSessionInfo Returns or refreshes the property values of the OracleGlobalization object that represents the globalization settings of the session (Overloaded)
GetType Inherited from Object
InitializeLifetimeService Inherited from 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 with implicit database connection 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 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 two isolation levels are supported:

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

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

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


See Also:


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

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

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();
 
    // 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 5-28.

Table 5-28 OracleConnection Events

Event Name Description
Disposed Inherited from 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

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