Master Detail JQGrid using MVC and JSON

In my previous article i have covered an example of using JQGrid using MVC and Jason( You can find my previous article here. Based on the response i received for the previous article, i thought of putting together another example on Master Detail JQGrid using MVC and JSON.



Here i will talking mostly the difference between my previous article and current one. From the first grid i am capturing the OnSelectRow event whenever a row is clicked. The id from the selected row gets passed as an Query Parameter, which then gets used to pull all the detail rows. You can also dynamically set the caption for the detail grid from your OnSelectRow event as shown below.

<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>
<asp:Content ID="indexTitle" ContentPlaceHolderID="TitleContent" runat="server">
    Home Page
</asp:Content>
<asp:Content ID="indexContent" ContentPlaceHolderID="HeadContent" runat="server">
    <script type="text/javascript">
        jQuery(document).ready(function() {
            jQuery("#list").jqGrid({
            url: '/Home/GetGridData/',
                datatype: 'json',
                mtype: 'GET',
                colNames: ['Customer ID', 'Contact Name', 'Address', 'City', 'Postal Code'],
                colModel: [
                  { name: 'CustomerID', index: 'CustomerID', width: 100, align: 'left' },
                  { name: 'ContactName', index: 'ContactName', width: 150, align: 'left' },
                  { name: 'Address', index: 'Address', width: 300, align: 'left' },
                  { name: 'City', index: 'City', width: 150, align: 'left' },
                  { name: 'PostalCode', index: 'PostalCode', width: 100, align: 'left' }
                ],
                pager: jQuery('#pager'),
                rowNum: 10,
                rowList: [5, 10, 20, 50],
                sortname: 'CustomerID',
                sortorder: "asc",
                viewrecords: true,
                imgpath: '/scripts/themes/steel/images',
                caption: '<b>Northwind Customer Information</b>',
                onSelectRow: function(ids) {
                    if (ids != null) {
                        var data = $("#list").getRowData(ids);
                        jQuery("#OrderList").setGridParam({ url: "/Home/GetDetailGridData/" + data.CustomerID, page: 1 })
               .setCaption("<b>Order Details for : " + data.ContactName+"</b>")
               .trigger('reloadGrid');
                    }
                }
            }).navGrid(pager, { edit: false, add: false, del: false, refresh: true, search: false });
            jQuery("#OrderList").jqGrid
            ({
                height: 100,
                datatype: "json",
                colNames: ['Order Date', 'Shipped Date', 'Freight', 'Ship Name', 'Ship Address', 'Ship City', 'Ship PostalCode'],
                colModel:
                [
                  { name: 'OrderDate', index: 'OrderDate', width: 100, align: 'left' },
                  { name: 'ShippedDate', index: 'ShippedDate', width: 100, align: 'left' },
                  { name: 'Freight', index: 'Freight', width: 100, align: 'left' },
                  { name: 'ShipName', index: 'ShipName', width: 150, align: 'left' },
                  { name: 'ShipAddress', index: 'ShipAddress', width: 150, align: 'left' },
                  { name: 'ShipCity', index: 'ShipCity', width: 100, align: 'left' },
                  { name: 'ShipPostalCode', index: 'ShipPostalCode', width: 100, align: 'left' }
                ],
                rowNum: 5,
                rowList: [5, 10, 20],
                imgpath: '/scripts/themes/steel/images',
                pager: jQuery('#OrderPager'),
                sortname: 'OrderDate',
                viewrecords: true,
                sortorder: "desc"
            }).navGrid('#OrderPager', { add: false, edit: false, del: false, search: false });
        });
    </script>

</asp:Content>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
    <h2>
        Customers List</h2>
    <table id="list" class="scroll" cellpadding="0" cellspacing="0" width="100%">
    </table>
    <div id="pager" class="scroll" style="text-align: center;">
    </div>
    <h2>Orders</h2>
    <table id="OrderList" class="scroll" cellpadding="0" cellspacing="0">
    </table>
    <div id="OrderPager" class="scroll" style="text-align: center;">
    </div>
</asp:Content>

trigger function will reload the detail grid.

I have written a new controller method for getting the data for the detail grid. The URL to this method gets set on the same onSelectRow event as shown above.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using JQGridMVCDemo.Helper;

namespace MvcApplication1.Controllers {
    [HandleError]
    public class HomeController : Controller {
        public ActionResult Index() {
            ViewData["Message"] = "Welcome to ASP.NET MVC!";
            return View();
        }

        public ActionResult About() {
            return View();
        }
        public ActionResult GetDetailGridData(string id,string sidx, string sord, int page, int rows) {
            int startIndex = ((page - 1) * rows) + 1;
            int endIndex = page * rows;
            string tcQuery = @"SELECT COUNT(*) FROM Orders WHERE CustomerID='"+id+"'";
            string dtQuery = @"WITH PAGED_ORDERS  AS
                                (
                                    SELECT OrderDate, ShippedDate, Freight, ShipName, ShipAddress, 
                                        ShipCity, ShipPostalCode,ROW_NUMBER() 
                                 OVER (ORDER BY " + sidx + @" " + sord + @") AS RowNumber
                                    FROM ORDERS
                                 WHERE CustomerID='" + id + @"'
                                )
                                SELECT CONVERT(VARCHAR(10), OrderDate, 101) OrderDate, 
                                 CONVERT(VARCHAR(10), ShippedDate, 101) ShippedDate, 
                                 Freight, ShipName, ShipAddress, ShipCity, ShipPostalCode
                                FROM PAGED_ORDERS
                                WHERE RowNumber BETWEEN " + startIndex + @" AND " + endIndex + @";";
            return Content(JsonHelper.JsonForJqgrid(GetDataTable(dtQuery), rows, GetTotalCount(tcQuery), page), "application/json");
        }
        public ActionResult GetGridData(string sidx, string sord, int page, int rows) {
            int startIndex = ((page - 1) * rows) + 1;
            int endIndex = page * rows;
            string tcQuery = @"SELECT COUNT(*) FROM Customers";
            string dtQuery = @"WITH PAGED_CUSTOMERS  AS
                        (
                         SELECT  CustomerID, ContactName, Address, City, PostalCode,
                           ROW_NUMBER() OVER (ORDER BY " + sidx + @" " + sord + @") AS RowNumber
                         FROM CUSTOMERS
                        )
                        SELECT CustomerID, ContactName, Address, City, PostalCode
                        FROM PAGED_CUSTOMERS
                        WHERE RowNumber BETWEEN " + startIndex + @" AND " + endIndex + @";";

            return Content(JsonHelper.JsonForJqgrid(GetDataTable(dtQuery), rows, GetTotalCount(tcQuery), page), "application/json");
        }

        public DataTable GetDataTable(string sql) {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["mainConnection"].ConnectionString);
            SqlDataAdapter adap = new SqlDataAdapter(sql,conn);
            var rows=adap.Fill(dt);
            return dt;
        }

        public int GetTotalCount(string sql) {
            SqlConnection conn=null;
            try {
                 conn= new SqlConnection(ConfigurationManager.ConnectionStrings["mainConnection"].ConnectionString);
                SqlCommand comm = new SqlCommand(sql, conn);
                conn.Open();
                return (int)comm.ExecuteScalar();
            } catch {
            } finally {
                try {
                    if (ConnectionState.Closed != conn.State) {
                        conn.Close();
                    }
                }catch {
                }
            }
            return -1;
        }
    }
}

You can download the sample code
.