实现功能:行内修改,批量行内添加,多条件查询;(查询当中不是联查)
JavaScript:
$(function(){
<span style="font-family: Arial, Helvetica, sans-serif;"> initGrid8();</span>
<span style="font-family: Arial, Helvetica, sans-serif;"> });</span>
//定义初始化方法
function initGrid8() {
//初始化comblbox数据
initComoboxData();
//给姓名录入框添加清楚图标
$('#txtname').textbox().textbox('addClearBtn', 'icon-clear');
//为日期组合框设置默认值
var now = new Date();
var time1 = now.getFullYear()-50 + "-" + (now.getMonth()+1).toString() + "-" + now.getDate().toString();
var time2 = now.getFullYear() + "-" + (now.getMonth() + 1).toString() + "-" + now.getDate().toString();
$("#datebox1").datebox('setValue', time1);
$("#datebox2").datebox('setValue', time2);
//各种查询
$("#btn_save").linkbutton({ onClick: function () { grid8Accept(); return false; }});
//$("#btn_search").linkbutton({ onClick: function () { doSearch(); } });
$("#cmbGender").combobox({ onChange: function () { doSearch(); } });
//$("#deptCombobox8").combobox({ onChange: function () { doSearch(); } });
//$("#deptCombobox8").combobox({ onChange: function () { alert(); doSearch(); } });
//$("#datebox1").datebox({ onChange: function (nValue, oValue) { if (oValue != null && oValue != '') { doSearch(); }}});
//主体:
$("#dg8").datagrid({
height: 300,
width: 800,
title: '自定义demo1',
fitColumns: true,
toolbar: '#tb8',
url: '/Home/grid8GetData',
queryParams: tbForm8Json("tbForm8"),
rownumbers: true,
pagination: true,
pageNumber:1,
sortName: 'NAME',
sortOrder: 'desc',
multiSort: true,
pageList: [5,10,15,20,30,50],
pageSize:10,
columns: [[
{ field: 'ck', checkbox: true },
{ field: 'EMPID', title: '员工编号', width: 80, align: 'CENTER',sortable:true},
{
field: 'NAME', title: '姓名', width: 120, align: 'CENTER', sortable: true,
editor: { type: 'text' }
},
{
field: 'GENDER', title: '性别', width: 80, align: 'CENTER', sortable: true,
editor: { type: 'combobox', options: { textField: 'label', valueField: 'value', data: [{ label: '男', value: '1' }, { label: '女', value: '0' }] } }
},
{
field: 'BIRTHDAY', title: '出生日期', width: 80, align: 'CENTER', sortable: true,
editor: { type: 'datebox' }
},
{
field: 'DEPTNO', title: '部门', width: 80, align: 'CENTER', sortable: true,
editor: {
type: 'combobox', options: {
textField: 'NAME', valueField: 'DEPTID', url: '/Home/initCombobox8Data',
onLoadSuccess: function (res) {
var str = JSON.parse(res);
if (editIndex != undefined)
{
var ed = $('#dg8').datagrid('getEditor', { index: editIndex, field: 'DEPTNO' });
$(ed.target).combobox('loadData', str);
}
if (addRowsCount > 0)
{
var ed = $('#dg8').datagrid('getEditor', { index: 0, field: 'DEPTNO' });
$(ed.target).combobox('loadData', str);
}
}
}
}
}
]],
onLoadSuccess: function (res) {
},
onDblClickRow: function (index,row){
grid8OnClickRow(index,row);
}
});
}
//文本框添加清除图标方法
$.extend($.fn.textbox.methods, {
addClearBtn: function (jq, iconCls) {
return jq.each(function () {
var t = $(this);
var opts = t.textbox('options');
opts.icons = opts.icons || [];
opts.icons.unshift({
iconCls: iconCls,
handler: function (e) {
$(e.data.target).textbox('clear').textbox('textbox').focus();
$(this).css('visibility', 'hidden');
}
});
t.textbox();
if (!t.textbox('getText')) {
t.textbox('getIcon', 0).css('visibility', 'hidden');
}
t.textbox('textbox').bind('keyup', function () {
var icon = t.textbox('getIcon', 0);
if ($(this).val()) {
icon.css('visibility', 'visible');
} else {
icon.css('visibility', 'hidden');
}
});
});
}
});
//文本框搜索事件:
function doSearch() {
$("#dg8").datagrid('reload', tbForm8Json("tbForm8"));
};
//添加行
function doAdd() {
$("#dg8").datagrid('insertRow', {
index: 0, row: {
EMPID: 88,
NAME:'',
GENDER:1,
BIRTHDAY:'',
DEPTNO:1
}
}).datagrid('selectRow', 0).datagrid('beginEdit', 0);
if (editIndex != undefined)
{
editIndex += 1;
}
addRowsCount += 1;
};
//为表单数据转为json:
function tbForm8Json(id) {
var arr = $("#" + id).serializeArray()
var jsonStr = "";
jsonStr += '{';
for (var i = 0; i < arr.length; i++) {
jsonStr += '"' + arr[i].name + '":"' + arr[i].value + '",'
}
jsonStr = jsonStr.substring(0, (jsonStr.length - 1));
jsonStr += '}'
var json = JSON.parse(jsonStr);
return json
}
//combobox初始化方法
function initComoboxData() {
$("#deptCombobox8").combobox({
url: '/Home/initCombobox8Data',
valueField: 'DEPTID',
textField: 'NAME',
width:70,
onLoadSuccess: function (res) {
var str = JSON.parse(res);
var a = {};
a.DEPTID = '-1';
a.NAME = "不限";
//数组中插入对象
str.unshift(a);
$("#deptCombobox8").combobox('loadData', str);
},
onChange: function (newValue,oldValue) {
if (oldValue != null && oldValue != '') {
doSearch();
}
}
})
$('#deptCombobox8').combobox('select', '-1');
}
//行内编辑所需各个方法:
function grid8EndEditing() {
if (editIndex == undefined) { return true }
if ($('#dg8').datagrid('validateRow', editIndex)) {
$('#dg8').datagrid('endEdit', editIndex);
editIndex = undefined;
return true;
} else {
return false;
}
}
//添加grid的双击行事件方法
function grid8OnClickRow(index,row) {
if (editIndex != index) {
if (grid8EndEditing()) {
$('#dg8').datagrid('selectRow', index)
.datagrid('beginEdit', index);
editIndex = index;
} else {
$('#dg8').datagrid('selectRow', editIndex);
}
}
}
function grid8Accept() {
//关掉编辑行及添加行的编辑状态
$('#dg8').datagrid('endEdit', editIndex);
for (var i = 0; i < addRowsCount; i++)
{
$('#dg8').datagrid('endEdit', i);
}
if (grid8EndEditing()) {
//手动添加保存代码
var rows = $('#dg8').datagrid('getChanges');
var strArr = new Array();
strArr[0] = "EMPID";
strArr[1] = "NAME";
strArr[2] = "GENDER";
strArr[3] = "BIRTHDAY";
strArr[4] = "DEPTNO";
var employees = JSON.stringify(rows, strArr);
if ($.trim(employees) != "[]") {
$.post('/Home/grid8SaveEditions', employees, function (res) {
var resObj = eval('(' + res + ')');
var resTxt = "共添加:" + resObj.addTotal + " 成功:" + resObj.addSuccessCount + "\n共修改:" + resObj.editTotal + " 成功:" + resObj.editSuccessCount;
alert(resTxt);
}, 'text');
$('#dg8').datagrid('acceptChanges');
}
else {
alert("您没有做任何添加或修改,请先操作再保存");
};
}
}
controller:
public string grid8GetData()
{
//获取datagrid排序和分页信息:
string sortName=Request.Form["sort"];
string sortOrder=Request.Form["order"];
int pageIndex = Convert.ToInt32(Request.Form["page"]);
int pageSize = Convert.ToInt32(Request.Form["rows"]);
//字符串转换数据库日期格式:(需要命名空间System.Globalization)
DateTimeFormatInfo dtFormat = new System.Globalization.DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy-mm-dd";
List<OracleParameter> parmList =new List<OracleParameter> {
new OracleParameter(":NAME","%"+Request.Form["name"]+"%"),
//new OracleParameter(":GENDER",Convert.ToInt16( Request.Form["gender"])),
//new OracleParameter(":DEPTNO",Convert.ToInt32( Request.Form["deptname"])),
new OracleParameter(":STIME",Convert.ToDateTime(Request.Form["stime"],dtFormat)),
new OracleParameter(":ETIME",Convert.ToDateTime(Request.Form["etime"],dtFormat))
};
StringBuilder sb= new StringBuilder(" ");
if(Request.Form["gender"]!="-1")
{
parmList.Add(new OracleParameter(":GENDER", Convert.ToInt16(Request.Form["gender"])));
sb.Append("and gender=:GENDER ");
}
if (Request.Form["deptname"] != "-1")
{
parmList.Add( new OracleParameter(":DEPTNO", Convert.ToInt32(Request.Form["deptname"])));
sb.Append("and deptno=:DEPTNO");
}
string where = sb.ToString();
int total = 0;
DataSet ds = grid8Service.GetEmployeesByPaging(where,sortName,sortOrder,pageSize,pageIndex,out total,parmList.ToArray() );
//利用hashtable容器为datagrid提供元数据
Hashtable hs = new Hashtable();
var formatter = new IsoDateTimeConverter();
formatter.DateTimeFormat = "yyyy-MM-dd";
hs.Add("total", total);
hs.Add("rows", ds.Tables[0]);
//序列化约束:
return JsonConvert.SerializeObject(hs, formatter);
//string jsonStr = JsonConvert.SerializeObject(ds.Tables[0],formatter);
//return jsonStr;
}
public ActionResult initCombobox8Data()
{
DataSet ds = grid8Service.GetAllDept();
string jsonStr = JsonConvert.SerializeObject(ds.Tables[0]);
return Json(jsonStr);
}
public string grid8SaveEditions()
{
//手写表单提交字符串转化对象集合:
string data = Request.Form[0];
data = data.Replace('[', ' ');
data = data.Replace(']', ' ');
string[] sss = { "}," };
string[] ss = data.Split(sss, StringSplitOptions.None);
int i = ss.Length;
int lastStrLength = ss[i - 1].Length;
ss[i - 1] = ss[i - 1].Remove(lastStrLength - 2, 1);
List<Employee_wcj> emps = new List<Employee_wcj>();
foreach (string s in ss)
{
string s1 = s + "}";
Employee_wcj emp = JsonConvert.DeserializeObject<Employee_wcj>(s1);
emps.Add(emp);
}
string res = grid8Service.grid8SaveEditions(emps);
return res;
}
服务层:
public class WCJgrid8
{
public DataSet GetAllDept()
{
string sql = "select * from department_w ";
return OracleHelper.Query(sql);
}
public DataSet GetEmployeesByPaging(string where, string sortName,string sortOrder, int pageSize, int currentPage, out int resultCount, OracleParameter[] parms)
{
String sql = @"select * from employee_w where NAME LIKE :NAME and BIRTHDAY between :STIME AND :ETIME"+where;
//String sql = @"select emp.empid,emp.name as emp_name,emp.gender,emp.birthday,emp.deptno,dept.name dept_name from employee_w AS emp join employee_w AS dept on emp.deptno=dept.deptid where emp.NAME LIKE :NAME and emp.BIRTHDAY between :STIME AND :ETIME" + where;
StringBuilder sortStr = new StringBuilder();
string orderby = "";
if (!string.IsNullOrEmpty(sortName))
{
if (sortName.Contains(','))
{
for (int i = 0; i < sortName.Split(',').Length; i++)
{
sortStr .Append( sortName.Split(',')[i] + " " + sortOrder.Split(',')[i] + ",");
}
sortStr = sortStr.Remove(sortStr.Length-1,1);
}
else
{
sortStr .Append( sortName + " " + sortOrder);
}
orderby = sortStr.ToString();
}
else
{
orderby = "";
}
return OracleHelper.FindPageBySql(sql, orderby, pageSize, currentPage, out resultCount, parms);
}
public string grid8SaveEditions( List<Employee_wcj> emps)
{
int editTotal = 0;
int addTotal = 0;
int editSuccessCount = 0;
int addSuccessCount = 0;
foreach(Employee_wcj emp in emps)
{
if (emp.EMPID != 88)
{
editTotal += 1;
editSuccessCount += grid8SaveUpdate(emp);
}
else
{
addTotal += 1;
addSuccessCount += grid8SaveAdd(emp);
}
}
return "{editTotal:" + editTotal.ToString() + ",addTotal:" + addTotal.ToString() + ",editSuccessCount:" + editSuccessCount.ToString() + ",addSuccessCount:" + addSuccessCount.ToString() + "}";
}
public int grid8SaveUpdate(Employee_wcj emp)
{
//string sql = "update employee_w set
name='"+emp.NAME+"',gender="+emp.GENDER+",birthday='"+emp.BIRTHDAY+"',deptno="+emp.DEPTNO.ToString()+" where empid="+emp.EMPID.ToString()+";";
//int rows = OracleHelper.ExecuteNonQuery(sql,CommandType.Text);
string sql = "update employee_w set name=:name ,gender=:gender, birthday=:birthday,deptno=:deptno where empid=:empid";
DateTimeFormatInfo dtFormat = new System.Globalization.DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy-MM-dd";
OracleParameter[] para = {
new OracleParameter(":name",emp.NAME),
new OracleParameter(":gender",int.Parse(emp.GENDER)),
new OracleParameter(":birthday", Convert.ToDateTime(emp.BIRTHDAY,dtFormat )),
new OracleParameter(":deptno",Convert.ToInt32(emp.DEPTNO)),
new OracleParameter(":empid",Convert.ToInt32(emp.EMPID))
};
int rows = OracleHelper.ExecuteNonQuery(sql, CommandType.Text, para);
return rows;
}
public int grid8SaveAdd(Employee_wcj emp)
{
//参数数组和todate()方法结合使用的sql语句
//string sql = "insert into employee_w values("+emp.EMPID.ToString()+",'"+emp.NAME+"',"+emp.GENDER.ToString()+",to_date('"+emp.BIRTHDAY+"','yyyy-mm-dd'),"+emp.DEPTNO.ToString()+");";
//int rows = OracleHelper.ExecuteNonQuery(sql,CommandType.Text);
string sql = "insert into employee_w values(:empid,:name,:gender,to_date(:birthday,'yyyy-mm-dd'),:deptno)";
DateTimeFormatInfo dtFormat = new System.Globalization.DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy-MM-dd";
OracleParameter[] para = {
new OracleParameter(":name",emp.NAME),
new OracleParameter(":gender",int.Parse(emp.GENDER)),
new OracleParameter(":birthday", Convert.ToDateTime(emp.BIRTHDAY,dtFormat )),
new OracleParameter(":deptno",Convert.ToInt32(emp.DEPTNO)),
new OracleParameter(":empid",Convert.ToInt32(emp.EMPID))
};
int rows = OracleHelper.ExecuteNonQuery(sql, CommandType.Text, para);
return rows;
}
}
文章来源:
http://blog.csdn.net/wcj920946195