How to Fetch Data from Oracle Database in C#?

In this tutorial, you will learn how to fetch data from Oracle database in C# (C-Sharp). The following are the software version information used for this example:

• Visual Studio 2017
• Oracle Database 11g (Schema HR)
• Oracle Data Access Components for VS.Net (check the following post to how to install and configure ODAC for Visual Studio 2017)

In the below example, It will ask the user to input the department number, and after entering the department number when a user presses the button, it will fetch the first name and last name data from the Employees table to display in Data Grid View. The C# program will connect to Oracle database using the ODP.NET managed driver.

Create Following Objects in C# Windows Desktop Application to Test:

1. Form (name Form1)
2. TextBox (name textBox1)
3. Label (name label1)
4. Button (name button1)
5. DataGridView (name dataGridView1)

Fetch Data From Oracle Database in C# Example

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client; // ODP.NET, Managed Driver 
using Oracle.ManagedDataAccess.Types;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {

        }
        private void button1_Click(object sender, EventArgs e)
        {
            string oradb = "Data Source=localhost:1521/orcl;User Id=hr;Password=hrpsw;";

            OracleConnection conn = new OracleConnection(oradb);
            conn.Open();
            OracleCommand cmd = new OracleCommand();
            OracleParameter parm = new OracleParameter();
            OracleParameter parm1 = new OracleParameter();
            parm.OracleDbType = OracleDbType.Int64;

            parm.Value = textBox1.Text;
            cmd.Connection = conn;
            cmd.Parameters.Add(parm);
            cmd.CommandText = "select first_name, last_name from employees where department_id = :1";
            cmd.CommandType = CommandType.Text;
            DataTable tbl = new DataTable();
            tbl.Columns.Add("first_name", typeof(string));
            tbl.Columns.Add("last_name", typeof(string));

            try
            {
                OracleDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                    tbl.Rows.Add(dr.GetString(0), dr.GetString(1));
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error");
            }
            dataGridView1.DataSource = tbl;
            conn.Dispose();
        }
    }
}

Output