Displaying a Sorted, Paged, and Filtered Grid of Data in ASP.NET MVC

A Multipart Series on Grids in ASP.NET MVC

Displaying a grid of data is one of the most common tasks faced by web developers. This article series shows how to display grids of data in an ASP.NET MVC application and walks through a myriad of common grid scenarios, including paging, sorting, filtering, and client-side enhancements. Displaying a Grid - this inaugural article walks through building the ASP.NET MVC demo application and retrieving and displaying database data in a simple grid. Sorting a Grid - learn how to display a sortable grid of data. Paging a Grid - shows how to efficiently page through a grid of data N records at a time. Filtering a Grid - see how to create a filtering interface to limit what records are displayed. Sorting and Paging a Grid - learn how to display a grid that is both sortable and can be paged. Sorting, Paging, and Filtering - shows how to display a grid that is sortable, pageable, and filterable. MvcContrib Grid - see how to use the free, open source MvcContrib Grid to display a grid of data.

Introduction

Over the past couple of months I've authored five articles on displaying a grid of data in an ASP.NET MVC application. The first article in the series focused on simply displaying data. This was followed by articles showing how to sort, page, and filter a grid of data. We then examined how to both sort and page a single grid of data. This article looks at how to add the final piece to the puzzle: we'll see how to combine sorting, paging and filtering when displaying data in a single grid.

Like with its predecessors, this article offers step-by-step instructions and includes a complete, working demo available for download at the end of the article. Read on to learn more!

- continued -

Step 0: A Brief Roadmap

This article walks through creating a grid whose data is sortable, pageable, and filterable. (The download available at the end of the article also includes all other feature combinations, namely a sortable, filterable grid and a filterable, pageable grid.) It is presumed that you have already read and worked through the previous articles in this series.

In each of the earlier articles we implemented paging, sorting, and filtering logic through the use of querystring parameters. For example, the demo created in Filtering a Grid of Data in ASP.NET MVC was available at the URL www.yoursite.com/Products/Filterable, which displayed the grid without any filtering logic applied. (That is, it showed all products.) However, one could filter what products were displayed by supplying one (or more) of the following querystring parameters in the URL:

• categoryId - an integer value. If specified, only those products with a matching CategoryID value are returned.
• minPrice - a decimal value. If specified and greater than 0, only those products with a UnitPrice value greater than or equal to minPrice are returned.
• omitDiscontinued - a Boolean value. If true, only those products that are not discontinued are returned.

For instance, visiting www.yoursite.com/Products/Filterable?CategoryID=1 would display only those products with a CategoryID value of 1. (Namely, those products in the Beverages category.)

Each feature-set - sorting, paging, and filtering - has its own unique set of querystring parameters that control the behavior. As we saw in Sorting and Paging a Grid of Data in ASP.NET MVC, combining multiple features in one grid (such as sorting and paging) involved accepting querystring parameters for the sorting logic and for the paging logic. For example, the demo URL for the sortable and pageable grid was www.yoursite.com/Products/SortAndPage, and it accepted any combination of the querystring parameters sortBy, ascending, page, and pageNumber. For instance:

• /Products/SortAndPage - displays the first 10 products sorted in the default ordering (by ProductName in alphabetical order).
• /Products/SortAndPage?sortBy=UnitPrice - displays the first 10 products sorted by the UnitPrice column in ascending order.
• /Products/SortAndPage?page=3 - displays the third page of products (records 21-30) sorted by the default ordering (by ProductName in alphabetical order).
• /Products/SortAndPage?sortBy=UnitPrice&ascending=false&page=2 - displays the second page of products (records 11-20) sorted by the UnitPrice column in descending order (from most expensive to least).
• /Products/SortAndPage?sortBy=QuantityPerUnit&ascending=true&page=2&pageSize=15 - displays the second page of products (records 16-30) sorted by the QuantityPerUnit column in descending order.

As you may have surmised, creating a grid that sorts, pages, and filters involves creating an action that accepts all three feature-sets' querystring parameters and applies the sorting, filtering, and paging logic to the Products table data before being sent to the View for rendering. We'll also need to make some minor modifications to the View to correctly supply the appropriate querystring parameters when the user sorts, pages, or filters the grid.

For this demo we will create a new action named SortPageAndFilter that will accept the seven querystring parameters noted above. As with the previous demos, the action will work with any combination of querystring parameters supplied. For example, visiting /Products/SortPageAndFilter will display the first page of data sorted in the default order (by ProductName) with no filtering criteria applied, whereas visiting /Products/SortPageAndFilter?sortBy=UnitPrice&ascending=false&page=2&categoryId=1 will display the second page of products in the Beverages category (CategoryID=1) ordered by UnitPrice in descending order.

Step 1: Creating the SortPageAndFilter Action

When a request arrives for www.yoursite.com/Products/SortPageAndFilter, ASP.NET MVC will execute the SortPageAndFilter action in the ProductsController class. The SortPageAndFilter action needs to accept seven input parameters, one for each querystring value. As we have seen in previous installments, when a request arrives for the action, ASP.NET MVC automatically maps any of the request's parameters to the input parameters of the executed action.

The SortPageAndFilter action is responsible for creating a ProductGridModel object and assigning its sorting-, paging-, and filtering-related values. It's also responsible for:

• Filtering the Products table data based on the categoryId, minPrice, and omitDiscontinued values,
• Sorting the results based on the sortBy and ascending parameters, and
• Returning just the subset of records to display given the specified page and pageSize input parameters.

Let's start by examining the code that constructs the sorting-, paging-, and filtering-related values of the ProductGridModel model:

public class ProductsController : Controller {    public ActionResult SortPageAndFilter(string sortBy = "ProductName", bool ascending = true, int page = 1, int pageSize = 10, int? categoryId = null, decimal minPrice = 0M, bool? omitDiscontinued = null)    {       var model = new ProductGridModel()       {          // Sorting-related properties          SortBy = sortBy,          SortAscending = ascending,          // Paging-related properties          CurrentPageIndex = page,          PageSize = pageSize,          // Paging-related properties          CategoryId = categoryId,          MinPrice = minPrice,          OmitDiscontinued = omitDiscontinued.HasValue ? omitDiscontinued.Value : false,          CategoryList = this.DataContext.Categories                                  .OrderBy(c => c.CategoryName)                                  .Select(c =>                                             new SelectListItem                                             {                                                Text = c.CategoryName,                                                Value = c.CategoryID.ToString()                                             }                                          )       };       ...    } }

The SortPageAndFilter action starts by creating a new ProductGridModel instance named model and assigning model's sorting-, paging-, and filtering-related properties. The model's SortBy and SortAscending properties are assigned the values of the sortBy and ascending input parameters; the CurrentPageIndex and PageSize are assigned the values of the page and pageSize input parameters; and the CategoryId, MinPrice, and OmitDiscontinued. properties are assigned the values of the categoryId, minPrice, and omitDiscontinued input parameters. (Recall that the model's CategoryList property is a collection of SelectListItem objects that are used to populate the category drop-down list in the filtering user interface.)

All that remains is to set the model object's Products and TotalRecordCount properties to the appropriately filtered, sorted, and paged data and to the total number of records being paged through, respectively. This is accomplished by the following code in the SortPageAndFilter action:

public class ProductsController : Controller {    public ActionResult SortPageAndFilter(string sortBy = "ProductName", bool ascending = true, int page = 1, int pageSize = 10, int? categoryId = null, decimal minPrice = 0M, bool? omitDiscontinued = null)    {       ...       // Filter the results       var filteredResults = this.DataContext.Products.AsQueryable();       if (categoryId != null)          filteredResults = filteredResults.Where(p => p.CategoryID == categoryId.Value);       if (minPrice > 0M)          filteredResults = filteredResults.Where(p => p.UnitPrice >= minPrice);       if (omitDiscontinued != null && omitDiscontinued.Value == true)          filteredResults = filteredResults.Where(p => p.Discontinued == false);       // Determine the total number of FILTERED products being paged through (needed to compute PageCount)       model.TotalRecordCount = filteredResults.Count();       // Get the current page of sorted, filtered products       model.Products = filteredResults                            .OrderBy(model.SortExpression)                            .Skip((model.CurrentPageIndex - 1) * model.PageSize)                            .Take(model.PageSize);       return View(model);    } }

Here, the SortPageAndFilter action gets the set of products in the Products table as a queryable collection. Next, the various filtering input parameters are inspected to determine which filtering logic applies. For instance, if the categoryId input parameter is not null (meaning a value was specified), then the query is updated to return only those products whose CategoryID value matches the categoryId input parameter's value. Likewise, if the minPrice input parameter is greater than 0 then the query is further refined to only include those products with a UnitPrice greater than or equal to minPrice.

Next, the model object's TotalRecordCount property is assigned. Recall that this property reports the total number of records being paged through and is used by the paging user interface to determine how many page numbers to show, whether to enable the user to move to the next page of data, and so on. It is important that we set TotalRecordCount to the count of filtered records and not the total count of Products, as we did in the paging and sorting and paging demos.

Finally, the filtered results are ordered by the sort criteria and the appropriate subset of data is snipped out. This final result set is assigned to the model object's Products property. The View is then passed model.

Step 2: Creating the View

To create the view for the SortPageAndFilter action, right-click on the action name in the Controller class file and choose the Add View option. From the Add View dialog box, check the "Create a strongly-typed view" checkbox and then select the Web.Models.ProductGridModel option from the "View data class" drop-down. Click OK. This should close the dialog box and create (and open) the new view, SortPageAndFilter.aspx.

Next, add the following markup and server-side code to the View in the Content control for the MainContent ContentPlaceHolder:

<p>     <i>You are viewing page <%: Model.CurrentPageIndex %> of <%: Model.PageCount %>...</i> </p>      <table class="grid" style="width: 90%">     <tr>         <th style="width: 35%"><% Html.RenderPartial("SmartLink", Model, new ViewDataDictionary { { "ColumnName", "ProductName" }, { "DisplayName", "Product" } }); %></th>         <th style="width: 25%"><% Html.RenderPartial("SmartLink", Model, new ViewDataDictionary { { "ColumnName", "Category.CategoryName" }, { "DisplayName", "Category" } }); %></th>         <th style="width: 25%"><% Html.RenderPartial("SmartLink", Model, new ViewDataDictionary { { "ColumnName", "QuantityPerUnit" }, { "DisplayName", "Qty/Unit" } }); %></th>         <th style="width: 15%"><% Html.RenderPartial("SmartLink", Model, new ViewDataDictionary { { "ColumnName", "UnitPrice" }, { "DisplayName", "Price" } }); %></th>         <th style="width: 5%"><% Html.RenderPartial("SmartLink", Model, new ViewDataDictionary { { "ColumnName", "Discontinued" }, { "DisplayName" , "Discontinued" } }); %></th>     </tr> <% foreach (var item in Model.Products)     { %>          <tr>         <td class="left"><%: item.ProductName%></td>         <td class="left"><%: item.Category.CategoryName%></td>         <td class="left"><%: item.QuantityPerUnit%></td>         <td class="right"><%: String.Format("{0:C}", item.UnitPrice)%></td>         <td>             <% if (item.Discontinued)                 { %>                 <img src="<%=Url.Content("~/Content/cancel.png") %>" alt="Discontinued" title="Discontinued" />             <% } %>         </td>     </tr>      <% } %>          <tr>         <td class="pager" colspan="5">             <% Html.RenderPartial("Pager", Model); %>         </td>     </tr>          </table>

The above markup is the same markup used in the SortAndPage View in Sorting and Paging a Grid of Data in ASP.NET MVC. (The SortAndPage View was a combination of the markup used to create the Sortable View in Sorting a Grid of Data in ASP.NET MVC and the Paged View in Displaying a Paged Grid of Data in ASP.NET MVC.)

Take a moment to visit this View through a browser. If you visit Products/SortPageAndFilter you should see a grid showing the first page of data sorted by their default ordering (that is, by ProductName in ascending order). What's more, an arrow in the grid header indicates that the results are indeed sorted by the product's name.

The sorting and paging logic and user interfaces should work as expected. If you click the Category header you are whisked to Products/SortPageAndFilter?sortBy=Category.CategoryName&ascending=True and the grid is sorted by category name alphabetically. Again, the first page of data is displayed. Likewise, you can use the paging interface at the bottom of the grid to move from one page of data to another.

Likewise, the filtering logic should work as expected, although at the moment there is no filtering user interface. But if you enter the filtering criteria in the querystring by hand you will see that they affect the output. For example, visiting Products/SortPageAndFilter?sortBy=UnitPrice&categoryId=1 shows just the Beverage products sorted by UnitPrice in ascending order. (Note that because there are only ten products in the Beverages category there is only one page of data displayed.)

Step 3: Building the Filtering Interface

In Filtering a Grid of Data in ASP.NET MVC, we created a filtering user interface in the Filterable View using a <form> with its method attribute set to GET and its action attribute set to /Products/Filterable. Consequently, whenever the form was submitted the browser would request the /Products/Filterable, passing along the names and values of the form's input fields via the querystring. In particular, the form contained input fields to capture the filtering-specific parameters, namely categoryId, minPrice, and omitDiscontinued.

Let's start by copying this <form> in the Filterable View into our new SortPageAndFilter View, as-is. Specifically, add the following markup to the SortPageAndFilter View, placing it beneath the "You are viewing page X of Y" message and above the grid itself.

<% using (Html.BeginForm("Filterable", "Products", FormMethod.Get)) { %>    <fieldset class="filtering">       <legend>Filter Product Results</legend>       <div>          <b>Show Products In:</b>          <%: Html.DropDownListFor(model => model.CategoryId, Model.CategoryList, "-- All Categories --") %>                    <b>Minimum Price:</b>          $<%: Html.TextBoxFor(model => model.MinPrice, new { size = 8 })%>          <b>Omit Discontinued?</b>          <%: Html.CheckBoxFor(model => model.OmitDiscontinued) %>          <input type="submit" value="Filter Results" />       </div>    </fieldset> <% } %>

With this markup in place, visit /Products/SortPageAndFilter through a browser. You should now see the filtering user interface. Try specifying a minimum price of $15 and clicking the "Filter Results" button. You should now see just those products that cost $15 or more. Note how there are now only five pages of data, rather than seven. What's more, you can sort and page through these filtered results. The screen shot below shows the grid when filtering so that only products that cost $15 or more are displayed, with the results sorted by UnitPrice in ascending order and with the second of five pages being displayed.

While the scenario I just outlined works great, try this: sort the data by the QuantityPerUnit column and advance to page 2. Now enter a new minimum price (say, $20) and click "Filter Results." Clicking "Filter Results" prompts the browser to make a request back to /Products/SortPageAndFilter but only the input fields in the <form> are passed back in the querystring. In short, the current values for the sortBy, ascending, page, and pageSize are lost. Consequently, the filtered results are sorted by ProductName (the default sort order) and the first page of data is displayed (rather than the second).

Ideally, when the user clicks the "Filter Results" button the sort order would be remembered, but the paging would return to the first page (since the new filter criteria may include fewer pages of data than the current page being viewed). To have the sortBy and ascending values remembered, we need to add them as input fields in the <form>. This can be done using hidden input fields. Add the following markup within the filtering user interface <form>:

<% using (Html.BeginForm("Filterable", "Products", FormMethod.Get)) { %>    ...    <%: Html.Hidden("sortBy", Model.SortBy)%>    <%: Html.Hidden("ascending", Model.SortAscending)%>    ... <% } %>

The above hidden input fields ensure that the current values of the Model's SortBy and SortAscending properties are echoed in the querystring as sortBy and ascending parameters when the filtering user interface form is submitted (that is, when the user clicks the "Filter Results" button).

And with that we are done! We now have a sortable, pageable, filterable grid by combining the lessons learned and the work we did throughout the previous five articles in this series.

But Wait... How Do the Sorting and Paging Interfaces Remember the Filtering Criteria?

To get filtering to work properly we had to add the current sorting settings as hidden form fields in the filtering <form>, but we didn't have to do anything extra to get the sorting or paging interfaces to work correctly - sorting the grid or moving from one page to another automatically includes the current filtering querystring parameters (if any). At this point you may be scratching your head and wondering why sorting and paging work automatically, but filtering doesn't. Well, sorting and paging don't really work automatically - we added some code in the Sorting and Paging a Grid of Data in ASP.NET MVC article. In particular, we updated the PagerLink.ascx and SmartLink.ascx partial views, instructing them to include all querystring parameters in their route data collection. (Recall that the PagerLink.ascx and SmartLink.ascx partial views are responsible for generating the paging and sorting hyperlinks, respectively.) Because the links generated by the PagerLink.ascx and SmartLink.ascx partial views already include all querystring parameters in their route data collection, the filtering querystring parameters are automatically included in the paging and sorting hyperlinks. This is why when sorting and paging the grid the filtering criteria is remembered.

Looking Forward...

At this point we have seen how to display a grid of data in ASP.NET MVC from the ground up. This entailed creating the action, a view-specific Model, and the View. There are some off-the-shelf components designed to assist in displaying a grid of data in ASP.NET MVC. The next installment in this series will look at displaying grids using MvcContrib, an open source suite of tools for ASP.NET MVC applications.

Until then... Happy Programming!

• By Scott Mitchell

  ---

  Attachments:
  

• Download the Demo Code Used in this Article

  Further Reading
  

• Displaying a Grid of Data in ASP.NET MVC
• Sorting a Grid of Data in ASP.NET MVC
• Displaying a Paged Grid of Data in ASP.NET MVC
• Sorting and Paging a Grid of Data in ASP.NET MVC