October 16, 2019 11:06 by
PeterBy default, pagination is not enabled in a Repeater control. We have to write custom pager control to use paging in a Repeater control. Here, I will explain how to implement paging in Repeater control in ASP.NET with and without a stored procedure. I am using Visual Studio 2019 to create the application.
Step 1
First, we have to create a table “tblCustomers” to test the paging in the repeater control.
CREATE TABLE [dbo].[tblCustomers](
[Id] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Company] [nvarchar](50) NULL,
[Phone] [nvarchar](50) NULL,
[Address] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL
)
After creating the table, add some record to the table.
Step 2
Open Visual Studio and click on "Create a new project".
Select ASP.NET Web Application from templates and click on “Next”.
Then, give the project name as “AspRepeater” and then click “Create”.
Now, choose “Web Forms” from the template and click on “Create”.
ASP.NET Repeater Control without Stored Procedure
Step 3
Now, create a new weborm “RepeaterControl” and write the code following code in your “RepeaterControl.aspx” page.
Code for RepeaterControl.aspx page.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="RepeaterControl.aspx.cs" Inherits=" AspRepeater.RepeaterControl" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Repeater Control without Stored Procedure</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Repeater ID="Repeater2" runat="server">
<HeaderTemplate>
<table id="tbDetails" style="width: 100%; border-collapse: collapse;" border="1" cellpadding="5" cellspacing="0">
<tr style="background-color: lightgray; height: 30px; color: black; text-align: center">
<th>Id</th>
<th>Customer Name</th>
<th>Company Name</th>
<th>Phone</th>
<th>Address</th>
<th>E-Mail</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr style="height: 25px;">
<td>
<%#Eval("Id").ToString()%>
</td>
<td>
<%#Eval("Name").ToString()%>
</td>
<td>
<%#Eval("Company").ToString()%>
</td>
<td>
<%#Eval("Phone").ToString()%>
</td>
<td>
<%#Eval("Address").ToString()%>, <%#Eval("Country").ToString()%>
</td>
<td>
<%#Eval("Email").ToString()%>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</div>
<br />
<div style="text-align:center">
<asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand">
<ItemTemplate>
<asp:LinkButton ID="lnkPage"
Style="padding: 8px; margin: 2px; background: lightgray; border: solid 1px #666; color: black; font-weight: bold"
CommandName="Page" CommandArgument="<%# Container.DataItem %>" runat="server" Font-Bold="True"><%# Container.DataItem %>
</asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</div>
</form>
</body>
</html>
Code for RepeaterControl.aspx.cs,
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
namespace AspRepeater
{
public partial class RepeaterControl : System.Web.UI.Page
{
private int iPageSize = 15;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetCustomers();
}
}
private void GetCustomers()
{
DataTable dtData = new DataTable();
string conString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
SqlConnection sqlCon = new SqlConnection(conString);
sqlCon.Open();
SqlCommand sqlCmd = new SqlCommand("Select * From tblCustomers", sqlCon);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dtData);
sqlCon.Close();
PagedDataSource pdsData = new PagedDataSource();
DataView dv = new DataView(dtData);
pdsData.DataSource = dv;
pdsData.AllowPaging = true;
pdsData.PageSize = iPageSize;
if (ViewState["PageNumber"] != null)
pdsData.CurrentPageIndex = Convert.ToInt32(ViewState["PageNumber"]);
else
pdsData.CurrentPageIndex = 0;
if (pdsData.PageCount > 1)
{
Repeater1.Visible = true;
ArrayList alPages = new ArrayList();
for (int i = 1; i <= pdsData.PageCount; i++)
alPages.Add((i).ToString());
Repeater1.DataSource = alPages;
Repeater1.DataBind();
}
else
{
Repeater1.Visible = false;
}
Repeater2.DataSource = pdsData;
Repeater2.DataBind();
}
protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
{
ViewState["PageNumber"] = Convert.ToInt32(e.CommandArgument);
GetCustomers();
}
}
}
ASP.NET Repeater Control with Stored Procedure
Step 4
By using a Stored Procedure, we can fetch only one-page records from the available records based on the page index. For example, if our table has 300 records and we need to display only 15 records per page, then we will fetch only 15 records based on the page index.
Script for the Stored Procedure,
CREATE PROCEDURE GetCustomer
@PageIndex INT = 1,
@PageSize INT = 15,
@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER(ORDER BY Id ASC)AS RowNumber,ID,
Name,Company,Phone,Address,Country,Email
INTO #Results FROM tblCustomers
SELECT @RecordCount = COUNT(*) FROM #Results
SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1
AND (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
Step 5
Now, create a new webform “RepeaterControl1” and write the following code in your “RepeaterControl1.aspx” page.
Code for RepeaterControl.aspx page.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="RepeaterControl1.aspx.cs" Inherits="AspRepeater.RepeaterControl1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Repeater Control with Stored Procedure</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate>
<table id="tbDetails" style="width: 100%; border-collapse: collapse;" border="1" cellpadding="5" cellspacing="0">
<tr style="background-color: lightgray; height: 30px; color: black; text-align: center">
<th>Id</th>
<th>Customer Name</th>
<th>Company Name</th>
<th>Phone</th>
<th>Address</th>
<th>E-Mail</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr style="height: 25px;">
<td>
<%#Eval("Id").ToString()%>
</td>
<td>
<%#Eval("Name").ToString()%>
</td>
<td>
<%#Eval("Company").ToString()%>
</td>
<td>
<%#Eval("Phone").ToString()%>
</td>
<td>
<%#Eval("Address").ToString()%>, <%#Eval("Country").ToString()%>
</td>
<td>
<%#Eval("Email").ToString()%>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</div>
<br />
<div style="text-align:center">
<asp:Repeater ID="Repeater2" runat="server" OnItemCommand="Repeater2_ItemCommand">
<ItemTemplate>
<asp:LinkButton ID="lnkPage"
Style="padding: 8px; margin: 2px; background: lightgray; border: solid 1px #666; color: black; font-weight: bold"
CommandName="Page" CommandArgument="<%# Container.DataItem %>" runat="server" Font-Bold="True"><%# Container.DataItem %>
</asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</div>
</form>
</body>
</html>
Code for RepeaterControl1.aspx.cs.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
namespace AspRepeater
{
public partial class RepeaterControl1 : System.Web.UI.Page
{
private int iPageSize = 15;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.GetCustomers(1);
}
}
private void GetCustomers(int iPageIndex)
{
string conString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
SqlConnection sqlCon = new SqlConnection(conString);
sqlCon.Open();
SqlCommand sqlCmd = new SqlCommand("GetCustomer", sqlCon);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@PageIndex", iPageIndex);
sqlCmd.Parameters.AddWithValue("@PageSize", iPageSize);
sqlCmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
sqlCmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
IDataReader iDr = sqlCmd.ExecuteReader();
Repeater1.DataSource = iDr;
Repeater1.DataBind();
iDr.Close();
sqlCon.Close();
int iRecordCount = Convert.ToInt32(sqlCmd.Parameters["@RecordCount"].Value);
double dPageCount = (double)((decimal)iRecordCount / Convert.ToDecimal(iPageSize));
int iPageCount = (int)Math.Ceiling(dPageCount);
List<ListItem> lPages = new List<ListItem>();
if (iPageCount > 0)
{
for (int i = 1; i <= iPageCount; i++)
lPages.Add(new ListItem(i.ToString(), i.ToString(), i != iPageIndex));
}
Repeater2.DataSource = lPages;
Repeater2.DataBind();
}
protected void Repeater2_ItemCommand(object source, RepeaterCommandEventArgs e)
{
int iPageIndex = Convert.ToInt32(e.CommandArgument);
GetCustomers(iPageIndex);
}
}
}
