In this article I’ll describe how to implement Custom Paging in ASP.Net GridView control.
Require for Custom Pagination
ASP.Net GridView fetches all the records and then displays one page from the fetched records. Thus for illustration if your table has 1000 records and you want to display only 50 records per page, GridView will fetch all 1000 records throw away the 950 records and display the 50 records based on the page index selected by the users.
In 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 apply 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.
Above I am passing the PageIndex, PageSize as input parameters so that we can get the records for the preferred 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.
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 technique that will implement the stored procedure and bind the data to the ASP.Net GridView 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
Below is the event that is raised when the Page Size DropDownList is changed. This method simply calls the GetCustomersPageWise() method.
At last 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
5 comments:
Thanks to this ...
But what will be happen if i have 100000 or more pages?????
Hi Shreeniwas,
It doesn’t make much difference even if you have 100,000 records.
You just need to have most optimised select statement and much optimised table structure to return fast result.
Thanks,
Bhavik
-- Author:
-- Create date: <11 March 2013>
-- Description:
-- =============================================
Create PROCEDURE sp_GetAll_Evaluation_Questions
AS
BEGIN
select eq.*,c.CategoryName, sc.SubCategoryName, qt.QuestionTypeDesc from Evaluation_Questions eq
inner join QuestionTypes qt
on eq.QuestionTypeID=qt.QuestionTypeID
inner join Categories c
on eq.CategoryID =c.CategoryID
left outer join SubCategories sc
on eq.SubCategoryID=sc.SubCategoryID
where eq.IsDeleted = 0
END
GO
this is my store procedure which is giving me result in a huge count
i want to implement this paging for it.
plz help me how can i do this????
Nice Article
This one with code -
Custom paging in asp.net using stored procedure in gridview
You were able to very clearly illustrate each stage of work in order to get the necessary result and continue to move on.
Post a Comment