Using jQGrid w/ ASP.net MVC

PIN

Before we begin, you must have:

  • An MVC 2/3 project ready to go in Visual Studio 2008/2010
  • An existing DB with some data you can query into
  • Latest version of jQuery, jQGrid and jQuery UI scripts referenced

In this post, we will cover:

  • Creating a basic model
  • Creating a view-model for jQGrid
  • Getting data from a data context and return the Json data that will be mapped to the jQGrid columns

We will split this into three parts, as with any MVC application:

  1. The Model
  2. The View
  3. The Controller
Model

Our simple car table is setup as:

Now let us setup a corresponding model for the DB. We will include the table fields as properties, some standard data annotation validations and one custom validation routine as well.

Things to note:

  • Validation won’t really be used until we add some sort of Create or Edit function to our grid
  • Data annotations validations works when data is POST-ed back to the server
namespace com.utsavized.demos.jqgridmvc.Models
{
    public class CarModel
    {
        //Required Properties
        public int Id { get; set; }
        [StringLength(50),Required]
        public string Make { get; set; }
        [StringLength(50),Required]
        public string Model { get; set; }
        [Range(2001, 2012), Required]
        public int? Year { get; set; }

        //Optional Property
        public double? EngineSize { get; set; }
        public int? Cylinders { get; set; }
        public int? Valve { get; set; }
        [ValidateEngineType]
        public string EngineType { get; set; }
        public decimal? RetailPrice { get; set; }
    }

    [AttributeUsage(AttributeTargets.Property)]
    public sealed class ValidateEngineTypeAttribute : ValidationAttribute
    {
        private const string _defaultErrorMessage = "Engine can either be V, Straight/Inclined (I) or W.";
        public ValidateEngineTypeAttribute() : base(_defaultErrorMessage) { }

        public override bool IsValid(object value)
        {
            string _EngineType = (value as string).ToUpper();

            if (_EngineType != "V" && _EngineType != "I" && _EngineType != "W")
               return false;
            else
                return true;
        }

        public override string FormatErrorMessage(string name)
        {
            return String.Format(CultureInfo.CurrentUICulture, ErrorMessageString);
        }
    }
}

 

View

When using the jQGrid, your view is more than just a view, it sort of becomes the view-model as you will have to redefine your model so that they can properly map into your grid columns. For the sake of this example, we will define a simple jQGrid, some options and some custom formatting as well.

Things to note:

  • Always try to use strongly-typed views. That way you won’t have to completely rely on jQuery to get your model values.
  • You will have your MVC Html helpers handy to access your model data elsewhere on the page as well.

Your code for the view should look something like this:

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<com.utsavized.demos.jqgridmvc.Models.CarModel>" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">

    <link href="../../Content/Site.css" rel="stylesheet" type="text/css" />
    <link href="../../Content/ui.jqgrid.css" rel="stylesheet" type="text/css" />
    <link href="../../Content/ui.multiselect.css" rel="stylesheet" type="text/css" />
    <link href="../../Content/smoothness/jquery-ui-1.8.21.css" rel="stylesheet" type="text/css" />

    <script src="../../Scripts/jQuery/main/jquery-ui-1.8.21.js" type="text/javascript"></script>
    <script src="../../Scripts/jQuery/main/jquery-1.7.2.min.js" type="text/javascript"></script>
    <script src="../../Scripts/jQGrid/i18n/grid.locale-en.js" type="text/javascript"></script>
    <script src="../../Scripts/jQGrid/jquery.jqGrid.min.js" type="text/javascript"></script>

<script type="text/javascript">
    $(function() {
        $("#CarsGrid").jqGrid({
            url: '/Home/CarsGridData/',
            datatype: 'json',
            mtype: 'GET',
            jsonReader: { root: "rows", page: "page", total: "total", records: "records", repeatitems: false, id: 0, userdata: "userdata" },
            colNames: ['Id', 'Make', 'Model', 'Year', 'Engine Size', 'Cylinders', 'Valve', 'Engine Type', 'RetailPrice'],
            colModel: [
                    { name: 'Id', index: 'Id', editable: false, hidden: true, key: true },
                    { name: 'Make', index: 'Make', editable: true, hidden: false, sortable: false },
                    { name: 'Model', index: 'Model', editable: true, hidden: false, sortable: false },
                    { name: 'Year', index: 'Year', editable: true, hidden: false, sortable: false },
                    { name: 'EngineSize', index: 'EngineSize', editable: true, hidden: false, sortable: false },
                    { name: 'Cylinders', index: 'Cylinders', editable: true, hidden: false, sortable: false },
                    { name: 'Valve', index: 'Valve', editable: true, hidden: false, sortable: false },
                    { name: 'EngineType', index: 'EngineType', editable: true, hidden: false, sortable: false },
                    { name: 'RetailPrice', index: 'RetailPrice', editable: true, hidden: false, sortable: false },
                ],
            sortable: false,
            viewrecords: true,
            height: '240',
            width: '800',
            scroll: 1,
            hidegrid: false,
            rowNum: 50,
            pager: $('#CarsGridPager')
        });
    });
</script>
</head>
<body>
    <div>
        <table id="CarsGrid"></table>
        <div id="CarsGridPager"></div>
    </div>
</body>
</html>

Controller

Finally, the meat of it all goes into the Controller that sets up the grid and returns the data. Here is what your code should look like:

Things you can add:

  • Sorting function – A simple OrderBy(sidx + “ “ + sord) on the LINQ Query should do the trick.
namespace com.utsavized.demos.jqgridmvc.Controllers
{
    [HandleError]
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public JsonResult CarsGridData(string sidx, string sord, int? page, int? rows, string filters, string nd, int? npage)
        {
            var query = GetCars();
            int totalRecords = query.Count();
            int currentPage = page ?? 1;
            int pageIndex = currentPage - 1;
            int pageSize = rows ?? int.MaxValue;
            int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

            query = query.Skip(pageIndex * pageSize)
                    .Take(pageSize);
            try
            {
                var jsonData = new
                {
                    total = totalPages,
                    page = currentPage,
                    records = totalRecords,
                    rows = query.ToArray()
                };

                return Json(jsonData, JsonRequestBehavior.AllowGet);

            }

            catch (Exception ex)
            {
                throw ex;
            }

        }

        public IQueryable<CarModel> GetCars()
        {
            CarsDataContext db = new CarsDataContext();
            var cars = from c in db.Cars
                       select new CarModel
                       {
                           Id = c.Id,
                           Make = c.Make,
                           Model = c.Model,
                           Year = c.Year,
                           EngineSize = c.EngineSize,
                           Cylinders = c.Cylinders,
                           Valve = c.Valve,
                           EngineType = c.EngineType,
                           RetailPrice = c.RetailPrice
                       };

            return cars;

        }
    }
}

That’s it! This should not be ready to build and run. Here is how this all should come together as: