Here is an example:
1) A stored procedure that gets page of records from products table in
Northwind database
--------------------------------------------------------------------------------------
ALTER PROC usp_GetProductsPage
(
@pgNdx int,
@pgSize int,
@catId int = NULL
)
AS
-- create a temporary table with the columns we are interested in
CREATE TABLE #ProductsPage
(
RecordID int IDENTITY PRIMARY KEY,
ProductID int,
ProductName nvarchar(40),
UnitPrice money,
UnitsInStock smallint,
CategoryName nvarchar(15),
CompanyName nvarchar(40),
QuantityPerUnit nvarchar(20),
UnitsOnOrder smallint,
ReorderLevel smallint,
Discontinued bit,
CategoryId int,
SupplierId int
)
-- fill the temp table with all the employees
INSERT INTO #ProductsPage
(
ProductID,
ProductName,
UnitPrice,
UnitsInStock,
CategoryName,
CompanyName,
QuantityPerUnit,
UnitsOnOrder,
ReorderLevel,
Discontinued,
CategoryID,
SupplierID
)
SELECT ProductID,ProductName,UnitPrice,UnitsInStock,CategoryName,
CompanyName,QuantityPerUnit,UnitsOnOrder,ReorderLevel,Discontinued,
CategoryID,SupplierID
FROM vw_ProductsList
WHERE CategoryID = @catId OR @catId IS NULL OR @catId=-1
ORDER BY ProductID
-- declare two variables to calculate the range of records
-- to extract for the specified page
DECLARE @fromId int
DECLARE @toId int
SET @fromId= (@pgNdx * @pgSize)
SET @toId= @fromId + @pgSize -1
-- select the page of records
SELECT * FROM #ProductsPage
WHERE (RecordID BETWEEN @fromId AND @toId)AND
(CategoryID = @catId OR @catId IS NULL OR @catId=-1)
GO
-- usp_GetProductsPage 0,10
---------------------------------------------------------------------------------
You will also need another procedures that returns total number of records
in your products table.
2)then I suggest for simplisity, to add a dropdownlist to your DataList
header or any where in the page, fill this dropdownlist with numbers from 1
to total number of pages which should be
pages = totalNumOfRecords / pageRecordsCount
increase the pages by 1 if the totalNumOfRecords Mod pageRecordsCount is
greater that 0
3)Now when the user change the page from the dropdownlist, you need to
submit the page number along with the page size to the above stored procedure.
I wish I could make clearer.. Please contact me by your e-mail and I'll
submit a full example to you if you wish.
Reagrds
