MVC multiple field search with webgrid - Step 5

First of all we will try for multiple field search with paging and sorting for products by using WebGrid. We will create relationship between products and category table so we can show the category name in grid. To search we need a separate model for search having all the paging, sorting, total records and products. We need full featured page to search records by providing product name, price and category. After searching the records it should have the sorting feature by clicking the column name in grid as well as paging.

Main problem with paging and sorting we face is to sorting and paging with multiple field search. Suppose we entered product name and price and selected some category and search the result, now if we will try tp sort the result by clicking grid column, or click on any page number, we will loss all the search criteria. So solve it we will use FormMethod.Get, we will see it latter in this article.

First we will modify our products model to have property to save images and relationship with category.

public class Product
{
    public Int32 ProductId { get; set; }

    [Required]
    [Display(Name="Product Name")]
    public String ProductName { get; set; }

    [Required]
    [Display(Name = "Price")]
    public Decimal Price { get; set; }

    [Required]
    [Display(Name = "Category")]
    public Int32 CategoryId { get; set; }

    public String Image { get; set; }

    public String Thumb { get; set; }

    public virtual Category Categories { get; set; } 
}  

See the last property with virtual keyword, it is used to create the relationship between tables.

Now we will create the model for search with name "ProductSearchModels" having all the columns we want to search and properties like page, page size, sort column, sort direction, total number of records etc.

public class ProductSearchModels
{
    [Display(Name = "Product Name")]
    public String ProductName { get; set; }

    [Display(Name = "Price (Max.)")]
    public Decimal? Price { get; set; }

    [Display(Name = "Category")]
    public Int32? Category { get; set; }

    public Int32 Page { get; set; }
    public Int32 PageSize { get; set; }
    public String Sort { get; set; }
    public String SortDir { get; set; }
    public Int32 TotalRecords { get; set; }
    public List<Product> Products { get; set; }

    public ProductSearchModels()
    {
        Page = 1;
        PageSize = 5;
        Sort = "ProductId";
        SortDir = "DESC";
    }
}

We added all the columns which we want to search and the other properties like page, pagesize, sort and sortdir are default properties from gridview so I try to keep them same. At the end we have constructor of the class to assign default values like sort column, sort direction, current page and page size.

Create a new empty controller named "ProductsController" and add a private member db to access the database

private LearningContext db = new LearningContext();

When we add an empty controller, by default it adds Index action method, if not then add it manually and add the following code in it

public ActionResult Index(ProductSearchModels model)
{
    // To Bind the category drop down in search section
    ViewBag.Categories = db.Categories.Where(x => x.IsActive == true);

    // Get Products
    model.Products = db.Products
        .Where(
            x=>
            (model.ProductName == null || x.ProductName.Contains(model.ProductName))
            && (model.Price == null || x.Price < model.Price)
            && (model.Category == null || x.CategoryId == model.Category)
           )
        .OrderBy(model.Sort + " " + model.SortDir)
        .Skip((model.Page - 1) * model.PageSize)
        .Take(model.PageSize)                    
        .ToList();

    // total records for paging
    model.TotalRecords = db.Products
        .Count(x =>
            (model.ProductName == null || x.ProductName.Contains(model.ProductName))
            && (model.Price == null || x.Price < model.Price)
            && (model.Category == null || x.CategoryId == model.Category)
            );

    return View(model);
}

OrderBy will give error because I used here dynamic sorting, to make it working we need to install "System.Linq.Dynamic.Library", open package manage and run "Install-Package System.Linq.Dynamic.Library".

First part of the code is used to get products and next to get the total records count.

Let's create view for it

• Right click inside the index action
• Click Add View
• View Name: Index
• Template: Empty
• Model Class: ProductSearchModels (Advance.Learning.WebApp.Models)
• Data Context Class: blank
• Uncheck: Create as a partial view
• Check: Reference script library
• Check: Use a layout page
• provide the layout name: ~/Views/Shared/_Layout.cshtml
• Click Add

It will create a blank view with basic model and layout settings, update it with following code

@model Advance.Learning.WebApp.Models.ProductSearchModels
@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<h1>Search Product</h1>

@using (Html.BeginForm("index", "products", FormMethod.Get))
{
<div class="row">

    <div class="form-horizontal">
        <div class="col-lg-3">
            @Html.TextBoxFor(m => m.ProductName, new { @class = "form-control", @placeholder = "Product Name" })
        </div>
        <div class="col-lg-3">
            @Html.TextBoxFor(m => m.Price, new { @class = "form-control", @placeholder = "Price (max.)" })
        </div>
        <div class="col-lg-3">
            @Html.DropDownListFor(m => m.Category, new SelectList(ViewBag.Categories, "CategoryId", "CategoryName"), 
                   "All Categories", new { @class = "form-control", @placeholder = "Category" })

        </div>
        <div class="col-lg-3">
            <button type="submit" class="btn btn-success">
                <span class="glyphicon glyphicon-search"></span>
            </button>

            <a class="btn btn-success pull-right" data-modal="" href="/Products/Create" id="btnCreate">
                <span class="glyphicon glyphicon-plus"></span>
            </a>
        </div>
    </div>
</div>

<div style="margin-top:17px;">
@{
var grid = new WebGrid(
                    canPage: true,
                    rowsPerPage: Model.PageSize,
                    canSort: true,
                    ajaxUpdateContainerId: "grid");

grid.Bind(Model.Products, rowCount: Model.TotalRecords, autoSortAndPage: false);
grid.Pager(WebGridPagerModes.All);

@grid.GetHtml(htmlAttributes: new { id = "grid" },  
        fillEmptyRows: false,
        tableStyle: "table table-bordered table-hover",
        mode: WebGridPagerModes.All,
        columns: grid.Columns(
              grid.Column("ProductId", "ID"),
              grid.Column("ProductName", "ProductName", style: "col-lg-6"),
              grid.Column("Price", header: "Price", format: @<text>@String.Format("{0:C}", item.Price) </text>),
              grid.Column("Categories.CategoryName", "Category", style: "col-lg-2"),
              grid.Column(header: "Action", canSort: false, style: "action",
                format: @<text>
                @Html.Raw("<a data-modal='' href='/products/details/" + item.ProductId + "' id='" + item.ProductId + "' title='Detail'> <span class='glyphicon glyphicon-search'> </span> </a>")
                @Html.Raw("<a data-modal='' href='/products/edit/" + item.ProductId + "' id='" + item.ProductId + "' title='Edit'> <span class='glyphicon glyphicon-edit'> </span> </a>")
                @Html.Raw("<a data-modal='' href='/products/delete/" + item.ProductId + "' id='" + item.ProductId + "' title='Delete'> <span class='glyphicon glyphicon-trash'> </span> </a>")
                </text>)
        ));
    }
</div>
}

Here note @using (Html.BeginForm("index", "products", FormMethod.Get)), remove all the parameters in BeginForm and run the application and search result by providing some values and change page number or sort the record, we will loss all the search criteria, that's why I used FormMethod.Get so it will add the search criteria, paging and sorting into url as a query string and will work smooth.

grid.Column("Categories.CategoryName", "Category", style: "col-lg-2"),

Might be you noticed the binding Categories.CategoryName, it is not in our model show how we can bind it, correct, we created our table to link with category so we can access that table values directly, that's why this line works and bind the category name into our webgrid.

I cannot see anything new which I need to explain, let me know if anyone want to explain any part of the code which can help other to understand the code easily.

Other blogs you may like

mvc search page example with code

MVC Searh page with pagination: It’s very easy to create a search page in asp.net but when I try to create the same in MVC I faced many problems, how to create model, how to keep searched values in search controls, pagination, I found myself nowhere, so start searching for some good examples but... By Ali Adravi   On 25 Aug 2013  Viewed: 40,117

MVC insert update delete and select records

CRUD (Create, Retrieve, Update and Delete) in MVC. When we start to learn new language, first we try to run an application with “Hello World” and then CRUD functionality. So in this article we will see how to select records from database (with WebGrid, pagination and sort functionality), update a... By Ali Adravi   On 17 Aug 2013  Viewed: 105,947

How to create a single thanks page for entire controller in MVC

Sometimes we need a thanks page say we have user registration, change password, activate account functionality in our application then we need a thanks page after registering with our site, to say thanks for registering with us or showing confirmation that your password is successfully changed or... By Hamden   On 30 Jun 2013  Viewed: 3,738

MVC jquery autocomplete with value and text field

In MVC, autocomplete with jquery is the best way to pull data from database and render according to our requirements, so in this article we will use jquery to show the auto complete text box without any ajax controls. We need a method to get the data from database, a controller method to handle the... By Ali Adravi   On 29 Jun 2013  Viewed: 6,997

Upload files with model data in MVC

Upload multiple files with model data in MVC is really very easy, when I started to test by uploading some files, I though it would be more complicated but it is really not. In my previous post [ASP.Net MVC file upload][1], I promised to post soon about how to upload multiple files. When I was... By Ali Adravi   On 04 Jun 2013  Viewed: 25,493