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.
Nice post. Although it would be cleaner to use using for SqlConnection.
ReplyDeleteI have one question.
Do I always have to customize JSON format for grid? Can't I just use JavaScriptSerializer or DataContractJsonSerializer for thus purpose?
Would be nice if there's an out of the box way to use this control with Asp.Net. Been trying to customize this for more than a day with my data...to find no success yet...
ReplyDeleteGodwin if you only want view/sort/search there is mvccrud.codeplex.com, at the moment no editing or decent documentation but does seem to be in active development.
ReplyDeletehow can i implement a "search" in this jqgrid and mvc scenario
ReplyDeletewould like to implement the SEARCH feature of the grid, how to?
ReplyDeleteHello,
ReplyDeleteI read your master detail article and it was enlightening. Could you advise how your demo can be changed if one of the columns were to become a drop down? Your assistance is greatly appreciated! Some code snippets would be great!
Yours sincerely,
hkwok201@yahoo.com 5-26-10
For asp.net use http handlers for processing your data.. mail me at abdulkaderjeelani@gmail.com for further details
ReplyDeleteI have successfully created an MVC application using yours as a model.
ReplyDeleteThe MVC app runs fine locally but returns an error when I try to run it on my production server.
Looks to be a javascript error - "Object expected" on the status line.
Do you have any suggestions of what I need to look at to fix this issue?
Wonderful post!
ReplyDeleteIt really helped me....
Great Post!!!
ReplyDeleteBut, it doesnt work with jqGrid 3.7, just woth the 3.6 or below.
Great function working fine!
ReplyDeleteThis is really very useful article! thanks for uploading it. One question here.... If I want to edit this data on the UI and save it back to my data table, then how to go for it? Can you pleae guide me?
ReplyDeleteThanks!
Great tutorial.
ReplyDeleteIs there a substitute for SQLite concerning the part "ROW_NUMBER() OVER (ORDER BY " I think this is OLAP. But can't find an answer on google...
Thanks in advance..
Grt Post
ReplyDeleteGood One
ReplyDeleteI was using a list of Dinamyc Expando Objects to fill the jqgrid using the Impromptu library to convert the dinamyc objects to normal objects but never works for me. And now converting the list of expando objects to data table and using your method "JsonForJqgrid" finally works for me.
ReplyDelete