Using JQGrid ASP .Net MVC

After i read Phil Haack about Using jQuery Grid With ASP.NET MVC, i have idea to create my own library to create JQGrid module. Then I search reference to build it. then I’ve got library  from trirand . I create this library Base on Trirand JQGrid for MVC Library structure.

I fix some issue in trirand library, among others:

• can filter Null and Not Null data, which is in trirand library is not support
• Optimize data access. using Entity framework and Dynamic Query to access data.
• Add Multiple Row Footer.
• And More…

In this articles i want to share how to make JQGrid ASP .Net MVC.

First… Create New MVC Project

After Create New MVC Project, you should add Aiska Library reference to your project, the library can be download here

Second, add Entity model for Grid.in this case i’m using Northwind database.

After you create entity model for grid lets create the model for grid,

using System;
using System.Collections.Generic;
using Aiska.Web.Jquery;
using System.Web.UI.WebControls;
    public class OrderGridModel
    {
        public Grid OrderGrid { get; set; }
        public Grid OrderDetailGrid { get; set; }

        public OrderGridModel()
        {
            OrderGrid = new Grid()
            {
                Height = Unit.Percentage(100),
                ID = "Orders",
                Columns = new List<GridColumn>()
                {
                    new GridColumn()
                    {
                        DataField = "OrderId",
                        PrimaryKey = true,
                    },
                    new GridColumn()
                    {
                        DataField = "RequiredDate",
                    },
                    new GridColumn()
                    {
                        DataField = "ShippedDate",
                    },
                    new GridColumn()
                    {
                        DataField = "CompanyName",
                    },
                    new GridColumn()
                    {
                        DataField = "ShipName",
                        HeaderText = "Ship To"
                    }
                }
            };

            OrderDetailGrid = new Grid()
            {
                ID = "Orders",
                Height = Unit.Percentage(100),
                Columns = new List<GridColumn>()
                {
                    new GridColumn()
                    {
                        DataField = "Product",
                    },
                    new GridColumn()
                    {
                        DataField = "Price",
                        TextAlign = Aiska.Web.Jquery.TextAlign.Right,
                        DataFormatString = "{0:#,##0.00;;-}"
                    },
                    new GridColumn()
                    {
                        DataField = "Quantity",
                        TextAlign = Aiska.Web.Jquery.TextAlign.Right
                    },
                    new GridColumn()
                    {
                        DataField = "Discount",
                        TextAlign = Aiska.Web.Jquery.TextAlign.Right,
                        DataFormatString = "{0:#,##0.00;;-}"
                    },
                    new GridColumn()
                    {
                        DataField = "Total",
                        TextAlign = Aiska.Web.Jquery.TextAlign.Right,
                        DataFormatString = "{0:#,##0.00;;-}"
                    }
                }
            };

            OrderGrid.ClientSideEvents.SubGridRowExpanded = "ShowSubGrid";
            OrderGrid.HierarchySettings.HierarchyMode = HierarchyMode.Parent;
            OrderGrid.ToolBarSettings.ShowRefreshButton = true;

            OrderDetailGrid.HierarchySettings.HierarchyMode = HierarchyMode.Child;
            OrderDetailGrid.ToolBarSettings.ShowRefreshButton = true;
            OrderDetailGrid.AppearanceSettings.ShowFooter = true;
            OrderDetailGrid.AppearanceSettings.FooterRow = 3;

Third, Create Controller for Order Grid

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Aiska.Web.Jquery;

    public class OrderController : Controller
    {
        //
        // GET: /Order/

        public ActionResult Index()
        {
            OrderGridModel model = new OrderGridModel();
            model.OrderGrid.DataUrl = Url.Action("GetData");
            model.OrderDetailGrid.DataUrl = Url.Action("GetDetailData");
            return View(model);
        }

        public JsonResult GetData()
        {
            OrderGridModel model = new OrderGridModel();
            var context = new NORTHWNDEntities();
            var data = from o in context.Orders
                       join c in context.Customers
                       on o.CustomerID equals c.CustomerID
                       select new {o.OrderID, o.RequiredDate, o.ShippedDate, c.CompanyName, o.ShipName};
            return model.OrderGrid.DataBind(data);
        }

        public JsonResult GetDetailData(int parentRowId)
        {
            OrderGridModel model = new OrderGridModel();
            model.OrderDetailGrid.DataResolved += new GridDataResolvedEventHandler(GrandTotal);
            var context = new NORTHWNDEntities();
            var data = from o in context.Order_Details
                       join p in context.Products
                       on o.ProductID equals p.ProductID
                       where o.OrderID == parentRowId
                       select new OrderDetailModel ()
                       {
                           Product = p.ProductName,
                           Price = (double)o.UnitPrice,
                           Quantity = o.Quantity,
                           Discount = (double)o.Discount * (double)o.UnitPrice,
                           Total = (double)o.Quantity * (double)o.UnitPrice
                        };
            return model.OrderDetailGrid.DataBind(data);
        }
        void GrandTotal(object sender, GridDataResolvedEventArgs e)
        {
            GridColumn Price = e.GridModel.Columns.Find(c => c.DataField == "Price");
            GridColumn SubTotal = e.GridModel.Columns.Find(c => c.DataField == "Total");
            var context = new NORTHWNDEntities();
            IQueryable<OrderDetailModel> data = (IQueryable<OrderDetailModel>)e.CurrentData;

            double subtotal = data.Sum(c => c.Total);
            double discount = data.Sum(c => c.Discount);

            Price.FooterValue[0] = "Total: ";
            Price.FooterValue[1] = "Discount: ";
            Price.FooterValue[2] = "Grand Total: ";
            SubTotal.FooterValue[0] = subtotal != 0 ? string.Format("{0:#,###.00;;-}", subtotal) : "-";
            SubTotal.FooterValue[1] = discount != 0 ? string.Format("{0:#,###.00;;-}", discount) : "-";
            SubTotal.FooterValue[2] = string.Format("{0:#,###.00;;-}", (subtotal - discount));
        }
    }

Then Create View for Grid

@using Aiska.Web.Mvc
@using Aiska.Web.Jquery
@model MvcMultiRowFooterJQGrid.Models.OrderGridModel

@{
    ViewBag.Title = "Multiple Footer Row";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

@Html.Aiska().Grid(Model.OrderGrid)
@Html.Aiska().Grid(Model.OrderDetailGrid)
<script type="text/javascript">
    function ShowSubGrid(subgrid_id, row_id) {
        showSubGrid_Orders(subgrid_id, row_id);
    }
</script>

If Success and no error the display should be like this

The Full sample you can download here

please Don’t hesitate to ask in comment or send me mail at aiska_hendra@yahoo.com

I’m sorry for my bad English.

Thank you…

Best Regard,
Aiska Hendra

Senior Developer
Enerren Technologies