How to Bind Gridview with Datareader in asp.net

Introduction: In this post I have try to explain how to bind Gridview in Asp.net using SqlDatareader, Store Procedure.

Description:

I have created a table name STUDENT_DETAIL and insert data into table.

STUDENT_ID	int
STUDENT_NAME	varchar(50)
STUDENT_ADDRESS	varchar(50)
STUDENT_CLASS	varchar(50)

STUDENT_ID is primary key.

Now open the Visual Studio>Go to File>New>Website. Add the Connectionstring in web.config file of website.

<configuration>

       <connectionStrings>

    <add name="con" connectionString="Data Source=SYS-1F78031ED0A;Initial Catalog=TestBlog;Integrated Security=True"/>

       </connectionStrings>

       <system.web>

        <compilation debug="true" targetFramework="4.0" />

    </system.web>

</configuration>

After that add new web form to website, drag and drop the Gridview data control
from Toolbox.

<asp:GridView ID="grdstudentdetail" runat="server" AutoGenerateColumns="False" DataKeyNames="STUDENT_ID">

            <Columns>

                <asp:TemplateField HeaderText="STUDENT NAME">

                    <ItemTemplate>

                        <asp:Label ID="lblname" runat="server" Text='<%# Eval("STUDENT_NAME") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="STUDENT ADDRESS">

                    <ItemTemplate>

                        <asp:Label ID="lbladdress" runat="server" Text='<%# Eval("STUDENT_ADDRESS") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="STUDENT CLASS">

                    <ItemTemplate>

                        <asp:Label ID="lblclass" runat="server" Text='<%# Eval("STUDENT_CLASS") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

            </Columns>

        </asp:GridView>

Now go to .aspx.cs page and add namespace. Here DISPLAY_DATA is Store Procedure.

using System.Configuration;

using System.Data.SqlClient;

using System.Data;

Create a function to bind Gridview.

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

    protected void Page_Load(object sender, EventArgs e)

    {

        if(!IsPostBack)

        {

            Bindgrid();

        }

    }

    private void Bindgrid()

    {

        SqlCommand cmd = new SqlCommand("DISPLAY_DATA", con);

        con.Open();

        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)

        {

            grdstudentdetail.DataSource = dr;

            grdstudentdetail.DataBind();

            con.Close();

        }

        else

        {

            grdstudentdetail.DataSource = null;

            grdstudentdetail.DataBind();

            Messagebox("No Records Aviable");

        }

    }

         //To show message

    private void Messagebox(string Message)

    {

        Label lblMessageBox = new Label();

        lblMessageBox.Text =

            "<script language='javascript'>" + Environment.NewLine +

            "window.alert('" + Message + "')</script>";

        Page.Controls.Add(lblMessageBox);

    }

In VB

Add namespace to .aspx.vb page.

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Create a function to bind Gridview.

Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ToString())

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then

            Bindgrid()

        End If

    End Sub

    Private Sub Bindgrid()

        Dim cmd As New SqlCommand("DISPLAY_DATA", con)

        con.Open()

        Dim dr As SqlDataReader = cmd.ExecuteReader()

        If dr.HasRows Then

            grdstudentdetail.DataSource = dr

            grdstudentdetail.DataBind()

            con.Close()

        Else

            grdstudentdetail.DataSource = Nothing

            grdstudentdetail.DataBind()

            Messagebox("No Records Aviable")

        End If

    End Sub

    'To show message

    Private Sub Messagebox(ByVal Message As String)

        Dim lblMessageBox As New Label()

        lblMessageBox.Text = "<script language='javascript'>" + Environment.NewLine & "window.alert('" & Message & "')</script>"

        Page.Controls.Add(lblMessageBox)

    End Sub

Now debug the application and check the result.

Related Articles on Gridview:

Ø  How toBind,Edit,Update and Delete in Gridview in Asp.net

Ø  How to bind Gridview using Sqldataadapter, Datatable andQuery in Asp.net

Ø  How to highlight row on mouse hover in Gridview

Ø  How to edit and update Dropdownlist in Gridview datacontrol in Asp.net

Ø  How to Search Records in Gridview in Asp.net

Ø  How to Bind Gridview using Store Procedure, SqlDataAdapterand Datatable in Asp.net

Ø  How to use RadioButtonList control inside the Gridview inAsp.net

                         

  ØHow to use Fileupload control in Gridview inAsp.net

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.