소프트웨어/C# & ASP.NET

JQGrid using MVC, Json and Datatable.

falconer 2010. 2. 10. 15:10

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.

view plaincopy to clipboardprint?
  1. <%@ Master Language="C#" Inherits="System.Web.Mvc.ViewMasterPage" %>  
  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">  
  3. <html xmlns="http://www.w3.org/1999/xhtml">  
  4. <head runat="server">  
  5.     <title><asp:ContentPlaceHolder ID="TitleContent" runat="server" /></title>  
  6.     <link href="../../Content/Site.css" rel="stylesheet" type="text/css" />  
  7.     <script src="/Scripts/jquery-1.3.2.js" type="text/javascript"></script>      
  8.     <script src="../../Scripts/jquery-1.3.2.min.js" type="text/javascript"></script>  
  9.     <link rel="stylesheet" type="text/css" href="../../Scripts/themes/steel/grid.css" title="steel"  
  10.         media="screen" />  
  11.     <link href="../../Scripts/themes/jqModal.css" rel="stylesheet" type="text/css" />  
  12.     <script src="../../Scripts/jquery.jqGrid.js" type="text/javascript"></script>  
  13.     <script src="../../Scripts/js/jqModal.js" type="text/javascript"></script>  
  14.     <script src="../../Scripts/js/jqDnR.js" type="text/javascript"></script>  
  15.       
  16.     <asp:ContentPlaceHolder ID="HeadContent" runat="server" />      
  17. </head>  
  18. <body>  
  19.     <div class="page">  
  20.         <div id="header">  
  21.             <div id="title">  
  22.                 <h1>Sample from arahuman.blogspot.com</h1>  
  23.             </div>  
  24.             <div id="logindisplay">  
  25.                 <% Html.RenderPartial("LogOnUserControl"); %>  
  26.             </div>   
  27.             <div id="menucontainer">  
  28.                 <ul id="menu">                
  29.                     <li><%= Html.ActionLink("Home", "Index", "Home")%></li>  
  30.                     <li><%= Html.ActionLink("About", "About", "Home")%></li>  
  31.                 </ul>  
  32.             </div>  
  33.         </div>  
  34.         <div id="main">  
  35.             <asp:ContentPlaceHolder ID="MainContent" runat="server" />  
  36.             <div id="footer">  
  37.             </div>  
  38.         </div>  
  39.     </div>  
  40. </body>  
  41. </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.

view plaincopy to clipboardprint?
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Data;  
  6. using Newtonsoft.Json;  
  7. using System.Text;  
  8. using System.IO;  
  9.   
  10. namespace JQGridMVCDemo.Helper {  
  11.     public class JsonHelper {  
  12.         public static string JsonForJqgrid(DataTable dt, int pageSize, int totalRecords,int page) {  
  13.             int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);  
  14.             StringBuilder jsonBuilder = new StringBuilder();  
  15.             jsonBuilder.Append("{");  
  16.             jsonBuilder.Append("\"total\":" + totalPages + ",\"page\":" + page + ",\"records\":" + (totalRecords) + ",\"rows\"");  
  17.             jsonBuilder.Append(":[");  
  18.             for (int i = 0; i < dt.Rows.Count; i++) {  
  19.                 jsonBuilder.Append("{\"i\":"+ (i) +",\"cell\":[");  
  20.                 for (int j = 0; j < dt.Columns.Count; j++) {  
  21.                     jsonBuilder.Append("\"");  
  22.                     jsonBuilder.Append(dt.Rows[i][j].ToString());  
  23.                     jsonBuilder.Append("\",");  
  24.                 }  
  25.                 jsonBuilder.Remove(jsonBuilder.Length - 1, 1);  
  26.                 jsonBuilder.Append("]},");  
  27.             }  
  28.             jsonBuilder.Remove(jsonBuilder.Length - 1, 1);  
  29.             jsonBuilder.Append("]");  
  30.             jsonBuilder.Append("}");  
  31.             return jsonBuilder.ToString();  
  32.         }  
  33.     }  
  34. }  



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

view plaincopy to clipboardprint?
  1. <%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>  
  2. <asp:Content ID="indexTitle" ContentPlaceHolderID="TitleContent" runat="server">  
  3.     Home Page  
  4. </asp:Content>  
  5. <asp:Content ID="indexContent" ContentPlaceHolderID="HeadContent" runat="server">  
  6.     <script type="text/javascript">  
  7.         jQuery(document).ready(function() {  
  8.             jQuery("#list").jqGrid({  
  9.             url: '/Home/GetGridData/',  
  10.                 datatype: 'json',  
  11.                 mtype: 'GET',  
  12.                 colNames: ['Customer ID', 'Contact Name', 'Address', 'City', 'Postal Code'],  
  13.                 colModel: [  
  14.                   { name: 'CustomerID', index: 'CustomerID', width: 100, align: 'left' },  
  15.                   { name: 'ContactName', index: 'ContactName', width: 150, align: 'left' },  
  16.                   { name: 'Address', index: 'Address', width: 300, align: 'left' },  
  17.                   { name: 'City', index: 'City', width: 150, align: 'left' },  
  18.                   { name: 'PostalCode', index: 'PostalCode', width: 100, align: 'left' }  
  19.                 ],  
  20.                 pager: jQuery('#pager'),  
  21.                 rowNum: 10,  
  22.                 rowList: [5, 10, 20, 50],  
  23.                 sortname: 'CustomerID',  
  24.                 sortorder: "asc",  
  25.                 viewrecords: true,  
  26.                 imgpath: '/scripts/themes/steel/images',  
  27.                 caption: 'Northwind Customer Information'  
  28.             }).navGrid(pager, { edit: false, add: false, del: false, refresh: true, search: false });  
  29.         });  
  30.     </script>  
  31.   
  32. </asp:Content>  
  33. <asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">  
  34.     <h2>  
  35.         Customers List</h2>  
  36.     <table id="list" class="scroll" cellpadding="0" cellspacing="0" width="100%">  
  37.     </table>  
  38.     <div id="pager" class="scroll" style="text-align: center;">  
  39.     </div>  
  40. </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.

view plaincopy to clipboardprint?
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using System.Data.SqlClient;  
  7. using System.Configuration;  
  8. using System.Data;  
  9. using JQGridMVCDemo.Helper;  
  10.   
  11. namespace MvcApplication1.Controllers {  
  12.     [HandleError]  
  13.     public class HomeController : Controller {  
  14.         public ActionResult Index() {  
  15.             ViewData["Message"] = "Welcome to ASP.NET MVC!";  
  16.             return View();  
  17.         }  
  18.   
  19.         public ActionResult About() {  
  20.             return View();  
  21.         }  
  22.   
  23.         public ActionResult GetGridData(string sidx, string sord, int page, int rows) {  
  24.             return Content(JsonHelper.JsonForJqgrid(GetDataTable(sidx,sord,page,rows), rows, GetTotalCount(), page), "application/json");  
  25.         }  
  26.   
  27.         public DataTable GetDataTable(string sidx, string sord, int page, int pageSize) {  
  28.             int startIndex = (page-1) * pageSize;  
  29.             int endIndex = page * pageSize;  
  30.             string sql = @"WITH PAGED_CUSTOMERS  AS  
  31.                         (  
  32.                          SELECT  CustomerID, ContactName, Address, City, PostalCode,  
  33.                            ROW_NUMBER() OVER (ORDER BY " + sidx + @" " + sord + @") AS RowNumber  
  34.                          FROM CUSTOMERS  
  35.                         )  
  36.                         SELECT CustomerID, ContactName, Address, City, PostalCode  
  37.                         FROM PAGED_CUSTOMERS  
  38.                         WHERE RowNumber BETWEEN " + startIndex + @" AND " + endIndex + @";";  
  39.                           
  40.             DataTable dt = new DataTable();  
  41.             SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["mainConnection"].ConnectionString);  
  42.             SqlDataAdapter adap = new SqlDataAdapter(sql,conn);  
  43.             var rows=adap.Fill(dt);  
  44.             return dt;  
  45.         }  
  46.   
  47.         public int GetTotalCount() {  
  48.             string sql = @"SELECT COUNT(*) FROM Customers";  
  49.             SqlConnection conn=null;  
  50.             try {  
  51.                  conn= new SqlConnection(ConfigurationManager.ConnectionStrings["mainConnection"].ConnectionString);  
  52.                 SqlCommand comm = new SqlCommand(sql, conn);  
  53.                 conn.Open();  
  54.                 return (int)comm.ExecuteScalar();  
  55.             } catch {  
  56.             } finally {  
  57.                 try {  
  58.                     if (ConnectionState.Closed != conn.State) {  
  59.                         conn.Close();  
  60.                     }  
  61.                 }catch {  
  62.                 }  
  63.             }  
  64.             return -1;  
  65.         }  
  66.     }  
  67. }  




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.

 

출처 :ㅣ http://arahuman.blogspot.com/2009/06/jqgrid-using-mvc-json-and-datatable.html

'소프트웨어 > C# & ASP.NET' 카테고리의 다른 글

Xml to Json  (0) 2010.02.18
C# 강좌  (0) 2010.01.12
C# 4.0의 새로운 기능  (0) 2009.12.15
잉여들을 위한 클래스설계 이야기 2/4  (0) 2009.12.11
잉여들을 위한 클래스설계 이야기 1/4  (0) 2009.12.11

'소프트웨어/C# & ASP.NET'의 다른글

  • 현재글JQGrid using MVC, Json and Datatable.

관련글

티스토리툴바