SQL Server Stored Procedure paging with .net

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
AS
BEGIN
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
/***********************************************/
SET NOCOUNT ON;

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

END

Share: