使用 T-SQL 的其他行分页技术

引言

当我们处理小数据时，网页显示记录看起来很正常。但是当数据量变大时，我们会发现“超时”先生来告诉你“抱歉，已过期”。

这种方法与其他分页方法不同。通过使用存储过程，我们可以获得包含特定行的表结果，而不是获取整个表。

这是存储过程的例程

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

现在我们可以使用通用的执行查询函数调用存储过程。

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