List<t> paging via LINQ to Objects

The various grid controls shipped with asp.net provides plug and play data paging on its underlying data source. Its quiet simple to get a paged view of your data by setting a few properties on the grid before calling the Bind() method to connect your grid to the data.  This scenario works well in most situations. On occasions where the paging logic needs to reside outside of the UI layer, a more custom paging mechanism is required.

In this post, I'll examine a simple way to implement a custom paginging mechanism via LINQ to objects, removing the paging logic from the UI component and placing it at a lower level layer, maybe in a controller class (MVC) or in a presenter class in an MVP pattern.

Lets imagine we have just retrieved a list of 5,000 rows of data from a product catalog table living in a SQL Server 2005 database, via a datatable object. The data retrieved is broken out into the following columns:

ProductId (int)
Name (string)
Description (string)
Price (decimal)
Category (string)

We first need to convert this datatable object to a strongly typed list of product summaries (each row in the datatable = a product summary)

Lets define a product summary class that we can map each product to:

public class ProductSummary
{
      public int ProductId { get; set; }
      public string Name { get; set; }
      public string Description { get; set; }
      public decimal Price { get; set; }
      public string Category { get; set; }
}


We will also define another method that takes the datatable object and convert it into a strongly typed list of ProductSummary Instances.

public List<ProductSummary> ConvertTableToList(DataTable aDataTable)
{
     List<ProductSummary> _lst = new List<ProductSummary>();
     foreach (DataRow aDataRow in aDataTable.Rows)
     {
          ProductSummary aSummary = new ProductSummary();
          aSummary.Category = aDataRow["category"].ToString();
          aSummary.Description = aDataRow["description"].ToString();
          aSummary.Name = aDataRow["name"].ToString();
          aSummary.Price = (decimal)aDataRow["price"];
          aSummary.ProductId = (int)aDataRow["productid"];
          _lst.Add(aSummary);
     }
     return _lst;
}

So far we have created a class ProductSummary that will hold a row of data from our datatable. We have also defined a method called ConvertTableToList that will take our datatable object and convert it into a list of Productsummary objects.

public static List<ProductSummary> GetProducts(int? page, int? pageSize)
{
     //default to page 1 if no page supplied
     int _page = (page.HasValue) ? page.Value : 1;

     DataTable dt = PupulateDataTable() //replace PopulateDataTable() with your logic to retrieve the data from the underlying data store

     //if no page size specified, set page size to total # of rows in data table
     int _pagesize = (pageSize.HasValue) ? pageSize.Value : dt.Rows.Count;

     List<ProductSummary> lst = ConvertTableToList(dt);     

     //paging logic
     return lst.Skip((_page - 1) * _pagesize).Take(_pagesize).ToList();
}

And here is how we might access a page of data from an asp.net web form page code-behind using the method defined above.
Assume that our paging method was  defined in a class called Products. Here we are retrieving page one, and we are telling the method to return 50 rows of data per page.

List<ProductSummary> products = Products.GetProducts(1, 50);
foreach (ProductSummary aSummary in products)
{
     Response.Write(string.Format("{0} {1} [{2}]","<li>",aSummary.Name, aSummary.Category));
}
products = null;

 

No Comments