WebGrid with filtering, paging and sorting "turn"

WebGrid with filtering, paging and sorting

by Jose M. Aguilar on April, at Web development

A few days ago I received some questions on the use of the Webgrid Helper in the comments section of my personal blog, spe Cifically on what implement custom filters on the data displayed. Since It's not easy-to-answer through the comments section, I had forced myself to write a article on it. Here it's.

The main obstacle here is a WebGrid doesn ' t include any kind of tool to perform this frequent task. It simply displays the data we provide, and if we want to the filter we had to do it manually. In theory, it would is enough to just to supply the Controller the filters we want to apply. Then the Controller queries the info from the Model following the criteria set.

However, something that could seem relatively simple gets harder if we want to also keep the sort order and paging capabilit IES untouched because we have to arrange for the state maintenance between requests as well. Well, the something we can solve in a few minutes;-)

Starting out from the project developed in the "the" of the series on Webgrid, let's see how we can add a couple of search Criteria to the grid, so it can appear to is this-to:

The text field enables us to look for substrings in the names and surnames of the people stored in the database, and the O Ther, Min-max, the children they. As usual, we ' ll combine these conditions with a logic and.

1. The View

First, we enter the form we is going to request the user of the search criteria in the view, right before generating The grid:

The code is as follows:

@using (html.beginform (null, NULL, Formmethod.get)) {    <fieldset>        <legend>search criteria</ Legend>        @Html. Label ("Search", "Text:")        @Html. TextBox ("search")         @Html. Label ("Minchildren", " Children min: ")        @Html. TextBox (" Minchildren ", NULL, new {@class =" num "})         @Html. Label (" Maxchildren "," children Max: ")        @Html. TextBox (" Maxchildren ", NULL, new {@class =" num "})        <input type=" Submit "value=" Apply "/>    </fieldset>}

Notice The simplicity of the form. We don ' t even has the use of the lambda syntax in the edit helpers. We generate the controllers starting out of the fields whose values would be is in the query string (and not from the Model, Which is the usual thing to do). For this reason, notice, the form was set to being sent using the HTTP GET method.

This propagate easily the values of the controls (textbox) between calls:

• If the user enters criteria and clicks the Send button, the URL of the request is performed on'll be, for instance,/friend S/index?search=aguilar&minchildren=1&maxchildren=4.
• If the user uses the grid ' s navigation buttons (Next/previous page, go to page, or reorder), these parameters would be Adde D to the previous ones, and therefore they preserve their values between the different calls. This is because Webgrid generates the links to these actions preserving the current query string parameters. In other words, if we is filtering and we go to page 4, we access a address that includes both the search criteria info As well as the paging, something like:/friends/index?search=aguilar&minchildren=1&maxchildren=4&page=4.

And by doing so, we are the view layer is finished.

2. The controller

The action method in charge of getting, the grid data and sending the view with the data to the user receives three Paramet Ers:current page, sort order field and its direction (ascending/descending).

Since now we had to get the sort order criteria, we had to extend it definition adding parameters for these values:

Public actionresult Index (int page = 1, string sort = "Surname",                           string sortdir = "ASC", string search = null,                           int ? Minchildren = null, int? Maxchildren = null)

See how all the parameters is optional, and we set them to null to easily detect when they come filled.

And at which point does we need to use these new parameters? Only in:

• In the "We make" to the "Model to count" of the total amount of the grid rows. We have the "to inform the grid" on the filtering criteria so that the counting is performed properly.
• In the "We make" to the "Model to get" the rows to is displayed in the current page, where we obviously has Account the filters.

The action is results somewhat like this:

Public actionresult Index (int page = 1, string sort = "Surname",                           string sortdir = "ASC", string search = null,                          int ? Minchildren = null,                           int? maxchildren = null) {  var friendscount = _services. Getfriendscount (Search, Minchildren, maxchildren);  var friends = _services. Getfriendspage (page, friends_per_page, sort, sortdir,                                               Search, Minchildren, maxchildren);  var data = new Friendspageviewmodel ()                 {                     numberoffriends = friendscount,                     friendsperpage = Friends_per_page,                     Friends = Friends                 };  return View (data);

The Comtroller.

3. The Model

And at the last, now on the Model, we have a to do the methods used from the controller (Getfriendscount and Getfriendspage) t Ake into account the parameters in which we indicate the search conditions.

In the first one, we simply return the number of people which follow the criteria returned as parameters:

public int Getfriendscount (string searchtext = null, int? minchildren = null,                            int? maxchildren = null) {    IQueryable <Friend> query = _data. People;    query = Filterpeople (SearchText, Minchildren, Maxchildren, query);    return query. Count ();}

The helper Method Filterpeople () We use are only in charge of adding the WHERE clauses to the To account the specified conditions:

private static Iqueryable<friend> filterpeople (                    string searchtext, int? minchildren,                     int? Maxchildren, iqueryable<friend> query) {    if (!string. Isnullorwhitespace (searchtext))        query = query. Where (p = p.name.contains (searchtext)                                  | | p.surname.contains (SEARCHTEXT));    if (Maxchildren! = null)        query = query. Where (p = p.children <= maxchildren);    if (Minchildren! = null)        query = query. Where (p = p.children >= minchildren);    return query;}

At last, we implement the method, which obtains the data to display in the current page:

Public ienumerable<friend> getfriendspage (int currentpage, int friendsperpage, String SortColumn, String Sortdir, string searchtext, int? Minchildren, int?                       Maxchildren) {//Validate input data Sortdir = sortdir.equals ("desc", stringcomparison.currentcultureignorecase)?    Sortdir: "ASC";    var validcolumns = new[] {"Surname", "Birthdate", "email", "Children"};    if (!validcolumns.contains (Sortcolumn.tolower ())) SortColumn = "surname";    if (CurrentPage < 1) currentpage = 1;    if (Friendsperpage < 1) friendsperpage = 10; Create the query var query = (iqueryable<friend>) _data. People.    ("it." + SortColumn + "" + Sortdir);    query = Filterpeople (SearchText, Minchildren, Maxchildren, query); return query. Skip ((currentPage-1) * friendsperpage). Take (FRIendsperpage). ToList ();}

There is not much to say on this code. In first place, a basic check of the entry parameters are performed, and then generate the query to be performed on the DAT Abase. As can see, the Como podéis Observar, the Querypeoplefiltered () method was performed to apply the query criteria.

Summarizing ...

As we have seen, implementing search criteria in Webgrid doesn ' t differ much from "What we have described earlier" in my Other posts about WebGrid. We just the following points:

• Firstly, include a form in the View where the "query criteria is collected" in order to send it to the controller.
• Secondly, prepare the Controller so it can receive this criteria and make it reach the Model.
• Thirdly, in the Model, simply apply the if countimg the total amount of rows, and if obtaining the data to B e displayed in the grid page.

Here's the can download the example code for this post.

WebGrid with filtering, paging and sorting "turn"