In this article I have explained how to use / apply / set / enable / implement paging in Repeater control in ASP.Net.
Repeater control by default does not have paging (pagination) enabled hence we need to write custom stored procedure as well as developer a custom pager control in order to use / apply / set / enable / implement paging in Repeater control in ASP.Net
 
Concept
Now the question arises how to implement paging in ASP.Net Repeater that does not have inbuilt Paging mechanism and also does not inherit the IPageableItemContainer interface like ListView with which we can use the DataPager control.
The answer is Custom SQL Server Stored Procedure Paging that fetches only one page from the available records. With custom pagination we will fetch records per page based on the page index. Thus if our table has 1000 records and we need to display only 50 records per page, then we will fetch only 50 records based on page index. Thus this will boost the performance of the application.
 
Database
For this article I am making use of the Microsoft’s Northwind Database. Download and install instructions are provided in the link below
 

Pagination Stored Procedure
SQL Server 2005 came up with the new ROW_NUMBER() keyword that allows us to give row numbers to the records that we select from the table. Using this row number we can implement our custom pagination logic within the SQL Server Stored Procedure.
I am using the Customers table Microsoft’s Northwind Database for this article and below is the Stored Procedure that allows us to fetch records page wise.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE GetCustomersPageWise
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
      ,[CustomerID]
      ,[CompanyName]
      ,[ContactName]
     INTO #Results
      FROM [Customers]
     
      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
GO
 
Above I am passing the PageIndex, PageSize as input parameters so that we can fetch the records for the desired page index. And for populating the Pager in front end we will need the total number of records in the table which we are fetching using the RecordCount parameter.
 
Namespaces
You will need to import the following namespaces
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
 
 
HTML Markup
The HTML markup is quite simple it has a Repeater for populating the Customer records and another Repeater which will be used for populating the pager control.
Repeater for populating records
The Repeater control is wrapped inside an HTML Table so that the records can be populated as a Grid.
<table class="Repeater" cellspacing="0" rules="all" border="1">
    <tr>
        <th scope="col" style="width: 80px">
            Customer Id
        </th>
        <th scope="col" style="width: 150px">
            Customer Name
        </th>
        <th scope="col" style="width: 150px">
            Company Name
        </th>
    </tr>
    <asp:Repeater ID="rptCustomers" runat="server">
        <ItemTemplate>
            <tr>
                <td>
                    <asp:Label ID="lblCustomerId" runat="server" Text='<%# Eval("CustomerId") %>' />
                </td>
                <td>
                    <asp:Label ID="lblContactName" runat="server" Text='<%# Eval("ContactName") %>' />
                </td>
                <td>
                    <asp:Label ID="lblCompanyName" runat="server" Text='<%# Eval("CompanyName") %>' />
                </td>
            </tr>
        </ItemTemplate>
    </asp:Repeater>
</table>
 
Repeater for pager
Below is the Repeater control for populating the Pager, since the syntax of the inline code is different in C# and VB.Net, I given their respective HTML Markups. The HTML Markup has some C# and VB.Net code to swap the CSS style classes for the Current Page and the remaining pages so that the Current Page is viewed distinguished from others.
C#
<asp:Repeater ID="rptPager" runat="server">
    <ItemTemplate>
        <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
            CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
            OnClick="Page_Changed" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
   </ItemTemplate>
</asp:Repeater>
 
VB.Net
<asp:Repeater ID="rptPager" runat="server">
    <ItemTemplate>
        <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
            CssClass='<%# If(Convert.ToBoolean(Eval("Enabled")), "page_enabled", "page_disabled")%>'
            OnClick="Page_Changed" OnClientClick='<%# If(Not Convert.ToBoolean(Eval("Enabled")), "return false;", "") %>'></asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>
 
 
Binding the Repeater with Customized Paged Data
In the Page Load event of the page, a call is made to the method GetCustomersPageWise and it executes the stored procedure discussed earlier and binds the returned Page Wise Records to the Repeater control.
Since the PageSize value will be required throughout, I have created private variable for it and set its value to 10.
The Record Count of the records is fetched from the Output Parameter and is passed to the PopulatePager method along with the PageIndex.
C#
private int PageSize = 10;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.GetCustomersPageWise(1);
    }
}
 
private void GetCustomersPageWise(int pageIndex)
{
    string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("GetCustomersPageWise", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", PageSize);
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            con.Open();
            IDataReader idr = cmd.ExecuteReader();
            rptCustomers.DataSource = idr;
            rptCustomers.DataBind();
            idr.Close();
            con.Close();
            int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
            this.PopulatePager(recordCount, pageIndex);
        }
    }
}
 
VB.Net
Private PageSize As Integer = 10
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Me.GetCustomersPageWise(1)
    End If
End Sub
 
Private Sub GetCustomersPageWise(pageIndex As Integer)
    Dim constring As String = ConfigurationManager.ConnectionStrings("constring").ConnectionString
    Using con As New SqlConnection(constring)
        Using cmd As New SqlCommand("GetCustomersPageWise", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
            cmd.Parameters.AddWithValue("@PageSize", PageSize)
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4)
            cmd.Parameters("@RecordCount").Direction = ParameterDirection.Output
            con.Open()
            Dim idr As IDataReader = cmd.ExecuteReader()
            rptCustomers.DataSource = idr
            rptCustomers.DataBind()
            idr.Close()
            con.Close()
            Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("@RecordCount").Value)
            Me.PopulatePager(recordCount, pageIndex)
        End Using
    End Using
End Sub
 
 
Populating the Pager control
In the last line of the above method we are calling the method described below to populate the pager Repeater control based on the record count that we fetch from the database. Here we pass the total of the records present in the database table and the Current Page that was fetched and displayed in the Repeater. Then some calculations are done to find the first and the last page of the pager and the using a loop a List of Pages are populated and finally are bound to the Repeater control for populating the Pager.
C#
private void PopulatePager(int recordCount, int currentPage)
{
    double dblPageCount = (double)((decimal)recordCount / Convert.ToDecimal(PageSize));
    int pageCount = (int)Math.Ceiling(dblPageCount);
    List<ListItem> pages = new List<ListItem>();
    if (pageCount > 0)
    {
        for (int i = 1; i <= pageCount; i++)
        {
            pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
        }
    }
    rptPager.DataSource = pages;
    rptPager.DataBind();
}
 
VB.Net
Private Sub PopulatePager(recordCount As Integer, currentPage As Integer)
    Dim dblPageCount As Double = CDbl(CDec(recordCount) / Convert.ToDecimal(PageSize))
    Dim pageCount As Integer = CInt(Math.Ceiling(dblPageCount))
    Dim pages As New List(Of ListItem)()
    If pageCount > 0 Then
        For i As Integer = 1 To pageCount
            pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
        Next
    End If
    rptPager.DataSource = pages
    rptPager.DataBind()
End Sub
 
Finally the below event is executed when the page number LinkButton is clicked. This event makes a database call to get new set of records based on the PageIndex fetched from the CommandArgument property of the clicked LinkButton.
C#
protected void Page_Changed(object sender, EventArgs e)
{
    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    this.GetCustomersPageWise(pageIndex);
}
 
VB.Net
Protected Sub Page_Changed(sender As Object, e As EventArgs)
    Dim pageIndex As Integer = Integer.Parse(TryCast(sender, LinkButton).CommandArgument)
    Me.GetCustomersPageWise(pageIndex)
End Sub
 
How to use (apply) Paging in Repeater control in ASP.Net
 
Demo
 
Downloads