Oracle® Data Provider for .NET Developer's Guide 10g Release 1 (10.1) Part Number B10117-01 |
|
|
View PDF |
This section describes OracleConnection
provider-specific features, including:
Table 3-1 lists the supported connection string attributes.
Table 3-1 Supported Connection String Attributes
Connection String Attribute | Default value | Description |
---|---|---|
Connection Lifetime |
0 |
Maximum life time (in seconds) of the connection |
Connection Timeout |
15 |
Maximum time (in seconds) to wait for a free connection from the pool |
Data Source |
empty string | Oracle Net Service Name that identifies the database to connect to |
DBA Privilege |
empty string | Administrative privileges: SYSDBA or SYSOPER |
Decr Pool Size |
1 | Controls the number of connections that are closed when an excessive amount of established connections are unused |
Enlist |
true |
Enables or disables serviced components to automatically enlist in distributed transactions |
Incr Pool Size |
5 | Controls the number of connections that are established when all the connections in the pool are used |
Max Pool Size |
100 |
Maximum number of connections in a pool |
Min Pool Size |
1 |
Minimum number of connections in a pool |
Password |
empty string | Password for the user specified by User Id |
Persist Security Info |
false |
Enables or disables the retrieval of password in the connection string |
Pooling |
true |
Enables or disables connection pooling |
Proxy User Id |
empty string | User name of the proxy user |
Proxy Password |
empty string | Password of the proxy user |
User Id |
empty string | Oracle user name |
The following example uses connection string attributes to connect to an Oracle Database:
// C# ... OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;"; con.Open(); ...
ODP.NET connection pooling is enabled and disabled using the Pooling
connection string attribute. By default, connection pooling is enabled. The following are ConnectionString
attributes that control the behavior of the connection pooling service:
Pooling
Connection
Lifetime
Connection
Timeout
Max
Pool
Size
Min
Pool
Size
Incr
Pool
Size
Decr
Pool
Size
The following code opens a connection using ConnectionString
attributes related to connection pooling.
// C# ... OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;" + "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" + "Incr Pool Size=5; Decr Pool Size=2"; con.Open(); ...
With connection pooling enabled (the default), the Open
and Close
methods of the OracleConnection
object implicitly use the connection pooling service. In the preceding code, the Open
call uses the connection pooling service, which is responsible for returning a connection to the application.
Connection pools are created by the connection pooling service using the ConnectionString
as a signature to uniquely identify a pool.
If no pool with the exact attribute values in the ConnectionString
exists, the connection pooling service creates a new connection pool. If a pool already exists with the requested signature, a connection is returned to the application from that pool.
When a connection pool is created, the connection-pooling service initially creates the number of connections defined by the Min
Pool
Size
attribute of the ConnectionString
. This number of connections is always maintained by the connection pooling service for the connection pool.
At any given time, these connections are available in the pool or used by the application.
The Incr
Pool
Size
attribute of the ConnectionString
defines the number of new connections to be created by the connection pooling service when more connections are needed in the connection pool.
When the application closes a connection, the connection pooling service determines whether the connection lifetime has exceeded the Connection
Lifetime
attribute; if so, the connection pooling service closes the connection; otherwise, the connection goes back to the connection pool. The connection pooling service only enforces the Connection
Lifetime
when a connection is going back to the connection pool.
The Max
Pool
Size
attribute of the ConnectionString
sets the maximum number of connections for a connection pool. If a new connection is requested, no connections are available, and Max
Pool
Size
has been reached, then the connection pooling service waits for the time defined by Connection
Timeout
. If the Connection
Timeout
has been reached and there are still no connections available in the pool, the connection pooling service raises an exception indicating that the pooled connection request has timed-out.
The connection pooling service closes connections when they are not used; connections are closed every three minutes. The Decr
Pool
Size
attribute of the ConnectionString
provides connection pooling service for the maximum number of connections that can be closed in one run.
The Oracle Database can use Windows user login credentials to authenticate database users. To open a connection using Windows user login credentials, the User
Id
ConnectionString
attribute must be set to /
. If Password
is provided, it is ignored.
// C# ... OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=/;Data Source=oracle;"; con.Open(); ...
See Also: Oracle Database Platform Guide for Windows for information on how to set up an Oracle Database to authenticate database users using Windows user login credentials |
Oracle allows database administrators to connect to an Oracle Database with either SYSDBA
or SYSOPER
privileges. This is done through the DBA
Privilege
attribute of the ConnectionString
.
The following example connects SYS
/SYS
as SYSDBA
:
// C# ... OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=SYS;Password=SYS;" + "DBA Privilege=SYSDBA;Data Source=oracle;"; con.Open(); ...
Oracle allows users' password to expire. ODP.NET lets applications handle the password expiration by providing a new method, OpenWithNewPassword
, that opens the connection with a new password.
The following code snippet uses the OracleConnection
OpenWithNewPassword
method to connect with a new password of panther
:
// C# ... OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;"; // Here the con.Open() fails if the password has expired. // An application catches this and attempts to reconnect with a new password // of "panther". The initial connection string must remain intact. try { con.Open();} catch { con.OpenWithNewPassword("panther");} ...
Note: OpenWithNewPassword should only be used when the user password has expired, not for changing the password. |
The Oracle Database allows a middle-tier server to connect to proxy clients in a secure fashion.
In multitier environments, proxy authentication allows control of middle-tier application security by preserving client identities and privileges through all tiers, and by auditing actions taken on behalf of clients. The proxy authentication feature allows the identity of a user using a Web application to be passed through the application to the database server.
ODP.NET supports proxy authentication with or without a client password by providing the Proxy
User
Id
and Proxy
Password
attributes of the ConnectionString
property.
// C# ... OracleConnection con = new OracleConnection(); // Connecting using proxy authentication con.ConnectionString = "User Id=customer;Password=lion;" + "Data Source=oracle;Proxy User Id=appserver;Proxy Password=eagle; "; con.Open(); ...
See Also:
|
Transparent Application Failover (TAF) is a feature in the Oracle Database that provides high availability.
TAF enables an application connection to automatically reconnect to a database if the connection fails. Active transactions roll back, but the new database connection, made by way of a different node, is identical to the original. This is true regardless of how the connection fails.
With Transparent Application Failover, a client notices no loss of connection as long as there is one instance left serving the application. The database administrator controls which applications run on which instances and also creates a failover order for each application.
Given the delays that failovers can cause, applications may wish to be notified by a TAF callback. ODP.NET supports TAF callback through the Failover
event of the OracleConnection
object, which allows applications to be notified whenever a failover occurs. To receive TAF callbacks, an event handler function must be registered with the Failover
event.
When a failover occurs, the Failover
event is raised and the registered event handler is invoked several times during the course of reestablishing the connection to another Oracle instance.
The first call to the event handler occurs when the Oracle Database first detects an instance connection loss. This allows the application to act accordingly for the upcoming delay for the failover.
If the failover is successful, the Failover
event is raised again when the connection is reestablished and usable. At this time, the application can resynchronize the OracleGlobalization
session setting and inform the application user that a failover has occurred.
If failover is unsuccessful, the Failover
event is raised to inform the application that a failover did not take place.
The application can determine whether or not the failover is successful by checking the OracleFailoverEventArgs
that is passed to the event handler.
The following code example registers an event handler method called OnFailover
:
// C# ... OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;"; con.Open(); con.Failover += new OracleFailoverEventHandler(OnFailover); ...
The Failover
event only invokes one event handler. If multiple Failover
event handlers are registered with the Failover
event, only the event handler registered last is invoked.
Note: Distributed transactions are not supported in an environment where failover is enabled. |