Tuesday, November 18, 2008

SQL Server Stored Procedure paging with .net

Leave a Comment
We face Paging problem while working with datalist.

Datalist does not support paging like grid.

Here, is the solution for this major problem.

use Sql server stored procedure to get proper paging which we can call from Dot Net

Suppose I have a Table master Contains Fields "UserName,Code, Address1,FatherName" etc.

To Make this ex. working follow steps
1) Make New Database
2) Create Table Master
3) Create Fields
UserName Nvarchar(50),
Code Nvarchar(50),
Address1 Nvarchar(50),
FatherName Nvarchar(50)
4) Then run following command is sql query window.
5) After creating this exec [Get_Records] 1,10
6) First param stands for pageno and second is for No. of records per page.

-- =============================================
Create PROCEDURE [dbo].[Get_Records]
@Int_PageNo int,@Int_RecordPerPage int
Declare @Int_From_Page int,@int_To_Page int,@Int_Total_Records int
set @Int_From_Page=((@Int_PageNo-1)*@Int_RecordPerPage)+1
set @int_To_Page=@Int_PageNo*@Int_RecordPerPage
print @Int_From_Page
print @int_To_Page

Select @Int_Total_Records=Count(*) From Master;

WITH ListEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Code ASC)AS Row, UserName,Code, Address1,FatherName
FROM Master
SELECT Row, UserName,Code, Address1,FatherName FROM ListEntries
WHERE Row between @Int_From_Page and @int_To_Page