When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, January 26, 2011

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

By 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 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


    This article is the fifth installment in an ongoing series on displaying a grid of data in an ASP.NET MVC application. Previous articles in this series examined how to sort, page, and filter a grid of data, but none have looked at combining one or more of these features in a single grid. This article and the next one show how to merge these features into a single grid. In particular, this article looks at displaying a grid that can handle both sorting and paging. The subsequent article will examine combining sorting, paging and filtering.

    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 both sortable and pageable. It is presumed that you have already read and worked through the previous three articles in this series.

    Recall that in Sorting a Grid of Data in ASP.NET MVC the demo was available at the URL www.yoursite.com/Products/Sortable. Visiting this URL displayed the grid using its default sorting characteristics, which was to sort the grid by the ProductName column in ascending order. To have the data sorted by an alternate column or sorting direction, you'd pass in the column name and sort direction through the querystring like so: www.yoursite.com/Products/Sortable?sortBy=ColumnName&ascending=true|false. For example, the URL /Products/Sortable?sortBy=UnitPrice&ascending=false would display the products sorted by the UnitPrice column in descending order.

    The demo in Displaying a Paged Grid of Data in ASP.NET MVC worked much in the same way. Visiting www.yoursite.com/Products/Paged displayed the first 10 records. To view an alternate page of data, or to change the number of records displayed per page, you'd visit the URL www.yoursite.com/Products/Paged?page=pageNumber&pageSize=numberOfRecordsPerPage.

    This article looks at how to combine the two URL patterns. We'll create a new action named SortAndPage that will accept four input parameters:

    • sortBy - specifies the column by which the data will be sorted. If not specified, the data is sorted by ProductName.
    • ascending - a true/false value that indicates whether the data is sorted in ascending or descending order. If not specified, the data is sorted in ascending order.
    • page - specifies the index of the page of data to display; note that this index starts at 1, meaning to view page 2 you'd specify a page parameter of 2. If this value is omitted it defaults to showing the first page of data (that is, it has a default value of 1).
    • pageSize - indicates how many records to show per page. Defaults to 10.
    The following URL examples illustrate how these querystring parameters affect the grid:
    • /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.
    In the sorting installment we created a view-specific Model class named ProductGridModel. Initially, this class contained properties that indicated the sorting preferences, namely the column by which the data was sorted and the sort direction. Then, in the paging installment, we augmented this class to include paging-related properties, namely properties to track the current page index, the number of records to show per page, and the total number of records being paged through.

    For this installment we do not need to make any additions to the ProductGridModel class - it already has all the functionality we'll need.

    Step 1: Creating the SortAndPage Action


    When a request arrives for www.yoursite.com/Products/SortAndPage, ASP.NET MVC will execute the SortAndPage action in the ProductsController class. The SortAndPage action needs to accept four input parameters - a string parameter named sortBy, a Boolean parameter named ascending, an integer parameter named page, and an integer parameter named pageSize. When a request arrives for the SortAndPage action, ASP.NET MVC automatically maps any of the request's parameters to the input parameters of the executed action. For instance, when a request arrives for the URL www.yoursite.com/Products/SortAndPage?sortBy=UnitPrice&ascending=true&page=2&pageSize=10, ASP.NET MVC invokes the SortAndPage action and passes in the values UnitPrice, true, 2, and 10 to the action's sortBy, ascending, page, and pageSize input parameters.

    The SortAndPage action is responsible for creating a ProductGridModel object and assigning its sorting- and paging-related values. It's also responsible for sorting and returning just the subset of records to display given the specified page and pageSize input parameters. The following snippet of code performs these steps.

    public class ProductsController : Controller
    {
       // GET: /Products/SortAndPage?SortColumn=columnName&Ascending=true|false&page=number&pageSize=number
       public ActionResult SortAndPage(string sortBy = "ProductName", bool ascending = true, int page = 1, int pageSize = 10)
       {
          var model = new ProductGridModel()
          {
             // Sorting-related properties
             SortBy = sortBy,
             SortAscending = ascending,

             // Paging-related properties
             CurrentPageIndex = page,
             PageSize = pageSize
          };

          // Determine the total number of products being paged through (needed to compute PageCount)
          model.TotalRecordCount = this.DataContext.Products.Count();

          // Get the current page of products sorted in the appropriate order
          model.Products = this.DataContext.Products
                               .OrderBy(model.SortExpression)
                               .Skip((model.CurrentPageIndex - 1) * model.PageSize)
                               .Take(model.PageSize);

          return View(model);
       } }

    The SortAndPage action starts by creating a new ProductGridModel instance named model and assigning model's sorting- and paging-related properties. In particular, the model's SortBy and SortAscending properties are assigned the values of the sortBy and ascending input parameters, while the model's CurrentPageIndex and PageSize are assigned the values of the page and pageSize input parameters.

    Next, the SortAndPage action determines the total number of records being paged through - this.DataContext.Products.Count() - and assigns the resulting number to model's TotalRecordCount property. This total count of records being paged through is needed by the paging interface so that it can determine how many numeric pages to show and can determine how many total pages of data are being paged through.

    Finally, the model's Products property is assigned the appropriate subset of records sorted in the specified way. Note that we start by referencing all of the products (DataContext.Products). Next, the data is sorted based on the model's SortExpression (which is a single string property that is composed based on the SortBy and SortAscending properties). Following that, the Skip and Take methods are used to excise the appropriate subset of records for display. (Refer back to the Displaying a Paged Grid of Data in ASP.NET MVC article for a detailed discussion at how the Skip and Take methods efficiently retrieve the appropriate subset of records for display.)

    After the sorting and paging logic has been applied, the model object is passed to the View.

    Step 2: Creating the View


    To create the view for the SortAndPage 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, Filterable.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 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. The <table>'s header row markup was taken from the Sortable View while the markup for the "You are viewing page X of Y," the <table>'s body rows in the foreach, and the paging interface in the footer of the grid are from the Paged View.

    Take a moment to visit this View through a browser. If you visit Products/SortAndPage 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 first page of data is displayed, sorted by ProductName.

    If you click the Category header you are whisked to Products/SortAndPage?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.

    Unfortunately, there is a particularly glaring shortcoming of our grid at this point - paging and sorting information is lost when moving from one mode to another. For instance, if you visit Products/SortAndPage and then move to page two you are taken to Products/SortAndPage?page=2&pageSize=10. If you then opt to sort by UnitPrice you are taken to Products/SortAndPage?sortBy=UnitPrice&ascending=True. Note how the paging-related querystring fields have been lost. The net effect is that once you sort you are immediately returned to the first page of data. Similarly, if you sort and then page, you are taken to the second page of data but the sort information is lost, meaning you are taken to the second page of data when sorted by the default sorting order (ProductName alphabetically).

    In my opinion, when a user chooses to re-sort a grid she should be returned to the first page of data (rather than kept on the current page), so losing the paging information upon sorting is not an issue. However, a user should be able to page through a grid sorted in some other way than the default sorting order. Therefore, we need to ensure that any sorting information is retained when paging, which is the topic for Step 3.

    Step 3: Remembering the Sorting Order When Paging


    The grid's paging interface is a collection of hyperlinks that, when clicked, whisk the user to the current action, but pass along the new page number in the querystring. The problem is that these links only contain page (and pageSize) querystring parameters. They do not include the sorting related querystring parameters (sortBy and ascending). We need to update these links so that they do include this additional information.

    In Displaying a Paged Grid of Data in ASP.NET MVC we created a partial view named PagerLink.ascx that was responsible for rendering the hyperlinks in the paging interface. The querystring parameters included in each link were specified as a RouteValueDictionary collection using the following code:

    var routeData = new RouteValueDictionary {
                              { "page", ViewData["PageIndex"].ToString() },
                              { "pageSize", Model.PageSize }
                      };

    We want to include the sorting parameters as part of this RouteValueDictionary object (if they exist). This can be accomplished using code similar to the following:

    var routeData = new RouteValueDictionary { ... };

    // See if sortBy querystring parameter is specified... if so, include it in the link's querystring
    if (!string.IsNullOrEmpty(Request.QueryString["sortBy"]))
       routeData.Add("sortBy", Request.QueryString["sortBy"]);

    // See if ascending querystring parameter is specified... if so, include it in the link's querystring
    if (!string.IsNullOrEmpty(Request.QueryString["ascending"]))
       routeData.Add("ascending", Request.QueryString["ascending"]);

    I took this concept and created an extension method on the RouteValueDictionary named AddQueryStringParameters that is a little more generic - it loops through all of the querystring parameters, adding their key/value pairs to the RouteValueDictionary object if the key is not already present. (See the ~/HelperClasses/RouteValueDictionaryExtensions.cs class to examine this extension method.) With this extension method in place, we can use the following code in place of the if statements above:

    var routeData = new RouteValueDictionary { ... };

    // Add the querystring parameters to the routeData collection
    routeData.AddQueryStringParameters();

    And that's all there is to it! Now that the pager links include the sort information, paging retains the sort order. For example, sorting by UnitPrice in descending order and then moving to page 2 sends the user to the URL Products/SortAndPage?page=2&pageSize=10&sortBy=UnitPrice&ascending=False. Consequently, the grid will display the second page of data (records 11-20) when sorted by UnitPrice in descending order. The screen shot below shows this grid. Note how we are viewing page 2 of 7 and how the results are sorted by UnitPrice in descending order.

    The grid shows the second page of data when ordered by UnitPrice in descending order.

    Conclusion and Looking Forward...


    This article showed how to combine the work we did in two previous installments into a single grid capable of both paging and sorting. In the following installment we'll see how to combine sorting and paging and filtering all in one grid.

    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


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article