Displaying a Sorted, Paged, and Filtered Grid of Data in ASP.NET MVCBy Scott Mitchell
|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
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!
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
CategoryIDvalue are returned.
minPrice- a decimal value. If specified and greater than 0, only those products with a
UnitPricevalue greater than or equal to
omitDiscontinued- a Boolean value. If
true, only those products that are not discontinued are returned.
www.yoursite.com/Products/Filterable?CategoryID=1would display only those products with a
CategoryIDvalue 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
www.yoursite.com/Products/SortAndPage, and it accepted any combination of the querystring parameters
pageNumber. For instance:
/Products/SortAndPage- displays the first 10 products sorted in the default ordering (by
ProductNamein alphabetical order).
/Products/SortAndPage?sortBy=UnitPrice- displays the first 10 products sorted by the
UnitPricecolumn in ascending order.
/Products/SortAndPage?page=3- displays the third page of products (records 21-30) sorted by the default ordering (by
ProductNamein alphabetical order).
/Products/SortAndPage?sortBy=UnitPrice&ascending=false&page=2- displays the second page of products (records 11-20) sorted by the
UnitPricecolumn 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
QuantityPerUnitcolumn in descending order.
Productstable 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
will display the second page of products in the Beverages category (
CategoryID=1) ordered by
UnitPrice in descending order.
Step 1: Creating the
When a request arrives for
www.yoursite.com/Products/SortPageAndFilter, ASP.NET MVC will execute the
SortPageAndFilteraction in the
SortPageAndFilteraction 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.
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
Productstable data based on the
- Sorting the results based on the
- Returning just the subset of records to display given the specified
SortPageAndFilter action starts by creating a new
ProductGridModel instance named
model and assigning
sorting-, paging-, and filtering-related properties. The
SortAscending properties are assigned the values of
ascending input parameters; the
PageSize are assigned
the values of the
pageSize input parameters; and the
properties are assigned the values of the
omitDiscontinued input parameters. (Recall that the
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
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 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
(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
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
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
Products property. The View is then passed
Step 2: Creating the View
To create the view for the
SortPageAndFilteraction, 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.ProductGridModeloption from the "View data class" drop-down. Click OK. This should close the dialog box and create (and open) the new view,
Next, add the following markup and server-side code to the View in the Content control for the
The above markup is the same markup used in the
SortAndPage View in Sorting and Paging a Grid of Data in ASP.NET MVC.
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
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
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
FilterableView using a
methodattribute set to
actionattribute 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
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.
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
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
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
The above hidden input fields ensure that the current values of the Model's
SortAscending properties are echoed in the
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 |
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
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!