Search

Monday, October 21, 2013
0 comments

How to use SqlDataReader class in ASP.NET

12:57 AMMonday, October 21, 2013

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

Protected by Copyscape Online Copyright Protection Software

0 comments :

Post a Comment

 
Toggle Footer
Top