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

How to load more data in repeater on demand with progress wait image in asp.net
Introduction: In this article i am going to share the code to get/fetch more rows/records on demand in repeater data control on button click with wait/progress image using Ajax UpdateProgress while fetching records from sql server 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 bind desired number of records/data in from sql server database table to repeater.
  • How to initially bind some data in repeater and on every click of "Load More Data button" get more data and bind in repeater.
  • How to display wait/progress image using Ajax "UpdateProgress" and "ProgressTemplate" while fetching more data from sql server table.
In previous articles i explained How to Load more records in Asp.Net Gridview on button click from sql and   Bind, edit, update and delete data in Repeater  and Upload and save image in binary format in Sql server database and retrieve, bind to Repeater and Custom paging in Repeater  and Show Empty template in repeater with header and No records found message and Highlight Repeater cell background color based on condition and Ajax ModalPopupExtender example to open login form in popup window and Close Ajax ModalPopUpExtender on Background click outside the popup window

 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 Repeater .
 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:365px;">
    <legend>Load more data on demand in repeater</legend>
    <table>
    <tr>
    <td>
      <asp:Repeater ID="rptStudentDetails" runat="server">
        <HeaderTemplate>
        <table border="1" cellpadding="1">
        <tr style="background-color:#fa7b16; color:#FFF; height:35px;" align="center">
        <th>Student Name</th>
        <th>Class</th>
        <th>Age</th>
        <th>Gender</th>
        <th>Address</th>
        </tr>
        </HeaderTemplate>
        <ItemTemplate>
        <tr style="background-color:white;" align="center">
        <td><%#Eval("StudentName") %></td>
        <td><%#Eval("Class") %></td>
        <td><%#Eval("Age") %></td>
        <td><%#Eval("Gender") %></td>
        <td><%#Eval("Address") %></td>
        </tr>
        </ItemTemplate>      
        <FooterTemplate>
        </table>       
        </FooterTemplate>
        </asp:Repeater> 
    </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 repeater.
            //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;
               BindRepeater(num);
        }
    }

    private void BindRepeater(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 request 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)
            {
                rptStudentDetails.DataSource = dt;
                rptStudentDetails.DataBind();
            }
            else
            {
                rptStudentDetails.DataSource = null;
                rptStudentDetails.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 BindRepeater function to get 4 rows.
        BindRepeater(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 repeater.
            '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
            BindRepeater(num)
        End If
    End Sub
  
    Private Sub BindRepeater(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 request 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
                rptStudentDetails.DataSource = dt
                rptStudentDetails.DataBind()
            Else
                rptStudentDetails.DataSource = Nothing
                rptStudentDetails.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 BindRepeater function to get 4 rows.
        BindRepeater(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 repeater  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 »

1 comments:

Click here for comments
Anonymous
admin
September 10, 2015 ×

In the above load more table, how to display as the table only for age 22

Congrats bro Anonymous you got PERTAMAX...! hehehehe...
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..