Paging using LINQ

SQL Server supports many ways of implementing paging in the applications. With the introduction of LINQ (Language Integrated Query) support in VS 2008 paging is further made simple. Please follow the below steps to implement paging for Employee List screen

Step 1 :
Write a method to retrieve the employee list from the table “Employee” using LINQ as shown in the method “GetEmployeeList”

public List GetEmployeeList(int PageNo ,out int TotalPages)
{
Int NoofRecordsPerPage =10;
List oResultOut = new List();
var query = (from p in dc.Employees
select p).ToList();
if (PageNo == 0)
TotalPages = query.Count / NoofRecordsPerPage;

oResultOut = query.Skip(PageNo * NoofRecordsPerPage)
.Take(NoofRecordsPerPage)
.ToList();

}
The parameter “PageNo” is used to determine the current page of Employee List view. It is often required to know the total number of pages, the List view will span during the first execution of the query. The above method will fill the total number of pages using the “query.Count” attribute.
The query.Skip() will filter the records of the previous pages and the query.Take() method will show only the records qualified for the current page. The number of records to be displayed on each page can be controlled using the variable “NoofRecordsPerPage” .

Step 2 :
Calling the “GetEmplyeeList” from the UI based on current page selected by the user in the Employee List view

// assume the user sees the first page
List oResult = new List();
int TotalPages =0;
oResult = GetEmployeeList( 0, out Totalages);

// assume the user sees the fifth page
List oResult = new List();
int TotalPages =0;
oResult = GetEmployeeList( 5, out Totalages);

We will see the benefits of using LINQ and how to implement paging for retrievals using the stored procedures using DLINQ in my next post

Paging with MS SQL Server 2005

Paging can be easily achieved in MS SQL Server 2005 by using the ROW_NUMBER function and CTE (Common Table Expressions). The ROW_NUMBER function provides the ability to issue a query and just return a subset of the result set and the CTE allows us to define the temporary result set with clause. The below example shows how to retrieve first ten Patients from a result set using the CTE and ROW_NUMBER function.

USE Patient_DB;
GO
WITH PatientDetail
AS
(
SELECT
Surname,
Forename,
Age,
ROW_NUMBER() OVER (order by Surname) AS 'RowNumber'
FROM Patient
)
SELECT * FROM
PatientDetail
WHERE
RowNumber between 1 and 10;
GO

The OVER clause is used to determine the partitioning and ordering of the intermediary result set before the ROW_NUMBER function is applied. The SELECT statement can be parameterized to retrieve data for the specified Range as given below.

USE Patient_DB;
GO
WITH PatientDetail
AS
(
SELECT
Surname,
Forename,
Age,
ROW_NUMBER() OVER (order by Surname) AS 'RowNumber'
FROM Patient
)

SELECT * FROM
PatientDetail
WHERE RowNumber between
@RowNumberFrom and @RowNumberTo;
GO