24 July 2012

MVC jqGrid advanced scenario

The MvcJqGrid is an Html Helper that eases greatly the implementation of the jqGrid in MVC 3 with the Razor view engine or MVC WebForms.
In my previous article (see here) I have explained a simple scenario to use the MvcJqGrid.

In a more advanced scenario, the requested type preferred should be POST, since the default is GET. The SetRequestType method allows the defintion of the request type.
The previous example uses an array that must return the values in the same order as the column definitions. A more generic solution is to return a json object with named properties. The MvcJqGrid supports this scenario by the SetJsonReader method.
The SetJsonReader method is used to configure the grid jsonReader so that the json data doesn't have to match the column model (ColModel) order of jqGrid.
Another feature that is advanced, is the capability to search in each desired column, using different search types.
The SetSearchType sets the seach type for the column. The seach type can be:
  • Input (Default)
  • Select. The SetSearchTerms method receives the collection of strings that define the select options.
  • Datepicker. The SetSearchDateFormat method allow the definition of the date format.
To enable the toolbar searching SetSearchToolbar must be set to true.
The SetSearchOnEnter is used to define when the search is executed:
  • true : the search is executed when the user presses enter
  • false: the search is executed after the user stops typing

The SetRowList creates a dropdownlist in the pager of the grid with the specified number of rows per page.

Now we can create an example using the customer class.
The view:
@(Html.Grid("customerGrid") .SetRequestType(RequestType.Post) .SetJsonReader(new MvcJqGrid.DataReaders.JsonReader { Id="id", RepeatItems = false}) .SetCaption("Customers") .AddColumn(new Column("FirstName").SetWidth(100).SetLabel("First Name")) .AddColumn(new Column("LastName").SetWidth(100).SetLabel("Last Name")) .AddColumn(new Column("CountryName").SetLabel("Country") .SetSearchType(Searchtype.Select) .SetSearchTerms((string[])ViewBag.Countries)) .AddColumn(new Column("Phone").SetWidth(100)) .AddColumn(new Column("BirthDate").SetWidth(80).SetSearchType(Searchtype.Datepicker) .SetSearchDateFormat("yy-mm-dd")) .AddColumn(new Column(" ").SetSearch(false).SetCustomFormatter("buttonize") .SetWidth(25) .SetAlign(Align.Right)) .SetUrl(Url.Action("Search", "Customer")) .SetAutoWidth(true) .SetRowNum(10) .SetRowList(new[] { 10, 15, 20, 50 }) .SetViewRecords(true) .SetPager("pager") .SetSearchToolbar(true).SetSearchOnEnter(false) )

The SetCustomFormatter was explained previously and it allows to format the content of the column using a javascript function.
The controller that returns json with named properties:
public class CustomerController : Controller
{
    public ActionResult Index()
    {
        var countries = new CountryRepository().Search();
        ViewBag.Countries = (from c in countries select c.Name).ToArray();
        return View();
    }

    public JsonResult Search(GridSettings gridSettings)
    {
        List<CustomerSearchResult> customers = null;
        int totalRecords;
        CustomerRepository customerRepository = new CustomerRepository();
            
        CustomerSeachFilter filter = new CustomerSeachFilter();
        if (gridSettings.IsSearch)
        {
            filter.FirstName = gridSettings.Where.rules.Any(r => r.field == "FirstName") ? 
                    gridSettings.Where.rules.FirstOrDefault(r => r.field == "FirstName").data : string.Empty;
            filter.LastName = gridSettings.Where.rules.Any(r => r.field == "LastName") ? 
                    gridSettings.Where.rules.FirstOrDefault(r => r.field == "LastName").data : string.Empty;
            filter.CountryName = gridSettings.Where.rules.Any(r => r.field == "CountryName") ? 
                    gridSettings.Where.rules.FirstOrDefault(r => r.field == "CountryName").data : string.Empty;
            filter.Phone = gridSettings.Where.rules.Any(r => r.field == "Phone") ? 
                    gridSettings.Where.rules.FirstOrDefault(r => r.field == "Phone").data : string.Empty;
            filter.BirthDate = gridSettings.Where.rules.Any(r => r.field == "BirthDate") ? 
            filter.BirthDate = gridSettings.Where.rules.Any(r => r.field == "BirthDate") ? 
                    DateTime.ParseExact(gridSettings.Where.rules.FirstOrDefault(r => r.field == "BirthDate").data, 
                                        "yyyy-MM-dd", null) : DateTime.MinValue;
        }

        customers = customerRepository.Search(filter, 
                                              gridSettings.SortColumn, 
                                              gridSettings.SortOrder, 
                                              gridSettings.PageSize, 
                                              gridSettings.PageIndex, 
                                              out totalRecords);

        var jsonData = new
        {
            total = totalRecords / gridSettings.PageSize + 1,
            page = gridSettings.PageIndex,
            records = totalRecords,
            rows = (
                from c in customers
                select new
                {
                    id = c.CustomerID,
                    FirstName = c.FirstName,
                    LastName = c.LastName,
                    BirthDate = c.BirthDate.ToString("yyyy-MM-dd"),
                    CountryName = c.CountryName,
                    EmailAddress = c.EmailAddress,
                    Phone = c.Phone,
                })
        };
        return Json(jsonData);
    }
}
The MvcJqGrid search feature also allows the configuration of the seach:
  • SetSortName - Defines the default seach field for the grid. 
  • SetSortOrder - Defines the default sort order, using the SortOrder enumerator for the grid. 
  • SetDefaultSearchValue - Sets the default search value for a specific column.

3 comments:

Anonymous said...

Hi,

Thanks. Nice post. Helped me a lot. However I am struggling in implementing paging. I am herewith posting my code. See if you can help me. Thanks in advance.

Paging Interface

public interface IPagination : IList
{
int PageIndex { get; }
int PageSize { get; }
int TotalCount { get; }
int TotalPages { get; }
bool HasPreviousPage { get; }
bool HasNextPage { get; }
}

Paging Class

public Pagination(IQueryable source, int pageIndex, int pageSize)
{
int totalRecords = source.Count();
this.TotalCount = totalRecords;
this.PageIndex = pageIndex;
this.PageSize = pageSize;
this.TotalPages = totalRecords / pageSize;
if (totalRecords % pageSize > 0)
TotalPages++;
this.AddRange(source.Skip(PageIndex * pageSize).Take(pageSize).ToList());
}

public Pagination(List source, int pageIndex, int pageSize)
{
int totalRecords = source.Count();
this.TotalCount = totalRecords;
this.PageIndex = pageIndex;
this.PageSize = pageSize;
this.TotalPages = totalRecords / pageSize;
if (totalRecords % pageSize > 0)
TotalPages++;
this.AddRange(source.Skip(PageIndex * pageSize).Take(pageSize).ToList());
}

public int PageIndex { get; private set; }
public int PageSize { get; private set; }
public int TotalCount { get; private set; }
public int TotalPages { get; private set; }

public bool HasPreviousPage
{
get { return (PageIndex > 0); }
}

public bool HasNextPage
{
get { return (PageIndex + 1 < TotalPages); }
}

Generic Repository Interface

public interface IRepository where T : class
{
void Add(T entity);
void Delete(T entity);
void Update(T entity);
IQueryable GetAll();
T FindBy(Expression expression);
IQueryable FilterBy(Expression expression);
IPagination GetPaged(Expression expression,int pageIndex, int pageSize);
}

Repository Base

public abstract class Repository : IRepository where T : class
{
private STNDataContext _stnDataContext;
private readonly IDbSet _dbSet;

protected Repository(IDatabaseFactory databaseFactory)
{
DatabaseFactory = databaseFactory;
_dbSet = StnDataContext.Set();
}

protected IDatabaseFactory DatabaseFactory { get; private set; }
public STNDataContext StnDataContext
{
get { return _stnDataContext ?? (_stnDataContext = new STNDataContext()); }
}
public void Add(T entity)
{
_dbSet.Add(entity);
//_stnDataContext.Commit();
}

public void Delete(T entity)
{
_dbSet.Remove(entity);
}

public void Update(T entity)
{
_dbSet.Attach(entity);
_stnDataContext.Entry(entity).State = EntityState.Modified;
//_stnDataContext.Commit();
}

public IQueryable GetAll()
{
return _dbSet.ToList().AsQueryable();
}

public T FindBy(Expression expression)
{
return FilterBy(expression).FirstOrDefault();
}

public IQueryable FilterBy(Expression expression)
{
return GetAll().Where(expression).AsQueryable();
}

public IPagination GetPaged(Expression expression,int pageIndex, int pageSize)
{
//return FilterBy(expression).AsQueryable().Skip(pageIndex*pageSize).Take(pageSize).ToList();
throw new NotImplementedException();
}
}

Can you help me figure out how my GetPaged() method should be? Please comment with your suggestions.

Thanks

Rui said...

Your problem is related to LINQ implementation.
I suggest that you read how to implement the Repository pattern using LINQ.
There are a lot of articles in the Internet about that subject that can help you.

Anonymous said...

Hi, thanks for the simple yet nice sample.

I implemented it (adapting to my columns and controller actions) and got the search bar to appear on top. One of the columns uses a dropdown list where I am supplying an arbitrary array of strings.

When I run the application and try to select a value from the dropdown, it does invoke the action method, but the grid.IsSearch property is always false. After much frustration I tried another column which has a simple textbox for search, and as soon as I enter some text and hit Enter, the grid.IsSearch property is set to true.

So the dropdown search does not work, but the textbox search works. What gives?