1.Easyui 的官网只是给出了客户端分页的demo。本人觉得客户端分页并不能很好的解决项目中的实际需求，所以，在此记录一下Easyui treegrid服务端分页。
2.Easyui treegrid服务端分页其实是对父节点等于0有多少条数据的分页，先把父节点等于0的数据全部取出来，在根据父节点取出所有子节点的数据。
3.返回的Json格式例子如下：
{"total":9,"rows":[
{"id":1,"region":"Wyoming"},
{"id":11,"region":"Albin","f1":2000,"f2":1800,"f3":1903,"f4":2183,"f5":2133,"f6":1923,"f7":2018,"f8":1838,"_parentId":1},
{"id":12,"region":"Canon","f1":2000,"f2":1800,"f3":1903,"f4":2183,"f5":2133,"f6":1923,"f7":2018,"f8":1838,"_parentId":1},
{"id":13,"region":"Egbert","f1":2000,"f2":1800,"f3":1903,"f4":2183,"f5":2133,"f6":1923,"f7":2018,"f8":1838,"_parentId":1},
{"id":2,"region":"Washington"},
{"id":21,"region":"Bellingham","f1":2000,"f2":1800,"f3":1903,"f4":2183,"f5":2133,"f6":1923,"f7":2018,"f8":1838,"_parentId":2},
{"id":22,"region":"Chehalis","f1":2000,"f2":1800,"f3":1903,"f4":2183,"f5":2133,"f6":1923,"f7":2018,"f8":1838,"_parentId":2},
{"id":23,"region":"Ellensburg","f1":2000,"f2":1800,"f3":1903,"f4":2183,"f5":2133,"f6":1923,"f7":2018,"f8":1838,"_parentId":2},
{"id":24,"region":"Monroe","f1":2000,"f2":1800,"f3":1903,"f4":2183,"f5":2133,"f6":1923,"f7":2018,"f8":1838,"_parentId":2}
]}
4.返回的数据中如果父级节点等于0，不添加"_parentId"属性，如果不等于0，则添加"_parentId"属性
代码记录仅供自己参考：
数据层：
/// <summary> /// 获取数据字典分类中ParentID=0的数据 /// </summary> /// <param name="dbContext">数据库连接</param> /// <param name="startIndex">开始索引</param> /// <param name="endIndex">结束索引</param> /// <param name="pid">父级id</param> /// <param name="categoryId">分类id</param> /// <returns></returns> public static DataSet GetDataDicDataSet(this DbContext dbContext, int startIndex, int endIndex, string pid, int categoryId) { StringBuilder sql = new StringBuilder(); sql.Append(" SELECT *"); sql.Append(" FROM (SELECT ROW_NUMBER() OVER(ORDER BY T.KEYID) AS TROW, T.*"); sql.Append(" FROM A_DICS T"); sql.AppendFormat(" WHERE T.PARENTID = '{0}'", pid); sql.AppendFormat(" AND CATEGORYID = {0}) TT", categoryId); // sql.Append(" CONNECT BY PRIOR T.KEYID = T.PARENTID) TT"); sql.AppendFormat(" WHERE TT.TROW BETWEEN {0} AND {1}", startIndex, endIndex); //创建command var cmd = CreateCommand(dbContext, sql.ToStr(), null); //使用liger.Data内置的数据适配器预处理command DataSet ds = dbContext.Db.ExecuteDataSet(cmd); return ds; } /// <summary> /// 获取ParentID=0 and categoryID=categoryId的总条数 /// </summary> /// <param name="dbContext">创建数据库连接</param> /// <param name="pid">父级ID</param> /// <param name="categoryId">分类ID</param> /// <returns></returns> public static int GetDataDicCount(this DbContext dbContext, string pid, int categoryId) { StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT COUNT(1) FROM A_DICS WHERE PARENTID='0' AND CATEGORYID={0}", categoryId); //创建command var cmd = CreateCommand(dbContext, sql.ToStr(), null); //使用liger.Data内置的数据适配器预处理command DataSet ds = dbContext.Db.ExecuteDataSet(cmd); if (ds != null) { return Convert.ToInt32(ds.Tables[0].Rows[0][0]); } else { return 0; } }
Json格式转换：
#region treegrid分页 /// <summary> /// DataSet转换成Json格式 /// </summary> /// <param name="ds">DataSet</param> /// <returns></returns> public static string DatasetToTreeGridJson(DataSet ds, int total = -1) { StringBuilder json = new StringBuilder(); if (ds != null) { 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(DataTableToTreegridJson(dt)); json.Append("]}"); } } else { json.Append("{\"total\":"); json.Append("0"); json.Append(",\"rows\":["); json.Append("]}"); } return json.ToString(); } /// <summary> /// 根据父级Id找到对应所有子节点数据 /// </summary> /// <param name="pid">父级id</param> /// <param name="categoryid">分类id</param> /// <returns></returns> public static DataTable GetDataFromParentId(string pid, string categoryid) { StringBuilder sql = new StringBuilder(); sql.Append(" SELECT *"); sql.Append(" FROM A_DICS T"); sql.AppendFormat(" START WITH T.PARENTID ='{0}'", pid); sql.AppendFormat(" AND CATEGORYID = {0}", categoryid); sql.Append(" CONNECT BY PRIOR T.KEYID = T.PARENTID"); DataSet ds = DbHelper.Db.Db.ExecuteDataSet(CommandType.Text, sql.ToString()); return ds.Tables[0]; } /// <summary> /// 将datatable转成treegrid分页形式的json /// </summary> /// <param name="dt"></param> /// <returns></returns> public static string DataTableToTreegridJson(DataTable dt) { StringBuilder jsonBuilder = new StringBuilder(); DataTable dtChildren=null; for (int i = 0; i < dt.Rows.Count; i++) { //根据父节点查询出对应子节点的数据 dtChildren = GetDataFromParentId(dt.Rows[i]["KEYID"].ToString(), dt.Rows[i]["CATEGORYID"].ToString()); //父节点开始 jsonBuilder.Append("{"); for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Columns[j].ColumnName.ToLower() == "keyid") { jsonBuilder.Append("\""); jsonBuilder.Append("id"); jsonBuilder.Append("\":\""); jsonBuilder.Append(dt.Rows[i][j].ToString()); jsonBuilder.Append("\","); } else { jsonBuilder.Append("\""); jsonBuilder.Append(dt.Columns[j].ColumnName); jsonBuilder.Append("\":\""); jsonBuilder.Append(dt.Rows[i][j].ToString()); jsonBuilder.Append("\","); } } if (dt.Columns.Count > 0) { jsonBuilder.Remove(jsonBuilder.Length - 1, 1); } jsonBuilder.Append("},"); //循环添加子节点数据 for (int k = 0; k < dtChildren.Rows.Count; k++) { jsonBuilder.Append("{"); for (int l = 0; l < dtChildren.Columns.Count; l++) { jsonBuilder.Append("\""); if (dtChildren.Columns[l].ColumnName.ToLower() == "keyid") { jsonBuilder.Append("id"); jsonBuilder.Append("\":\""); jsonBuilder.Append(dtChildren.Rows[k][l].ToString()); jsonBuilder.Append("\","); } else if (dtChildren.Columns[l].ColumnName.ToLower() == "parentid" && dtChildren.Rows[k][l].ToString() != "0") { jsonBuilder.Append("_parentId"); jsonBuilder.Append("\":\""); jsonBuilder.Append(dtChildren.Rows[k][l].ToString()); jsonBuilder.Append("\","); } else { jsonBuilder.Append(dtChildren.Columns[l].ColumnName); jsonBuilder.Append("\":\""); jsonBuilder.Append(dtChildren.Rows[k][l].ToString()); jsonBuilder.Append("\","); } } if (dt.Columns.Count > 0) { jsonBuilder.Remove(jsonBuilder.Length - 1, 1); } jsonBuilder.Append("},"); } } if (dt.Rows.Count > 0) { jsonBuilder.Remove(jsonBuilder.Length - 1, 1); } return jsonBuilder.ToString(); } #endregion
返回数据到前台：
/// <summary> /// 获取数据列表 /// </summary> /// <param name="context"></param> public void List(HttpContext context) { //获取分页和排序信息：页大小，页码 //int pagerows; //pagerows = 20; // page = 1; int pageIndex = 0; if (!string.IsNullOrEmpty(context.Request["page"])) { pageIndex = Convert.ToInt32(context.Request["page"]); } int pagesize = 0; if (!string.IsNullOrEmpty(context.Request["rows"])) { pagesize = Convert.ToInt32(context.Request["rows"]); } string strPId = "0";// context.Request["id"].ToString();//父节点值，参数只能为id,不能为其他名称 string strCategoryId = context.Request["categoryId"].ToString();//分类id int count = 0; DataSet ds = null; //获取总个数 count = DbHelper.Db.GetDataDicCount(strPId, Convert.ToInt32(strCategoryId)); ds = DbHelper.Db.GetDataDicDataSet((pageIndex - 1) * pagesize + 1, pageIndex * pagesize, strPId, Convert.ToInt32(strCategoryId)); // string strJson = DbHelper.Db.GetDataDicJson((pageIndex - 1) * pagesize + 1, pageIndex * pagesize, strPId, Convert.ToInt32(strCategoryId)); string strJson = JSONHelper.DatasetToTreeGridJson(ds, count);//DataSet数据转化为Json数据 context.Response.Write(strJson);//返回给前台页面 context.Response.End(); }
前台JS代码：
var mygrid = { databind: function (winSize) { $('#dicGrid').treegrid({ toolbar: '#toolbar', title: "数据字典", iconCls: 'icon icon-list', // width: winSize.width, // height: winSize.height, width: '100%', height: '100%', nowrap: false, //折行 rownumbers: true, //行号 striped: true, //隔行变色 idField: 'id', //主键 treeField: 'TITLE', singleSelect: true, //单选 frozenColumns: [[]], columns: [[ { title: 'ID', field: 'id', width: 60 }, { title: '名称', field: 'TITLE', width: 180 }, { title: '编码', field: 'CODE', width: 140 }, { title: '排序', field: 'SORTNUM', width: 80 }, { title: '状态', field: 'STATUS', width: 60, formatter: function (v, d, i) { return '<img src="../../Resource/images/' + (v == '1' ? 'checkmark.gif' : 'checknomark.gif') + '" />'; }, align: 'center' }, { title: '默认值', field: 'ISDEFAULT', width: 60, formatter: function (v, d, i) { return '<img src="../../Resource/images/' + (v == '1' ? 'checkmark.gif' : 'checknomark.gif') + '" />'; }, align: 'center' }, { title: '说明', field: 'REMARK', width: 300 } ]], pagination: true, pageSize: 10, // pageList: [20], onBeforeLoad: function (row, param) { if (!row) { // load top level rows param.id = 0; // set id=0, indicate to load new page rows } } }); } }
浙公网安备 33010602011771号