Connecting to Oracle Database from C# .Net

Just like connecting to a SQLServer database, it is also possible to connect to an Oracle Database using the following approach.

Prerequisites
- Install the Oracle Server or Oracle Express Edition.
- If the Oracle server is placed in a different / remote machine then, Install Oracle Client software, which is compatible to the version of the Oracle server.
- Install the correct version of Oracle Data Access Components (ODAC) which is compatible with the version of the Oracle Client, and the version of the .Net framework. This will provide the supporting assemblies to connect to Oracle.

Both these softwares are available free and can be downloaded from the Oracle site (http://www.oracle.com)

Once the required files are installed, we can connect to the Oracle database from C# .Net code.

Connection String:
strConnection = "Data Source=(DESCRIPTION=" + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=xxxx)))" + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxxxxx)));" + "User Id= xxxxxx;Password= xxxxxx;";

In the above connection string replace xxxxx with the correct values for the following
HOST (IP Address of the Oracle Server)
PORT (Port of connection, by default it is 1521)
SERVICE_NAME (Service name for the Oracle Server)
User Id (User ID to connect to the Oracle Server)
Password (Password to connect to the Oracle Server)

C# Code
Before proceeding with the code, make sure that you add a reference to the System.Data.OracleClient assembly from the GAC

Add the following line to the top of the C# class
using System.Data.OracleClient;

OracleConnection conn = null;
OracleCommand cmd = null;
OracleDataAdapter objDA = null;

conn = new OracleConnection(strConn);

string strQuery = "Select * From Employee";

cmd = new OracleCommand(strQuery, conn);

objDA = new OracleDataAdapter(cmd);

objDA.Fill(dsEmployee,"Employee");

That's it, we have connected to the Oracle Database from C#.Net code.