In this article I will explain how to connect to Oracle database from Windows application using C#
In this article I will explain how to connect to Oracle Database 11g Express Edition database from a Windows Forms application using C# and display data from Oracle Database to a DataGridView control.
Step 1: Download and install Oracle Database 11g Express Edition
Go to http://www.oracle.com/technetwork/products/express-edition/downloads/index.html and download express edition of the Oracle Database 11g. You need to sign up to download this setup. Select 32 bit or 64 bit setup according to your OS.
Extract downloaded zip file and run setup.exe file inside OracleXE112_Win32\DISK1 folder. Just follow the setup. This will install the database to your system.
Note: While installing this setup, it will suggest default port numbers for TNS Port, MTS Port, and HTTP Port. But if any of the port is already in use. In that case, change that port to a port which is not in use and click on Back and then Next button
Step 2: Download and install SQL Developer
Go to http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html and download SQL Developer (Oracle equivalent of SQL Server’s Management Studio), GUI tool to browse and create database objects.
Step 3: Configure sample table provided with the database
Uncompress downloaded zip file of SQL Developer and run sqldeveloper.exe from sqldeveloper-3.2.20.09.87\sqldeveloper folder to open SQL Developer
In the Connections tab, expand Auto-Generated Local Connections and then system-XE. Now it will ask for Username and Password. Leave pre entered username ‘system’ and enter the password that you had given at the time of installing Oracle Database(Step 1) and click OK.
Note: If Auto-Generated Local Connections is not displayed inside Connections tab, right click on the “Connections” and select “Create Local Connections”. It should create connection for system-XE.
We will display data from Employees table that is provided with the database inside HR user. To be able to access this table from .NET application we have to grant some privileges to the HR user.
Expand Other Users and Right click on the HR. Click Edit User to open Create/Edit User dialog box. Go to Roles tab and ensure that Granted and Default Checkbox against Connect and Resource roles are checked.
Step 4: Create Windows Application to fetch data from the database
Create a Windows Forms application and drag a DataGridView control on Form1.
Set Dock property of the DataGridView to Fill,
Step 5: Include Oracle data access library to the project
Add reference to Oracle.DataAccess dll using Add Reference dialog box. You can find it in .NET tab. And following namespace at the top
Step 6: Write code to retrieve data from the database
private void Form1_Load(object sender, EventArgs e)
private void LoadData()
string ConString = "Data Source=XE;User Id=system;Password=*****;";
using (OracleConnection con = new OracleConnection(ConString))
OracleCommand cmd = new OracleCommand("SELECT * FROM HR.Employees", con);
OracleDataAdapter oda = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
if (ds.Tables.Count > 0)
dataGridView1.DataSource = ds.Tables.DefaultView;
catch (Exception ex)
Data source name “XE” is the TNS name that contains connection information to connect to the Oracle Database. You can always change it to any other name. You can find it in a configuration file named TNSNAMES.ORA at following location
It looks something like the following
Output: All the rows from HR.Employees table is loaded in the DataGridView control