In this article I have explained how to allow paging in DataList control in ASP.Net.
DataList control by default does not have paging (pagination) enabled hence we need to write custom stored procedure as well as develop a custom pager control in order to use / apply / set / enable / implement paging in DataList control in ASP.Net
Concept
Now the question arises how to implement paging in ASP.Net DataList 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 of 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 [dbo].[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]
,[City]
,[Country]
,[Phone]
,[Fax]
,[PostalCode]
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
Above I am passing the PageIndex and 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 DataList for populating the Customer records and a Repeater control which will be used for populating the pager control.
DataList for populating records
The DataList control consists of an HTML table for displaying the records.
<asp:DataList ID="dlCustomers" runat="server" RepeatDirection="Horizontal" RepeatColumns="3">
<ItemTemplate>
<table cellpadding="2" cellspacing="0" class="Item">
<tr>
<td class="header">
<b><u>
<%# Eval("ContactName") %></u></b>
</td>
</tr>
<tr>
<td class="body">
<b>City: </b>
<%# Eval("City") %><br />
<b>Postal Code: </b>
<%# Eval("PostalCode") %><br />
<b>Country: </b>
<%# Eval("Country")%><br />
<b>Phone: </b>
<%# Eval("Phone")%><br />
<b>Fax: </b>
<%# Eval("Fax")%>
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
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 DataList 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 DataList control.
Since the PageSize value will be required throughout, I have created private variable for it and set its value to 6.
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 = 6;
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 = 6
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 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)
{
List<ListItem> pages = new List<ListItem>();
int startIndex, endIndex;
int pagerSpan = 5;
//Calculate the Start and End Index of pages to be displayed.
double dblPageCount = (double)((decimal)recordCount / Convert.ToDecimal(PageSize));
int pageCount = (int)Math.Ceiling(dblPageCount);
startIndex = currentPage > 1 && currentPage + pagerSpan - 1 < pagerSpan ? currentPage : 1;
endIndex = pageCount > pagerSpan ? pagerSpan : pageCount;
if (currentPage > pagerSpan % 2)
{
if (currentPage == 2)
{
endIndex = 5;
}
else
{
endIndex = currentPage + 2;
}
}
else
{
endIndex = (pagerSpan - currentPage) + 1;
}
if (endIndex - (pagerSpan - 1) > startIndex)
{
startIndex = endIndex - (pagerSpan - 1);
}
if (endIndex > pageCount)
{
endIndex = pageCount;
startIndex = ((endIndex - pagerSpan) + 1) > 0 ? (endIndex - pagerSpan) + 1 : 1;
}
//Add the First Page Button.
if (currentPage > 1)
{
pages.Add(new ListItem("First", "1"));
}
//Add the Previous Button.
if (currentPage > 1)
{
pages.Add(new ListItem("<<", (currentPage - 1).ToString()));
}
for (int i = startIndex; i <= endIndex; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
//Add the Next Button.
if (currentPage < pageCount)
{
pages.Add(new ListItem(">>", (currentPage + 1).ToString()));
}
//Add the Last Button.
if (currentPage != pageCount)
{
pages.Add(new ListItem("Last", pageCount.ToString()));
}
rptPager.DataSource = pages;
rptPager.DataBind();
}
VB.Net
Private Sub PopulatePager(recordCount As Integer, currentPage As Integer)
Dim pages As New List(Of ListItem)()
Dim startIndex As Integer, endIndex As Integer
Dim pagerSpan As Integer = 5
'Calculate the Start and End Index of pages to be displayed.
Dim dblPageCount As Double = CDbl(CDec(recordCount) / Convert.ToDecimal(PageSize))
Dim pageCount As Integer = CInt(Math.Ceiling(dblPageCount))
startIndex = If(currentPage > 1 AndAlso currentPage + pagerSpan - 1 < pagerSpan, currentPage, 1)
endIndex = If(pageCount > pagerSpan, pagerSpan, pageCount)
If currentPage > pagerSpan Mod 2 Then
If currentPage = 2 Then
endIndex = 5
Else
endIndex = currentPage + 2
End If
Else
endIndex = (pagerSpan - currentPage) + 1
End If
If endIndex - (pagerSpan - 1) > startIndex Then
startIndex = endIndex - (pagerSpan - 1)
End If
If endIndex > pageCount Then
endIndex = pageCount
startIndex = If(((endIndex - pagerSpan) + 1) > 0, (endIndex - pagerSpan) + 1, 1)
End If
'Add the First Page Button.
If currentPage > 1 Then
pages.Add(New ListItem("First", "1"))
End If
'Add the Previous Button.
If currentPage > 1 Then
pages.Add(New ListItem("<<", (currentPage - 1).ToString()))
End If
For i As Integer = startIndex To endIndex
pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
Next
'Add the Next Button.
If currentPage < pageCount Then
pages.Add(New ListItem(">>", (currentPage + 1).ToString()))
End If
'Add the Last Button.
If currentPage <> pageCount Then
pages.Add(New ListItem("Last", pageCount.ToString()))
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
![Implement Paging in DataList control in ASP.Net]()
Demo
Downloads
