Oracle Data Provider for .NET

Oracle Data Provider for .NET

Author Date Of Submission User Level Rama Mohan G. 02/20/2004 Intermediate

Features of Oracle Data Provider for .NET Oracle Data Provider for .NET provider-specific features and how to use them to develop .NET applications. Connecting to the Oracle Database Server This section describes OracleConnection provider-specific features. Connection String Attributes 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 server: // C# ... OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;"; con.Open(); ...   Connection Pooling 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 Connection Pooling Example 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. Operating System Authentication The Oracle database server 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(); ... Privileged Connections Oracle allows database administrators to connect to an Oracle database server 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(); ... Password Expiration 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"); } ...   Proxy Authentication Oracle 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(); ... Transparent Application Failover (TAF) Callback Support Transparent Application Failover(TAF) is a feature in Oracle 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.