In this article, we learn how to connect an Oracle database with a .Net project. These days I am doing my summer training at 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 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 a 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 the 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;" on our aspx.cs page.
  2. Now we require a TNS entry. The TNS is the .Net configuration file that defines database addresses for establishing a connection with an Oracle database.
    The following is a sample of how to create a TNS:
    1. "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:
    1. 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:
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using Oracle.DataAccess.Client;
  8. using System.Data;
  10. public partial class _Default: System.Web.UI.Page
  11. {
  12. string TNS = "Data Source=(DESCRIPTION =" +
  13. "(ADDRESS = (PROTOCOL = TCP)(HOST = pankaj)(PORT = 1521))" +
  14. "(CONNECT_DATA =" +
  15. "(SERVER = DEDICATED)" +
  16. "(SERVICE_NAME = ORCL)));" +
  17. "User Id= hr;Password=hr";
  19. OracleConnection Con;
  20. protected void Page_Load(object sender, EventArgs e)
  21. {
  23. OracleConnection Con = new OracleConnection(TNS);
  24. Con.Open();
  25. Response.Write("Connected to Oracle " + Con.ServerVersion);
  26. Con.Close();
  27. Con.Dispose();
  29. Response.Write(" Disconnected");
  30. }
  31. protected void Submit_Click(object sender, EventArgs e)
  32. {
  33. OracleConnection Con = new OracleConnection(TNS);
  34. Con.Open();
  35. DataTable tab = new DataTable();
  36. OracleDataAdapter da = new OracleDataAdapter("select * from JOBS", Con);
  37. da.Fill(tab);
  38. GridView1.DataSource = tab;
  39. GridView1.DataBind();
  40. Con.Close();
  41. Con.Dispose();
  42. }
  43. }
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.