EasyUI笔记（六）数据表格
前言
用asp.net结合easyui做个一个数据网格的页面，包括对数据的增删改查，界面如下：
用asp.net结合easyui做个一个数据网格的页面，包括对数据的增删改查，界面如下：
一、UI界面
先写UI界面，代码如下，记得引入相关js和css文件
<body><table id="tb1"></table><div id="tb" style="padding:5px"><div style="margin-bottom:5px"><a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-reload'" onclick="reload()">刷新</a><a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-add'" onclick="Add()">添加</a><a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-edit'" onclick="edit()">修改</a><a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-remove'" onclick="destroy()">删除</a></div><div>查询名称：<input type="text" id="Eq" name="Eq" style="width:150px"/><select id="order" class="easyui-combobox" style="width:100px"><option value="asc">升序</option><option value="desc">降序</option></select><a href="#" class="easyui-linkbutton" data-options="iconCls:'icon-search'" onclick="obj.search()">查询</a></div></div><%-- 弹窗 Start--%><div id="dlg" class="easyui-dialog" style="width:400px;height:320px;padding:10px 20px" data-options="closed:true,buttons:'#dlg-buttons'"><div class="ftitle">设备信息</div><form id="fm" method="post" novalidate="novalidate"><div class="fitem"><label>产品编号:</label><input id="ProductID" name="ProductID" class="easyui-validatebox" data-options="required:false"/></div><div class="fitem"><label>产品名称:</label><input id="Name" name="Name" class="easyui-validatebox" data-options="required:true"/></div><div class="fitem"><label>描述:</label><input id="Description" name="Description" class="easyui-validatebox" data-options="required:true"/></div><div class="fitem"><label>种类:</label><input id="Category" name="Category" class="easyui-validatebox" data-options="required:true"/></div><div class="fitem"><label>价格:</label><input id="Price" name="Price" class="easyui-validatebox" data-options="required:true,validType:'intOrFloat'"/></div></form></div><div id="dlg-buttons"><a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-ok'" onclick="save()" style="width:90px">Save</a><a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-cancel'" onclick="javascript:$('#dlg').dialog('close')" style="width:90px">Cancel</a></div><%-- 弹窗 End --%></body>
二、JS加载
关于datagrid的加载，我用js，要记住列名要和数据源对应
$(function () {$('#tb1').datagrid({url: '../Handler/GetDataFormSql.ashx',width: 1200,title: '管理',method: 'get',toolbar: '#tb',//rownumbers: true,singleSelect:true,columns: [[{ field: 'ProductID', title: '编号', width: 150 },{ field: 'Name', title: '名称', width: 150 },{ field: 'Description', title: '描述', width: 250 },{ field: 'Category', title: '种类', width: 150 },{ field: 'Price', title: '价格', width: 150 }]],pagination: true,pageSize: 20,pageList: [20, 30 , 40]});})
三、一般处理程序
GetDataFormSql.ashx 文件内容：
public void ProcessRequest(HttpContext context){context.Response.ContentType = "text/plain";int page = 1, rows = 10;//EasyUI自带的两个参数rows与page ,表示当前页与行数if (context.Request.QueryString["rows"] != null){rows = int.Parse(context.Request.QueryString["rows"].ToString().Trim());}if (context.Request.QueryString["page"] != null){page = int.Parse(context.Request.QueryString["page"].ToString().Trim());}//查询分页 stratIndex endIndexint stratIndex, endIndex;stratIndex = (page - 1) * rows + 1;endIndex = page * rows;//查询排序 key orderstring key = "", order = "desc";if (context.Request.QueryString["key"] != null){key = context.Request.QueryString["key"].ToString().Trim();}if (context.Request.QueryString["order"] != null){order = context.Request.QueryString["order"].ToString().Trim();}//设置模糊查询StringBuilder sqlWhere = new StringBuilder();if (key != "") {sqlWhere.AppendFormat("upper(ProductID) like '%{0}%' or "+"upper(Name) like '%{0}%' or "+"upper(Description) like '%{0}%' or " +"upper(Category) like '%{0}%' or " +"upper(Price) like '%{0}%'",key);}//查询数据库DAL.SqlHelper sqlhelper = new DAL.SqlHelper();//获取查询数据的行数int count = sqlhelper.EUGetRecordCount(sqlWhere.ToString());//封装数据到datasetDataSet ds = sqlhelper.GetListByPagebykey(sqlWhere.ToString(), order, stratIndex, endIndex);//将dataset转化为Json格式string strToJon = DAL.ToJson.DatasetJson(ds, count);context.Response.Write(strToJon);context.Response.End();}
三、DAL数据访问层
接下来就是数据访问层SqlHelper的代码
public int EUGetRecordCount(string key){StringBuilder strSql = new StringBuilder();strSql.Append("select count(1) FROM Products ");if (key.Trim() != ""){strSql.Append("where " + key);}object obj = DBHelper.GetSingle(strSql.ToString());if (obj == null){return 0;}else{return Convert.ToInt32(obj);}}public DataSet GetListByPagebykey(string Key, string order, int startIndex, int endIndex){StringBuilder strSql = new StringBuilder();strSql.Append("SELECT * FROM ( ");strSql.Append(" SELECT ROW_NUMBER() OVER (");if (!string.IsNullOrEmpty(Key.Trim())){strSql.Append("order by T." + Key+" " + order);}else{strSql.Append("order by T.ProductID asc");}strSql.Append(")AS Row, T.* from Products T ");if (!string.IsNullOrEmpty(Key.Trim())){strSql.Append(" WHERE " + Key);}strSql.Append(" ) TT");strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);return DBHelper.Query(strSql.ToString());}
四、辅助方法
还需要dataset转json的代码，其实这个可以从网上找到
public class ToJson{/// <summary>/// DataSet转换成Json格式/// </summary>/// <param name="ds">DataSet</param>/// <returns></returns>public static string DatasetJson(DataSet ds, int total = -1){StringBuilder json = new StringBuilder();foreach (DataTable dt in ds.Tables){json.Append("{\"total\":");if (total == -1){json.Append(dt.Rows.Count);}else{json.Append(total);}json.Append(",\"rows\":");json.Append(DataTableJson(dt));json.Append("]}");} return json.ToString();}private static string DataTableJson(DataTable dt){StringBuilder jsonBuilder = new StringBuilder();jsonBuilder.Append("[");for (int i = 0; i < dt.Rows.Count; i++){jsonBuilder.Append("{");for (int j = 0; j < dt.Columns.Count; j++){jsonBuilder.Append("\"");jsonBuilder.Append(dt.Columns[j].ColumnName);jsonBuilder.Append("\":\"");jsonBuilder.Append(dt.Rows[i][j].ToString());jsonBuilder.Append("\",");}jsonBuilder.Remove(jsonBuilder.Length - 1, 1);jsonBuilder.Append("},");}jsonBuilder.Remove(jsonBuilder.Length - 1, 1);return jsonBuilder.ToString();}}
到这就大致实现了网格加载的功能，还剩四个增删改查的按钮功能，这里只列出增加数据的数据访问层代码，其中应用了SqlParameter参数化SQL，可以防止SQL注入
public int Add(Products model){StringBuilder strSql = new StringBuilder();strSql.Append("insert into Products(");strSql.Append("Name,Description,Category,Price)");strSql.Append(" values (");strSql.Append("@Name,@Description,@Category,@Price)");strSql.Append(";select @@IDENTITY");SqlParameter[] parameters = {new SqlParameter("@Name", SqlDbType.NVarChar,255),new SqlParameter("@Description", SqlDbType.NVarChar,255),new SqlParameter("@Category", SqlDbType.NVarChar,255),new SqlParameter("@Price ", SqlDbType.Decimal,255),};//parameters[0].Value = model.ProductID;parameters[0].Value = model.Name;parameters[1].Value = model.Description;parameters[2].Value = model.Category;parameters[3].Value = model.Price;object obj = DBHelper.GetSingle(strSql.ToString(), parameters);if (obj == null){return 0;}else{return Convert.ToInt32(obj);}}
