jqGrid与MySql数据库的数据交互
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>jqGrid</title>
<link href="jqGrid/css/ui.jqgrid-bootstrap-ui.css" rel="stylesheet" type="text/css" />
<link href="jqGrid/css/ui.jqgrid.css" rel="stylesheet" type="text/css" />
<link href="jqGrid/jquery-ui-1.11.4.custom/jquery-ui.theme.css" rel="stylesheet"
type="text/css" />
<script src="jqGrid/jquery-1.8.2.min.js" type="text/javascript"></script>
<script src="jqGrid/js/jquery-1.7.2.min.js" type="text/javascript"></script>
<script src="jqGrid/js/i18n/grid.locale-cn.js" type="text/javascript"></script>
<script src="jqGrid/js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function () {
$("#rowed1").jqGrid({
url: "Handler.ashx",
datatype: "json",
colNames: ['id', 'Name', 'Pwd'],
colModel: [
{ name: 'id', index: 'id', editable: true },
{ name: 'Name', index: 'Name', editable: true },
{ name: 'Pwd', index: 'Pwd', editable: true }
],
rownumbers: true, //添加左侧行号
viewrecords: true, //是否在浏览导航栏显示记录总数
rowNum: 30, //每页显示记录数
rowList: [10, 15, 30], //用于改变显示行数的下拉列表框的元素数组。
pager: '#prowed1',
multiselect: true,
sortname: 'id',
sortorder: "desc",
editurl: "clientArray",
jsonReader: {
root: "rows",
total: "total",
page: "page",
records: "records",
repeatitems: false
},
caption: "JSON Example"
}).navGrid('#prowed1', { add: true, edit: true, del: true, search: false, refresh: false });
zsy(); //调用自适应屏幕宽高的方法
});
function zsy() {
$("#rowed1").setGridWidth($(window).width() * 0.99);
$("#rowed1").setGridHeight($(window).height() * 0.8);
};
</script>
</head>
<body>
<table id="rowed1"></table>
<div id="prowed1" class="scroll"></div>
<br />
</body>
</html>
后台的一般处理程序：
<%@ WebHandler Language="C#" class="Handler" %>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Runtime.Serialization.Json;
using System.Web.Script.Serialization;
public class Handler : IHttpHandler {
public void ProcessRequest (HttpContext context) {
context.Response.ContentType = "text/plain";
System.Data.SqlClient.SqlConnection s = new System.Data.SqlClient.SqlConnection("Server=.;database=MyLi;uid=sa;pwd=Aa123456");
s.Open();
string sPage = HttpContext.Current.Request.Params["page"].ToString();//获取当前页
int iPage = int.Parse(sPage);
int pageSize = 10;
string strSql = "SELECT * FROM dbo.shi WHERE id IN (SELECT TOP (10) id FROM dbo.shi WHERE id NOT IN ( SELECT TOP ( "+10 * (iPage-1)+" )id FROM dbo.shi) ) "; //sql语句分页
System.Data.SqlClient.SqlDataAdapter d = new System.Data.SqlClient.SqlDataAdapter(strSql, s);
DataTable DT = new DataTable();
d.Fill(DT);
string strSqll = " select * from dbo.shi "; //我这里选择是为了下面得到总记录数
System.Data.SqlClient.SqlDataAdapter ds = new System.Data.SqlClient.SqlDataAdapter(strSqll, s);
DataTable dt = new DataTable();
ds.Fill(dt);
s.Close();
int rowCount =dt.Rows.Count; //总记录数
//int pageCount = (rowCount + 10 - 1) / 10 ;
int pageCount = rowCount /pageSize == 0 ? rowCount / pageSize : rowCount / pageSize+1;//计算总页数
//int rowCountt = rowCount * pageCount;
var resultObj = new resultObj
{ //形成一个数组
total =pageCount, //总页数
records =rowCount, //总记录数
rows=DT
};
string returnStr = Newtonsoft.Json.JsonConvert.SerializeObject(resultObj);//转换成json格式，序列化json
context.Response.Write(returnStr); //返回returnStr
}
public bool IsReusable {
get {
return false;
}
}
}
