In this article I am going to explain how we can use jQ
Grid in asp.net c#.
Below is my Data Table in Design mode from which I am
going to show data in jQ Grid:
Image 1.
Script
of my Data Table is:
CREATE TABLE [dbo].[Employee](
[Emp_ID] [int]
IDENTITY(1,1) NOT NULL,
[Name]
[varchar](50) NULL,
[Designation]
[varchar](50) NULL,
[City]
[varchar](50) NULL,
[State]
[varchar](50) NULL,
[Country]
[varchar](50) NULL,
CONSTRAINT [PK_Employee] PRIMARY
KEY CLUSTERED
(
[Emp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON
PRIMARY]
) ON [PRIMARY]
GO
Data in My Table is:
Image 2.
Below
is my aspx code:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="jQGridExample.Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link type="text/css" href="http://jqueryrock.googlecode.com/svn/trunk/css/jquery-ui-1.9.2.custom.css" rel="stylesheet" />
<link type="text/css" href="http://jqueryrock.googlecode.com/svn/trunk/jqgrid/css/ui.jqgrid.css" rel="stylesheet" />
<script type="text/javascript" src="http://jqueryrock.googlecode.com/svn/trunk/js/jquery-1.8.3.js"></script>
<script type="text/javascript" src="http://jqueryrock.googlecode.com/svn/trunk/js/jquery-ui-1.9.2.custom.js"></script>
<script src="http://jqueryrock.googlecode.com/svn/trunk/jqgrid/js/grid.locale-en.js" type="text/javascript"></script>
<script src="http://jqueryrock.googlecode.com/svn/trunk/jqgrid/js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
$("#dataGrid").jqGrid({
url: 'Default.aspx/GetDataFromDB',
datatype: 'json',
mtype: 'POST',
serializeGridData: function (postData) {
return JSON.stringify(postData);
},
ajaxGridOptions: { contentType:
"application/json" },
loadonce: true,
colNames: ['Employee ID', 'Name', 'Designation', 'City', 'State', 'Country'],
colModel: [
{ name: 'Emp_ID', index: 'Employee ID', width: 80 },
{ name: 'Name', index: 'Name', width: 140 },
{ name: 'Designation', index: 'Designation', width: 160 },
{ name: 'City', index: 'City', width: 180 },
{ name: 'State', index: 'State', width: 180 },
{ name: 'Country', index: 'Country', width: 180 }
],
pager: '#pagingGrid',
rowNum: 5,
rowList: [10, 20, 30],
viewrecords: true,
gridview: true,
jsonReader: {
page: function (obj) { return 1; },
total: function (obj) { return 1; },
records: function (obj) { return obj.d.length; },
root: function (obj) { return obj.d; },
repeatitems: false,
id: "0"
},
caption: 'jQ Grid Example'
});
}).pagingGrid("#pager", { edit: true, add: true, del: false });
</script>
</head>
<body style="font-family: Arial; font-size: 10pt">
<table style="border: solid 15px red; width: 100%; vertical-align: central;">
<tr>
<td style="padding-left: 20px; padding-top: 20px; padding-bottom: 20px; background-color: skyblue; font-family: 'Times New Roman'; font-weight:
bold; font-size: 20pt; color: chocolate;">jQ Grid Example
In ASP.NET C#
</td>
</tr>
<tr>
<td style="text-align: center; vertical-align: central; padding: 50px;">
<table id="dataGrid" style="text-align: center;"></table>
<div id="pagingGrid"></div>
</td>
</tr>
</table>
</body>
</html>
Below
is my aspx.cs ocde:
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Data.SqlClient;
using
System.Configuration;
using System.Data;
using
System.Web.Script.Serialization;
using
System.Web.Services;
namespace jQGridExample
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
[WebMethod]
public static List<Dictionary<string, object>>
GetDataFromDB()
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(@"Data
Source=INDIA\MSSQLServer2k8; Initial Catalog=EmployeeManagement; Uid=sa;
pwd=india;"))
{
using (SqlCommand cmd = new SqlCommand("SELECT
Emp_ID, Name, Designation, City, State,Country FROM Employee ORDER BY
Emp_ID,Country,State, City", con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
System.Web.Script.Serialization.JavaScriptSerializer serializer = new
System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName,
dr[col]);
}
rows.Add(row);
}
return rows;
}
}
}
}
}
Now run the application:
All Record in
jQGrid. Here I set Page Size 5.
Image 3.
Now do paging:
Image 4.
Image 5.
Image 6.
Image 7.