Load More Records in ASP.NET Gridview on Button Click from SQL Server Table

Webcodeexpert.com

4.62/5 (15 votes)

Jun 2, 2014

CPOL

2 min read

35114

In this article, I am going to demonstrate how to get more data on demand i.e. On every click of button fetch more records from SQL Server database table and load in GridView with wait/progress/loading image as shown in image in ASP.NET using both C# and VB languages.

Introduction

Basically you will learn the following through this article:

How to initially bind specified number of records from SQL Server database table to Gridview.
How to bind some data in Gridview and on every click of "Load More Databutton" fetch more data and load in Gridview.
How to show wait/progress loading image using Ajax "UpdateProgress" and "ProgressTemplate" while fetching more data from SQL Server table and binding to gridview.

Using the Code

Implementation: Let's create a demo website to demonstrate the concept.

First of all, create a database in SQL Server and name it, e.g. "DB_Student" and in this database, create a table with the following Columns and Data type as shown below and name this table "Tbl_Student".

Column Name	Data Type
`StudentId`	`Int(Primary Key. So set is identity=true)`
`StudentName`	`varchar(100)`
`Class`	`varchar(50)`
`Age`	`Int`
`Gender`	`varchar(50)`
`Address`	`varchar(500)`

Create a stored procedure to get student details to be filled in GridView Data Control.

CREATE  PROCEDURE [dbo].[GetStudentDetails_SP]
                @topVal INT
AS
BEGIN
--if @topVal=2 then the below query will become SELECT top (2) * FROM dbo.Tbl_Student and get 2 records
                SELECT top (@topVal) * FROM dbo.Tbl_Student
END

Create another stored procedure to count the number of rows present in table:

CREATE PROCEDURE [dbo].[GetStudentDetailsCount_SP]             
AS
BEGIN
                SELECT COUNT(*) FROM dbo.Tbl_Student
END

Now let's connect our ASP.NET application with SQL Server database.

So in the <configuration> tag of web.config file, create the connection string as:

<connectionStrings>
    <add name="conStr" connectionString="Data Source=LALIT;_
    Initial Catalog=DB_Student;Integrated Security=True"/>
  </connectionStrings>

Note: Replace the Data Source and Initial Catalog as per your database settings.

ASP.NET C# Section

Below is the HTML source of the Default.aspx page.

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

<!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">
    <div>
    <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>       
<fieldset style="width:370px;">
    <legend>Load more data on demand in GridView</legend>
    <table>
    <tr>
    <td>
     <asp:GridView ID="grdStudentDetails" runat="server" AutoGenerateColumns="False"
            CellPadding="4" ForeColor="#333333" GridLines="None">
         <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        <Columns>
        <asp:BoundField HeaderText="Student Name"  DataField="StudentName" />
        <asp:BoundField HeaderText="Class"  DataField="Class" />
        <asp:BoundField HeaderText="Age"  DataField="Age" />
        <asp:BoundField HeaderText="Gender"  DataField="Gender" />
        <asp:BoundField HeaderText="Address"  DataField="Address" />    
        </Columns>
         <EditRowStyle BackColor="#999999" />
         <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
         <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
         <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
         <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
         <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
         <SortedAscendingCellStyle BackColor="#E9E7E2" />
         <SortedAscendingHeaderStyle BackColor="#506C8C" />
         <SortedDescendingCellStyle BackColor="#FFFDF8" />
         <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
        </asp:GridView>        
    </td>
    </tr>
    <tr>
    <td align="center">
        <asp:Button ID="btnLoadMore" runat="server" Text="Load More Data"
            onclick="btnLoadMore_Click" />
            </td>
            </tr>
            <tr>
            <td align="center">
            <asp:UpdateProgress ID="UpdateProgress1" runat="server" 
            ClientIDMode="Static"DisplayAfter="10">
    <ProgressTemplate>
        <img src="ajax-loader.gif" alt="wait image" />   
    </ProgressTemplate>
    </asp:UpdateProgress>
            </td></tr>
    </table>
    </fieldset>
    </ContentTemplate>   
        </asp:UpdatePanel>   
    </div>
    </form>
</body>
</html>

Note: You need to search on the internet and download any wait/progress GIF image of your choice and name it "ajax-loader.gif" and paste in root folder of your project.

ASP.NET C# Code Section

In code behind file (default.aspx.cs), write the code as:

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

public partial class _Default: System.Web.UI.Page
{ 
    SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

   int num = 0;

protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
          //Set the num variable equal to the value that you want to load data in GridView.
          //e.g if initially you want to load 2 rows in Gridview then set num=2.
            num = 2;
          //Store this num variable value in ViewState so that we can get this value on Load more button click
            ViewState["num"]=num;
            BindGridView(num);
        }
    }

    private void BindGridView(int numOfRows)
    {
        DataTable dt = new DataTable();
        SqlCommand cmd = null;
        SqlDataAdapter adp = null;
        try
        {
            //get number rows in table by calling the rowCount function i created.
            int rCount = rowCount();

            // hide the "Load More Data button" if the number of requested rows becomes greater than the rows in table
            if (numOfRows > rCount)
            {
                btnLoadMore.Visible = false;
            }
            cmd = new SqlCommand("GetStudentDetails_SP", con);

            //Passs numOfRows variable value to stored procedure to get desired number of rows

            cmd.Parameters.AddWithValue("@topVal", numOfRows);
            cmd.CommandType = CommandType.StoredProcedure;
            adp = new SqlDataAdapter(cmd);
            adp.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                grdStudentDetails.DataSource = dt;
                grdStudentDetails.DataBind();
            }
            else
            {
                grdStudentDetails.DataSource = null;
                grdStudentDetails.DataBind();
            }
        }
        catch (Exception ex)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", 
            "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", true);
        }
        finally
        {
            con.Close();
            cmd.Dispose();
            adp = null;
            dt.Clear();
            dt.Dispose();
        }
    }

    protected int rowCount()
    {
        int NoOfRows = 0;
        SqlCommand cmd = new SqlCommand("GetStudentDetailsCount_SP", con);
        cmd.CommandType = CommandType.StoredProcedure;

        try
        {
            con.Open();
            NoOfRows = Convert.ToInt32(cmd.ExecuteScalar());
        }
        catch (Exception ex)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", 
            "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", true);
        }
        finally
        {
            con.Close();
            cmd.Dispose();
        }
        return NoOfRows;
    }

    protected void btnLoadMore_Click(object sender, EventArgs e)
    {
        //On every click of this button it will add 2 to the ViewState["num"]
        //whose value was set to 2 initially on page load. So numval is 4 now.

        int numVal = Convert.ToInt32(ViewState["num"])+ 2;

        //Now pass numVal whose value is 4 to the BindGridView function to get 4 rows.

        BindGridView(numVal);

        //Set ViewState["num"] equal to the numVal i.e. 4 so that 
        //when we again click this button it will be 4 + 2= 6 and so on.

        ViewState["num"]= numVal;
    }
}

ASP.NET VB Section

Design the page (default.aspx) as in above ASP.NET C# section, but replace the lines:

<asp:Button ID="btnLoadMore" runat="server" Text="Load More Data"
onclick="btnLoadMore_Click" />

with the following line:

<asp:Button ID="btnLoadMore" 
runat="server" Text="Load More Data" />

In the code behind file (e.g. default.aspx.vb), write the code as:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Partial Class _Default
    Inherits System.Web.UI.Page

    Dim con As NewSqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

    Dim num As Integer = 0


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

        If Not Page.IsPostBack Then
            'Set the num variable equal to the value that you want to load data in gridView
            'e.g if initially you want to load 2 rows in GridView then set num=2.

            num = 2

            'Store this num variable value in ViewState so that we can get this value on Load More Data button click

            ViewState("num")=2
            BindGridView(num)
        End If
    End Sub


    Private Sub BindGridView(numOfRows As Integer)
        Dim dt As New DataTable()
        Dim cmd As SqlCommand = Nothing
        Dim adp As SqlDataAdapter = Nothing

        Try
            'get number rows in table by calling the rowCount function i created.
            Dim rCount As Integer = rowCount()

            'hide the "Load More Data button" 
            'if the number of requested rows becomes greater than the rows in table

            If numOfRows > rCount Then
                btnLoadMore.Visible = False
            End If

            cmd = New SqlCommand("GetStudentDetails_SP", con)

            'Passs numOfRows variable value to stored procedure to get desired number of rows

            cmd.Parameters.AddWithValue("@topVal", numOfRows)
            cmd.CommandType = CommandType.StoredProcedure
            adp = New SqlDataAdapter(cmd)
            adp.Fill(dt)

            If dt.Rows.Count > 0 Then
                grdStudentDetails.DataSource = dt
                grdStudentDetails.DataBind()
            Else
                grdStudentDetails.DataSource = Nothing
                grdStudentDetails.DataBind()
            End If
        Catch ex As Exception
            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", _
            "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", True)

        Finally
            con.Close()
            cmd.Dispose()
            adp = Nothing
            dt.Clear()
            dt.Dispose()
        End Try
    End Sub

    Protected Function rowCount() As Integer
        Dim NoOfRows As Integer = 0
        Dim cmd As New SqlCommand("GetStudentDetailsCount_SP", con)
        cmd.CommandType = CommandType.StoredProcedure

        Try
            con.Open()
            NoOfRows = Convert.ToInt32(cmd.ExecuteScalar())
        Catch ex As Exception
            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", _
            "alert('Oops!! Error occured: " + ex.Message.ToString() + "');", True)

        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return NoOfRows
    End Function

    Protected Sub btnLoadMore_Click(sender As Object, e As System.EventArgs) HandlesbtnLoadMore.Click

        'On every click of this button it will add 2 to the ViewState("num") 
        'whose value was set to 2 initially on page load. So numval is 4 now.

        Dim numVal As Integer = Convert.ToInt32(ViewState("num")) + 2

        'Now pass numVal whose value is 4 to the BindGridView function to get 4 rows.

        BindGridView(numVal)

        'Set ViewState("num") equal to the numVal i.e. 4 so that 
        'when we again click this button it will be 4 + 2= 6 and so on.

        ViewState("num") = numVal
    End Sub
End Class