Connecting To Oracle Database through C#

Introduction

Connecting to oracle database through C#, well as we all know Oracle database are most widely used database system available in the market. And on the other had if we talk about .net framework, it’s a software framework developed by software giant Microsoft (we all know this), it includes a large class library know as Framework Class Library (FCL) and provides language interoperability across several programing language.

In this post I’ll discuss how we can setup connection between Oracle database and .Net Framework using C#. This article will focus more on elaborating how connection can be made and what all issues one can face and of course how to tackle them.

In upcoming articles we will perform operations on database using SQL Queries.

Prerequisite

1. Microsoft Visual Studio 2008 or higher (I’ll be using VS 2012).
2. Oracle database 9.2 or higher or Oracle Database XE
3. Install Oracle Data Access Component (ODAC)

Use this link to download ODAC

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

Moving Forward

Once you have installed ODAC, you must look for following assembly files.

Oracle.DataAccess.dll (this assembly file is must)

Oracle.ManagedDataAccess.dll (optional)

Oracle.Web.dll (optional)

Adding Reference

1. Go to solution explorer.
2. Right click and select Add Reference
3. Click on Extensions
4. Select the above mentioned files and click OK.

Cool, now reference has been added in you project….

How would I know if the reference has been added or not? Good Question!

Go to web.config file and you can see below code.

And you can also see a folder name bin has already been created in the solution explorer.

Hope everything is fine till now!

Now comes the main part. Yes, the code.

First you need to know about TNS entries, or else you might encounter following error

ORA-12154: TNS: could not resolve the connect identifier specified

This error usually occurs when the code doesn’t find the specified settings. This is quite confusing and irritating as well. The best thing you can do is declare the TNS setting in code itself to prevent such errors.

Below is the sample how you can create TNS entries.

You must know the following details before creating TNS entries.

1. Your host provider

This SQL query could help you know you host name

SELECT SYS_CONTEXT (‘USERENV’, ‘SERVER_HOST’) FROM DUAL;

2. You need to know Service name, you can find this in tnsnames.ora, this allow you to register an instance with the listener.
3. Third is the user id and password.
4. Don’t forget to add using Oracle.DataAccess.Client; namespace.

Default.aspx.cs

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
{
    //creating TNS entries 
    string oradb = "Data Source=(DESCRIPTION =" +
    "(ADDRESS = (PROTOCOL = TCP)(HOST = Your host name)(PORT = 1521))" +
    "(CONNECT_DATA =" +
      "(SERVER = DEDICATED)" +
      "(SERVICE_NAME = XE)));" +
      "User Id= your user id;Password=******;";
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btn_Click(object sender, EventArgs e)
    {
        OracleConnection conn = new OracleConnection(oradb);
        conn.Open();
        Response.Write("Connected to Oracle" + conn.ServerVersion);
        // Close and Dispose OracleConnection object
        conn.Close();
        conn.Dispose();
        Response.Write("Disconnected");
    }
}