ADO.NET Cookbook

1.9. Connecting to an Oracle Database

Problem

You want to connect to an Oracle database.

Solution

You can connect to an Oracle database using either the Oracle .NET data provider or the OLE DB .NET data provider.

The sample code contains two event handlers:

Oracle Button.Click

Creates and opens a connection to an Oracle database using the Oracle .NET data provider. Information about the database is displayed from the properties of the OracleConnection object.

OLE DB Button.Click

Creates and opens a connection to an Oracle database using the OLE DB .NET data provider. Information about the database is displayed from the properties of the OleDbConnection object.

The C# code is shown in Example 1-7.

Example 1-7. File: ConnectOracleForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data.OleDb;
using System.Data.OracleClient;

//  . . . 

private void oracleProviderButton_Click(object sender, System.EventArgs e)
{
    // Connect to Oracle using Microsoft Oracle .NET data provider.
    OracleConnection conn = new OracleConnection(
        ConfigurationSettings.AppSettings["Oracle_Scott_ConnectString"]);

    resultTextBox.Text = "Connection with ORACLE Provider" +
        Environment.NewLine;
    try
    {
        conn.Open( );

        resultTextBox.Text += "ConnectionState = " + conn.State +
            Environment.NewLine +
            "DataSource = " + conn.DataSource +
            Environment.NewLine +
            "ServerVersion = " + conn.ServerVersion +
            Environment.NewLine;
    }
    catch(OracleException ex)
    {
        resultTextBox.Text += "ERROR: " + ex.Message;
    }
    finally
    {
        conn.Close( );
        resultTextBox.Text += "ConnectionState = " + conn.State;
    }
}

private void oleDbButton_Click(object sender, System.EventArgs e)
{
    // Connect to Oracle using OLE DB .NET data provider.
    OleDbConnection conn = new OleDbConnection(
        ConfigurationSettings.AppSettings["OleDb_Oracle_ConnectString"]);

    resultTextBox.Text = "Connection with OLE DB Provider" +
        Environment.NewLine;
    try
    {
        conn.Open( );

        resultTextBox.Text += "ConnectionState = " + conn.State +
            Environment.NewLine +
            "DataSource = " + conn.DataSource +
            Environment.NewLine +
            "ServerVersion = " + conn.ServerVersion +
            Environment.NewLine;
    }
    catch(OleDbException ex)
    {
        resultTextBox.Text += "ERROR: " + ex.Message;
    }
    finally
    {
        conn.Close( );
        resultTextBox.Text += "ConnectionState = " + conn.State +
            Environment.NewLine;
    }        
}

Discussion

You can access an Oracle database using three different provider types: native Oracle, OLE DB, and ODBC. These alternatives are discussed in the following subsections.

Native Oracle

The Microsoft Oracle .NET data provider accesses an Oracle database using the Oracle Call Interface (OCI) through Oracle client connectivity software. The provider can access Oracle 7.3.4 or later and requires Oracle 8i Release 3 (8.1.7) or later client software. The classes are located in the System.Data.OracleClient namespace. An example of a connection string using integrated security is shown in the following snippet:

Data Source=myOracleDb;Integrated Security=yes;

Without integrated security, the connection string is:

Data Source=myOracleDb;User Id=scott;Password=tiger;

The Microsoft Oracle .NET data provider is included with .NET Framework Version 1.1. It is not included with the .NET Framework Version 1.0, but you can download it from http://msdn.microsoft.com/downloads. The Oracle .NET data provider can access Oracle8 Release 8.0 or later and requires the Oracle9i Client Release 2 (9.2) or later.

Here are desciptions of available managed providers:

• Oracle has released a .NET data provider. It is available for free download from http://otn.oracle.com/software/tech/windows/odpnet/content.html.

• Data Direct Technologies licenses a fully managed provider that does not require client libraries for Oracle8i Release 2 (8.1.6) or later databases. More information is available at http://www.datadirect-technologies.com/products/dotnet/dotnetindex.asp.

Native providers generally perform better than OLE DB or ODBC providers because they are built specifically for the database and because they remove a layer of indirection from the application to the database.

OLE DB

You can use the OLE DB .NET data provider with the Oracle OLE DB provider (MSDAORA) to access Oracle data not supported by a .NET Oracle provider. An example of the connection string is shown here:

Provider=MSDAORA;Data Source=myOracleDb;User Id=scott;Password=tiger;

The OLE DB provider should be used primarily as a bridge from applications that already use OLE DB. Use a native Oracle .NET data provider where practical.

ODBC

Finally, the ODBC .NET data provider can connect to an Oracle database. An example of the connection string is shown here:

Driver={Microsoft ODBC for Oracle};Server=myOracleDb;
    Trusted_Connection=yes;

The ODBC .NET data provider should be used primarily as a bridge from applications that already use ODBC. Use a native Oracle .NET data provider where practical.

TNSNAMES.ORA

Oracle uses a configuration file named TNSNAMES.ORA to locate the Oracle database and determine how to connect to it based on the Data Source or Database attribute in the connection string.

An example of an entry in the TNSNAMES.ORA file for the alias MYORCLDB follows:

MYORCLDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)
        (HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.myurl.com)
    )
  )

In this simple example, the connection to the alias MYORLCDB uses TCP/IP on port 1521 (the default) and is running on the computer called myserver. The name of the Oracle service is ORCL.MYURL.COM.