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.
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 .
@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
<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.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 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."
1 comments:
Click here for commentsIn the above load more table, how to display as the table only for age 22
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..