Hi jQgrid....20110124
快过年了，给大家拜个早年.做了个jQgrid的DEMO。哈哈。
主要运用MVC 和 jQgrid(后期会加上Nhibernate 以及spring.Net)
环境 W-XP SP3+ VS2008SP1 + Oracle.
准备工作：
下载：
1、jquery.jqGrid-3.8.1.zip;
2、jquery-ui-1.8.6.custom2.zip;(这个样式可随便下载)
（以上2个包以及源码在附件当中）
打开ORACLE 创建数据库
SqlCode
--Create the province table and add some data
create table province
(
ProvinceID int,
ProvinceCode varchar(20),
ProvinceName varchar(20),
primary key (ProvinceID)
);
select * from province;
insert into province
select 1001 , 1001,'北京' from dual
union all
select 1002 , 1002,'天津' from dual
union all
select 1003 , 1003,'重庆' from dual
union all
select 1004 , 1004,'四川' from dual
union all
select 1005 , 1005,'辽宁' from dual;
commit;
----Create the city table and add some data
Create table city
(
CityID int,
CityCode varchar(20),
CityName varchar(20),
ProcinceID int,
primary key (CityID)
);
insert into city
select 101 , 101,'北京市',1001 from dual
union all
select 102 , 102,'朝阳',1001 from dual
union all
select 103 , 103,'海淀',1001 from dual
union all
select 104 , 104,'昌平',1001 from dual
union all
select 105 , 105,'天津市',1002 from dual
union all
select 106 , 106,'重庆市',1003 from dual
union all
select 107 , 107,'南坪',1003 from dual
union all
select 108 , 108,'李家沱',1003 from dual
union all
select 109 , 109,'鱼洞',1003 from dual
union all
select 110 , 110,'界石',1003 from dual
union all
select 111 , 111,'成都市',1004 from dual
union all
select 112 , 112,'天府软件园',1004 from dual
union all
select 113, 113,'华阳镇',1004 from dual
union all
select 114 , 114,'沈阳市',1005 from dual
union all
select 115 , 115,'大连市',1005 from dual;
commit;
create table province
(
ProvinceID int,
ProvinceCode varchar(20),
ProvinceName varchar(20),
primary key (ProvinceID)
);
select * from province;
insert into province
select 1001 , 1001,'北京' from dual
union all
select 1002 , 1002,'天津' from dual
union all
select 1003 , 1003,'重庆' from dual
union all
select 1004 , 1004,'四川' from dual
union all
select 1005 , 1005,'辽宁' from dual;
commit;
----Create the city table and add some data
Create table city
(
CityID int,
CityCode varchar(20),
CityName varchar(20),
ProcinceID int,
primary key (CityID)
);
insert into city
select 101 , 101,'北京市',1001 from dual
union all
select 102 , 102,'朝阳',1001 from dual
union all
select 103 , 103,'海淀',1001 from dual
union all
select 104 , 104,'昌平',1001 from dual
union all
select 105 , 105,'天津市',1002 from dual
union all
select 106 , 106,'重庆市',1003 from dual
union all
select 107 , 107,'南坪',1003 from dual
union all
select 108 , 108,'李家沱',1003 from dual
union all
select 109 , 109,'鱼洞',1003 from dual
union all
select 110 , 110,'界石',1003 from dual
union all
select 111 , 111,'成都市',1004 from dual
union all
select 112 , 112,'天府软件园',1004 from dual
union all
select 113, 113,'华阳镇',1004 from dual
union all
select 114 , 114,'沈阳市',1005 from dual
union all
select 115 , 115,'大连市',1005 from dual;
commit;
数据库创建好了后 看下系统的结构图(除Nhibernate、Spring.net):
Common 文件里存放的是准备实现分页的部分.
Content中的Themes 包含jQgrid的主题.
Extension中包含的是MVC有无分页的JSON数据组装.
其他应该就是基本的MVC结构了。
总体效果预览:
Edit
1、先把两个实体建立好
建立Entities文件夹 在其下建立两个实体类 分别为City,Province.
City.csusing System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
namespace Marlboro.Web.Entities
{
public class City
{
public int City_ID { get; set; }
public string City_Code { get; set; }
public string City_Name { get; set; }
public int province_ID { get; set; }
public bool IsActive { get; set; }
}
}
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
namespace Marlboro.Web.Entities
{
public class City
{
public int City_ID { get; set; }
public string City_Code { get; set; }
public string City_Name { get; set; }
public int province_ID { get; set; }
public bool IsActive { get; set; }
}
}
Province.csusing System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
namespace Marlboro.Web.Entities
{
public class Province
{
public int Province_ID { get; set; }
public string Province_Code { get; set; }
public string Province_Name { get; set; }
public bool Province_IsActive { get; set; }
}
}
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
namespace Marlboro.Web.Entities
{
public class Province
{
public int Province_ID { get; set; }
public string Province_Code { get; set; }
public string Province_Name { get; set; }
public bool Province_IsActive { get; set; }
}
}
2、在Controller下建立CityController
CityController.csusing System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Marlboro.Web.Entities;
using Marlboro.Web.Extension;
namespace Marlboro.Web.Controllers
{
public class CityController : Controller
{
//
// GET: /City/
public ActionResult Index()
{
return View();
}
/// <summary>
/// 返回绑定的DROPDOWNLIST数据
/// </summary>
/// <returns></returns>
public ActionResult Citylist()
{
ViewData["ProvinceList"] = new Marlboro.Web.Models.CityModels().GetDropdownlistItem();
return View();
}
/// <summary>
/// 通过省的ID 取出所有城市的JSON格式
/// </summary>
/// <param name="provinceId"></param>
/// <returns></returns>
public JsonResult Getlist(int? provinceId)
{
List<City> Citylist = new Marlboro.Web.Models.CityModels().Citylist(provinceId);
var retVal = new Extension.BuildJson<City, int>(
Citylist,
city => city.City_ID,
city => "",
city => city.City_ID.ToString(),
city => city.City_Code,
city => city.City_Name,
city => city.province_ID.ToString()
).Build();
return Json(retVal, JsonRequestBehavior.AllowGet);
}
[HttpPost]
public ActionResult Edit(long id, FormCollection collection)
{
try
{
//[0000055] bug fix
string CityID = collection["CityID"];
string code = collection["CityCode"];
string name = collection["CityName"];
string Provinceid = collection["ProvinceID"];
if (string.IsNullOrEmpty(code))
{
return this.Content("City code is required!");
}
if (string.IsNullOrEmpty(name))
{
return this.Content("City name is required!");
}
City c = new Marlboro.Web.Models.CityModels().GetCitylistByID(Convert.ToInt32(CityID));
if (c == null)
{
return this.Content("The city to be update has been deleted by another user.");
}
c.City_ID = Convert.ToInt32(CityID);
c.City_Code = code;
c.City_Name = name;
c.province_ID = Convert.ToInt32(Provinceid);
new Marlboro.Web.Models.CityModels().UpdateCityByID(c);
}
catch (Exception ex)
{
//this.Response.Headers["exceptionMessage"] = ex.Message;
//throw ex;
return this.Content(ex.Message);
}
return this.Content("");
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Marlboro.Web.Entities;
using Marlboro.Web.Extension;
namespace Marlboro.Web.Controllers
{
public class CityController : Controller
{
//
// GET: /City/
public ActionResult Index()
{
return View();
}
/// <summary>
/// 返回绑定的DROPDOWNLIST数据
/// </summary>
/// <returns></returns>
public ActionResult Citylist()
{
ViewData["ProvinceList"] = new Marlboro.Web.Models.CityModels().GetDropdownlistItem();
return View();
}
/// <summary>
/// 通过省的ID 取出所有城市的JSON格式
/// </summary>
/// <param name="provinceId"></param>
/// <returns></returns>
public JsonResult Getlist(int? provinceId)
{
List<City> Citylist = new Marlboro.Web.Models.CityModels().Citylist(provinceId);
var retVal = new Extension.BuildJson<City, int>(
Citylist,
city => city.City_ID,
city => "",
city => city.City_ID.ToString(),
city => city.City_Code,
city => city.City_Name,
city => city.province_ID.ToString()
).Build();
return Json(retVal, JsonRequestBehavior.AllowGet);
}
[HttpPost]
public ActionResult Edit(long id, FormCollection collection)
{
try
{
//[0000055] bug fix
string CityID = collection["CityID"];
string code = collection["CityCode"];
string name = collection["CityName"];
string Provinceid = collection["ProvinceID"];
if (string.IsNullOrEmpty(code))
{
return this.Content("City code is required!");
}
if (string.IsNullOrEmpty(name))
{
return this.Content("City name is required!");
}
City c = new Marlboro.Web.Models.CityModels().GetCitylistByID(Convert.ToInt32(CityID));
if (c == null)
{
return this.Content("The city to be update has been deleted by another user.");
}
c.City_ID = Convert.ToInt32(CityID);
c.City_Code = code;
c.City_Name = name;
c.province_ID = Convert.ToInt32(Provinceid);
new Marlboro.Web.Models.CityModels().UpdateCityByID(c);
}
catch (Exception ex)
{
//this.Response.Headers["exceptionMessage"] = ex.Message;
//throw ex;
return this.Content(ex.Message);
}
return this.Content("");
}
}
}
3、Models （暂时先写的SQL语句写的比较垃圾。后期打算合并NhiberNate)
CityModels.csusing System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Data;
using System.Collections;
using Marlboro.Web.Entities;
namespace Marlboro.Web.Models
{
public class CityModels
{
/// <summary>
/// 获取所有省信息 绑定Dropdownlist
/// </summary>
/// <returns></returns>
public List<SelectListItem> GetDropdownlistItem()
{
List<SelectListItem> list = new List<SelectListItem>();
DataTable Itemsdt = new DataTable();
Itemsdt = new DataBaseTest.OracleHelper().ExecuteDataTable("select * from PROVINCE");
if (Itemsdt.Rows.Count != 0)
{
for (int i = 0; i < Itemsdt.Rows.Count; i++)
{
list.Add(new SelectListItem { Text = Itemsdt.Rows[i]["ProvinceName"].ToString(), Value = Itemsdt.Rows[i]["ProvinceID"].ToString() });
}
}
return list ?? null;
}
/// <summary>
/// 通过procinveId 获取City列表
/// </summary>
/// <param name="provinceId"></param>
/// <returns></returns>
public List<City> Citylist(int? provinceId)
{
List<City> Citylist = new List<City>();
DataTable dt = new DataTable();
dt = new DataBaseTest.OracleHelper().ExecuteDataTable("select * from City where ProcinceID='" + provinceId + "'");//where ProcinceID='" + provinceID + "'
if (dt.Rows.Count != 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
City city = new City();
city.City_ID = Convert.ToInt32(dt.Rows[i]["CITYID"]);
city.City_Code = dt.Rows[i]["CITYCODE"].ToString();
city.City_Name = dt.Rows[i]["CITYNAME"].ToString();
city.province_ID = Convert.ToInt32(dt.Rows[i]["PROCINCEID"]);
Citylist.Add(city);
}
}
return Citylist;
}
/// <summary>
/// 通过CityID 获取实体
/// </summary>
/// <param name="CityID"></param>
/// <returns></returns>
public City GetCitylistByID(int? CityID)
{
City city = new City();
DataTable dt = new DataTable();
dt = new DataBaseTest.OracleHelper().ExecuteDataTable("select * from City where CITYID='" + CityID + "'");//where ProcinceID='" + provinceID + "'
if (dt.Rows.Count != 0)
{
city.City_ID = Convert.ToInt32(dt.Rows[0]["CITYID"]);
city.City_Code = dt.Rows[0]["CITYCODE"].ToString();
city.City_Name = dt.Rows[0]["CITYNAME"].ToString();
city.province_ID = Convert.ToInt32(dt.Rows[0]["PROCINCEID"]);
}
return city;
}
/// <summary>
/// 更新City数据 提供给前台JS
/// </summary>
/// <param name="city"></param>
public void UpdateCityByID(City city)
{
string sql = "update City set CITYCODE='" + city.City_Code + "',CITYNAME='" + city.City_Name + "',PROCINCEID='" + city.province_ID + "' where CITYID='" + city.City_ID + "'";
new DataBaseTest.OracleHelper().ExcuteCity(sql);
}
}
}
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Data;
using System.Collections;
using Marlboro.Web.Entities;
namespace Marlboro.Web.Models
{
public class CityModels
{
/// <summary>
/// 获取所有省信息 绑定Dropdownlist
/// </summary>
/// <returns></returns>
public List<SelectListItem> GetDropdownlistItem()
{
List<SelectListItem> list = new List<SelectListItem>();
DataTable Itemsdt = new DataTable();
Itemsdt = new DataBaseTest.OracleHelper().ExecuteDataTable("select * from PROVINCE");
if (Itemsdt.Rows.Count != 0)
{
for (int i = 0; i < Itemsdt.Rows.Count; i++)
{
list.Add(new SelectListItem { Text = Itemsdt.Rows[i]["ProvinceName"].ToString(), Value = Itemsdt.Rows[i]["ProvinceID"].ToString() });
}
}
return list ?? null;
}
/// <summary>
/// 通过procinveId 获取City列表
/// </summary>
/// <param name="provinceId"></param>
/// <returns></returns>
public List<City> Citylist(int? provinceId)
{
List<City> Citylist = new List<City>();
DataTable dt = new DataTable();
dt = new DataBaseTest.OracleHelper().ExecuteDataTable("select * from City where ProcinceID='" + provinceId + "'");//where ProcinceID='" + provinceID + "'
if (dt.Rows.Count != 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
City city = new City();
city.City_ID = Convert.ToInt32(dt.Rows[i]["CITYID"]);
city.City_Code = dt.Rows[i]["CITYCODE"].ToString();
city.City_Name = dt.Rows[i]["CITYNAME"].ToString();
city.province_ID = Convert.ToInt32(dt.Rows[i]["PROCINCEID"]);
Citylist.Add(city);
}
}
return Citylist;
}
/// <summary>
/// 通过CityID 获取实体
/// </summary>
/// <param name="CityID"></param>
/// <returns></returns>
public City GetCitylistByID(int? CityID)
{
City city = new City();
DataTable dt = new DataTable();
dt = new DataBaseTest.OracleHelper().ExecuteDataTable("select * from City where CITYID='" + CityID + "'");//where ProcinceID='" + provinceID + "'
if (dt.Rows.Count != 0)
{
city.City_ID = Convert.ToInt32(dt.Rows[0]["CITYID"]);
city.City_Code = dt.Rows[0]["CITYCODE"].ToString();
city.City_Name = dt.Rows[0]["CITYNAME"].ToString();
city.province_ID = Convert.ToInt32(dt.Rows[0]["PROCINCEID"]);
}
return city;
}
/// <summary>
/// 更新City数据 提供给前台JS
/// </summary>
/// <param name="city"></param>
public void UpdateCityByID(City city)
{
string sql = "update City set CITYCODE='" + city.City_Code + "',CITYNAME='" + city.City_Name + "',PROCINCEID='" + city.province_ID + "' where CITYID='" + city.City_ID + "'";
new DataBaseTest.OracleHelper().ExcuteCity(sql);
}
}
}
4、建立文件夹Extension ，在文件夹下建立BuildJson类
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections;
using System.Collections.Generic;
using Marlboro.Web.Common;
namespace Marlboro.Web.Extension
{
public class BuildJson<TEntity, IdType>
{
/// <summary>
/// MVC 无分页 Json数据构造
/// </summary>
/// <param name="entities">对象实体集合</param>
/// <param name="idFunc">泛型委托，传入匿名函数lambda 传入TEntity 返回 IdType</param>
/// <param name="propertyFuncs">泛型委托集合 用法同上</param>
public BuildJson(
List<TEntity> entities,
Func<TEntity, IdType> idFunc,
params Func<TEntity, string>[] propertyFuncs
)
{
//无分页，实际分页为1
this.ActualPageIndex = 1;
//总页数为1
this.TotalPageCount = 1;
//总行数则为对象实体集合的实体个数
this.TotalRowCount = entities.Count;
//声明一个List来存入构造出的对象(每一行代表一个实体)
this.Rows = new List<object>();
//循环传入的实体集合
foreach (TEntity entity in entities)
{
//声明一个obj的list来存放泛型委托集合中每个泛型委托返回的值
List<string> obj = new List<string>();
//循环泛型委托集合
foreach (Func<TEntity, string> propertyFunc in propertyFuncs)
{
//将每一个泛型委托 的返回值传入obj
obj.Add(propertyFunc(entity));
}
//将每个实体的ID 以及其他属性存入一个row当中
this.Rows.Add(new
{
id = idFunc(entity),
cell = obj.ToArray()
});
}
}
//实际页数
public int ActualPageIndex { get; set; }
//总页数
public int TotalPageCount { get; set; }
//总行数
public int TotalRowCount { get; set; }
//接收每个实体的集合
public List<object> Rows { get; private set; }
//数据组装
public object Build()
{
return new
{
total = this.TotalPageCount,
page = this.ActualPageIndex,
records = this.TotalRowCount,
rows = this.Rows
};
}
/// <summary>
/// MVC 分页 Json数据构造
/// </summary>
/// <param name="pagerResponse"></param>
/// <param name="idFunc"></param>
/// <param name="propertyFuncs"></param>
public BuildJson(
PagerResponse<TEntity> pagerResponse,
Func<TEntity, IdType> idFunc,
params Func<TEntity, string>[] propertyFuncs)
{
this.TotalRowCount = pagerResponse.TotalRowCount;
this.ActualPageIndex = pagerResponse.ActualPageIndex;
this.TotalPageCount = pagerResponse.PageCount;
this.Rows = new List<object>();
foreach (TEntity entity in pagerResponse.Entities)
{
List<string> obj = new List<string>();
foreach (Func<TEntity, string> propertyFunc in propertyFuncs)
{
obj.Add(propertyFunc(entity));
}
this.Rows.Add(
new
{
id = idFunc(entity),
cell = obj.ToArray()
});
}
}
}
}
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections;
using System.Collections.Generic;
using Marlboro.Web.Common;
namespace Marlboro.Web.Extension
{
public class BuildJson<TEntity, IdType>
{
/// <summary>
/// MVC 无分页 Json数据构造
/// </summary>
/// <param name="entities">对象实体集合</param>
/// <param name="idFunc">泛型委托，传入匿名函数lambda 传入TEntity 返回 IdType</param>
/// <param name="propertyFuncs">泛型委托集合 用法同上</param>
public BuildJson(
List<TEntity> entities,
Func<TEntity, IdType> idFunc,
params Func<TEntity, string>[] propertyFuncs
)
{
//无分页，实际分页为1
this.ActualPageIndex = 1;
//总页数为1
this.TotalPageCount = 1;
//总行数则为对象实体集合的实体个数
this.TotalRowCount = entities.Count;
//声明一个List来存入构造出的对象(每一行代表一个实体)
this.Rows = new List<object>();
//循环传入的实体集合
foreach (TEntity entity in entities)
{
//声明一个obj的list来存放泛型委托集合中每个泛型委托返回的值
List<string> obj = new List<string>();
//循环泛型委托集合
foreach (Func<TEntity, string> propertyFunc in propertyFuncs)
{
//将每一个泛型委托 的返回值传入obj
obj.Add(propertyFunc(entity));
}
//将每个实体的ID 以及其他属性存入一个row当中
this.Rows.Add(new
{
id = idFunc(entity),
cell = obj.ToArray()
});
}
}
//实际页数
public int ActualPageIndex { get; set; }
//总页数
public int TotalPageCount { get; set; }
//总行数
public int TotalRowCount { get; set; }
//接收每个实体的集合
public List<object> Rows { get; private set; }
//数据组装
public object Build()
{
return new
{
total = this.TotalPageCount,
page = this.ActualPageIndex,
records = this.TotalRowCount,
rows = this.Rows
};
}
/// <summary>
/// MVC 分页 Json数据构造
/// </summary>
/// <param name="pagerResponse"></param>
/// <param name="idFunc"></param>
/// <param name="propertyFuncs"></param>
public BuildJson(
PagerResponse<TEntity> pagerResponse,
Func<TEntity, IdType> idFunc,
params Func<TEntity, string>[] propertyFuncs)
{
this.TotalRowCount = pagerResponse.TotalRowCount;
this.ActualPageIndex = pagerResponse.ActualPageIndex;
this.TotalPageCount = pagerResponse.PageCount;
this.Rows = new List<object>();
foreach (TEntity entity in pagerResponse.Entities)
{
List<string> obj = new List<string>();
foreach (Func<TEntity, string> propertyFunc in propertyFuncs)
{
obj.Add(propertyFunc(entity));
}
this.Rows.Add(
new
{
id = idFunc(entity),
cell = obj.ToArray()
});
}
}
}
}
这个代码应该很清晰了。
下面就开始介绍VIEW
5、 建立City文件夹 在其下 建立Citylist.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<IEnumerable<Marlboro.Web.Models.CityModels>>" %>
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
Index
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<center>
<%= Html.DropDownList("dplProvince", ViewData["ProvinceList"] as List<SelectListItem>)%></center>
<script type="text/javascript">
var JQGrid = function(provinceId) {
$('#cityList').jqGrid({
url: '<%= Url.Action("Getlist", "City") %>?provinceId=' + provinceId,
datatype: 'json',
colNames: ['Action', 'CityID', 'CityCode', 'CityName', 'ProvinceID',],
colModel: [
{ name: 'customZone', width: '200', sortable: false,align:'center' },
{ name: 'CityID',index:'CityID', sortable: true,align:'center' },
{ name: 'CityCode', index: 'CityCode', editable: true, sortable: true,align:'center'},
{ name: 'CityName', index: 'CityName', editable: true, sortable: true,align:'center' },
{ name: 'ProvinceID', index: 'ProvinceID', editable: true, sortable: true,align:'center' },
],
rowNum: -1,
caption: 'City List',
viewrecords: true,
autowidth: false,
width:700,
height: 'auto',
gridComplete: function() {
//传入行的ID 然后通过editBtn_id的事件来警醒editRow方法
//从而实现了编辑状态
var editEvent = function(id) {
$('#editBtn_' + id).click(function() {
jQuery('#cityList').editRow(id);
return false;
});
}
//同理 获取code和name的值
//调用saveRow这个方法，方法中访问'<%=Url.Action("Edit","City") %>',CityController中的Edit方法来
//来进行保存的
var saveEvent = function(id) {
$('#saveBtn_' + id).click(function() {
var code = $('#' + id + '_CityCode').val();
var name = $('#' + id + '_CityName').val();
if (code == '' || name == '') {
alert('City code/name is required!');
}
/*
else if (name.length > 10) {
alert('Hospital category name must be less than 10 characters!');
}*/
else {
jQuery('#cityList').saveRow(
id,
false,
'<%=Url.Action("Edit","City") %>',
null,
function(id, xhr) {
var msg = xhr.responseText;
if (msg.length == 0) {
alert("Saved successfully!");
} else {
jQuery('#cityList').editRow(id);
alert("Failed because: " + msg);
}
},
function(id, xhr) {
var msg = xhr.getResponseHeader("exceptionMessage");
alert(
"Can not save data because of some unknown exception, HTTP status: " +
xhr.status +
", server exception message: " +
msg);
});
}
return false;
});
}
var cancelEvent = function(id) {
$('#cancelBtn_' + id).click(function() {
jQuery('#cityList').restoreRow(id);
return false;
});
}
//最后把jQgrid中的每一行的事件注册上即可.
var ids = jQuery("#cityList").jqGrid('getDataIDs');
for (var i = 0; i < ids.length; i++) {
var cl = ids[i];
var editBtn = '<a href="javascript:void(0)" class="editBtn" title="Edit" id="editBtn_' + cl + '">Edit</a>';
var saveBtn = '<a href="javascript:void(0)" class="saveBtn" title="Save" id="saveBtn_' + cl + '">Save</a>';
var cancelBtn = '<a href="javascript:void(0)" class="cancelBtn" title="Cancel" id="cancelBtn_' + cl + '">Cancel</a>';
jQuery("#cityList").jqGrid('setRowData', ids[i], { customZone: editBtn + ' | ' + saveBtn + ' | ' + cancelBtn });
editEvent(cl);
saveEvent(cl);
cancelEvent(cl);
}
}
});
};
//这个功能在下一节实现.
var CityCreate = function() {
var provinceId = $('#dplProvince option:selected').val();
if (provinceId == 0) {
alert('Please select a province before creating new city.');
}
else {
window.location.href = '<%= Url.Action("Create","City")%>/' + provinceId;
}
};
$('#dplProvince').find('option:first').attr('selected', true);
JQGrid();
$("#dplProvince").change(function(){
var provinceId = $(this).val();
$("#cityList").setGridParam(
{
url: '<%= Url.Action("Getlist", "City") %>?provinceId=' + provinceId
}
).trigger("reloadGrid");
});
});
</script>
<div style="text-align: center">
<%--显示主数据的Table ID为list--%>
<table id="cityList">
</table>
<%--分页用的DIV ID为pager--%>
<div id="pager">
</div>
</div>
</asp:Content>
<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
Index
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<center>
<%= Html.DropDownList("dplProvince", ViewData["ProvinceList"] as List<SelectListItem>)%></center>
<script type="text/javascript">
var JQGrid = function(provinceId) {
$('#cityList').jqGrid({
url: '<%= Url.Action("Getlist", "City") %>?provinceId=' + provinceId,
datatype: 'json',
colNames: ['Action', 'CityID', 'CityCode', 'CityName', 'ProvinceID',],
colModel: [
{ name: 'customZone', width: '200', sortable: false,align:'center' },
{ name: 'CityID',index:'CityID', sortable: true,align:'center' },
{ name: 'CityCode', index: 'CityCode', editable: true, sortable: true,align:'center'},
{ name: 'CityName', index: 'CityName', editable: true, sortable: true,align:'center' },
{ name: 'ProvinceID', index: 'ProvinceID', editable: true, sortable: true,align:'center' },
],
rowNum: -1,
caption: 'City List',
viewrecords: true,
autowidth: false,
width:700,
height: 'auto',
gridComplete: function() {
//传入行的ID 然后通过editBtn_id的事件来警醒editRow方法
//从而实现了编辑状态
var editEvent = function(id) {
$('#editBtn_' + id).click(function() {
jQuery('#cityList').editRow(id);
return false;
});
}
//同理 获取code和name的值
//调用saveRow这个方法，方法中访问'<%=Url.Action("Edit","City") %>',CityController中的Edit方法来
//来进行保存的
var saveEvent = function(id) {
$('#saveBtn_' + id).click(function() {
var code = $('#' + id + '_CityCode').val();
var name = $('#' + id + '_CityName').val();
if (code == '' || name == '') {
alert('City code/name is required!');
}
/*
else if (name.length > 10) {
alert('Hospital category name must be less than 10 characters!');
}*/
else {
jQuery('#cityList').saveRow(
id,
false,
'<%=Url.Action("Edit","City") %>',
null,
function(id, xhr) {
var msg = xhr.responseText;
if (msg.length == 0) {
alert("Saved successfully!");
} else {
jQuery('#cityList').editRow(id);
alert("Failed because: " + msg);
}
},
function(id, xhr) {
var msg = xhr.getResponseHeader("exceptionMessage");
alert(
"Can not save data because of some unknown exception, HTTP status: " +
xhr.status +
", server exception message: " +
msg);
});
}
return false;
});
}
var cancelEvent = function(id) {
$('#cancelBtn_' + id).click(function() {
jQuery('#cityList').restoreRow(id);
return false;
});
}
//最后把jQgrid中的每一行的事件注册上即可.
var ids = jQuery("#cityList").jqGrid('getDataIDs');
for (var i = 0; i < ids.length; i++) {
var cl = ids[i];
var editBtn = '<a href="javascript:void(0)" class="editBtn" title="Edit" id="editBtn_' + cl + '">Edit</a>';
var saveBtn = '<a href="javascript:void(0)" class="saveBtn" title="Save" id="saveBtn_' + cl + '">Save</a>';
var cancelBtn = '<a href="javascript:void(0)" class="cancelBtn" title="Cancel" id="cancelBtn_' + cl + '">Cancel</a>';
jQuery("#cityList").jqGrid('setRowData', ids[i], { customZone: editBtn + ' | ' + saveBtn + ' | ' + cancelBtn });
editEvent(cl);
saveEvent(cl);
cancelEvent(cl);
}
}
});
};
//这个功能在下一节实现.
var CityCreate = function() {
var provinceId = $('#dplProvince option:selected').val();
if (provinceId == 0) {
alert('Please select a province before creating new city.');
}
else {
window.location.href = '<%= Url.Action("Create","City")%>/' + provinceId;
}
};
//实现选择DDL 来ReloadjQgrid
$(function() {$('#dplProvince').find('option:first').attr('selected', true);
JQGrid();
$("#dplProvince").change(function(){
var provinceId = $(this).val();
$("#cityList").setGridParam(
{
url: '<%= Url.Action("Getlist", "City") %>?provinceId=' + provinceId
}
).trigger("reloadGrid");
});
});
</script>
<div style="text-align: center">
<%--显示主数据的Table ID为list--%>
<table id="cityList">
</table>
<%--分页用的DIV ID为pager--%>
<div id="pager">
</div>
</div>
</asp:Content>
jQgrid的属性就不说了。
如果不是很清晰 参考
Edit、Cancel、Save 3个方法
基本都是在
gridComplete
这个属性中写。
这也是jQgrid强大的地方。 这篇文章介绍的是在不存在分页的jQgrid。
下一篇介绍有分页的jQgrid. 样式以及框架在以后调整...
最后赋上 ORACLEHelper类+连接字符串。哈哈
OracleHelper.csusing System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Configuration;
using System.Data;
namespace DataBaseTest
{
public class OracleHelper
{
public static OracleConnection cn = null;
public OracleHelper()
{
}
public OracleConnection Open()
{
cn = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.171.142.12) (PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=****)));Persist Security Info=True;User Id=****; Password=****");
cn.Open();
return (cn);
}
public void Close()
{
cn.Close();
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="CmdString"></param>
/// <param name="TableName"></param>
/// <returns></returns>
public DataSet GetDataSet(string CmdString)
{
Open();
OracleDataAdapter myDa = new OracleDataAdapter();
myDa.SelectCommand = new OracleCommand(CmdString, cn);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
Close();
return myDs;
}
public void ExcuteCity(string sql)
{
Open();
OracleCommand cmd = new OracleCommand(sql, cn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception er)
{
throw er;
}
finally
{
Close();
}
}
/// <summary>
/// get datatable by the cmdstring
/// </summary>
/// <param name="CmdString"></param>
/// <returns></returns>
public DataTable GetDataTable(string CmdString)
{
Open();
DataTable dt = new DataTable();
OracleDataReader dr = null;
OracleCommand cmd = new OracleCommand();
cmd.CommandText = CmdString;
cmd.Connection = cn;
using (dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(dr);
}
Close();
return dt;
}
/// <summary>
/// 执行一个查询,并返回查询结果
/// </summary>
/// <param name="commandText">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
/// <param name="parameters">PL-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string commandText)
{
Open();
DataSet data = new DataSet();//实例化DataTable，用于装载查询结果集
using (OracleCommand command = new OracleCommand(commandText, cn))
{
//通过包含查询SQL的SqlCommand实例来实例化SqlDataAdapter
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(data);//填充DataTable
}
Close();
return data.Tables[0];
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Configuration;
using System.Data;
namespace DataBaseTest
{
public class OracleHelper
{
public static OracleConnection cn = null;
public OracleHelper()
{
}
public OracleConnection Open()
{
cn = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.171.142.12) (PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=****)));Persist Security Info=True;User Id=****; Password=****");
cn.Open();
return (cn);
}
public void Close()
{
cn.Close();
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="CmdString"></param>
/// <param name="TableName"></param>
/// <returns></returns>
public DataSet GetDataSet(string CmdString)
{
Open();
OracleDataAdapter myDa = new OracleDataAdapter();
myDa.SelectCommand = new OracleCommand(CmdString, cn);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
Close();
return myDs;
}
public void ExcuteCity(string sql)
{
Open();
OracleCommand cmd = new OracleCommand(sql, cn);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception er)
{
throw er;
}
finally
{
Close();
}
}
/// <summary>
/// get datatable by the cmdstring
/// </summary>
/// <param name="CmdString"></param>
/// <returns></returns>
public DataTable GetDataTable(string CmdString)
{
Open();
DataTable dt = new DataTable();
OracleDataReader dr = null;
OracleCommand cmd = new OracleCommand();
cmd.CommandText = CmdString;
cmd.Connection = cn;
using (dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(dr);
}
Close();
return dt;
}
/// <summary>
/// 执行一个查询,并返回查询结果
/// </summary>
/// <param name="commandText">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
/// <param name="parameters">PL-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string commandText)
{
Open();
DataSet data = new DataSet();//实例化DataTable，用于装载查询结果集
using (OracleCommand command = new OracleCommand(commandText, cn))
{
//通过包含查询SQL的SqlCommand实例来实例化SqlDataAdapter
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(data);//填充DataTable
}
Close();
return data.Tables[0];
}
}
}
母版页中
<%@ Master Language="C#" Inherits="System.Web.Mvc.ViewMasterPage" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>
<asp:ContentPlaceHolder ID="TitleContent" runat="server" />
</title>
<%--CSS文件--%>
<%--引用Custom CSS文件--%>
<link href="http://www.cnblogs.com/Content/Themes/dark-hive/jquery-ui-1.8.6.custom.css" rel="stylesheet"
type="text/css" />
<%--第二套CSS文件--%>
<%--<link href="http://www.cnblogs.com/Content/Themes/ui-lightness/jquery-ui-1.8.6.custom.css" rel="stylesheet"
type="text/css" />--%>
<%--Jquery-ui的jqgrid CSS文件--%>
<link href="http://www.cnblogs.com/Content/ui.jqgrid.css" rel="stylesheet" type="text/css" />
<%--jQuery主文件 这里用1.4.2版本--%>
<script src="http://www.cnblogs.com/Scripts/jquery-1.4.2.min.js" type="text/javascript"></script>
<%--语言包，语言包一定要放在 jQgrid.min.js 前 这里引用英文--%>
<script src="http://www.cnblogs.com/Scripts/i18n/grid.locale-en.js" type="text/javascript"></script>
<%--最后就是jQgrid的JS文件了--%>
<script src="http://www.cnblogs.com/Scripts/jquery.jqGrid.min.js" type="text/javascript"></script>
</head>
<body>
<div class="page">
<div id="header">
<div id="title" style="text-align:center">
<h1>
Hi~ jqGrid</h1>
</div>
</div>
<div id="main">
<asp:ContentPlaceHolder ID="MainContent" runat="server" />
<div id="footer">
</div>
</div>
</div>
</body>
</html>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>
<asp:ContentPlaceHolder ID="TitleContent" runat="server" />
</title>
<%--CSS文件--%>
<%--引用Custom CSS文件--%>
<link href="http://www.cnblogs.com/Content/Themes/dark-hive/jquery-ui-1.8.6.custom.css" rel="stylesheet"
type="text/css" />
<%--第二套CSS文件--%>
<%--<link href="http://www.cnblogs.com/Content/Themes/ui-lightness/jquery-ui-1.8.6.custom.css" rel="stylesheet"
type="text/css" />--%>
<%--Jquery-ui的jqgrid CSS文件--%>
<link href="http://www.cnblogs.com/Content/ui.jqgrid.css" rel="stylesheet" type="text/css" />
<%--jQuery主文件 这里用1.4.2版本--%>
<script src="http://www.cnblogs.com/Scripts/jquery-1.4.2.min.js" type="text/javascript"></script>
<%--语言包，语言包一定要放在 jQgrid.min.js 前 这里引用英文--%>
<script src="http://www.cnblogs.com/Scripts/i18n/grid.locale-en.js" type="text/javascript"></script>
<%--最后就是jQgrid的JS文件了--%>
<script src="http://www.cnblogs.com/Scripts/jquery.jqGrid.min.js" type="text/javascript"></script>
</head>
<body>
<div class="page">
<div id="header">
<div id="title" style="text-align:center">
<h1>
Hi~ jqGrid</h1>
</div>
</div>
<div id="main">
<asp:ContentPlaceHolder ID="MainContent" runat="server" />
<div id="footer">
</div>
</div>
</div>
</body>
</html>
文章中的相关下载（jQgrid包、两套样式、源码）
欢迎大家拍砖。。。。
