极极极极极简的的增删查改(CRUD)解决方案
[ { title:"名称", data:"字段名", type:"字段类型", value:"值" } ]
对于一个简单的form表单,这个结构足以描述。太复杂的页面?自己写...。而就我的实践经验告诉我,这个结构足以满足90%后台系统中的新增与修改。
this.initForm = function () { var form = $('<form id="' + this.formId + '" class="form-horizontal layer-row-container" role="form"></form>'); form.appendTo($("body")); for (var i = 0, iLen = this.columns.length; i < iLen; i++) { var colType = this.columns[i]["type"]; if (!colType) continue; var row; if (["text", "number", "datepicker", "timepicker", "hide"].indexOf(colType) >= 0) { row = $('<div class="form-group"><label for="' + this.columns[i]["data"] + '" class="col-sm-3 control-label">' + this.columns[i]["title"] + ':</label><div class="col-sm-9"><input type="text" class="form-control" id="' + this.columns[i]["data"] + '"></div></div>'); if (colType === "number") { $.bode.tools.input.formatDiscount(row.find("#" + this.columns[i]["data"])); } else if (colType === "datepicker" || colType === "timepicker") { var showTime = colType === "timepicker"; $.bode.tools.input.formatTime(row.find("#" + this.columns[i]["data"]), showTime); } else if (colType === "hide") { row.hide(); } row.appendTo(form); } else if (["switch", "dropdown"].indexOf(colType) >= 0) { var source = this.columns[i]["source"]; var valueFiled = source.valueField || "value"; var textField = source.textField || "text"; row = $('<div class="form-group"><label for="' + this.columns[i]["data"] + '" class="col-sm-3 control-label">' + this.columns[i]["title"] + ':</label><div class="col-sm-9"><select class="form-control" id="' + this.columns[i]["data"] + '"></select></div></div>'); var select = row.find("#" + this.columns[i]["data"]); for (var j = 0, jLen = source["data"].length; j < jLen; j++) { var option = source["data"][j]; $('<option value="' + option[valueFiled] + '">' + option[textField ] + '</option>') .appendTo(select); } row.appendTo(form); } else if (colType === "img") { row = $('<div class="form-group"><label for="' + this.columns[i]["data"] + '" class="col-sm-3 control-label">' + this.columns[i]["title"] + ':</label><div class="col-sm-9"><div class="uploader-list"><div class="file-item thumbnail"><img style="width:160px;height:90px;" id="img_' + this.columns[i]["data"] + '" src="" /></div></div><div id="' + this.columns[i]["data"] + '">选择图片</div></div></div>'); row.appendTo(form); // 初始化Web Uploader var uploader = WebUploader.create({ auto: true, // 选完文件后,是否自动上传。 swf: '/Content/js/plugs/webuploader/Uploader.swf', // swf文件路径 server: this.imgSaveUrl, // 文件接收服务端。 // 选择文件的按钮。可选。 // 内部根据当前运行是创建,可能是input元素,也可能是flash. pick: '#' + this.columns[i]["data"], // 只允许选择图片文件。 accept: { title: 'Images', extensions: 'gif,jpg,jpeg,bmp,png', mimeTypes: 'image/*' } }); uploader.on("uploadSuccess", function (file, resp) { $("#img_" + this.options.pick.substring(1)).attr("src", resp); }); } else if (colType === "textarea") { row = $('<div class="form-group"><label for="' + this.columns[i]["data"] + '" class="col-sm-3 control-label">' + this.columns[i]["title"] + ':</label><div class="col-sm-9"><textarea class="form-control" id="' + this.columns[i]["data"] + '" style="overflow: hidden; word-wrap: break-word; resize: horizontal; height: 48px;"></textarea></div></div>'); row.appendTo(form); row.find('textarea').autosize({ append: "\n" }); } else if (colType === "richtext") { row = $('<div class="form-group"><label for="' + this.columns[i]["data"] + '" class="col-sm-3 control-label">' + this.columns[i]["title"] + ':</label><div class="col-sm-9"><textarea class="form-control" id="' + this.columns[i]["data"] + '" style="heght:150px;"></textarea></div></div>'); row.appendTo(form); var editor = new wangEditor(this.columns[i]["data"]); editor.config.uploadImgUrl = this.imgSaveUrl; editor.config.withCredentials = false; editor.create(); editorHash[this.columns[i]["data"]] = editor; } } $('<hr class="wide" />').appendTo(form); this.isFormInited = true; }
最后在公共的js里将整个表单加入页面上,这样就完成了省去新增和编辑页面的工作。页面是省去了,但是数据如何保存,试想我们一般的新增和编辑接口,其提交的数据结构大概是这样:
{
字段名1:值1,
字段名2:值2,
字段名3:值3
}
而从上文的数据结构中我们能很容易的提取出需要提交的结构,所以我们只需要一个提交的服务端地址即可,这便是省去新增、编辑页面的整个思路。但是新增、编辑过程中还会有很多细节性的问题,比如:
{ "pageIndex":1, "pageSize":15, "sortConditions":[ { "sortField":"name", "listSortDirection":1 } ], "filterGroup":{ "rules":[ { "field":"name", "operate":"contains", "value":"a" } ] } }
排序很容易,难点在于筛选条件的处理,特别是结合EF的使用,如何将filterGroup转化为查询的表达式,还是看代码吧:
using Abp.Extensions; using System; using System.Collections.Generic; using System.Linq; using System.Linq.Expressions; using System.Reflection; namespace Abp.Application.Services.Query { /// <summary> /// 查询表达式辅助操作类 /// </summary> public static class FilterHelper { #region 字段 private static readonly Dictionary<FilterOperate, Func<Expression, Expression, Expression>> ExpressionDict = new Dictionary<FilterOperate, Func<Expression, Expression, Expression>> { { FilterOperate.Equal, Expression.Equal }, { FilterOperate.NotEqual, Expression.NotEqual }, { FilterOperate.Less, Expression.LessThan }, { FilterOperate.Greater, Expression.GreaterThan }, { FilterOperate.LessOrEqual, Expression.LessThanOrEqual }, { FilterOperate.GreaterOrEqual, Expression.GreaterThanOrEqual }, { FilterOperate.StartsWith, (left, right) => { if (left.Type != typeof(string)) { throw new NotSupportedException("“StartsWith”比较方式只支持字符串类型的数据"); } return Expression.Call(left, typeof(string).GetMethod("StartsWith", new[] { typeof(string) }), right); } }, { FilterOperate.EndsWith, (left, right) => { if (left.Type != typeof(string)) { throw new NotSupportedException("“EndsWith”比较方式只支持字符串类型的数据"); } return Expression.Call(left, typeof(string).GetMethod("EndsWith", new[] { typeof(string) }), right); } }, { FilterOperate.Contains, (left, right) => { if (left.Type != typeof(string)) { throw new NotSupportedException("“Contains”比较方式只支持字符串类型的数据"); } return Expression.Call(left, typeof(string).GetMethod("Contains", new[] { typeof(string) }), right); } } }; #endregion /// <summary> /// 获取指定查询条件组的查询表达式 /// </summary> /// <typeparam name="T">表达式实体类型</typeparam> /// <param name="group">查询条件组,如果为null,则直接返回 true 表达式</param> public static Expression<Func<T, bool>> GetExpression<T>(FilterGroup group) { ParameterExpression param = Expression.Parameter(typeof(T), "m"); Expression body = GetExpressionBody(param, group); Expression<Func<T, bool>> expression = Expression.Lambda<Func<T, bool>>(body, param); return expression; } /// <summary> /// 获取指定查询条件的查询表达式 /// </summary> /// <typeparam name="T">表达式实体类型</typeparam> /// <param name="rule">查询条件,如果为null,则直接返回 true 表达式</param> /// <returns></returns> public static Expression<Func<T, bool>> GetExpression<T>(FilterRule rule = null) { ParameterExpression param = Expression.Parameter(typeof(T), "m"); Expression body = GetExpressionBody(param, rule); Expression<Func<T, bool>> expression = Expression.Lambda<Func<T, bool>>(body, param); return expression; } /// <summary> /// 把查询操作的枚举表示转换为操作码 /// </summary> /// <param name="operate">查询操作的枚举表示</param> public static string ToOperateCode(this FilterOperate operate) { Type type = operate.GetType(); MemberInfo[] members = type.GetMember(operate.To<string>()); if (members.Length > 0) { OperateCodeAttribute attribute = members[0].GetAttribute<OperateCodeAttribute>(); return attribute == null ? null : attribute.Code; } return null; } /// <summary> /// 获取操作码的查询操作枚举表示 /// </summary> /// <param name="code">操作码</param> /// <returns></returns> public static FilterOperate GetFilterOperate(string code) { Type type = typeof(FilterOperate); MemberInfo[] members = type.GetMembers(BindingFlags.Public | BindingFlags.Static); foreach (MemberInfo member in members) { FilterOperate operate = member.Name.To<FilterOperate>(); if (operate.ToOperateCode() == code) { return operate; } } throw new NotSupportedException("获取操作码的查询操作枚举表示时不支持代码:" + code); } #region 私有方法 private static Expression GetExpressionBody(ParameterExpression param, FilterGroup group) { //如果无条件或条件为空,直接返回 true表达式 if (group == null || (group.Rules.Count == 0 && group.Groups.Count == 0)) { return Expression.Constant(true); } List<Expression> bodys = new List<Expression>(); bodys.AddRange(group.Rules.Select(rule => GetExpressionBody(param, rule))); bodys.AddRange(group.Groups.Select(subGroup => GetExpressionBody(param, subGroup))); if (group.Operate == FilterOperate.And) { return bodys.Aggregate(Expression.AndAlso); } if (group.Operate == FilterOperate.Or) { return bodys.Aggregate(Expression.OrElse); } throw new Exception("查询参数序列化失败"); } private static Expression GetExpressionBody(ParameterExpression param, FilterRule rule) { if (rule == null || rule.Value == null || string.IsNullOrEmpty(rule.Value.ToString())) { return Expression.Constant(true); } LambdaExpression expression = GetPropertyLambdaExpression(param, rule); Expression constant = ChangeTypeToExpression(rule, expression.Body.Type); return ExpressionDict[rule.Operate](expression.Body, constant); } private static LambdaExpression GetPropertyLambdaExpression(ParameterExpression param, FilterRule rule) { string[] propertyNames = rule.Field.Split('.'); Expression propertyAccess = param; Type type = param.Type; foreach (string propertyName in propertyNames) { PropertyInfo property = type.GetProperty(propertyName); if (property == null) { throw new InvalidOperationException(string.Format("指定属性{0}在类型{1}中不存在.", rule.Field, type.FullName)); } type = property.PropertyType; propertyAccess = Expression.MakeMemberAccess(propertyAccess, property); } return Expression.Lambda(propertyAccess, param); } private static Expression ChangeTypeToExpression(FilterRule rule, Type conversionType) { Type elementType = conversionType.GetUnNullableType(); object value = rule.Value is string ? rule.Value.ToString().To(conversionType) : Convert.ChangeType(rule.Value, elementType); return Expression.Constant(value, conversionType); } #endregion } }
using Abp.Application.Services.Dto; using Abp.Domain.Entities; using System; using System.Linq; using System.Linq.Expressions; using Abp.Extensions; using System.Collections.Generic; using System.ComponentModel; namespace Abp.Application.Services.Query { /// <summary> /// 集合扩展辅助操作类 /// </summary> public static class CollectionExtensions { /// <summary> /// 从指定<see cref="IQueryable{T}"/>集合中查询指定数据筛选的分页信息 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <typeparam name="TResult">分页数据类型</typeparam> /// <param name="source">要查询的数据集</param> /// <param name="predicate">查询条件谓语表达式</param> /// <param name="pageCondition">分页查询条件</param> /// <param name="selector">数据筛选表达式</param> /// <returns>分页结果信息</returns> public static PagedResultDto<TResult> ToPage<TEntity, TResult>(this IQueryable<TEntity> source, Expression<Func<TEntity, bool>> predicate, PageCondition pageCondition, Expression<Func<TEntity, TResult>> selector) { return source.ToPage(predicate, pageCondition.PageIndex, pageCondition.PageSize, pageCondition.SortConditions, selector); } /// <summary> /// 从指定<see cref="IQueryable{T}"/>集合中查询指定数据筛选的分页信息 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <typeparam name="TResult">分页数据类型</typeparam> /// <param name="source">要查询的数据集</param> /// <param name="predicate">查询条件谓语表达式</param> /// <param name="pageIndex">分页索引</param> /// <param name="pageSize">分页大小</param> /// <param name="sortConditions">排序条件集合</param> /// <param name="selector">数据筛选表达式</param> /// <returns>分页结果信息</returns> public static PagedResultDto<TResult> ToPage<TEntity, TResult>(this IQueryable<TEntity> source, Expression<Func<TEntity, bool>> predicate, int pageIndex, int pageSize, SortCondition[] sortConditions, Expression<Func<TEntity, TResult>> selector) { int total; TResult[] data = source.Where(predicate, pageIndex, pageSize, out total, sortConditions).Select(selector).ToArray(); return new PagedResultDto<TResult>() { TotalCount = total, Items = data }; } /// <summary> /// 从指定<see cref="IQueryable{T}"/>集合中查询指定分页条件的子数据集 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> /// <param name="source">要查询的数据集</param> /// <param name="predicate">查询条件谓语表达式</param> /// <param name="pageCondition">分页查询条件</param> /// <param name="total">输出符合条件的总记录数</param> /// <returns></returns> public static IQueryable<TEntity> Where<TEntity>(this IQueryable<TEntity> source, Expression<Func<TEntity, bool>> predicate, PageCondition pageCondition, out int total) { return source.Where(predicate, pageCondition.PageIndex, pageCondition.PageSize, out total, pageCondition.SortConditions); } /// <summary> /// 从指定<see cref="IQueryable{T}"/>集合中查询指定分页条件的子数据集 /// </summary> /// <typeparam name="TEntity">动态实体类型</typeparam> /// <param name="source">要查询的数据集</param> /// <param name="predicate">查询条件谓语表达式</param> /// <param name="pageIndex">分页索引</param> /// <param name="pageSize">分页大小</param> /// <param name="total">输出符合条件的总记录数</param> /// <param name="sortConditions">排序条件集合</param> /// <returns></returns> public static IQueryable<TEntity> Where<TEntity>(this IQueryable<TEntity> source, Expression<Func<TEntity, bool>> predicate, int pageIndex, int pageSize, out int total, ICollection<SortCondition> sortConditions = null) { if (!typeof(TEntity).IsEntityType()) { string message = $"类型“{typeof(TEntity).FullName}”不是实体类型"; throw new InvalidOperationException(message); } total = source.Count(predicate); source = source.Where(predicate); if (sortConditions == null || sortConditions.Count == 0) { source = source.OrderBy("Id", ListSortDirection.Descending); } else { int count = 0; IOrderedQueryable<TEntity> orderSource = null; foreach (SortCondition sortCondition in sortConditions) { var sortField = sortCondition.SortField.ToPascalCase(); orderSource = count == 0 ? CollectionPropertySorter<TEntity>.OrderBy(source, sortField, sortCondition.ListSortDirection) : CollectionPropertySorter<TEntity>.ThenBy(orderSource, sortField, sortCondition.ListSortDirection); count++; } source = orderSource.ThenBy("Id", ListSortDirection.Descending); } return source?.Skip((pageIndex - 1) * pageSize).Take(pageSize) ?? Enumerable.Empty<TEntity>().AsQueryable(); } /// <summary> /// 从指定<see cref="IQueryable{T}"/>集合中查询指定分页条件的子数据集 /// </summary> /// <typeparam name="TEntity">动态实体类型</typeparam> /// <param name="source">要查询的数据集</param> /// <param name="input">查询条件</param> /// <param name="total">输出符合条件的总记录数</param> /// <returns></returns> public static IQueryable<TEntity> Where<TEntity>(this IQueryable<TEntity> source, QueryListPagedRequestInput input, out int total) { Expression<Func<TEntity, bool>> predicate = p => true; if (input.FilterGroup != null) { foreach (var item in input.FilterGroup.Rules) { item.Field = item.Field.ToPascalCase(); //TODO:处理FilterGroup.Groups } predicate = FilterHelper.GetExpression<TEntity>(input.FilterGroup); } return source.Where<TEntity>(predicate, input.PageIndex, input.PageSize, out total, input.SortConditions); } } }
最后我们为IQueryable添加了扩展方法供业务层使用,于是乎,几乎所有业务层表格的查询代码都能统一为:
/// <inheritdoc/> public async Task<PagedResultDto<GetActivityListOutput>> GetActivityPagedList(QueryListPagedRequestInput input) { int total; var list = await _activityRepository.GetAll().Where(input, out total).ToListAsync(); return new PagedResultDto<GetActivityListOutput>(total, list.MapTo<List<GetActivityListOutput>>()); }
简洁却又功能强大,减少了工作量的同时也规范了编码。对于多表联合查询,只需要组装需要的IQueryable后一样可以使用该方法。如此,我们服务端的查询几乎可以说是零代码了,那么前端呢?如何省去相关代码,排序依然很容易,只是在表头上添加事件即可,而筛选,动态添加dom结构并绑定事件难度也不高,我们一样在公共js中可以完成相关工作,实现具体页面中零代码。源码都在这里,有兴趣的可以看看:
@{ Layout = "~/Areas/Admin/Views/Shared/_AdminLayout.cshtml"; } @section header{ <link href="~/Content/css/dataTables.bootstrap.css" rel="stylesheet" type="text/css" /> <link href="~/Content/css/bootstrap-datetimepicker.min.css" rel="stylesheet" type="text/css" /> <link href="~/Content/js/plugs/wangEditor-2.1.23/dist/css/wangEditor.min.css" rel="stylesheet" type="text/css" /> <link href="~/Content/js/plugs/webuploader/webuploader.css" rel="stylesheet" type="text/css" /> <style> .query-input { height: 32px; line-height: 32px; width: 30%; vertical-align: middle; } .form-inline .radio input[type=radio], .form-inline .checkbox input[type=checkbox] { position: absolute; } </style> } @section footer{ <script src="~/Content/js/plugs/select2/select2.js" type="text/javascript"></script> <script src="~/Content/js/plugs/datetime/bootstrap-datetimepicker.min.js" type="text/javascript"></script> <script src="~/Content/js/plugs/datetime/bootstrap-datetimepicker.zh-CN.js" type="text/javascript"></script> <script src="~/Content/js/plugs/webuploader/webuploader.js" type="text/javascript"></script> <script src="~/Content/js/plugs/wangEditor-2.1.23/dist/js/wangEditor.js" type="text/javascript"></script> <script src="~/Content/js/plugs/textarea/jquery.autosize.js" type="text/javascript"></script> <script src="~/Content/js/zooming.js" type="text/javascript"></script> <script src="~/Content/js/bode/bode.grid.js" type="text/javascript"></script> <script type="text/javascript"> var datatable; var tableOption = { url: {}, columns: [], permission: {}, pageSize: 15, actions: [], formWidth: "40%", isBatch: false, extraFilters: [], imgSaveUrl: $.bode.config.imgSaveUrl, loadDataComplete: function (data) { } }; var startfunction = function () { }; var endfunction = function () { }; </script> @RenderSection("customScript", true) <script type="text/javascript"> $(function () { startfunction(); //初始化数据 datatable = new $.bode.grid("#dataTable", tableOption); endfunction(); }); </script> } @RenderSection("headHtml", false) <div class="page-container"> <div class="page-body" style="padding:0;"> <div class="row"> <div class="col-xs-12"> <div class="widget flat radius-bordered"> @*<div class="widget-header bg-info"> <span class="widget-caption"><strong>@ViewBag.Title</strong></span> </div>*@ <div class="widget-body"> <div role="grid" id="editabledatatable_wrapper" class="dataTables_wrapper form-inline no-footer"> <div class="row" style="padding-bottom: 10px;"> <div class="col-sm-4"> <select style="width: 25%"></select> <select style="width: 25%"></select> <input type="text" class="query-input"> <a class="btn btn-info btn-sm icon-only query-add" href="javascript:void(0);"><i class="fa fa-plus-square-o"></i></a> </div> <div class="col-sm-8"> <div class="form-group" style="float: right" id="actionArea"></div> </div> </div> <table class="table table-bordered table-hover table-striped dataTable no-footer" id="dataTable" aria-describedby="editabledatatable_info"> <thead> <tr></tr> </thead> <tbody></tbody> </table> </div> </div> </div> </div> </div> </div> </div> @RenderBody() @RenderSection("footHtml", false)
最后需要写的页面代码就只剩下这么点了:
@{ ViewBag.Title = "Table"; Layout = "~/Areas/Admin/Views/Shared/_GridLayout.cshtml"; } @section customScript{ <script type="text/javascript"> var enums = @Html.Raw(Json.Encode(@ViewBag.Enums)); tableOption.url = { read: "/api/services/activity/activities/GetPagedList", add: "/api/services/activity/activities/Create", edit: "/api/services/activity/activities/Update", delete: "/api/services/activity/activities/Delete" }; tableOption.columns = [ { data: "id", title: "编号",type:"hide" }, { data: "name", title: "类别名称", type: "text", query: true, editor: {},display:{} }, { data: "isStatic", title: "是否静态的类别", type: "switch", query: true, editor: {} }, { data: "order", title: "排序号", type: "number", query: true, editor: {} }, { data: "creationTime", title: "创建时间", type: "datepicker", editor: {} }, { title: "操作选项", type: "command", actions: [ { name: "操作", icon: "fa-trash-o", onClick: function (d) { alert(d["id"]); } } ] } ]; </script> }
并且完全实现了分页、查询、排序、新增、编辑、删除等基础功能,并且提供了自定义功能按钮的支持。
其他功能
经过一年多的使用与不断的调整,我在表格的基础上新增了很多的功能特性,包括:
1、结合树的使用,树表操作和纯表格相差无几,同样是极简的思路。
2、结合abp的权限实现表格按钮级的权限控制,没有权限的按钮不显示。
3、支持非表格以外的字段筛选,并且保存时自动提交该字段。
由于功能介绍不是本篇博客的重点,就暂不展开介绍。本文提供了节省增删查改编码量的思路与部分示例代码,欢迎交流,也推荐大家自己动手尝试。