Displaying a Sorted, Paged, and Filtered Grid of Data in ASP.NET MVC
来源:互联网 发布:fedora和ubuntu 编辑:程序博客网 时间:2024/05/18 06:23
Introduction
Over the past couple of months I've authored five articles on displaying a grid of data in anASP.NET MVC application. The first article in the series focused on simplydisplaying data. This was followed by articles showing how tosort, page, and filter a grid of data. We then examined how to bothsort 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 inFiltering a Grid of Data in ASP.NET MVC was available at the URLwww.yoursite.com/Products/Filterable
, which displayed the grid without any filtering logic applied. (That is, it showedall 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 matchingCategoryID
value are returned.minPrice
- a decimal value. If specified and greater than 0, only those products with aUnitPrice
value greater than or equal tominPrice
are returned.omitDiscontinued
- a Boolean value. Iftrue
, only those products that are not discontinued are returned.
www.yoursite.com/Products/Filterable?CategoryID=1
would display only those products with aCategoryID
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 inSorting 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 waswww.yoursite.com/Products/SortAndPage
, and it accepted any combination of the querystring parameterssortBy
, ascending
,page
, and pageNumber
. For instance:
/Products/SortAndPage
- displays the first 10 products sorted in the default ordering (byProductName
in alphabetical order)./Products/SortAndPage?sortBy=UnitPrice
- displays the first 10 products sorted by theUnitPrice
column in ascending order./Products/SortAndPage?page=3
- displays the third page of products (records 21-30) sorted by the default ordering (byProductName
in alphabetical order)./Products/SortAndPage?sortBy=UnitPrice&ascending=false&page=2
- displays the second page of products (records 11-20) sorted by theUnitPrice
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 theQuantityPerUnit
column in descending order.
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 (byProductName
) 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 byUnitPrice
in descending order.
Step 1: Creating the SortPageAndFilter
Action
When a request arrives for
www.yoursite.com/Products/SortPageAndFilter
, ASP.NET MVC will execute theSortPageAndFilter
action in the ProductsController
class. TheSortPageAndFilter
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 thecategoryId
,minPrice
, andomitDiscontinued
values, - Sorting the results based on the
sortBy
andascending
parameters, and - Returning just the subset of records to display given the specified
page
andpageSize
input parameters.
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 namedmodel
and assigning model
's sorting-, paging-, and filtering-related properties. Themodel
's SortBy
and SortAscending
properties are assigned the values of thesortBy
and ascending
input parameters; theCurrentPageIndex
and PageSize
are assigned the values of thepage
and pageSize
input parameters; and theCategoryId
, MinPrice
, and OmitDiscontinued
. properties are assigned the values of thecategoryId
, minPrice
, and omitDiscontinued
input parameters. (Recall that themodel
'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
andTotalRecordCount
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 theSortPageAndFilter
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 theProducts
table as a queryable collection. Next, the various filtering input parameters are inspected to determine which filtering logic applies. For instance, if thecategoryId
input parameter is notnull
(meaning a value was specified), then the query is updated to return only those products whoseCategoryID
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 aUnitPrice
greater than or equal to minPrice
.
Next, the model
object's TotalRecordCount
property is assigned. Recall that this property reports thetotal 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 setTotalRecordCount
to the count of filtered records and not the total count ofProducts
, 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 themodel
object's Products
property. The View is then passedmodel
.
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 theWeb.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 theMainContent
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 inSorting and Paging a Grid of Data in ASP.NET MVC. (TheSortAndPage
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, byProductName
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 toProducts/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, visitingProducts/SortPageAndFilter?sortBy=UnitPrice&categoryId=1
shows just the Beverage products sorted byUnitPrice
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 toGET
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, namelycategoryId
, minPrice
, and omitDiscontinued
.Let's start by copying this <form>
in the Filterable
View into our newSortPageAndFilter
View, as-is. Specifically, add the following markup to theSortPageAndFilter
View, placing it beneath the "You are viewing pageX 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 theQuantityPerUnit
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 thesortBy
, ascending
,page
, and pageSize
are lost. Consequently, the filtered results are sorted byProductName
(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 thesortBy
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 assortBy
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.
<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 theSorting and Paging a Grid of Data in ASP.NET MVC article. In particular, we updated thePagerLink.ascx
and SmartLink.ascx
partial views, instructing them to includeall querystring parameters in their route data collection. (Recall that thePagerLink.ascx
and SmartLink.ascx
partial views are responsible for generating the paging and sorting hyperlinks, respectively.) Because the links generated by thePagerLink.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!
Attachments:
Further Reading
- Displaying a Sorted, Paged, and Filtered Grid of Data in ASP.NET MVC
- MVC architecture in ASP.Net using C# and Microsoft Data Access Application block
- Can we call the Method of a controller from another controller in asp.net MVC?
- Creating a Cascading Dropdown in ASP.net MVC 3 and jQuery (1)
- Creating and displaying a DataSet in Dreamweaver M
- ASP.NET MVC - loop model data in javascript
- Export Grid Data To Excel In Advance Kendo UI Using MVC WEB API And Entity Framework
- Simplified and Extended Data Binding Syntax in ASP.NET 2.0
- ASP.NET WebForms and MVC together in one project
- Authorize attribute and jquery AJAX in asp.net MVC
- ASP.NET MVC 4 - Layout and Section in Razor
- ExportAttribute, ImportAttribute, CompositionContainer and MEF in ASP.NET MVC 3
- Upload files in ASP.NET MVC with JavaScript and C#
- ASP.NET MVC - Redirect-and-Post-JSON-Object-in-ASP-NET-MVC
- VB .NET 2003 using datagridTableStyles on multiple tables in a data grid
- Rendering a Form in ASP.NET MVC Using HTML Helpers
- Display a view from another controller in ASP.NET MVC
- Adding and displaying a background
- 中缀表达式转后缀表达式求值
- 一个页面中多个window.onload = function(){}冲突问题解决方案
- ios5 支持 json 转化
- 常用的颜色色值(转)
- 向老大学到的东东
- Displaying a Sorted, Paged, and Filtered Grid of Data in ASP.NET MVC
- fcvt()浮点数转换为字符串
- android recovery模式及ROM制作
- 有关读写文件的操作 (个人总结)
- LoadRunner 实现监控Tomcat
- php文件头部空白影响CSS布局
- 工作经验
- 流缓冲类fstream()
- 在项目中用java处理json格式的数据