ORACLE DATA PROVIDER FOR .NET

In ASP.NET we can not only use sql server and mysql but also oracle as the backend to maintain the database files.

This post covers how to connect to an Oracle database using the Oracle Data Provider for .NET (ODP.NET) and how to control parameters that manage ODP.NET database connection pooling.

 For even clear picture and in depth look at the following document:http://www.mediafire.com/?c1jd6tmnh8fyw31

Post Contents
1.Introduction
2.Code Samples

Introduction

Objective:
Connect to an Oracle database using Oracle Data Provider for .NET (ODP.NET) and control parameters that manage ODP.NET database connection pooling.
Prerequisite:
I assume you are familiar with Microsoft Visual Studio .NET, and also have an basic understanding of ODP.NET and databases.
Introduction:
ODP.NET, which is included in the Oracle.DataAccesss.dll assembly, provides a robust 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 tutorial, I will discuss how to use the OracleConnection class, provided by ODP.NET, then to establish a connection to an Oracle database and interact with the database. I will also show the usage of a small code fragment that demonstrates how to connect to an Oracle database using ODP.NET.
Connection pooling is enabled by default in ODP.NET. I will also explain 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.
• Your choice of languages either C# or VB.NET, and Microsoft Visual Studio .NET

Description:
When you install ODP.NET, the Oracle Universal Installer automatically registers ODP.NET with the Global Assembly Cache (GAC).
The most important class with respect to this tutorial is the OracleConnection class. An OracleConnection object represents a connection to an Oracle database. In this tutorial, I will display how to connect to an Oracle database and list names of all the employees and their employee number present in a table.

Now that I have discussed the objective, prerequisite, introduction, requirements, and description it is time to move on to the code.




Code Samples
Sample Code:
Include Required Namespaces: It is advisable 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 code:
C#

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

1. Set the any connection parameters such as 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=userid;Password=password;Data Source=dbinstance;";

2. Now open the database connection through ODP.NET:
C#

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

3. Create a 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;

4. Obtain the data result and place this result into an OracleDataReader object and then display the data via the console. Then, simply 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(); 
}

}