In this article I will explain with an example, how to implement Custom Paging in ASP.Net GridView control using
SQL Server Stored Procedure with C# and VB.Net.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Stored Procedure
The
PageIndex and
PageSize values are passed as parameter and the
Stored Procedure returns the Total records of the Table using the
RecordCounts Output parameter.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- [GetImagesPageWise] 1, 6, NULL
CREATE PROCEDURE [dbo].[GetImagesPageWise]
@PageIndex INT = 1
,@PageSize INT = 3
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [Id] ASC
) AS RowNumber
,*
INTO #Results
FROM [Images]
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
HTML Markup
The HTML Markup consists of following controls:
DropDownList – For capturing user input. It is used to decide the PageSize of the GridView.
GridView – For displaying data.
Repeater – For displaying paging Buttons.
Repeater consists of an ItemTemplate which consists of LinkButton for paging.
Repeater has been assigned with the CommandArgument and Enabled property which is set based on the PageCount and current PageIndex.
The Repeater has been also assigned with an OnClick event handler.
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="gvCustomers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="Customer Id" />
<asp:BoundField DataField="ContactName" HeaderText="Name" />
<asp:BoundField DataField="Country" HeaderText="Country" />
</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="OnPageChanged"></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Binding the GridView using C# and VB.Net
Inside the Page Load event handler, the GetCustomersPageWise method is called with PageIndex parameter value as 1.
Inside the method, first a connection is made to the database and the Stored Procedure is executed which fetches the records based on PageIndex and PageSize from Customers Table of the Northwind database using ExecuteReader method of SqlCommand class.
Note: The PageSize value is fetched from the DropDownList (explained earlier).
Then, the count of total records is fetched from the RecordCount Output parameter which is passed as parameter to the PopulatePager method (explained later) 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 spName = "Customers_GetCustomersPageWise";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(spName, 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();
gvCustomers.DataSource = idr;
gvCustomers.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 spName As String = "Customers_GetCustomersPageWise"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(spName, 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()
gvCustomers.DataSource = idr
gvCustomers.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
PopulatePager
Inside this method, first PageCount is determined and a FOR loop is executed for the PageCount.
Inside the FOR loop, a Generic List collection of ListItem class is populated by setting Text, Value and Enabled property.
Finally, the object of Generic List collection of ListItem class is assigned to the Repeater and the repeater is populated along with the custom 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 GridView on DropDownList (PageSize) Change
When PageSize or the DropDownList value is changed, the GetCustomersPageWise method (explained earlier) is called with PageIndex value 1 as parameter.
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 GridView on Pager LinkButton Click
When the Page Number LinkButton is clicked, the PageIndex is referenced using LinkButton CommandArgument property.
Finally, GetCustomersPageWise method (explained earlier) is with PageIndex value accepted as a parameter.
C#
protected void OnPageChanged(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
