How to: Connect to an Oracle Database Using ODP.NET

Date: 19-Jun-2003

Objective

After reading this how-to document you should be able to:

• Connect to an Oracle database using Oracle Data Provider for .NET (ODP.NET)
• Control basic parameters that govern ODP.NET connection pooling

Prerequisite

Introduction

ODP.NET, which is included in the Oracle.DataAccesss.dll assembly, provides a rich collection of classes that assist in easy database interaction. It uses Oracle's native APIs to offer fast and reliable access to Oracle data and features from any .NET application.

In this how-to we'll look at how to use the OracleConnection class, provided by ODP.NET, to establish a connection to an Oracle database and interact with the database. Here, we'll use a small code fragment to demonstrate how to connect to an Oracle database using ODP.NET.

Further, connection pooling is enabled in ODP.NET (by default). So we'll also look at how you can control the connection pooling parameters provided by ODP.NET.

Requirements

• Oracle Data Provider for .NET (ODP.NET)

• Oracle9i Database or later running SQL*Net TCP/IP listener . Refer Oracle By Example(OBE) Series on Oracle9i Database for step-by-step instructions on how to install and set up the Oracle9i database.

• C# or VB.NET installed with Microsoft Visual Studio .NET (including Microsoft .NET Framework 1.0 or later)

Description

When you install ODP.NET, the Oracle Universal Installer automatically registers ODP.NET with the Global Assembly Cache (GAC). The GAC in the .NET framework provides a central place for registering assemblies. These assemblies once registered are then available to all applications for usage, including the development environments like Visual Studio.NET.

The most important class with respect to this how-to is the OracleConnection class. An OracleConnection object represents a connection to an Oracle database. In this how-to we will demonstrate how to connect to an Oracle database and list names of all the employees and their employee number present in a table. Alternatively, you may use any database user account that has connect and resource privileges to connect to the Oracle database.

Prior to connecting to an Oracle Database using ODP.NET, one should add Net Service Names. Specifying the Net Service Names also known as TNS alias is used to identify an Oracle Database Instance.

Add Net Service Names

ODP.NET uses Net Service Names to identify the data source (database) it connects to. The Oracle Net Configuration Assistant Tool, a post installation tool that configures basic network components, can be used to setup the Net Service Names. Please refer to the Oracle9i Net Services Administrator's Guide available at OTN for the details on using this tool to setup the Net Services Names.

Create Required Database Table

To create the required "emptab" table, follow the instructions given below:

1. Start SQL*Plus.
2. Connect to the Oracle database using the desired username, password and connect string. Ensure to use the same parameters later, in your ODP.NET ConnectionString.
3. Paste the following SQL script (Listing 1) on the SQL> prompt:
   Listing 1
   

   DROP TABLE emptab; CREATE TABLE emptab (empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(10)); INSERT INTO emptab VALUES ( 1, 'Mark'); INSERT INTO emptab VALUES ( 2, 'Roger'); INSERT INTO emptab VALUES ( 3, 'Crook'); COMMIT;

   
   

Code Walk-Through

Include Required Namespaces: It is worthwhile to add references of the namespaces in the 'general declarations' section of the .cs or .vb file, to avoid qualifying their usage later in the script:

C#

using System; using System.Data; using Oracle.DataAccess.Client;

Visual Basic .NET

Imports System Imports System.Data Imports Oracle.DataAccess.Client

1. Set the connection parameters like the User Id, Password and Data Source:

C#

// Create the connection object OracleConnection con = new OracleConnection(); // Specify the connect string // NOTE: Modify User Id, Password, Data Source as per your database set up con.ConnectionString = "User Id=scott;Password=tiger;Data Source=orcl9i;";

Visual Basic .NET

' Create the connection object Dim con As OracleConnection = New OracleConnection() ' Specify the connect string ' NOTE: Modify User Id, Password, Data Source as per your database set up con.ConnectionString = "User Id=Scott;Password=tiger;Data Source=ora9idb;"

2. Open database connection through ODP.NET:

C#

try { // Open the connection con.Open(); Console.WriteLine("Connection to Oracle database established successfully !"); Console.WriteLine(" "); } catch (Exception ex) { Console.WriteLine(ex.Message); }

Visual Basic .NET

Try ' Open the connection con.Open() Console.WriteLine("Connection to Oracle database established successfully !") Console.WriteLine(" ") Catch ex As Exception Console.WriteLine(ex.Message) End Try

3. Create command object to perform a query against the database:

C#

string cmdQuery = "SELECT empno, ename FROM emptab"; // Create the OracleCommand object OracleCommand cmd = new OracleCommand(cmdQuery); cmd.Connection = con; cmd.CommandType = CommandType.Text;

Visual Basic .NET

Dim cmdQuery As String = "SELECT empno, ename FROM emptab" ' Create the OracleCommand object Dim cmd As OracleCommand = New OracleCommand(cmdQuery) cmd.Connection = con cmd.CommandType = CommandType.Text

4. Fetch data into an OracleDataReader object and display the data on the console. Then, close the connection object:

C#

try { // Execute command, create OracleDataReader object OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { // Output Employee Name and Number Console.WriteLine("Employee Number: " + reader.GetDecimal(0) + " , " + "Employee Name : " + reader.GetString(1));   } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { // Dispose OracleCommand object cmd.Dispose(); // Close and Dispose OracleConnection object con.Close(); con.Dispose(); }

Visual Basic .NET

Try ' Execute command, create OracleDataReader object Dim reader As OracleDataReader = cmd.ExecuteReader() While (reader.Read()) ' Output Employee Name and Number Console.WriteLine("Employee Number : " & _ reader.GetDecimal(0) & _ " , " & _ "Employee Name : " & _ reader.GetString(1)) End While Catch ex As Exception Console.WriteLine(ex.Message) Finally ' Dispose OracleCommand object cmd.Dispose() ' Close and Dispose OracleConnection object con.Close() con.Dispose() End Try

Setup and Run the How-To

1. Open Visual Studio.NET.

2. Create a Console Application Project:

C#

Create a Console Application Project in C#. Class1.cs is added to the project by default.

Visual Basic .NET

Create a Console Application Project in Visual Basic .NET. Module1.vb is added to the project by default.

3. Ensure that your project contains references to the System, Oracle.DataAccess and System.Data namespaces. Add references to these namespaces if they do not exist.

4. Copy the code:

C#

Using Solution Explorer open Class1.cs. For complete listing of code for this How-to article in C# click here. Copy this code and overwrite the contents of Class1.cs.