Last week I posted about building custom paging with LINQ to SQL. And I wrapped the functionality with Extension Method to IQueryable<T> Interface.
Today I'm going to put the custom paging in a practical sample using ASP.NET ListView, DataPager and ObjectDataSource Control. Click here to download the sample. View demo here.
The sample is using Northwind Database. So first I created a Northwind LINQ to SQL class ".dbml"
Also I created the Extension Method that will handle paging. The following code shows the Extension Method:
/// <summary>
/// Perform custom paging using LINQ to SQL
/// </summary>
/// <typeparam name="T">Type of the Datasource to be paged</typeparam>
/// <typeparam name="TResult"></typeparam>
/// <param name="obj">Object to be paged through</param>
/// <param name="page">Page Number to fetch</param>
/// <param name="pageSize">Number of rows per page</param>
/// <param name="keySelector">Sorting Expression</param>
/// <param name="asc">Sort ascending if true. Otherwise descending</param>
/// <param name="rowsCount">Output parameter hold total number of rows</param>
/// <returns>Page of result from the paged object</returns>
public static IQueryable<T> Page<T, TResult>(this IQueryable<T> obj,
int page, int pageSize,
Expression<Func<T, TResult>> keySelector,
bool asc, out int rowsCount)
{
rowsCount = obj.Count();
int innerRows = rowsCount - (page * pageSize);
if(asc)
return obj.OrderByDescending(keySelector)
.Take(innerRows)
.OrderBy(keySelector)
.Take(pageSize)
.AsQueryable();
else
return obj.OrderBy(keySelector)
.Take(innerRows)
.OrderByDescending(keySelector)
.Take(pageSize)
.AsQueryable();
}
Note that this method is Generic Method, so it can be used with any collection implements IQueryable<T>. The method also support sorting in both directions ASC or DESC. So using this method, you don't have to write specific stored procedure or tabular function for each Table or View in your Database to implement custom database paging that support sorting too.
To be able to use ObjectDataSource in proper way, I made a simple Business Class that wrap the NorthwindDataContext -Northwind LINQ to SQL Class- and called it NorthwindProducts. This class basically contain 2 static methods one that returns total rows in Products table "GetProductsCount", and the other return paged results "GetProductsPage".
The 2 methods are shown below:
public partial class NorthwindProducts
{
public static int GetProductsCount()
{
using (var dc = new NorthwindDataContext())
{
int i = dc.Products.Count();return i;
}
}
public static IEnumerable<Product> GetProductsPage(int rowIndex, int pageSize, string sortExpression)
{
using (var dc = new NorthwindDataContext())
{
int totalRows;
int pageIndex = rowIndex / pageSize;
var products = new List<Product>();
IEnumerable<Product> productsPage;
bool asc = !sortExpression.Contains("DESC");
switch (sortExpression.Split(' ')[0])
{
case "ProductName":
productsPage = dc.Products
.Page(pageIndex, pageSize, p => p.ProductName, asc, out totalRows);
break;
case "QuantityPerUnit":
productsPage = dc.Products
.Page(pageIndex, pageSize, p => p.QuantityPerUnit, asc, out totalRows);
break;
case "UnitPrice":
productsPage = dc.Products
.Page(pageIndex, pageSize, p => p.UnitPrice, asc, out totalRows);
break;
case "UnitsInStock":
productsPage = dc.Products
.Page(pageIndex, pageSize, p => p.UnitsInStock, asc, out totalRows);
break;
case "UnitsOnOrder":
productsPage = dc.Products
.Page(pageIndex, pageSize, p => p.UnitsOnOrder, asc, out totalRows);
break;
default:
productsPage = dc.Products
.Page(pageIndex, pageSize, p => p.ProductID, asc, out totalRows);
break;
}
foreach (var product in productsPage)
{
products.Add(product);
}
return products;
}
}
}
Let's explore GetProductsPage method. This method will return IEnumrable<Product>. It accepts 3 parameters, rowIndex, pageSize and sortExpression:
- rowIndex: Index of the the next first record (row) in the result set.
- pageSize: Total number of records (rows) to be displayed (retrieved) in the result set.
- sortExpression: String that describe sort expression. Here I pass the sort expression constructed by ListView. For example if the sort is ascending the sort expression will be "ColumnName" while if the sort is descending the expression will be "ColumnName DESC".
That was all about core code that will perform paging. Its time to go through the presentation and how to use ListView and DataPager along with ObjectDataSource that will use the above mentioned methods.
For the UI design I used Matt's VS2008 Styled Grid styles to form the UI, so you can return to that post if you have any enquiry regarding UI styles.
I'll start with the ObjectDataSource the code is show below:
1: <asp:ObjectDataSource ID="odsProducts" runat="server" EnablePaging="True"
2: MaximumRowsParameterName="pageSize" SelectCountMethod="GetProductsCount"
3: SelectMethod="GetProductsPage" StartRowIndexParameterName="rowIndex"
4: TypeName="NorthwindProducts" SortParameterName="sortExpression">
5: </asp:ObjectDataSource>
As show on the code snippet:
- TypeName is "NorthwindProducts" the name of my business class.
- SelectCountMethod is "GetProductsCount".
- SelectMethod is "GetProductsPage".
- MaximumRowsParameterName is "pageSize", this is the parameter passed to the "GetProductsPage".
- StartRowIndexParameterName is "rowIndex", this is also parameter passed to the "GetProductsPage".
- SortParameterName is "sortExpression", again another parameter to be passed to the "GetProductsPage".
Nothing more on ObjectDataSource. Note that mapping between GetProductsPage parameters and MaximumRowsParameterName, StartRowIndexParameterName and SortParameterName properties of ObjectDataSource.
So Time to explore ListView with DataPager and link between ListView and ObjectDataSource:
1: <asp:ListView ID="lvProducts" runat="server" DataSourceID="odsProducts" >
2: <LayoutTemplate>
3: <table class="datatable" cellpadding="0" cellspacing="0" border="0">
4: <tr>
5: <th class="center">
6: <asp:LinkButton Text="ID" CommandName="Sort" CommandArgument="ProductID" runat="server" />
7: </th>
8: <th class="first">
9: <asp:LinkButton Text="Name" CommandName="Sort" CommandArgument="ProductName" runat="server" />
10: </td>
11: <th class="first">
12: <asp:LinkButton Text="Quantity" CommandName="Sort" CommandArgument="QuantityPerUnit" runat="server" />
13: </th>
14: <th class="right">
15: <asp:LinkButton Text="Unit Price" CommandName="Sort" CommandArgument="UnitPrice" runat="server" />
16: </th>
17: <th class="center">
18: <asp:LinkButton Text="In Stock" CommandName="Sort" CommandArgument="UnitsInStock" runat="server" />
19: </th>
20: <th class="center">
21: <asp:LinkButton Text="On Order" CommandName="Sort" CommandArgument="UnitsOnOrder" runat="server" />
22: </th>
23: </tr>
24: <asp:PlaceHolder ID="itemPlaceholder" runat="server"></asp:PlaceHolder>
25: <tr>
26: <td colspan="6">
27: <asp:DataPager ID="lvProductsPager" PagedControlID="lvProducts" PageSize="10" runat="server">
28: <Fields>
29: <asp:NextPreviousPagerField ShowPreviousPageButton="true" ShowFirstPageButton="true" ShowNextPageButton="false" ShowLastPageButton="false"/>
30: <asp:NumericPagerField />
31: <asp:NextPreviousPagerField ShowPreviousPageButton="false" ShowFirstPageButton="false" ShowNextPageButton="true" ShowLastPageButton="true"/>
32: </Fields>
33: </asp:DataPager>
34: </td>
35: </tr>
36: </table>
37: </LayoutTemplate>
38: <ItemTemplate>
39: <tr class='row'>
40: <td class="first">
41: <%# Eval("ProductID")%>
42: </td>
43: <td>
44: <%# Eval("ProductName")%>
45: </td>
46: <td>
47: <%#Eval("QuantityPerUnit")%>
48: </td>
49: <td>
50: <div class="money"><%#Eval("UnitPrice", "{0:C2}")%></div>
51: </td>
52: <td class="center">
53: <%#Eval("UnitsInStock")%>
54: </td>
55: <td class="center">
56: <%#Eval("UnitsOnOrder")%>
57: </td>
58: </tr>
59: </ItemTemplate>
60: </asp:ListView>
In the LayoutTemplate I declared TH tags that contains LinkButton with CommanName set to Sort and CommandArgument set to sort expression for each column. Also I declared a DataPager inside the ListView LayoutTemplate.
Now the sample is ready to run.
Conclusion:
Custom paging is required by many application, and is implemented with different approaches. The approach represented here is using LINQ to SQL to construct database paging query. It is not the best, but it is effective because using this method you can custom paging and sorting with generic method. So you don't have to create stored procedure of each table you wish to page through.
