Some time we don’t need to show full data from database. In
starting we show few records, then according to requirement we fetch more record.In this article I will describe the method to do this:
1.
Bind data in from database
table to repeater.
2.
Initially we bind few records in repeater and on click of
"Load More Data button" fetch more data and bind it to repeater.
3.
Display progress image using Ajax "UpdateProgress" and
"ProgressTemplate" while fetching more data from Databse.
Create a DataBase “Test” in Sql server, now create a table “Dept_table” with the following Columns and Data type as shown below :
Column
Name
|
Data
Type
|
Dept_Id_Pk
|
Int(Primary Key. So set is
identity=true)
|
Dept_Name
|
varchar(100)
|
- Create a stored procedure
to get student details to be filled in Repeater .
CREATE PROCEDURE [dbo].[GetDept_SP]
@topVal INT
AS
BEGIN
--if @topVal=2 then the below query will become SELECT top (2) *
FROM dbo. Dept_table and get 2 records
SELECT top (@topVal) * FROM dbo.Dept_table
END
Create
another stored procedure to count the number of rows
present in table
CREATE PROCEDURE [dbo].[GetDeptCount_SP]
AS
BEGIN
SELECT COUNT(*) FROM dbo.Dept_table
END
Now let's
connect application with Sql Server database:
<connectionStrings>
<add name="EmpCon" connectionString="Data Source=localhost;Initial
Catalog=test;IntegratedSecurity=True"/>
</connectionStrings>
Asp.Net C# Section
Design
Part:
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<style
type="text/css">
.style1
{
width:
22px;
}
.style2
{
width:
20px;
}
</style>
</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:200px; text-align:center;">
<legend>Load more data in Repeater</legend>
<table>
<tr><td class="style1"></td>
<td
align="center">
<asp:Repeater ID="rptDept" runat="server">
<HeaderTemplate>
<table border="1" cellpadding="1" width="200px;">
<tr style="background-color:#1B3854; color:#fff; height:30px;" align="center">
<th>Department
Name</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr style="background-color:white;" align="center">
<td><%#Eval("Dept_Name")
%></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</td>
<td
class="style2"></td>
</tr>
<tr><td class="style1"> </td>
<td
align="center">
</td><td class="style2"> </td>
</tr>
<tr>
<td class="style1">
</td>
<td align="center">
<asp:Button ID="btnLoadMore" runat="server" onclick="btnLoadMore_Click"
Text="Load More Data" />
</td>
<td class="style2">
</td>
</tr>
<tr><td class="style1"></td>
<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>
Place “ajax-loader.gif” in your root folder.
Asp.Net C# Code Section:
In code
behind, 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 Re_peater_button : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].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("GetDept_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)
{
rptDept.DataSource = dt;
rptDept.DataBind();
}
else
{
rptDept.DataSource = null;
rptDept.DataBind();
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(),
"Message", "alert('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("GetDeptCount_SP", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
NoOfRows = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(),
"Message", "alert('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 ,write the code as:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class Re_peater_button
Inherits System.Web.UI.Page
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Empcon").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("GetDept_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
rptDept.DataSource = dt
rptDept.DataBind()
Else
rptDept.DataSource = Nothing
rptDept.DataBind()
End If
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('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("GetDeptCount _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(' 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
0 comments:
Post a Comment