Abstract: Here Mudassar Ahmed Khan has explained how to implement Custom Paging in ASP.Net GridView using SQL Server Stored Procedure
In this article I’ll explain how to implement Custom Paging in ASP.Net GridView control.
Need for Custom Pagination
Now the question arises why there’s need to take so much pain when ASP.Net GridView control has in built paging mechanism.
The answer is that ASP.Net GridView fetches all the records and then displays one page from the fetched records. Thus for example if your table has 1000 records and you need to display only 50 records per page, GridView will fetch all 1000 records discard the 950 records and display the 50 records based on the page index selected by the users.
Thus the above approach is quite inefficient in terms of both bandwidth and performance. 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.
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.
HTML Markup
The HTML markup is quite simple it has a GridView, a DropDownLists selecting the Page Size and a Repeater which will be used for populating the pager.
<div>
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>
</div>
Implementing the Custom Pagination
Now let’s start implementing the custom pagination in the code behind. First you will need to import the following namespaces
C#
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
VB.Net
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data
Binding the GridView with Data
Below is the method that will execute the stored procedure and bind the data to the ASP.Net GridView Control
C#
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
Private Sub GetCustomersPageWise(ByVal pageIndex As Integer)
Dim constring As String = ConfigurationManager.ConnectionStrings("constring").ConnectionString
Dim con As SqlConnection = New SqlConnection(constring)
Dim 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 Sub
Populating the Pager
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
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
Below is the event that is raised when the Page Size DropDownList is changed. This method simply calls theGetCustomersPageWise() 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
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 and PageSize
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
The below screenshot describes the working of the ASP.Net GridView with Custom Pagination using SQL Server Stored Procedure
Downloads
You can download the complete source code in VB.Net and C# using the link below.
No comments:
Post a Comment