JqGrid
i've been a couple of days . Trying to understand and make this Jqgrid working at least for basic operation crud in Asp.net environment.
Well basically first you need set up the path on jquery.jqgrid.js
function jqGridInclude()
{
var pathtojsfiles = "./jqGrid-3.5.alfa/js/"; // need to be ajusted
}
The path is the the current Website ./ and then the path for the rest. watch your firebug where the resulted path is.
this js will load the other component using xmlrequest. remove the unneeded module if you like to.
if you want to combine all into one provide a server page/handler on server. reading using stream to combine it all.
combinedIncludeURL = "combine.ashx?type=javascript&files=";it will paste the module on the query string.
Here is the script that is on the html
var lastsel3;
function editrow2() {
jQuery("#list").editRow("2");
}
function saverow2() {
jQuery("#list").saveRow("2");
}
jQuery(document).ready(function() {
jQuery("#list").jqGrid({
url: 'example.ashx',
datatype: 'xml',
mtype: 'GET',
colNames: ['Inv No', 'Date', 'Amount', 'Tax', 'Total', 'Notes'],
colModel: [
{ name: 'invid', index: 'invid', width: 55 },
{ name: 'invdate', index: 'invdate', width: 90, editable: true },
{ name: 'amount', index: 'amount', width: 80, align: 'right', editable: true },
{ name: 'tax', index: 'tax', width: 80, align: 'right', editable: true },
{ name: 'total', index: 'total', width: 80, align: 'right', editable: true },
{ name: 'note', index: 'note', width: 150, sortable: false, editable: true}],
pager: jQuery('#pager'),
rowNum: 10,
sortname: 'id',
sortorder: "desc",
viewrecords: true,
imgpath: '/jqGrid-3.5.alfa/themes/lightness/images',
caption: 'My first grid',
editurl: "operation.ashx",
onSelectRow:onselectRowGrid
}).navGrid("#pager", { edit: true, add: true, del: true });
});
function onselectRowGrid(id) {
if (id && id !== lastsel3)
{ jQuery('#list').restoreRow(lastsel3); jQuery('#list').editRow(id, true, pickdates);
lastsel3 = id;
}
}
function pickdates(id) {
alert(document.getElementById(id + "_invdate"));
jQuery("#" + id + "_invdate", "#list").datepicker({ dateFormat: "yy-mm-dd" });
}
as the grid will request using query string to the url that you define, it contains the page,limit,order by,etc.
Retrieve DataSo now what you need is the serverside taking data and the paging.
See the code below.
public class Example : IHttpHandler {
public void ProcessRequest (HttpContext context) {
int page = Convert.ToInt32(context.Request.QueryString["page"].ToString());
int limit = Convert.ToInt32(context.Request.QueryString["rows"].ToString());
string sidx = context.Request.QueryString["sidx"].ToString();
string sord=context.Request.QueryString["sord"].ToString();
string result = "";
if(String.IsNullOrEmpty(sidx)) sidx = "1";
int count;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Documents and Settings\WindowsXP\My Documents\Visual Studio 2008\WebSites\testjqgrid\App_Data\test.mdf"";Integrated Security=True;User Instance=True"))
{
conn.Open();
System.Data.SqlClient.SqlCommand comm=new System.Data.SqlClient.SqlCommand("SELECT COUNT(*) AS count FROM invheader",conn);
result = comm.ExecuteScalar().ToString();
count = Convert.ToInt32(result);
}
int totalpages;
double hasilbagi = Convert.ToDouble(result) / Convert.ToDouble(limit);
if (int.Parse(result) > 0) totalpages = Convert.ToInt32(Math.Ceiling(hasilbagi));
else
totalpages = 0;
if (Convert.ToInt32(page) > totalpages) page=totalpages;
int start = limit*page - limit;
if(start <0) start = 0;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Documents and Settings\WindowsXP\My Documents\Visual Studio 2008\WebSites\testjqgrid\App_Data\test.mdf"";Integrated Security=True;User Instance=True"))
{
conn.Open();
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand("SELECT * FROM(SELECT *, ROW_NUMBER() OVER (ORDER BY invid) as RowNumber FROM invheader) a where RowNumber>=" + start + " and RowNumber<" + (limit+start), conn);
System.Data.SqlClient.SqlDataReader reader = comm.ExecuteReader();
context.Response.ContentType = "text/xml;charset=utf-8";
string xml = "<?xml version='1.0' encoding='utf-8'?><rows><page>" + page + "</page><total>" + totalpages + "</total>"
+ "<records>" + count + "</records>";
while (reader.Read())
{
xml +="<row id='" + reader["invid"] +"'>";
xml += "<cell>" + reader["invid"] + "</cell>";
xml += "<cell>" + Convert.ToDateTime(reader["invdate"]).ToShortDateString() + "</cell>";
xml += "<cell>" + reader["amount"] + "</cell>";
xml += "<cell>" + reader["tax"] + "</cell>";
xml += "<cell>" + reader["total"] + "</cell>";
xml += "</row>";
}
xml+="</rows>";
context.Response.Write(xml);
}
context.Response.End();
}
What interesting here is How we can get the LIMIT like on mysql in SQL server . Observe the query :
SELECT * FROM(SELECT *, ROW_NUMBER() OVER (ORDER BY invid) as RowNumber
FROM invheader) a where RowNumber>=" + start + " and RowNumber<"
+ (limit+start)
that is how it is done. the row number is the feature that is provid by sql server internally. that way we can get the row number.
Edit(Update), Delete and Add
You have define the edit url on your html.
editurl: "operation.ashx",
every operation edit ,delete and update will goes to this server side handler , with query string on it.
the logic is if edit than the grid will submit all data in form.
But if it's delete and add , there's an extra parameter called oper equals add , and del respectively.
So here's how your server side should looks like:
public void ProcessRequest (HttpContext context) {
//if add new it's post on a form
//there's oper means operation
System.Collections.Specialized.NameValueCollection forms= context.Request.Form;
string operation= forms.Get("oper");
//null means edit
if (String.IsNullOrEmpty(operation))
{
//update logic
}
//have values means add or delete
else
{
switch (operation)
{
case "add":
string query = "insert into invheader (invdate,amount,tax,total,note) values(";
query+= "'" + forms.Get("invdate") + "'";
query += forms.Get("client_id") + ",";
query += forms.Get("amount") + ",";
query += forms.Get("tax") + ",";
query += forms.Get("total") + ",";
query += "'" + forms.Get("note") + "'";
query += ")";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Documents and Settings\WindowsXP\My Documents\Visual Studio 2008\WebSites\testjqgrid\App_Data\test.mdf"";Integrated Security=True;User Instance=True"))
{
conn.Open();
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(query, conn);
comm.ExecuteNonQuery();
}
break;
case "del":
string id=forms.Get("id");
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Documents and Settings\WindowsXP\My Documents\Visual Studio 2008\WebSites\testjqgrid\App_Data\test.mdf"";Integrated Security=True;User Instance=True"))
{
conn.Open();
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand("delete FROM invheader where invid=" + id, conn);
comm.ExecuteNonQuery();
}
break;
}
}
}
what i still can not get it work is i want to edit the cell and show the jquery datepicker. on the demo it runs but hey it does not here.Weird dunno what cause it.
Note :
You will need to add 2 extra script if you want to works with your jQuery('grid').navGrid. and may be other function also
they are:
<script src="jqGrid-3.5.alfa/js/jqModal.js" type="text/javascript"></script>
<script src="jqGrid-3.5.alfa/js/jqDnR.js" type="text/javascript"></script>