Other Row Paging Technique Using T-SQL

Introduction

When we work with small data, our web page look so fine displaying Record. But when the data getting large, we will found Mr. Time Out Coming to say "Sorry Expired".

This method is different than other paging method. By using stored procedure we can get a table result that contain specific row rather than get the entire table.

Here the stored procedure routin :

CREATE PROCEDURE Get_MasterPart
(
Declare @ActivePage as BigInt = 1
Declare @PageCount as BigInt = 10
)
AS
Begin
Declare @StartDate as DateTime
SET @StartDate = GetDate()

--DECLARE @MidRow as BigInt

DECLARE @TotalRow as BigInt
DECLARE @TotalPage as BigInt
DECLARE @MidRow as BigInt
Select @TotalRow = Count(PartNumber) From Master_Part WITH (NOLOCK)
SET @MidRow = Round(@TotalRow/2,0)
SET @TotalPage = Round(@TotalRow / @PageCount,0)
IF @TotalRow > (@TotalPage * @PageCount) SET @TotalPage = @TotalPage + 1

DECLARE @RowPos as BigInt
DECLARE @ID as VarChar(100)
SET @RowPos = (@PageCount * @ActivePage) - @PageCount
IF @RowPos > @MidRow
Begin
SET @RowPos = @TotalRow - @RowPos 
SET ROWCOUNT @ROWPOS
SELECT @ID = PartNumber From Master_Part WITH (NOLOCK) ORDER BY PartNumber DESC
END
ELSE
Begin
SET @RowPos = @PageCount * @ActivePage
SET @RowPos = @RowPos - @PageCount + 1
SET ROWCOUNT @ROWPOS
SELECT @ID = PartNumber From Master_Part WITH (NOLOCK) ORDER BY PartNumber ASC
End

SET ROWCOUNT @PageCount
DECLARE @Part table(TotalPage Int DEFAULT 0, PartNumber VarChar(50), SparePart Char(255), PartOnHand int)
INSERT INTO @PART SELECT 0 as TotalPage, PartNumber,SparePart,PartOnHand FROM Master_Part WITH (NOLOCK) WHERE PartNumber >= @ID ORDER BY PartNumber ASC
UPDATE @Part SET TotalPage = @TotalPage
SELECT * FROM @Part Order By PartNumber
SELECT DateDiff(MilliSecond,@StartDate,GetDate())

END
GO

Now we can call the stored procedure using common execute Query function.

dim dt = dataset
dt = DBServices.ExecuteQuery("Get_MasterPart" ,StoredProcedure,Sqlparameter)