Post
Ask Question

Fetch Data from Oracle Database Using C#

Introduction

In this post we will discuss how to fetch data from Oracle database using C#. we will OracleDataReader . Before proceeding further I would suggest to go through Connecting To Oracle Database Using C#.

Go through the below code.

Default.aspx

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title>Fetch Data From Oracle DataBase</title>  
  8. </head>  
  9. <body>  
  10.     <form id="form1" runat="server">  
  11.     <div>  
  12.      <asp:GridView ID="grid" runat="server" ></asp:GridView>  
  13.         <asp:Button ID="btn_fetch" runat="server" Text="Fetch Data" OnClick="btn_fetch_Click" />  
  14.     </div>  
  15.     </form>  
  16. </body>  
  17. </html> 

Default.aspx.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using Oracle.DataAccess.Client;  
  8. using System.Data;  
  9.   
  10.   
  11. public partial class Default2 : System.Web.UI.Page  
  12. {  
  13.     //creating TNS entries   
  14.     string oradb = "Data Source=(DESCRIPTION =" +  
  15.     "(ADDRESS = (PROTOCOL = TCP)(HOST = your_host_name)(PORT = 1521))" +  
  16.     "(CONNECT_DATA =" +  
  17.       "(SERVER = DEDICATED)" +  
  18.       "(SERVICE_NAME = XE)));" +  
  19.       "User Id=your_user_id;Password=*******;";  
  20.     protected void Page_Load(object sender, EventArgs e)  
  21.     {  
  22.          
  23.     }  
  24.     protected void btn_fetch_Click(object sender, EventArgs e)  
  25.     {  
  26.         OracleConnection con = new OracleConnection(oradb);  
  27.         OracleCommand cmd = new OracleCommand();  
  28.         cmd.CommandText="select * from student";  
  29.         cmd.Connection = con;  
  30.         con.Open();  
  31.         OracleDataReader dr = cmd.ExecuteReader();  
  32.         if (dr.HasRows)  
  33.         {  
  34.             Response.Write("<table border='1'>");  
  35.             Response.Write("<tr><th>Name</th><th>Roll No</th></tr>");  
  36.             while (dr.Read())  
  37.             {  
  38.   
  39.                 Response.Write("<tr>");  
  40.                 Response.Write("<td>" + dr["name"].ToString() + "</td>");  
  41.                 Response.Write("<td>" + dr["roll_no"].ToString() + "</td>");  
  42.                 Response.Write("</tr>");  
  43.             }  
  44.             Response.Write("</table>");  
  45.         }  
  46.         else  
  47.         {  
  48.             Response.Write("No Data In DataBase");  
  49.         }  
  50.         con.Close();  
  51.     }  
  52. } 

Have a look at below code.

code

  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