Tuesday, June 30, 2009

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
.

Sunday, June 28, 2009

JQGrid using MVC, Json and Datatable.

Last couple of days i have been trying to make my sample JQGrid working ASP.NET MVC and DataTable. If you google it with this two terms MVC,JQGrid you will find lot of samples using Linq, but if you work with Databases like Oracle or any other databases which does not have a LINQ provider(atleast at the time of writing this article) your alternate choice is to go with DataSet/DataTable. So i thought of putting this example together to help others who are on the same boat like myself.

I have given a fully working sample of ASP.NET MVC with JQgrid using Datatable(See below for the download link).

I am not going to cover the basics of MVC in this article, for which you can refer to other blogs such as this one.

These are the features i have implemented in this sample,

  • Themes

  • Refresh Grid

  • Server side Paging

  • Sorting

  • JSON based


I will cover other features of JQGrid in my future articles.

Here are the steps to get started,

1. Download JQGrid from here

2. Create an MVC Application using the Visual Studio 2008 template( if you want a detailed explanation for creating an MVC application VS Template refer here).

3. Now move the downloaded JQGrid files into the <project>/scripts folders.

4. Usually with MVC application people tend to put all the themes under Content folder, if you do that here you will have to modify the js files for paging button's images.So i wouldn't recommend moving themes folder.

4. Open the Site.Master inside <project>/Shared/Site.Master and add links to the following files,
../../Scripts/themes/steel/grid.css
../../Scripts/themes/jqModal.css
../../Scripts/jquery.jqGrid.js
../../Scripts/js/jqModal.js
../../Scripts/js/jqDnR.js

5. If you don't like steel themes there 4 other themes( basic,coffee,green and sand) available inside themes folder.

6. Now you site.master will look similar to this.

<%@ Master Language="C#" Inherits="System.Web.Mvc.ViewMasterPage" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title><asp:ContentPlaceHolder ID="TitleContent" runat="server" /></title>
<link href="../../Content/Site.css" rel="stylesheet" type="text/css" />
<script src="/Scripts/jquery-1.3.2.js" type="text/javascript"></script>
<script src="../../Scripts/jquery-1.3.2.min.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="../../Scripts/themes/steel/grid.css" title="steel"
media="screen" />
<link href="../../Scripts/themes/jqModal.css" rel="stylesheet" type="text/css" />
<script src="../../Scripts/jquery.jqGrid.js" type="text/javascript"></script>
<script src="../../Scripts/js/jqModal.js" type="text/javascript"></script>
<script src="../../Scripts/js/jqDnR.js" type="text/javascript"></script>

<asp:ContentPlaceHolder ID="HeadContent" runat="server" />
</head>
<body>
<div class="page">
<div id="header">
<div id="title">
<h1>Sample from arahuman.blogspot.com</h1>
</div>
<div id="logindisplay">
<% Html.RenderPartial("LogOnUserControl"); %>
</div>
<div id="menucontainer">
<ul id="menu">
<li><%= Html.ActionLink("Home", "Index", "Home")%></li>
<li><%= Html.ActionLink("About", "About", "Home")%></li>
</ul>
</div>
</div>
<div id="main">
<asp:ContentPlaceHolder ID="MainContent" runat="server" />
<div id="footer">
</div>
</div>
</div>
</body>
</html>


7. Create a folder named Helper under the <project>/Helper folder and add the following Helper method to convert a Datatable into the JSON format.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using Newtonsoft.Json;
using System.Text;
using System.IO;

namespace JQGridMVCDemo.Helper {
public class JsonHelper {
public static string JsonForJqgrid(DataTable dt, int pageSize, int totalRecords,int page) {
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
StringBuilder jsonBuilder = new StringBuilder();
jsonBuilder.Append("{");
jsonBuilder.Append("\"total\":" + totalPages + ",\"page\":" + page + ",\"records\":" + (totalRecords) + ",\"rows\"");
jsonBuilder.Append(":[");
for (int i = 0; i < dt.Rows.Count; i++) {
jsonBuilder.Append("{\"i\":"+ (i) +",\"cell\":[");
for (int j = 0; j < dt.Columns.Count; j++) {
jsonBuilder.Append("\"");
jsonBuilder.Append(dt.Rows[i][j].ToString());
jsonBuilder.Append("\",");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
jsonBuilder.Append("}");
return jsonBuilder.ToString();
}
}
}


8. Now open the index page under <project>/SViews/Home/Index.aspx and add the following code,

<%@ 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: 'Northwind Customer Information'
}).navGrid(pager, { edit: false, add: false, del: false, refresh: true, 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>
</asp:Content>


the id (#list) links the html table with the jquery to inject the grid ui's code at runtime.
it makes an ajax calls using the url(/Home/GetGridData/) provided.
datatype: json refers to the output from the above call returns the JSON type results.

9. Now open the home controller page to add the GetGridDataMethod under <project>/Controller/HomeController.cs. Add the following code to it.


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 GetGridData(string sidx, string sord, int page, int rows) {
return Content(JsonHelper.JsonForJqgrid(GetDataTable(sidx,sord,page,rows), rows, GetTotalCount(), page), "application/json");
}

public DataTable GetDataTable(string sidx, string sord, int page, int pageSize) {
int startIndex = (page-1) * pageSize;
int endIndex = page * pageSize;
string sql = @"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 + @";";

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 = @"SELECT COUNT(*) FROM Customers";
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;
}
}
}



I have declared four paramters here which will be passed by the JQuery. To help us understand better i have named it same like the JGrid where sidx refers to Sort Index name, sord refers to Sort Direction, page refers to page being invoked and rows refers to rows per page.

That's it. You can download the fully functional source code here. Enjoy and leave me a comment if you like it.