Fetch Data From Oracle Database Using C#

Introduction

In this article, we will discuss how to fetch data from an Oracle database using C#. We will use the OracleDataReader. Before proceeding further I suggest you go through Connecting To Oracle Database Using C#.

Go through the following code.

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>  
<!DOCTYPE html>  
<html xmlns="http://www.w3.org/1999/xhtml">  
<head runat="server">  
    <title>Fetch Data From Oracle DataBase</title>  
</head>  
<body>  
    <form id="form1" runat="server">  
    <div>  
     <asp:GridView ID="grid" runat="server" ></asp:GridView>  
        <asp:Button ID="btn_fetch" runat="server" Text="Fetch Data" OnClick="btn_fetch_Click" />  
    </div>  
    </form>  
</body>  
</html> 

Default.aspx.cs

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Web;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
using Oracle.DataAccess.Client;  
using System.Data;  
public partial class Default2 : System.Web.UI.Page  
{  
    //creating TNS entries   
    string oradb = "Data Source=(DESCRIPTION =" +  
    "(ADDRESS = (PROTOCOL = TCP)(HOST = your_host_name)(PORT = 1521))" +  
    "(CONNECT_DATA =" +  
      "(SERVER = DEDICATED)" +  
      "(SERVICE_NAME = XE)));" +  
      "User Id=your_user_id;Password=*******;";  
    protected void Page_Load(object sender, EventArgs e)  
    {      
    }  
    protected void btn_fetch_Click(object sender, EventArgs e)  
    {  
        OracleConnection con = new OracleConnection(oradb);  
        OracleCommand cmd = new OracleCommand();  
        cmd.CommandText="select * from student";  
        cmd.Connection = con;  
        con.Open();  
        OracleDataReader dr = cmd.ExecuteReader();  
        if (dr.HasRows)  
        {  
            Response.Write("<table border='1'>");  
            Response.Write("<tr><th>Name</th><th>Roll No</th></tr>");  
            while (dr.Read())  
            {  
                Response.Write("<tr>");  
                Response.Write("<td>" + dr["name"].ToString() + "</td>");  
                Response.Write("<td>" + dr["roll_no"].ToString() + "</td>");  
                Response.Write("</tr>");  
            }  
            Response.Write("</table>");  
        }  
        else  
        {  
            Response.Write("No Data In DataBase");  
        }  
        con.Close();  
    }  
} 

Have a look at the following code.

Select query

  1. OracleConnection(): Initializes a new instance of the OracleConnection.
  2. OracleConnection(oradb): Initializes a new instance of the OracleConnection class with the specified connection string.
  3. OracleCommand(): Initializes a new instance of the OracleCommand.
  4. CommandText: Gets or sets the SQL statement or Stored Procedure to execute against the database. (Overrides DbCommand.CommandText.).
  5. Connection: Gets or sets the OracleConnection used by this instance of the OracleCommand.
  6. OracleDataReader: To create an OracleDataReader, you must call the ExecuteReader method of the OracleCommand object, rather than directly using a constructor. Changes made to a resultset by another process or thread while data is being read may be visible to the user of the OracleDataReader.

Output

Output


Similar Articles