Connecting to Oracle Database From Windows Forms Application Using C#

Introduction

In this article, I will explain how to connect to an Oracle Database 11g Express Edition database from a Windows Forms application using C# and display data from the 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 the express edition of the Oracle Database 11g. You need to sign up to download this setup. Select 32 bit or 64 bit setup depending on your OS.

Extract the downloaded zip file and run the setup.exe file inside the 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 ports are already in use, change that port to a port which is not in use and click on the Back and then the 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 the downloaded zip file of SQL Developer and run sqldeveloper.exe from the 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 the default username "system" and enter the password that you had given at the time of installing Oracle Database (Step 1) and click "OK".

We will display data from the Employees table that is provided with the database inside HR user. To be able to access this table from a .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 the "Create/Edit User" dialog box. Go to the "Roles" tab and ensure that "Granted" and the "Default" checkbox against "Connect and Resource roles" are checked.

Step 4

Create a Windows Application to fetch data from the database.

Create a Windows Forms application and drag a DataGridView control onto Form1.

Set the Dock property of the DataGridView to Fill.

Step 5

Include the Oracle data access library in the project.

Add a reference to the Oracle.DataAccess dll using the Add Reference dialog box. You can find it in the .NET tab. And add the following namespace at the top:

using Oracle.DataAccess.Client;

Step 6

Write code to retrieve data from the database, as in the followng:

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());
    }
}

You may wonder about the data source name "XE". It is the name that contains the connection information of the Oracle Database. You can always change it to any other name. You can find it at the following location:

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\ tnsnames.ora

It looks something like the following:

Output

All the rows from HR.Employees table are loaded in the DataGridView control.