Connecting to Oracle database from Windows Forms application using C#

Download source code

In this article I will explain how to connect to Oracle database from Windows application using C#

Introduction

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.

Description

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_Win32DISK1 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.87sqldeveloper 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.

New Project_2013-01-05_23-20-25

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

using Oracle.DataAccess.Client;

 Add Reference_2013-01-05_23-13-14

Step 6: Write code to retrieve data from the database

private void Form1_Load(object sender, EventArgs e)
{
     LoadData();
}

private void LoadData()
{
     try
     {
          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();
               oda.Fill(ds);
               if (ds.Tables.Count > 0)
               {
                    dataGridView1.DataSource = ds.Tables[0].DefaultView;
               }
          }
     }
     catch (Exception ex)
     {
          MessageBox.Show(ex.ToString());
     }
}

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

D:oraclexeapporacleproduct11.2.0servernetworkADMIN tnsnames.ora

It looks something like the following

tnsnames

Output: All the rows from HR.Employees table is loaded in the DataGridView control

 Form1_2013-01-05_23-41-28

References:

http://docs.oracle.com/cd/E17781_01/admin.112/e18585/toc.htm

http://www.orafaq.com/wiki/Tnsnames.ora

3 thoughts on “Connecting to Oracle database from Windows Forms application using C#

  1. Pingback: Creating database diagram of Oracle database using Oracle SQL Developer | DEEPAK SHARMA

Leave a Reply

Your email address will not be published. Required fields are marked *