jqGrid is a powerful plugin to display data as grid layout and Add,edit ,delete records.I had some free time so I thought to explore some features of jqGrid.
Our final Output will be :
Ok,Lets assume We have to show employee data in jqGrid using webmethod from database (sql server)
First create a employee table :
Table :employeeTable
Column Name
|
Data Type
|
employeeId
|
int
|
name
|
nvarchar(500)
|
email
|
nvarchar(200)
|
pwd
|
nvarchar(8)
|
phone
|
nchar(10)
|
nationalityId
|
int
|
dateOfBirth
|
date
|
Another table is :nationalTable
Column Name
|
Data Type
|
nationalId
|
int
|
natiionName
|
nvarchar(200)
|
Now create a procedure to get data from database to show the employee records:
CREATE PROC [dbo].[fetchemployeeData]
AS
SELECT employeeId,name,email,pwd,phone ,B.nationalId,CONVERT(VARCHAR(10),
dateOfBirth,103)AS dateOfBirth,B.natiionName FROM employeeTable
A JOIN nationalTable B ON A.nationalityId=B.nationalId ORDER BY name
Now lets work on jqGrid.
On the aspx page first add the jqGrid references as below inside the head tag:
<script src="JQGridReq/jquery-1.9.0.min.js" type="text/javascript"></script>
<link rel="stylesheet"
href="http://code.jquery.com/ui/1.10.3/themes/redmond/jquery-ui.css"
/>
<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
<script src="JQGridReq/grid.locale-en.js"
type="text/javascript"></script>
<script src="JQGridReq/jquery.jqGrid.js"
type="text/javascript"></script>
<link href="JQGridReq/ui.jqgrid.css"
rel="stylesheet"
type="text/css"
/>
<script src="Scripts/json2.js"
type="text/javascript"></script>
Inside the form take table and div as below:
<table id="list"></table> <%--for grid--%>
<div id="pager"> </div> <%--for paging--%>
Now In the script block copy paste the below code
<script type="text/javascript">
Now In the script block copy paste the below code
<script type="text/javascript">
$.ajax({
type: "POST",
contentType:
"application/json",
data: "{}",
url: "jqgridexample.aspx/getEmployee",
dataType:
"json",
success:
function (data) {
data = data.d;
$("#list").jqGrid({
datatype: "local",
colNames: ['Employee Id',
'Name', 'Email',
'Phone', 'Password',
'Nationality', 'Date
of Birth'],
colModel: [
{ name: 'employeeId',
index: 'employeeId', width: 55, stype: 'text', sortable: true,
editable: true, formoptions: { rowpos: 1,
colpos: 1 }, editrules: { integer: true} },
{ name: 'name',
index: 'name', width: 90, stype: 'text', sortable: true,
editable: true, editrules: { required: true }, formoptions: { rowpos: 2, colpos: 1} },
{ name: 'email',
index: 'email', width: 100, stype: 'text', sortable: true,
editable: true, editrules: { email: true, required: true
}, formoptions: { rowpos: 2, colpos: 2} },
{ name: 'phone',
index: 'phone', width: 80, align: "right", stype: 'text',
sortable: true, editable: true, formoptions: { rowpos: 3, colpos: 1} },
{
name: 'pwd', index: 'pwd',
width: 80, align: "right", stype: 'text', edittype: 'password',
sortable: true, editable: true, formoptions: { rowpos: 3, colpos: 2} },
{ name:
'nationalId', index: 'nationalId',
width: 80, align: "right",
formatter: 'select',stype: 'select', editable: true,
edittype: "select", editoptions: {
value: getCountry() }, formoptions: { rowpos: 4, colpos: 1 }
},
{ name: 'dateOfBirth',
index: 'dateOfBirth', width: 80, align: "right",
edittype: 'text', editable: true,
editoptions: {
dataInit: function (el)
{ $(el).datepicker({
dateFormat: "dd/mm/yy",
changeMonth: true,
changeYear: true, }); } },
formoptions: {
rowpos: 4, colpos: 2 }
}
],
data: JSON.parse(data),
rowno: 10,
loadonce:true,
/* multiselect:true,*/
rowlist: [5, 10, 20],
pager: '#pager',
viewrecords: true,
gridview: true,
sortorder: 'asc',
toppager: true,
cloneToTop: true,
altrows: true,
autowidth: false,
hoverrows: true,
height:300,
rownumbers: true,
caption: "Employee Data"
});
$('#list').jqGrid('navGrid', '#pager',
{
edit: true,
add: true,
del: true,
search: false,
searchtext: "Search",
addtext: "Add",
edittext: "Edit",
deltext: "Delete",
cloneToTop: true
});
/* to put search option in column toolbar*/
$("#list").jqGrid('filterToolbar', { stringResult: true, searchOnEnter: true
});
/* move add ,edit ,delete button on top */
var grid = $("#list");
var
topPagerDiv = $('#' + grid[0].id + '_toppager')[0
$("#" + grid[0].id + "_toppager_center",
topPagerDiv).remove();
$(".ui-paging-info",
topPagerDiv).remove();
var bottomPagerDiv = $("div#pager")[0];
$("#add_"
+ grid[0].id, bottomPagerDiv).remove();
$("#edit_" + grid[0].id,
bottomPagerDiv).remove();
$("#del_" + grid[0].id,
bottomPagerDiv).remove();
$("#refresh_" + grid[0].id,
bottomPagerDiv).remove();
// "#add_list"
},
error: function (XMLHttpRequest,
textStatus, errorThrown) {
debugger;
}
});
});
</script>
In the above grid national Names are in dropdown list ,so to fill the dropdown list dynamically use the below function
function getCountry() {
var country;
$.ajax({
type: "POST",
contentType: "application/json",
data: "{}",
async: false,
url: "jqgridexample.aspx/getNational",
dataType: "json",
success: function (data) {
country = data.d;
},
error: function (XMLHttpRequest,
textStatus, errorThrown) {
debugger;
}
});
return country;
}
Now time to create web method.
[WebMethod]
public static string getEmployee()
{
SqlConnection con = new
SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
SqlCommand cmd = new
SqlCommand();
cmd.CommandText = "fetchemployeeData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
con.Open();
SqlDataAdapter da = new
SqlDataAdapter(cmd);
con.Close();
DataSet ds = new DataSet();
da.Fill(ds);
return Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[0]);
}
[WebMethod]
public static string getNational()
{ string returnvalue="";
SqlConnection con = new
SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
SqlCommand cmd = new
SqlCommand();
cmd.CommandText = "fetchNationality";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
con.Open();
SqlDataAdapter da = new
SqlDataAdapter(cmd);
con.Close();
DataSet ds = new DataSet();
da.Fill(ds);
returnvalue+=""+""+""+":All;";
for (var i = 0; i <
ds.Tables[0].Rows.Count; i++) {
returnvalue +=
ds.Tables[0].Rows[i]["nationalId"].ToString().Trim()
+ ":" + ds.Tables[0].Rows[i]["natiionName"].ToString().Trim() + ";";
}
returnvalue =
returnvalue.Remove(returnvalue.LastIndexOf(';'));
return returnvalue;
}
Don't forget to add newtonsoft ref to serialize your data in json format. To add newtonsoft dll follow the below steps
GOTO-Tools->Library Package manager->Package manager console
PM> Install-Package Newtonsoft.Json
Here are some properties of jqGrid:
data: set the data to bind the grid
colNames: defines the columns Name as Header
colModel: bind column value in rows
stype: defines the search type (text or select)
sortable: It can be sort or not
editable: make it editable
edittypes: types of control at the time of editing(textbox,select,checkbox etc)
editrules: some validations and datatypes can be defined
editoptions: to bind the dynamic data inside the controls
formoptions : defines form layout at the time of generating form for edit,add
GOTO-Tools->Library Package manager->Package manager console
PM> Install-Package Newtonsoft.Json
Here are some properties of jqGrid:
data: set the data to bind the grid
colNames: defines the columns Name as Header
colModel: bind column value in rows
stype: defines the search type (text or select)
sortable: It can be sort or not
editable: make it editable
edittypes: types of control at the time of editing(textbox,select,checkbox etc)
editrules: some validations and datatypes can be defined
editoptions: to bind the dynamic data inside the controls
formoptions : defines form layout at the time of generating form for edit,add
edit: to show the edit button,
add: to show the add button,
del: to show the delete button,
search: to show the search button,
searchtext: search button text,
addtext: Add button text,
edittext: edit button text,
deltext: delete button text,
pager: div id to set the paging
multiselect: for select multiple rows
hoverows: shows tooltip on mouse hover
rownumbers :shows the row number
Any confusion contact to mkumar.soft@gmail.com.pager: div id to set the paging
multiselect: for select multiple rows
hoverows: shows tooltip on mouse hover
rownumbers :shows the row number
