Wednesday, October 22, 2008

Database Records Pagination in LINQ to SQL

Database Records Pagination in LINQ to SQL

Before diving in directly to how we can achieve DB Records pagination in LINQ to SQL, I wanted to give little glance about C# 3.0 partitioning operators Take, Skip, TakeWhile, SkipWhile.

The partitioning operator’s returns subset of a collection with these operators you will get partial result.

With Take you need to specify number of elements to take from the collections.

Skip will ignore the specified number of elements and take the rest.

TakeWhile takes the elements as long as the condition is true.

SkipWhile Bypasses elements in a collection as long as a specified condition is true and then returns the remaining elements from collection.

Today there was a requirement in my project I need to return the customer records in addition to pagination. Using LINQ to SQL I am selecting all customer records from customer table. Partitioning operators are needed for pagination of customer records.

Here is the code snippet

// Page size is configurable in app.config or web.config.
int pageSize = int.Parse(configSec.Get("PageSize").Trim());

var customerRecrods = from customer in dbconn.APVENMASTs
join custAddress in dbconn.APVENADDRs on customer.VENDOR.Trim() equals custAddress.VENDOR.Trim()
select new RefineryProfile

name = customer.VENDOR_VNAME,

customerNumber = customer.VENDOR,

phoneNumber = customer.PHONE_NUM,

postalCode = custAddress.POSTAL_CODE,

city = custAddress.CITY_ADDR5,

address = custAddress.ADDR1,

address1 = custAddress.ADDR2,
return customerRecrods.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();


No comments: