ADO.NET 3.5 Cookbook, 2nd Edition

1.7. Connecting to an Oracle Database

Problem

You want to connect to an Oracle database.

Solution

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

The solution creates and opens a connection to an Oracle database using the Oracle .NET data provider, OLE DB .NET data provider, and ODBC .NET data provider in turn. In each case, information about the connection is displayed from the properties of the connection object.

The solution requires a reference to the System.Data.OracleClient assembly.

The C# code in Program.cs in the project ConnectOracle is shown in Example 1-8.

Example 1-8. File: Program.cs for ConnectOracle solution

using System;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.Data.Odbc;

namespace ConnectOracle
{
    class Program Interprocess communication (IPC)
    {
        static void Main(string[] args)
        {
            // Connect using .NET data provider for Oracle
            string oracleConnectString =
                "Data Source=ORCL;User Id=hr;Password=password;";

            using (OracleConnection connection =
                new OracleConnection(oracleConnectString))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine("---Microsoft .NET Provider for Oracle---");
                Console.WriteLine("ConnectionString = {0}\n", oracleConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }

            // Connect using .NET data provider for OLE DB.
            string oledbConnectString =
                "Provider=MSDAORA;Data Source=ORCL;User Id=hr;Password=password;";
            
            using (OleDbConnection connection =
                new OleDbConnection(oledbConnectString))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine("\n---Microsoft .NET Provider for OLE DB---");
                Console.WriteLine("ConnectionString = {0}\n", oledbConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }
 
            // Connect using .NET data provider for ODBC
            string odbcConnectString = "Driver={Oracle in OraDb10g_home1};" +
                "Server=ORCL;uid=hr;pwd=password;";

            using (OdbcConnection connection =
                new OdbcConnection(odbcConnectString))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine("\n---Microsoft .NET Provider for ODBC---");
                Console.WriteLine("ConnectionString = {0}\n", odbcConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in Figure 1-7.

Figure 1-7. Output for ConnectOracle solution

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=ORCL;Integrated Security=yes;

Without integrated security, the connection string is:

	Data Source=ORCL;User Id=hr;Password=password;

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.

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. An example of the connection string is shown here:

	Provider=MSDAORA;Data Source=myOracleDb;User Id=hr;Password=password;

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 using the Oracle 10g driver from Oracle is shown here:

	"Driver={Oracle in OraDb10g_home1};Server=ORCL;uid=hr;pwd=password;";

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 possible.

ora: 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 ORCL follows:

	ORCL =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
	    (CONNECT_DATA =
	      (SERVER = DEDICATED)
	      (SERVICE_NAME = orcl)
	    )
	  )

In this simple example, the connection to the alias ORLC uses TCP/IP on port 1521 (the default).