There are always a problem to customize paging for DataList as below.
I'm able to develop a DataList Paging for my own application. Credit should give to Netomatix.
Please download and check the sample source code from my testing. For testing purpose, i use Northwind database and i've created a new stored proc called: GetProductByCategory
Stored Procedure: GetProductByCategory
CREATE PROCEDURE [dbo].[GetProductByCategory]
@PageIndex INT,
@NumRows INT,
@Discontinued BIT = 1
AS
BEGIN
SET NOCOUNT ON
DECLARE @StartRowIndex INT;
SET @StartRowIndex = (@PageIndex * @NumRows) + 1;
WITH myProducts AS(
SELECT ProductID, ProductName, UnitPrice,
CategoryName, Description, UnitsInStock,
ROW_NUMBER() OVER(ORDER BY ProductID DESC) as Row,
count(*) over() AS ResultRowCount, Discontinued
FROM Products P
INNER JOIN Categories C
ON P.CategoryID = C.CategoryID
WHERE Discontinued = @Discontinued
)
SELECT * FROM myProducts
WHERE Row BETWEEN @StartRowIndex AND (@StartRowIndex + @NumRows)-1
ORDER BY ProductID ASC
SET NOCOUNT OFF
END
