ASP.Net Example

On web 1000 of records are in Database.We need only small amount 

of data i.e 10,15 etc. default paging all records are load and

it take more time. Performance of our Application decreasing. 

we create custom Optimize Paging.

In this paging we retrieve only our page size data only from database 

for that we create store procedure.

Customize or Manual Paging Store procedure     

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,ASPdotNet-Example.blogspot.com>
-- Create date: <Create Date,,May 20 2011>
-- Description: <Description,,>
-- =============================================

CREATE PROCEDURE [dbo].[SP_Paging]

@PageIndex int,
@PageSize int,

@TotalRecord int output,
AS
BEGIN
 
 SET NOCOUNT ON;
select @TotalRecord=count(id) from tblemp
 

select * from 

(

select row_number() over (order by [id] asc )as RowNumber,

Name,depid,Salary from tblemp

)as a 

where a.rownumber between (@PageIndex-1)*@PageSize+1 AND 

(((@PageIndex-1)*@PageSize+1)+@PageSize)-1
END

if you have query comment here.