Oracle Database Connection Using .NET

In this article we learn how to connect an Oracle database with a .Net project. These days I am doing my summer training from CDAC Jaipur. My field is Oracle Database. A few days ago I tried to connect my Oracle database with an ASP.Net project. I found many methods to connect an Oracle database with an ASP.Net project but nothing worked correctly. But finally I was successful in connecting my Oracle database with my ASP.Net project. So I decided to write an article on this to topic. It may be possible that this article will help other people to solve the same problem that I encountered.

Now we learn how to connect our Oracle database with a .Net project.

Prerequisites

1. Microsoft Visual Studio 2008 or higher version.
2. Oracle Database.

If we want to connect your Oracle database with a .Net project then you need some assembly files. For the installation of these assembly files Oracle provides the Oracle Data Access Component (ODAC).

You can download this ODAC from the link Oracle Data Access Component (ODAC)

First download and install the ODAC. After successful installation we can connect to the Oracle database.

With our project.

First we create a project:

1. Go to the File menu and select Website.
2. Now select ASP.NET Empty Website.
3. Now we add a web form.
4. In this web form we use one GridView and one button.

After doing all that we will now add the Oracle assemblies to our project as in the following:

1. Go to Solution Explorer.
2. Right-click and select Add Reference.
3. Click on Extensions.
4. Select the following assembly files and click on the  OK Button.
   
   
   
   Figure 1: Assembly Files

Now we find that a Bin folder is created automatically and this folder contains all the required assembly files.
For more confirmation we can check our Web.Config file. If we check our web.config then we will find the following code.

Figure 2: WebConfig file

If our Web.config file contains the preceding code then that means everything is fine until now.

Now we make a connection with an Oracle database and retrieve the data as in the following:

1. First we need to add the namespace "using Oracle.DataAccess.Client;" in our aspx.cs page.
2. Now we require a TNS entry. The TNS is the .Net configuration file that defines datbase addresses for establishing a connection with an Oracle database.
   
   The following is the sample of how to create a TNS:
   

   "Data Source=(DESCRIPTION =" + "(ADDRESS = (PROTOCOL = TCP)(HOST = HOST_NAME)(PORT = 1521))" + "(CONNECT_DATA =" + "(SERVER = DEDICATED)" + "(SERVICE_NAME = ORCL)));" + "User Id= User_ID;Password=******"

Before making a connection with the Oracle database using TNS entries we should have the following information in the TNS.

1. HOST Provider: You can use the following command to get the Host Name:
   

   SELECT SYS_CONTEXT ('USERENV', 'SERVER_HOST') FROM DUAL;

   Example
   
   
   
   Figure 3: Dual Table
   
   
2. Service Name: To determine your Service Name you can use the following procedure.

• Press Window + R.
• Now enter Services.msc and press Enter.
• Now search for your Oracle Service. At the last of the service you can find your service name.
  
  
  
  Figure 4:Oracle Service
  
  Here my Oracle Service name is “ORCL”.

   3.  User Id and Password: Provide the User id and Password that you set during installation.

After providing all that information we can make our connection.

The following is the source code of the Aspx.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Oracle.DataAccess.Client;
using System.Data;

public partial class _Default: System.Web.UI.Page
{
    string TNS = "Data Source=(DESCRIPTION =" +
        "(ADDRESS = (PROTOCOL = TCP)(HOST = pankaj)(PORT = 1521))" +
        "(CONNECT_DATA =" +
        "(SERVER = DEDICATED)" +
        "(SERVICE_NAME = ORCL)));" +
        "User Id= hr;Password=hr";

    OracleConnection Con;
    protected void Page_Load(object sender, EventArgs e)
    {

        OracleConnection Con = new OracleConnection(TNS);
        Con.Open();
        Response.Write("Connected to Oracle " + Con.ServerVersion);
        Con.Close();
        Con.Dispose();

        Response.Write(" Disconnected");
    }
    protected void Submit_Click(object sender, EventArgs e)
    {
        OracleConnection Con = new OracleConnection(TNS);
        Con.Open();
        DataTable tab = new DataTable();
        OracleDataAdapter da = new OracleDataAdapter("select * from JOBS", Con);
        da.Fill(tab);
        GridView1.DataSource = tab;
        GridView1.DataBind();
        Con.Close();
        Con.Dispose();
    }
}

When we run this project we find the following message in our browser screen.



Figure 5: Run

In other words, our project has been connected with the Oracle database successfully. Now if we press the Submit Button then it will show all the information of the JOBS table into the GridView, such as:



Figure 6: GridView

So using that procedure we can easily make a connection between an Oracle database and any .Net Project.