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
Next Recommended Reading Import Excel Data to Database Using C#