Monday, April 25, 2005 8:00 AM
Hi
All of us would have implemented Paging in our applications.
Paging is particularly useful if you have lots of records to be displayed on a page and you can't get them displayed in one stretch. Say we have 1000 records to be displayed in a page. In this scenario, we cannot show up all the records in a single stretch in the page. Hence we need to implement Paging functionality whereby users can see a set of records and then click on a Button/Link to view the next set of records.
Paging can be implemented in 3 ways viz., Storing all the records at the browser level through XML, session etc., using built-in paging functionalities provided by controls like .NET Datagrid, using SQL Stored Procedures for retrieving set based results.
Out of the above 3, the SQL Method would be the best approach if you are aiming at Custom Paging Mechanism.
But, its a little tricky. Say you have a table which doesn't have an identity column or any sequential data column which you can use to determine the position of each row, then you need to write your procedure in a way that it can take the start values and end values for the set of records to be displayed.
The following procedure is useful to retrieve records in sets for displaying and useful in implementing custom paging.
Let us consider an Employees Table which doesn't have an Identity Column and we want to fetch sets of records based on the set requested for. Consider the following procedure
CREATE PROCEDURE uspPaging
@nStartValue INT,
@nEndValue INT
AS
SET NOCOUNT ON
DECLARE @tblTempData TABLE
(
nID INT IDENTITY,
EmployeeID INT,
LastName VARCHAR(50),
FirstName VARCHAR(50),
SupervisorID NCHAR(5)
)
INSERT INTO @tblTempData
(
EmployeeID,
LastName,
FirstName,
SupervisorID
)
SELECT
EmployeeID,
LastName,
FirstName,
ReportsTo
FROM Employees
ORDER BY
EmployeeID,
FirstName
SELECT EmployeeID,
LastName,
FirstName,
SupervisorID
FROM @tblTempData
WHERE nID BETWEEN @nStartValue AND @nEndValue
ORDER BY
nID ASC
The above procedure will accept 2 parameters @nStartValue and @nEndValue and display the records based on the values. The @tblTempData which we create within this procedure is useful in enforcing the IDENTITY Column which does not exist in the original table.
This procedure can be used when implementing custom paging and can be modified according to the actual requirements.
I welcome your comments.
Cheers and Happy Programming !!!