7.5.5.4 ConnectionString

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

Declaration

// C#
public override 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 7-47 lists the supported connection string attributes.

Table 7-47 Supported Connection String Attributes

Connection String Attribute Description Default Value

Application Continuity

Enables database requests to automatically replay transactional or non-transactional operations in a non-disruptive and rapid manner in the event of a severed database session, which results in a recoverable error.

Not Available in ODP.NET, Managed Driver and ODP.NET Core

true

Connection Lifetime

Minimum 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 upon a Close() or Dispose() call, the lifetime of the connection is checked. If the lifetime of the connection exceeds this property value, then the connection is destroyed. If this property value is 0, then the connection lifetime is never checked.

0

Connection Timeout

The time to wait (in seconds) for a new connection or an idle connection from the connection pool before a connection time out error can occur.

This attribute specifies the minimum amount of time (in seconds) that the Open() method must 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.

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.

1

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 database service, service member, or node goes down.

This feature can be used with Global Data Services, including Oracle RAC, Data Guard, GoldenGate, and single instance deployments. "pooling=true" must also be set

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

true

Load Balancing

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

This feature can be used with Global Data Services, including Oracle RAC, Active Data Guard, and GoldenGate. "pooling=true" must also be set.

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

true

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.

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.

empty string

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.

0

Self Tuning

Enables or disables self-tuning for the connection.

If self-tuning is enabled, then the StatementCacheSize settings in the registry, configuration files, and connection string are ignored.

If self-tuning is disabled, then a StatementCacheSize value of 0 is used unless StatementCachSize is specified in the registry, configuration file, or connection string.

true

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