Friday, May 20, 2011

Using Store Procedure customize Paging in GridView Data Control

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
ASPdotNET-Example