Load more records in Asp.Net Gridview on button click from sql server table

Load more records in asp.net gridview on button clickIntroduction: 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.

Description: Basically you will learn the following through this article: 
  • How to initially bind specified number of records/data from sql server database table to Gridview.
  • How to bind some data in Gridview and on every click of "Load More Data button" fetch more data and load in Gridview.
  • How to show wait/progress image using Ajax "UpdateProgress" and "ProgressTemplate" while fetching more data from sql server table and binding to gridview.

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 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 = new SqlConnection(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 repeater then set num=2.
            num = 2;
       //store this num value in ViewState so that we can get this value on Load more data 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"] is 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 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 New SqlConnection(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 repeater then set num=2.
            num = 2
            'store this num 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) Handles btnLoadMore.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") is 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
  
Now over to you:
" I hope you have got the way to load more data on demand in GridView in Asp.Net and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."  
Previous
Next Post »

6 comments

Click here for comments
Unknown
admin
June 01, 2014 ×

Good Post Lalit Sir, I like it.
Keep it up....

Reply
avatar
June 01, 2014 ×

Thanks Shaiwal Tripathi for appreciating my work..stay connected and keep reading for more useful articles like this...:)

Reply
avatar
Bhavik
admin
August 18, 2014 ×

Great Job.. Learn new thing

Reply
avatar
August 18, 2014 ×

Thanks Bhavik..i am glad you liked this articles..stay connected and keep reading:)

Reply
avatar
Anonymous
admin
September 16, 2014 ×

good article,am learned new concept..... :)

Reply
avatar
September 16, 2014 ×

I am glad you found this article useful for you..stay connected and keep reading..:)

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..