Monday, 21 March 2011

MvcContrib grid paging and searching in Asp.NET MVC3


Introduction

This article shows you how to implement MvcContrib grid paging,filtering and preserving search url in Asp.NET MVC3.

MvcContrib grid provides nice paging interface with column ordering. In real world, we need to aggregate complex ViewModels to present grid view. We also should provide multiple searching filters and keywords to the grid view. So, I implemented clean PagedViewModel<T> class to make MvcContrib Grid paging and filtering simple in ASP.NET MVC3 based on this nice article.
 

Implementation Summary

I added some codes and classes to the original source to simplify implementation as follows:

  • PagedViewModel<T> containing ViewModel collection, sorting and paging information.
  • Preserving the query url between list page and view page.

This is result view page using MvcContrib Grid and MVC3.


00.Overview

Business Layer

Let's use the music store database of http://chinookdatabase.codeplex.com/ for our AlbumServie. (*I attached the mdf with basic connection string in web.config in our MVC project.)
Let's add MVCMusicStoreDB EF4 model including Album,Genre and Artist tables. We can see the basic entity diagram.


01.EntityDiagram
 
The AlbumViewModel class is the composite ViewModel class of Album,Genre and Artist entities. The MvcContrib Grid uses those Display* and ScaffoldColumn Attributes in the AutoGenerateColumns() function.

using System.ComponentModel;
using System.ComponentModel.DataAnnotations;

namespace MvcMusicStore.Models
{
    public class AlbumViewModel
    {
        [DisplayName("ID")]
        public int AlbumId { get; set; }

        [ScaffoldColumn(false)]
        public int? GenreId { get; set; }

        [DisplayName("Genre")]
        public string Genre { get; set; }

        [ScaffoldColumn(false)]
        public int? ArtistId { get; set; }

        [DisplayName("Artist")]
        public string Artist { get; set; }

        [ScaffoldColumn(false)]
        public string AlbumTitle { get; set; }

        [DisplayName("Price")]
        [DisplayFormat(DataFormatString = "{0:c}")] 
        public decimal AlbumPrice { get; set; }
    }
}

 

Let's build our main business service methods. First step is adding some basic reading methods in AlbumService. We don't implement CUD methods and any UoW or Repository layer for simple & quick implementation.

namespace MvcMusicStore.Models
{
    public class AlbumService
    {
        private MvcMusicStoreEntities _context;

        public AlbumService()
        {
            _context = new MvcMusicStoreEntities();
        }

        public IQueryable<AlbumViewModel> GetAlbumsView()
        {
            var query = from a in GetAlbums()
                        select new AlbumViewModel
                        {
                            AlbumId = a.AlbumId,
                            GenreId = a.GenreId,
                            Genre = a.Genre.Name,
                            ArtistId = a.ArtistId,
                            Artist = a.Artist.Name,
                            AlbumTitle = a.Title,
                            AlbumPrice = a.Price
                        };
            return query;
        }

        public AlbumViewModel FindAlbumView(int albumId)
        {
            return GetAlbumsView().Where(a => a.AlbumId == albumId).Single();
        }

        public IQueryable<Album> GetAlbums()
        {
            return _context.Albums;
        }

        public IQueryable<Genre> GetGenres()
        {
            return _context.Genres;
        }

        public IQueryable<Artist> GetArtists()
        {
            return _context.Artists;
        }

        public void Save()
        {
            _context.SaveChanges();
        }
    }
}        

 

 

PagedViewModel<T>

PagedViewModel<T> is generic container holding all data for grid presentation including search filters.

To support fluent filter setting function to the PagedViewModel<T>, We should implement AddFilter and Setup methods.

namespace MvcMusicStore.Models
{
    public class PagedViewModel<T>
    {
         ...

        public PagedViewModel<T> AddFilter(Expression<Func<T, bool>> predicate)
        {
            Query = Query.Where(predicate);
            return this;
        }

        public PagedViewModel<T> AddFilter<TValue>(string key, TValue value, Expression<Func<T, bool>> predicate)
        {
            ProcessQuery(value, predicate);
            ViewData[key] = value;
            return this;
        }

        public PagedViewModel<T> AddFilter<TValue>(string keyField, object value, Expression<Func<T, bool>> predicate,
            IQueryable<TValue> query, string textField)
        {
            ProcessQuery(value, predicate);
            var selectList = query.ToSelectList(keyField, textField, value);
            ViewData[keyField] = selectList;
            return this;
        }

        public PagedViewModel<T> Setup()
        {
            if (string.IsNullOrWhiteSpace(GridSortOptions.Column))
            {
                GridSortOptions.Column = DefaultSortColumn;
            }
            PagedList = Query.OrderBy(GridSortOptions.Column, GridSortOptions.Direction)
                .AsPagination(Page ?? 1, PageSize ?? 10);
            return this;
        }

        private void ProcessQuery<TValue>(TValue value, Expression<Func<T, bool>> predicate)
        {
            if (value == null) return;
            if (typeof(TValue) == typeof(string))
            {
                if (string.IsNullOrWhiteSpace(value as string)) return;
            }
            Query = Query.Where(predicate);
        }
    }
}

 

Listing action method in controller

Let's make a listing method of AlbumController with searching filters,ordering and paging in our AlbumController. We can add fluently query filters to the filter pipeline of PagedViewModel.

namespace MvcMusicStore.Controllers
{
    public class AlbumController : Controller
    {
        private AlbumService _service;
        public AlbumController()
        {
            _service = new AlbumService();
        }
        public ActionResult Index(string albumTitle, int? genreId, int? artistId, GridSortOptions gridSortOptions, int? page)
        {
            var pagedViewModel = new PagedViewModel<AlbumViewModel>
            {
                ViewData = ViewData,
                Query = _service.GetAlbumsView(),
                GridSortOptions = gridSortOptions,
                DefaultSortColumn = "AlbumId",
                Page = page,
            }
            .AddFilter("albumTitle", albumTitle, a => a.AlbumTitle.Contains(albumTitle))
            .AddFilter("genreId", genreId, a => a.GenreId == genreId, _service.GetGenres(), "Name")
            .AddFilter("artistId", artistId, a => a.ArtistId == artistId, _service.GetArtists(), "Name")
            .Setup();
            return View(pagedViewModel);
        }
        ...
    }
}
 

 

Listing Razor View page for MvcContrib Grid

Let's make List View Page. We can easily fill two dropdown lists without any additional code from PagedViewModel<T>. Also, We add link using Html.ActionQueryLink helper method to keep query string like "/Album/Details/420?albumTitle=Ro&genreId=1".

@using MvcMusicStore.Common
@using MvcMusicStore.Models;
@using MvcContrib.UI.Grid;
@model PagedViewModel<AlbumViewModel>

@{
    ViewBag.Title = "Album List";
}

<h2>Album List</h2>

@using (Html.BeginForm("Index", "Album", FormMethod.Get, new { id = "albumSearch" }))
{   
<label>
Title   @Html.TextBox("albumTitle")
  Genre @Html.DropDownList("genreId", "-- Select All --")
</label>
<label>
Artist @Html.DropDownList("artistId", "-- Select All --")
  <input class="button" value="Search" type="submit" />
</label>
}

@{Html.RenderPartial("Pager", Model.PagedList);}
@Html.Grid(Model.PagedList).AutoGenerateColumns().Columns(column =>
 column.For(x => Html.ActionQueryLink(x.AlbumTitle, "Details", new { id = x.AlbumId }))
 .Named("AlbumTitle").InsertAt(2);
}).Sort(Model.GridSortOptions).Attributes(@class => "grid-style")

 
Let's see SearchBox with title keyword, Genre&Album Dropdown list. The contrib Grid provides Colum filtering and paging UI.


03.GridView
 

We can back to List page with preserved query option from Details page. Let's add a snippet to save routing url to ViewBag by using added ToRouteDic extension method.

namespace MvcMusicStore.Controllers
{
    public class AlbumController : Controller
    {   
        ...
        public ActionResult Details(int id)
        {
            var viewModel = _service.FindAlbumView(id);
            ViewBag.RouteDicForList = Request.QueryString.ToRouteDic();
            return View(viewModel);
        }
    }
}

Let's restore the ViewBag data to restore the query url in Details View page.
@model MvcMusicStore.Models.AlbumViewModel
@{
    ViewBag.Title = "Details";
}
<h2>Album Details - @Model.AlbumTitle</h2>
<p>
    @Html.ActionLink("Back to List", "Index", 
    ViewBag.RouteDicForList as RouteValueDictionary)
</p>

04.DetailView

 

Conclusion

MvcContrib Grid is nice web grid component for MVC3 framework. We can use great paging,filtering,ordering grid functions easily.

EDIT: You can download the source code from here
 

Reference

12 comments:

  1. This is a great idea. Did you post the source code? or am just missing the link?

    ReplyDelete
  2. @Anonymous
    Thanks. I updated source code link.
    You can find this article and some discussion at codeproject article.
    http://www.codeproject.com/KB/aspnet/Grid_Paging_In_MVC3.aspx

    ReplyDelete
  3. Nice samples, I like it, simple and straight to the point!

    On the search samples I noticed a couple of things:

    1. GetAlbumsView always pulls all the data from the database and let the controller filter, this may not be a good idea if you have millions of records

    2. When the data if filtered, the combo boxes still show all the data, it should only show the data available after the first filter was apply

    I know this is a quick sample and you didn’t want to elaborate, but I thought to share a quick solution, I only apply the logic to the Genre and Artist, not for the free text filter

    PS. I sent you an email b/c I could not paste the code here :)

    Thanks,

    Sebastian

    ReplyDelete
  4. @Sebastian
    Thanks for your kind comment and codes.
    Here is my explanation of my approach.

    1. GetAlbuesView returns just IQueryable, so the real sql query will be fired on getting IPagedList of PagedViewModel. It means you can just get the first 10(pageSize) records from the real database. Defferrd execution of IQueryable is important point of my implementation.

    And, in the ProcessQuery(...) method of PagedViewModel class, I already made the skipping "Where predicate" logic if the requested value is null or empty string.

    2. I think it depends on user story. In my story, I describe the selecting album catalog from the whole sub category list. Of course, we can add easily extended filter to the GetGenres().Where(...) like that!

    Thanks again!

    ReplyDelete
  5. Hi Sangsu.. I m new in MVC World.
    This is nice post where u have added razor view but will u plz send me the aspx view to implement it.
    mrbishu.anand@gmail.com
    thnx Sangsu..

    ReplyDelete
  6. @Bsu
    I sent aspx based code to your email.
    Happy MVCing!

    ReplyDelete
  7. At first many 2 Thnks to you Sangsu. I am also wondering to create a login form that will check username and password from database(SQL Server) in aspx view. If i am not dsturbing u then plz help me.

    ReplyDelete
  8. @Bsu Base

    MVC template already provide asp.net membership provider login forms.

    Check
    - Controllers > AccountController
    - Views > Account > LogOn.cshtml (or .aspx) And real login logic can be checked

    You can check database conncetion string on web.config to change real SQL server because the default connection string is for attached mdf file. ... I recommend this msdn for membership link.

    Happy Mvcing!

    ReplyDelete
  9. I am new to MVC and this is exactly what I am looking for. Thank you and keep up the good work!

    ReplyDelete
  10. Nice article. You are passing filter and sorting informations via GET method. Is there anyway you can send it via POST, so they are not visible in url.

    ReplyDelete
  11. Hi sangsu,
    We have discussed before about MVC but this time i am in problem with MVC reporting. Will you please provide simple and easy understandable example of database reporting(Crystal report, Report viewer or any more). I have to generate a query based report from database.

    ReplyDelete