Web applications often have pages that show a table of items. Often these items are filtered by some search criteria. We should be able to show a page of items at a time (usually called paging) and it's nice to be able to sort the items by clicking on the column headers (usually called column ordering). The one thing you really don't want to do when faced with these requirements is get the entire set back from the database and then filter,page and sort them either on the web server or the client.
Today I want to show you a simple framework for doing this with the MVC Framework. The MvcContrib project already has an excellent grid HTML helper written by Jeremy Skinner, so I've merely used the power of LINQ and extended the Grid to provide paging and column ordering.
Here's the page I want to produce. It shows orders from the Northwind database, you can filter by customer and shipper, click on the columns to order by the column's contents and page through the results. They all work together, so the pager remembers the filter and the column order and the column orderer remembers the page and filter:
Here's the SQL that produced this page:
exec sp_executesql N'SELECT [t3].[OrderID], [t3].[CustomerID], [t3].[EmployeeID],[t3].[OrderDate], [t3].[RequiredDate], [t3].[ShippedDate], [t3].[ShipVia],[t3].[Freight],[t3].[ShipName], [t3].[ShipAddress], [t3].[ShipCity], [t3].[ShipRegion],[t3].[ShipPostalCode], [t3].[ShipCountry]FROM (SELECT ROW_NUMBER() OVER (ORDER BY [t2].[CompanyName]) AS [ROW_NUMBER],[t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],[t0].[RequiredDate],[t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName],[t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode],[t0].[ShipCountry]FROM [dbo].[Orders] AS [t0]LEFT OUTER JOIN [dbo].[Shippers] AS [t1] ON [t1].[ShipperID] = [t0].[ShipVia]LEFT OUTER JOIN [dbo].[Customers] AS [t2] ON [t2].[CustomerID] = [t0].[CustomerID]WHERE ([t1].[ShipperID]) = @p0) AS [t3]WHERE [t3].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2ORDER BY [t3].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int',@p0=1,@p1=140,@p2=20
The filtering (by Shipper), paging and ordering are all executed on the database. Only twenty rows are returned by the query.
The key to making this work is a re-useable definition of the columns we want to display. Here's my one for this page:
public class OrderTableInfo : TableInfo<Order>{public OrderTableInfo(){AddColumn(order => order.OrderID, "Order Id");AddColumn(order => order.Customer.CompanyName, "Customer");AddColumn(order => order.Shipper.CompanyName, "Shipper");AddColumn(order => order.OrderDate, "Order Date");}}
I've created a new TableInfo class. Simply inherit this and add the columns you want to be displayed in the constructor. Each column definition is simply a Expression<Func<T, TParam>> that returns the property of the root object that we want to display. The expressions are reused both to grab the value of the property we want to display and as input to the OrderBy clause that does the column ordering.
Here's the controller action for this page:
[HandleError]public class HomeController : Controller{private readonly NorthwindDataContext db = new NorthwindDataContext();private const int pageSize = 20;public ActionResult Index(string customerId, int? shipperId, FormCollection form){// hack because property binder favors queryString over formif (form["CustomerId"] != null) customerId = form["CustomerId"];if (form["ShipperId"] != null) shipperId = (form["ShipperId"].Length == 0) ?(int?)null :int.Parse(form["ShipperId"]);var criteria = new OrderSearchCriteria{CustomerId = customerId,ShipperId = shipperId};var orderTableInfo = new OrderTableInfo();var orders = db.Orders.ThatMatch(criteria).SortColumns(orderTableInfo, Request.QueryString).ToPagedList(Request.QueryString.PageNumber(), pageSize);ViewData["Title"] = "Orders";var customers = db.Customers;var shippers = db.Shippers;return View("Index", new NorthwindViewData().WithOrders(orders).WithOrderTableInfo(orderTableInfo).WithOrderSearchCriteria(criteria).WithCustomers(customers).WithShippers(shippers));}public ActionResult About(){ViewData["Title"] = "About Page";return View();}}
As you can see we create a new instance of the OrderTableInfo that describes the table we want to create. We then get the orders to display from the database. The SortColumns(orderTableInfo, Request.QueryString) extension method looks in the query string for a key called 'sortby' and matches its value to the column name described in OrderTableInfo. If it finds a matching value it uses the expression from OrderTableInfo in an OrderBy clause. ToPagedList(Request.QueryString.PageNumber(), pageSize) appends Skip() and Take() clauses to do the paging. We then pass the list of orders and the orderTableInfo to the view.
Here's the section of the view that renders the table:
Pager is an extension method on HtmlHelper that uses the IPagedList from MvcContrib to render the pager. The table of orders is rendered by the MvcContrib grid, but instead of specifying our columns here we use the column specification from OrderTableInfo. I've created a new HtmlHelper extension method 'CreateGridColumnBuilder' that creates the Action<IRootGridColumnBuilder<T>> that defines the columns for the Grid.
You'll notice that the 'Order Date' shown in the screen shot above is a short date time (in UK format). So what happens when you want to add formatting to the column? Maybe adding a link column for example. I've provided an overloaded version of TableInfo's AddColumn method that takes a full MvcContrib grid column definition that you can use like this:
public class OrderTableInfo : TableInfo<Order>{public OrderTableInfo(){AddColumn(order => order.OrderID, "Order Id");AddColumn(order => order.Customer.CompanyName, "Customer");AddColumn(order => order.Shipper.CompanyName, "Shipper");AddColumn(order => order.OrderDate,col => col.For(order => order.OrderDate.Value.ToShortDateString()),"Order Date");}}
Note the Order Date now defines both the property we want to sort by and the full column definition. The problem here is that there's nothing to stop you from providing totally different values for these, so take care :)
So to sum up, just follow these steps:
- Create a definition for your table by inheriting from TableInfo<T>
- Chain SortColumns and ToPaged list to your IQueryable<T> expression that returns your items.
- Pass the table definition and items to the view
- Use the Pager and MvcContrib Grid to render the view. Pass Html.CreateGridColumnBuilder(<table definition>) to the Grid.
You can download the full source code for this example here:
