How to use SqlDataReader class in ASP.NET

Introduction

Provides a way of reading a forward-only stream of rows from a SQL Server database (according to msdn library). Means you can say a SqlDataReader class read DataRows in forward only stream. SqlDataReader class available in  System.Data.SqlClient namespace.

There are some useable public properties available in Library

 1. HasRows : You can check that your DataReader either empty or take some DataRows.
 2. FieldCount : Obtains numbers of fields in current rows.

There are some public method available in Library

Read( ) : Read Next record from the database table.
GetValue( ) : Gets the value of the specified column in its native format.

Lets take an simple example to read data from database_table using SqlDataReader class.

Algorithm behind the example

Step-1 : Create a SqlConnection( ) with proper connection string parameter.

SqlConnection con = new SqlConnection();
        con.ConnectionString =ConfigurationManager.ConnectionStrings ["ConnectionString"].ToString ();

        con.Open ();

Step-2 : Retrieve data from Database table using SqlCommand Class.

SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "select * from deltable";

        cmd.Connection = con;

Step-3 : Create a SqlDataReader, you must call the ExecuteReader method of the SqlCommand object, instead of directly using a constructor.

SqlDataReader rd = cmd.ExecuteReader();

Step-4: Read data from Read() method 

while (rd.Read())
        {

            snolabel.Text += "sno=" + rd.GetValue(0) + "<br/>";
            name.Text += "name=" + rd.GetValue(1) + "<br/>";
            Addlabel.Text += "Address=" + rd.GetValue(2) + "<br/>";
 
        }
 Step-5: Close reader.

Complete code


   <%@ Page Language="C#" AutoEventWireup="true" CodeFile="binding.aspx.cs" Inherits="binding" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="GetData"
        Width="123px" />
    <br />
    <br />
    <asp:Label ID="snolabel" runat="server"></asp:Label>&nbsp;&nbsp;&nbsp;&nbsp;
    <br />
    <div>
   
    <asp:Label ID="name" runat="server"></asp:Label>
   
        <br />
   
    </div>
    <p>
        <asp:Label ID="Addlabel" runat="server"></asp:Label>
    </p>
    </form>
</body>

</html>

Codebehind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.IO;

public partial class binding : System.Web.UI.Page
{
    int sno = 0;
    string Name = String.Empty;
    String Address = String.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString =ConfigurationManager.ConnectionStrings ["ConnectionString"].ToString ();
        con.Open ();

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "select * from deltable";
        cmd.Connection = con;
        SqlDataReader rd = cmd.ExecuteReader();
       
        while (rd.Read())
        {

            snolabel.Text += "sno=" + rd.GetValue(0) + "<br/>";
            name.Text += "name=" + rd.GetValue(1) + "<br/>";
            Addlabel.Text += "Address=" + rd.GetValue(2) + "<br/>";
 
        }
      
        rd.Close();
      
      



    }

   
}
Output
How to use SqlDataReader class in ASP.NET

How to use SqlDataReader class in ASP.NET How to use SqlDataReader class in ASP.NET Reviewed by Jacob Lefore on October 21, 2013 Rating: 5

No comments