In this article I will explain with an example, how to implement Custom Paging in ASP.Net GridView control using C# and VB.Net.
Custom Paging in GridView will be implemented with the help of Stored Procedure in ASP.Net using C# and VB.Net.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Stored Procedure for Paging
The following Stored Procedure makes use of ROW_NUMBER function to fetch records Page Wise from SQL Server Database Table.
The PageIndex and PageSize values are passed as parameter and the Stored Procedure returns the Total records of the Table using the RecordCount Output parameter.
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
HTML Markup
The following HTML Markup consists of a GridView, a DropDownList for selecting the Page Size and a Repeater which will be used for populating the pager.
The DropDownList is populated with some static values which will be later used for setting the Page Size of the GridView control.
PageSize:
<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
<asp:ListItem Text="10" Value="10" />
<asp:ListItem Text="25" Value="25" />
<asp:ListItem Text="50" Value="50" />
</asp:DropDownList>
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="CustomerId" DataField="CustomerId" />
<asp:BoundField HeaderText="ContactName" DataField="ContactName" />
<asp:BoundField HeaderText="CompanyName" DataField="CompanyName" />
</Columns>
</asp:GridView>
<br />
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
<asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
Enabled='<%# Eval("Enabled") %>' OnClick="Page_Changed"></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
Namespaces
You will need to import the following namespaces.
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
VB.Net
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Binding the GridView
Inside the Page Load event, the GetCustomersPageWise function is called with PageIndex parameter value as 1.
Inside the function, the Stored Procedure is executed and it fetches the records based on PageIndex and PageSize (fetched from the DropDownList) from Customers Table of the Northwind database using DataReader.
Finally, the DataReader is used to populate the GridView.
After execution of DataReader, the Total Record Count value is fetched using the RecordCount Output parameter.
The Total Record Count value is passed to the PopulatePager method along with the PageIndex.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.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", int.Parse(ddlPageSize.SelectedValue));
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
con.Open();
IDataReader idr = cmd.ExecuteReader();
GridView1.DataSource = idr;
GridView1.DataBind();
idr.Close();
con.Close();
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.GetCustomersPageWise(1)
End If
End Sub
Private Sub GetCustomersPageWise(ByVal pageIndex As Integer)
Dim constring As String = ConfigurationManager.ConnectionStrings("constring").ConnectionString
Using con As SqlConnection = New SqlConnection(constring)
Using cmd As SqlCommand = New SqlCommand("GetCustomersPageWise", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", Integer.Parse(ddlPageSize.SelectedValue))
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4)
cmd.Parameters("@RecordCount").Direction = ParameterDirection.Output
con.Open()
Dim idr As IDataReader = cmd.ExecuteReader()
GridView1.DataSource = idr
GridView1.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
The PopulatePager method accepts the count of the total records present in the Table and the current PageIndex.
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 / decimal.Parse(ddlPageSize.SelectedValue));
int pageCount = (int)Math.Ceiling(dblPageCount);
List<ListItem> pages = new List<ListItem>();
if (pageCount > 0)
{
pages.Add(new ListItem("First", "1", currentPage > 1));
for (int i = 1; i <= pageCount; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
}
rptPager.DataSource = pages;
rptPager.DataBind();
}
VB.Net
Private Sub PopulatePager(ByVal recordCount As Integer, ByVal currentPage As Integer)
Dim dblPageCount As Double = CType((CType(recordCount, Decimal) / Decimal.Parse(ddlPageSize.SelectedValue)), Double)
Dim pageCount As Integer = CType(Math.Ceiling(dblPageCount), Integer)
Dim pages As New List(Of ListItem)
If (pageCount > 0) Then
pages.Add(New ListItem("First", "1", (currentPage > 1)))
Dim i As Integer = 1
Do While (i <= pageCount)
pages.Add(New ListItem(i.ToString, i.ToString, (i <> currentPage)))
i = (i + 1)
Loop
pages.Add(New ListItem("Last", pageCount.ToString, (currentPage < pageCount)))
End If
rptPager.DataSource = pages
rptPager.DataBind()
End Sub
Binding the GridView on DropDownList Change
Following event handler is executed when the Page Size DropDownList is changed.
This method simply calls the GetCustomersPageWise method.
C#
protected void PageSize_Changed(object sender, EventArgs e)
{
this.GetCustomersPageWise(1);
}
VB.Net
Protected Sub PageSize_Changed(ByVal sender As Object, ByVal e As EventArgs)
Me.GetCustomersPageWise(1)
End Sub
Binding the GridView on Pager LinkButton Click
Following event handler is executed when the Page Number LinkButton is clicked.
Inside this event handler, the PageIndex is referenced using LinkButton CommandArgument property.
Finally, GetCustomersPageWise method is called by passing the PageIndex value to it.
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(ByVal sender As Object, ByVal e As EventArgs)
Dim pageIndex As Integer = Integer.Parse(CType(sender, LinkButton).CommandArgument)
Me.GetCustomersPageWise(pageIndex)
End Sub
Screenshot
![Custom Paging in ASP.Net GridView using SQL Server Stored Procedure]()
Demo
Downloads
