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