Monday, September 29, 2008

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:

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:

publicclass 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.

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:

publicclass 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 :)

3 comments:

Anonymous
said...

Great - thanks... I extended Stephen Walthers pager but ran into issues when it came to filtering the data. Whilst I haven't used your code exactly it gave me some fresh ideas on how to takle the problem.

This is a great example. I have learned a lot from it. I noticed that you write "So what happens when you want to add formatting to the column? Maybe adding a link column for example." For the life of me I can't figure out how I can do a link column from within the OrderTableInfo class. Wouldn't I need access to htmlHelper, or is there some other way to do it I am not considering. If it wasn't too much trouble, would you mind adding an example to the comments section or article like the one for the formatted order date column. Thanks so much. I really appreciate such a great article and you taking the time to work on the MVC platform, which I think is a great one for web development.

Hi Eric, I should probably update this to work better with the MvcContrib Grid. They might even have added column sorting to it, making this solution a bit legacy. You can check the code out in Suteki Shop, it should be simple to work out how to use the TableInfo class to create a link column.

Code Rant

Notepad, thoughts out loud, learning in public, misunderstandings, mistakes. undiluted opinions. I'm Mike Hadlow, an itinerant developer. I live (and try to work in) Brighton on the south coast of England. Please don't mistake me for an expert in anything. I love technology and programming, but make no claims to be any good at it. Much of what you read here may be poorly thought out, wrong, or just plain dangerous.