Custom Paging is one of the most efficient ways of improving performance in any application. When we use the paging that comes with in controls like Gridview, DataList, DetailsView, ListView etc... is not very efficient as the complete data gets loaded in the memory and paging takes place there. Custom Paging will help bring only request number of rows from our relational database.
In our example below we will be using Adventure Works Product table.
We would first need to get the total row count from table we are querying, as this would help us determine the number of pages in which result set will be divided. Say we have 995 total records in our table and we need to show 100 records/page, then whole result set will be displayed in 10 page, last page having 95 records.
So first we create our Count Procedure as below
create procedure dbo.Custom_Count
select COUNT(*) from SalesLT.Product
--Now we need to write the procedure that accepts 2 parameters, @startPageIndex for determining the starting point and @endPageIndex for accepting the number of records user wants view in one page.
alter PROCEDURE [dbo].[Custom_Paging]
SET NOCOUNT ON;
Declare @sql as nvarchar(max)
select @sql='; with e as(
select ProductID,Name,ProductNumber,ROW_NUMBER() OVER(ORDER BY ProductID ASC) AS rm
select ProductID,Name,ProductNumber,rm as Rownumber from e where cast(rm as int)>'+cast(@startPageIndex as varchar)
+' and cast(rm as int)<='+cast(@startPageIndex+@endPageIndex as varchar)
Exec sp_executesql @sql
If you want to implement this in your application you can view complete front end implementation here.
--Execute your above Procedure as below
EXEC @return_value = [dbo].[Custom_Paging]
@startPageIndex = 10,
@endPageIndex = 10
SELECT 'Return Value' = @return_value