Post
Ask Question

Paging Using Repeater Control In ASP.NET With And Without Stored Procedure

Introduction

 
By 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.
  1. CREATE TABLE [dbo].[tblCustomers](  
  2.     [Id] [int] NOT NULL,  
  3.     [Name] [nvarchar](50) NULL,  
  4.     [Company] [nvarchar](50) NULL,  
  5.     [Phone] [nvarchar](50) NULL,  
  6.     [Address] [nvarchar](50) NULL,  
  7.     [Country] [nvarchar](50) NULL,  
  8.     [Email] [nvarchar](50) NULL  
  9. )  
After creating the table, add some record to the table.
 
Step 2
  1. Open Visual Studio and click on "Create a new project".
  2. Select ASP.NET Web Application from templates and click on “Next”.
  3. Then, give the project name as “AspRepeater” and then click “Create”.
  4. 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.
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="RepeaterControl.aspx.cs" Inherits=" AspRepeater.RepeaterControl" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title>Repeater Control without Stored Procedure</title>  
  8. </head>  
  9. <body>  
  10.     <form id="form1" runat="server">  
  11.         <div>  
  12.             <asp:Repeater ID="Repeater2" runat="server">  
  13.                 <HeaderTemplate>  
  14.                     <table id="tbDetails" style="width: 100%; border-collapse: collapse;" border="1" cellpadding="5" cellspacing="0">  
  15.                         <tr style="background-color: lightgray; height: 30px; color: black; text-align: center">  
  16.                             <th>Id</th>  
  17.                             <th>Customer Name</th>  
  18.                             <th>Company Name</th>  
  19.                             <th>Phone</th>  
  20.                             <th>Address</th>  
  21.                             <th>E-Mail</th>  
  22.                         </tr>  
  23.                 </HeaderTemplate>  
  24.                 <ItemTemplate>  
  25.                     <tr style="height: 25px;">  
  26.                         <td>  
  27.                             <%#Eval("Id").ToString()%>  
  28.                         </td>  
  29.                         <td>  
  30.                             <%#Eval("Name").ToString()%>  
  31.                         </td>  
  32.                         <td>  
  33.                             <%#Eval("Company").ToString()%>  
  34.                         </td>  
  35.                         <td>  
  36.                             <%#Eval("Phone").ToString()%>  
  37.                         </td>  
  38.                         <td>  
  39.                             <%#Eval("Address").ToString()%>, <%#Eval("Country").ToString()%>  
  40.                         </td>  
  41.                         <td>  
  42.                             <%#Eval("Email").ToString()%>  
  43.                         </td>                          
  44.                     </tr>  
  45.                 </ItemTemplate>  
  46.                 <FooterTemplate>  
  47.                     </table>  
  48.                 </FooterTemplate>  
  49.             </asp:Repeater>  
  50.         </div>  
  51.         <br />  
  52.         <div style="text-align:center">  
  53.             <asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand">  
  54.                 <ItemTemplate>  
  55.                     <asp:LinkButton ID="lnkPage"  
  56.                         Style="padding: 8px; margin: 2px; background: lightgray; border: solid 1px #666; color: black; font-weight: bold"  
  57.                         CommandName="Page" CommandArgument="<%# Container.DataItem %>" runat="server" Font-Bold="True"><%# Container.DataItem %>  
  58.                     </asp:LinkButton>  
  59.                 </ItemTemplate>  
  60.             </asp:Repeater>  
  61.         </div>  
  62.     </form>  
  63. </body>  
  64. </html>  
Code for RepeaterControl.aspx.cs,
  1. using System;  
  2. using System.Collections;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Web.UI.WebControls;  
  7.   
  8. namespace AspRepeater  
  9. {  
  10.     public partial class RepeaterControl : System.Web.UI.Page  
  11.     {   
  12.         private int iPageSize = 15;  
  13.   
  14.         protected void Page_Load(object sender, EventArgs e)  
  15.         {  
  16.             if (!IsPostBack)  
  17.             {  
  18.                 GetCustomers();  
  19.             }  
  20.         }  
  21.   
  22.         private void GetCustomers()  
  23.         {  
  24.             DataTable dtData = new DataTable();  
  25.             string conString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;  
  26.             SqlConnection sqlCon = new SqlConnection(conString);  
  27.             sqlCon.Open();  
  28.             SqlCommand sqlCmd = new SqlCommand("Select * From tblCustomers", sqlCon);  
  29.             SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);  
  30.             sqlDa.Fill(dtData);  
  31.             sqlCon.Close();  
  32.   
  33.             PagedDataSource pdsData = new PagedDataSource();  
  34.             DataView dv = new DataView(dtData);  
  35.             pdsData.DataSource = dv;  
  36.             pdsData.AllowPaging = true;  
  37.             pdsData.PageSize = iPageSize;  
  38.             if (ViewState["PageNumber"] != null)  
  39.                 pdsData.CurrentPageIndex = Convert.ToInt32(ViewState["PageNumber"]);  
  40.             else  
  41.                 pdsData.CurrentPageIndex = 0;  
  42.             if (pdsData.PageCount > 1)  
  43.             {  
  44.                 Repeater1.Visible = true;  
  45.                 ArrayList alPages = new ArrayList();  
  46.                 for (int i = 1; i <= pdsData.PageCount; i++)  
  47.                     alPages.Add((i).ToString());  
  48.                 Repeater1.DataSource = alPages;  
  49.                 Repeater1.DataBind();  
  50.             }  
  51.             else  
  52.             {  
  53.                 Repeater1.Visible = false;  
  54.             }  
  55.             Repeater2.DataSource = pdsData;  
  56.             Repeater2.DataBind();  
  57.         }  
  58.           
  59.         protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)  
  60.         {  
  61.             ViewState["PageNumber"] = Convert.ToInt32(e.CommandArgument);  
  62.             GetCustomers();  
  63.         }  
  64.     }  
  65. }  

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,
  1. CREATE PROCEDURE GetCustomer  
  2.     @PageIndex INT = 1,  
  3.     @PageSize INT = 15,  
  4.     @RecordCount INT OUTPUT  
  5. AS  
  6. BEGIN  
  7.     SET NOCOUNT ON;  
  8.     SELECT ROW_NUMBER() OVER(ORDER BY Id ASC)AS RowNumber,ID,  
  9.     Name,Company,Phone,Address,Country,Email  
  10.     INTO #Results FROM tblCustomers  
  11.        
  12.     SELECT @RecordCount = COUNT(*) FROM #Results  
  13.              
  14.     SELECT * FROM #Results WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1   
  15.     AND (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1  
  16.        
  17.     DROP TABLE #Results  
  18. END  
Step 5
 
Now, create a new webform “RepeaterControl1” and write the following code in your “RepeaterControl1.aspx” page.
 
Code for RepeaterControl.aspx page.
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="RepeaterControl1.aspx.cs" Inherits="AspRepeater.RepeaterControl1" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title>Repeater Control with Stored Procedure</title>  
  8. </head>  
  9. <body>  
  10.     <form id="form1" runat="server">  
  11.         <div>  
  12.             <asp:Repeater ID="Repeater1" runat="server">  
  13.                 <HeaderTemplate>  
  14.                     <table id="tbDetails" style="width: 100%; border-collapse: collapse;" border="1" cellpadding="5" cellspacing="0">  
  15.                         <tr style="background-color: lightgray; height: 30px; color: black; text-align: center">  
  16.                             <th>Id</th>  
  17.                             <th>Customer Name</th>  
  18.                             <th>Company Name</th>  
  19.                             <th>Phone</th>  
  20.                             <th>Address</th>  
  21.                             <th>E-Mail</th>  
  22.                         </tr>  
  23.                 </HeaderTemplate>  
  24.                 <ItemTemplate>  
  25.                     <tr style="height: 25px;">  
  26.                         <td>  
  27.                             <%#Eval("Id").ToString()%>  
  28.                         </td>  
  29.                         <td>  
  30.                             <%#Eval("Name").ToString()%>  
  31.                         </td>  
  32.                         <td>  
  33.                             <%#Eval("Company").ToString()%>  
  34.                         </td>  
  35.                         <td>  
  36.                             <%#Eval("Phone").ToString()%>  
  37.                         </td>  
  38.                         <td>  
  39.                             <%#Eval("Address").ToString()%>, <%#Eval("Country").ToString()%>  
  40.                         </td>  
  41.                         <td>  
  42.                             <%#Eval("Email").ToString()%>  
  43.                         </td>  
  44.                     </tr>  
  45.                 </ItemTemplate>  
  46.                 <FooterTemplate>  
  47.                     </table>  
  48.                 </FooterTemplate>  
  49.             </asp:Repeater>  
  50.         </div>  
  51.         <br />  
  52.         <div style="text-align:center">  
  53.             <asp:Repeater ID="Repeater2" runat="server" OnItemCommand="Repeater2_ItemCommand">  
  54.                 <ItemTemplate>  
  55.                     <asp:LinkButton ID="lnkPage"  
  56.                         Style="padding: 8px; margin: 2px; background: lightgray; border: solid 1px #666; color: black; font-weight: bold"  
  57.                         CommandName="Page" CommandArgument="<%# Container.DataItem %>" runat="server" Font-Bold="True"><%# Container.DataItem %>  
  58.                     </asp:LinkButton>  
  59.                 </ItemTemplate>  
  60.             </asp:Repeater>  
  61.         </div>  
  62.     </form>  
  63. </body>  
  64. </html>  
Code for RepeaterControl1.aspx.cs.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Web.UI.WebControls;  
  7.   
  8. namespace AspRepeater  
  9. {  
  10.     public partial class RepeaterControl1 : System.Web.UI.Page  
  11.     {  
  12.         private int iPageSize = 15;  
  13.         protected void Page_Load(object sender, EventArgs e)  
  14.         {  
  15.             if (!IsPostBack)  
  16.             {  
  17.                 this.GetCustomers(1);  
  18.             }  
  19.         }  
  20.   
  21.         private void GetCustomers(int iPageIndex)  
  22.         {  
  23.             string conString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;  
  24.             SqlConnection sqlCon = new SqlConnection(conString);  
  25.             sqlCon.Open();  
  26.             SqlCommand sqlCmd = new SqlCommand("GetCustomer", sqlCon);  
  27.             sqlCmd.CommandType = CommandType.StoredProcedure;  
  28.             sqlCmd.Parameters.AddWithValue("@PageIndex", iPageIndex);  
  29.             sqlCmd.Parameters.AddWithValue("@PageSize", iPageSize);  
  30.             sqlCmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);  
  31.             sqlCmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;  
  32.             IDataReader iDr = sqlCmd.ExecuteReader();  
  33.             Repeater1.DataSource = iDr;  
  34.             Repeater1.DataBind();  
  35.             iDr.Close();  
  36.             sqlCon.Close();  
  37.             int iRecordCount = Convert.ToInt32(sqlCmd.Parameters["@RecordCount"].Value);  
  38.   
  39.             double dPageCount = (double)((decimal)iRecordCount / Convert.ToDecimal(iPageSize));  
  40.             int iPageCount = (int)Math.Ceiling(dPageCount);  
  41.             List<ListItem> lPages = new List<ListItem>();  
  42.             if (iPageCount > 0)  
  43.             {  
  44.                 for (int i = 1; i <= iPageCount; i++)  
  45.                     lPages.Add(new ListItem(i.ToString(), i.ToString(), i != iPageIndex));  
  46.             }  
  47.             Repeater2.DataSource = lPages;  
  48.             Repeater2.DataBind();  
  49.         }  
  50.   
  51.         protected void Repeater2_ItemCommand(object source, RepeaterCommandEventArgs e)  
  52.         {  
  53.             int iPageIndex = Convert.ToInt32(e.CommandArgument);  
  54.             GetCustomers(iPageIndex);  
  55.         }  
  56.     }  
  57. }  


Similar Articles