Connecting To Oracle Database Using C#

Introduction

This article explains how to connect to an Oracle database using C#. We all understand that Oracle is the most widely used database system available in the market. And on the other hand if we talk about the .Net framework, it’s a software framework developed by the software giant Microsoft (we all know this), it includes a large class library known as the Framework Class Library (FCL) and provides language interoperability across several programming languages.

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 and what all the issues are that one can encounter and of course how to tackle them.

Prerequisites

1. Microsoft Visual Studio 2008 or higher (I’ll be using Visual Studio 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 the following assembly files.

• Oracle.DataAccess.dll (this assembly file is required)
• Oracle.ManagedDataAccess.dll (optional)
• Oracle.Web.dll (optional)

Add the following references:

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 the references have been added to the project.

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

Go to the web.config file and you can see the following code.



And you can also see a folder name, bin, that has already been created in the Solution Explorer.

I hope everything is fine until now!

Now for the main part. Yes, the code.

First you need to be familiar with TNS entries, or else you might encounter the 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 the code itself to prevent such errors.

The following is the sample of how to create TNS entries.



You must understand the following details before creating TNS entries.

1. Your host provider.
   
   This SQL query can help you determine the host name,
   
   

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

2. You need to know the Service name. You can find it in tnsnames.ora. This allows 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.

The source code

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; & nbsp;
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=<strong>******</strong>;";
    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");
    }
}

Remarks

• OracleConnection(): Initializes a new instance of the OracleConnection.
• OracleConnection(oradb): Initializes a new instance of the OracleConnection class with the specified connection string.
• OracleCommand(): Initializes a new instance of the OracleCommand.
• CommandText: Gets or sets the SQL statement or Stored Procedure to execute against the database. (Overrides DbCommand.CommandText.)
• Connection: Gets or sets the OracleConnection used by this instance of the OracleCommand.
• OracleDataReader: To create an OracleDataReader, you must call the ExecuteReader method of the OracleCommand object, rather than directly using a constructor. Changes made to a resultset by another process or thread when data is being read may be visible to the user of the OracleDataReader.

Output