OracleConnection (.NET)

•  Standard:
  
  "Data Source=MyOracleDB;Integrated Security=yes;"

  This one works only with Oracle 8i release 3 or later

•  Specifying username and password:
  
  "Data Source=MyOracleDB;User Id=username;Password=passwd;Integrated Security=no;"

  This one works only with Oracle 8i release 3 or later

•  Declare the OracleConnection:
  

  C#:
  using System.Data.OracleClient;
  OracleConnection oOracleConn = new OracleConnection();
  oOracleConn.ConnectionString = "my connectionstring";
  oOracleConn.Open();

  
  
  

  VB.NET:
  Imports System.Data.OracleClient
  Dim oOracleConn As OracleConnection = New OracleConnection()
  oOracleConn.ConnectionString = "my connectionstring"
  oOracleConn.Open()

  
  
  

  Missing the System.Data.OracleClient namespace? Download .NET Managed Provider for Oracle >>

   

  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   tang

   

   

  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.