18 กุมภาพันธ์ 2554

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

Objective

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

Prerequisite

The reader is assumed to be familiar with MS Visual Studio.NET, and also possess an understanding of the rudimentaries of ODP.NET and databases.

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

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.

from http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/connect/index.html?_template=/ocom/print

เขียนโดย ที่
ป้ายกำกับ:

ไม่มีความคิดเห็น:

แสดงความคิดเห็น

สมัครสมาชิก: ส่งความคิดเห็น (Atom)

บทความยอดนิยม (ล่าสุด)

บทความยอดนิยม (All Time)