In this article I’ll discuss how to set up a connection between an Oracle database and the .Net Framework using C#. This article will focus more on elaborating how a connection can be made.

Prerequisites

1. Microsoft Visual Studio 2008 or higher (I’am using Visual Studio 2015).
2. Install Oracle Data Access Component (ODAC)

Use this link to download ODAC,

http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

First Install ODAC on your system.

After installing restart your system.

Go to your installed Location and open tnsnames.ora File in notepad as example below

E:\app\product\11.2.0\client_1\Network\Admin\Sample

Here you will get tnsnames.ora copy it and paste in side Admin Folder in same location

E:\app\product\11.2.0\client_1\Network\Admin

As per requirements Create TNS File As below

 

TestDB =

  (DESCRIPTION =  (ADDRESS_LIST =  (ADDRESS =  (COMMUNITY = tcp.world)

          (PROTOCOL = TCP) (Host = 103.205.66.85)  (Port = 1521)  )  )

    (CONNECT_DATA = (SID = TestDB)

    )

  )

Here I have Make TNS with database name TestDB and Host-103.205.66.85 and port 1521

means database in installed on 103.205.66.85 Ip Address Server and default port used 1521 of Oracle DB

Now Coding Part will Start
 

Create your Project

Open Visual Studio 2015 ->File ->New  -> Project -> Visual C# -> Web ->Asp.net Web Application.

Name section Specify Project name and select Location where you want save your project as below screen and click on OK button

Screen will be opened then select Empty and check Web Form and Click On Ok button then your project will be created.

Add Oracle.DataAccess.dll Reference

Go to Solution Explorer -> Right Click Reference -> Add reference -> then window will open their click on Browse button then go to your installed ODAC Location path and browse below path

E:\app\product\11.2.0\client_1\odp.net\bin\4

Select Oracle.DataAccess.dll and click ok button your reference will be Added.

 

 

In Web.config File write your Connection String As-

User Id and Password is Requird to Connect with Database as i have set below

<connectionStrings>
    <add name="oraconnection" connectionString="Data Source=TestDB;User Id=Testing;password=Testing;" providerName="Oracle.DataAccess.Client" />
  </connectionStrings>

Add references

using Oracle.DataAccess.Client;

using System.Configuration;

Write Below Code to Test Connection

protected void Page_Load(object sender, EventArgs e)
        {
            string _oraConn = ConfigurationManager.ConnectionStrings["oraconnection"].ToString();
            OracleConnection conn = new OracleConnection(_oraConn);
            conn.Open();
            Response.Write("Connected to Oracle" + conn.ServerVersion);
            // Close and Dispose OracleConnection object  
            conn.Close();
            conn.Dispose();
            Response.Write("connection successfully");
        }

 

If you want to configure datasouce on same page then write below connection string and test

protected void Page_Load(object sender, EventArgs e)
        {
             string _oraConn = "Data Source=(DESCRIPTION =" + "(ADDRESS = (PROTOCOL = TCP)(HOST =103.205.66.85)(PORT = 1521))" + "(CONNECT_DATA =" + "(SERVER = DEDICATED)" + "(SERVICE_NAME = TestDB)));" + "User Id=Testing;Password=Testing;";

            //string _oraConn = ConfigurationManager.ConnectionStrings["oraconnection"].ToString();
            OracleConnection conn = new OracleConnection(_oraConn);
            conn.Open();
            Response.Write("Connected to Oracle" + conn.ServerVersion);
            // Close and Dispose OracleConnection object  
            conn.Close();
            conn.Dispose();
            Response.Write("connection successfully");
        }