Let us assume we have Oracle properly installed in our system and also we have valid table created in the database. Lets say we have table name ‘emptab’. Table has two columns as follows:
empno NUMBER(4) PRIMARY KEY and,
ename VARCHAR2(10)
Given below are the steps for connecting to Oracle database using C# :-
Step 1:Include Required Namespaces
using System;
using System.Data;
using Oracle.DataAccess.Client;
Step2: Set the connection parameters like the User Id, Password and Data Source:
Step 2.1: Create the connection object
OracleConnection con = new OracleConnection();
Step 2.2: Specify the connect string. NOTE: Modify User Id, Password, Data Source as per your database set up.
con.ConnectionString = "User Id=scott;Password=tiger;Data Source=orcl9i;";
Step 3: Open database connection through ODP.NET(Oracle Data Provider for .NET):
try
{
// Open the connection
con.Open();
Console.WriteLine("Connection to Oracle database established successfully !");
Console.WriteLine(" ");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Till step 3 we are done with establishing connection with Oracle database. Further steps shows how to query database table and close the opened connection when work is completed.
Step 4: Create command object to perform a query against the database:
string cmdQuery = "SELECT empno, ename FROM emptab";
//Create the OracleCommand object
OracleCommand cmd = new OracleCommand(cmdQuery);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
Step 5: Fetch data into an OracleDataReader object and display the data on the console. Then, close the connection object
try
{
// Execute command, create OracleDataReader object
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
// Output Employee Name and Number
Console.WriteLine("Employee Number: " + reader.GetDecimal(0) +
" , " + "Employee Name : " + reader.GetString(1));
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Dispose OracleCommand object
cmd.Dispose();
// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
}