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" %>
  3. <!DOCTYPE html>
  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;
  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. {
  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. {
  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