媒介
搜刮功用是一个很经常使用的功用,固然这个搜刮不是指全文检索,是指网站的背景治理体系或ERP体系列表的搜刮功用。罕见做法平常就是在搜刮栏上加上几个经常使用字段来搜刮。代码能够平常如许完成
StringBuilder sqlStr = new StringBuilder(); if (!string.IsNullOrEmpty(RealName)) { sqlStr.Append(" and RealName = @RealName"); } if (Age != -1) { sqlStr.Append(" and Age = @Age"); } if (!string.IsNullOrEmpty(StartTime)) { sqlStr.Append(" and CreateTime >= @StartTime"); } if (!string.IsNullOrEmpty(EndTime)) { sqlStr.Append(" and CreateTime <= @EndTime"); } MySqlParameter[] paras = new MySqlParameter[]{ new MySqlParameter("@Age", Age), new MySqlParameter("@RealName", RealName), new MySqlParameter("@StartTime", StartTime), new MySqlParameter("@EndTime", EndTime) };
这段代码假如碰到下面几个需求,又该怎样处置惩罚?
再加一个查询字段
RealName须要改成隐约查询
Age须要支撑局限查询
能够大多数顺序猿主意,这是新的需求,那末就直接改代码,简朴粗犷。然后在前台加个age局限文本框,背景再加个if推断,realname的=号就直接改成like,就如许轻松搞定了。但需求老是不停变化,假如一张表有50个字段,同时须要支撑个中40个字段查询。我想多数数人第一回响反映:卧槽,精神病!岂非就没有一个通用的要领来处置惩罚这类搜刮的题目?我想说固然有,本文接下来就用DapperExtensions和反射的来处置惩罚这个题目,终究究完成的结果如下图:
DapperExtensions引见
DapperExtensions是基于Dapper的一个扩大,重要在Dapper基础上完成了CRUD的操纵。它还供应了一个谓词体系,能够完成更多庞杂的高等查询功用。还能够经由过程ClassMapper来定义实体类和表的映照。
通用搜刮功用完成
1.起首建立一个account表,然后增添一个Account类
public class Account { public Account() { Age = -1; } /// <summary> /// 账户ID /// </summary> [Mark("账户ID")] public int AccountId { get; set; } /// <summary> /// 姓名 /// </summary> [Mark("姓名")] public string RealName { get; set; } /// <summary> /// 岁数 /// </summary> [Mark("岁数")] public int Age { get; set; } /// <summary> /// 建立时候 /// </summary> [Mark("建立时候")] public DateTime CreateTime { get; set; } }
2.为了猎取字段对应的中文名称,我们增添一个MarkAttribute类。由于有壮大的反射功用,我们能够经由过程反射动态猎取每张表实体类的属性和中文名称。
[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = true)] public class MarkAttribute : Attribute { public MarkAttribute(string FiledName, string Description = "") { this.FiledName = FiledName; this.Description = Description; } private string _FiledName; public string FiledName { get { return _FiledName; } set { _FiledName = value; } } private string _Description; public string Description { get { return _Description; } set { _Description = value; } } }
3.通用搜刮思绪重如果把搜刮功用笼统出一个对象,本质上也就列名、操纵符、值构成的一个对象鸠合,如许就能够完成多个搜刮前提的组合。我们增添一个Predicate类
public class Predicate { /// <summary> /// 列名 /// </summary> public string ColumnItem { get; set; } /// <summary> /// 操纵符 /// </summary> public string OperatorItem { get; set; } /// <summary> /// 值 /// </summary> public object Value { get; set; } }
4.然后经由过程反射Account类的属性加载到前台列名的DropDownList,再增添一个操纵符的DropDownList
var columnItems = new List<SelectListItem>(); //经由过程反射来猎取类的属性 Type t = Assembly.Load("SearchDemo").GetType("SearchDemo.Models.Account"); foreach (PropertyInfo item in t.GetProperties()) { string filedName = (item.GetCustomAttributes(typeof(MarkAttribute), false)[0] as MarkAttribute).FiledName; columnItems.Add(new SelectListItem() { Text = filedName, Value = item.Name }); } ViewBag.columnItems = columnItems; var operatorItems = new List<SelectListItem>() { new SelectListItem() {Text = "即是", Value = "Eq"}, new SelectListItem() {Text = "大于", Value = "Gt"}, new SelectListItem() {Text = "大于或即是", Value = "Ge"}, new SelectListItem() {Text = "小于", Value = "Lt"}, new SelectListItem() {Text = "小于或即是", Value = "Le"}, new SelectListItem() {Text = "隐约", Value = "Like"} }; ViewBag.operatorItems = operatorItems;
5.前台界面完成代码
<!DOCTYPE html> <html> <head> <title>DapperExtensions通用搜刮</title> <script src="../../scripts/jquery-1.4.4.min.js" type="text/javascript"></script> <script type="text/javascript"> Date.prototype.format = function (format) { var o = { "M+": this.getMonth() + 1, //month "d+": this.getDate(), //day "h+": this.getHours(), //hour "m+": this.getMinutes(), //minute "s+": this.getSeconds(), //second "q+": Math.floor((this.getMonth() + 3) / 3), //quarter "S": this.getMilliseconds() //millisecond } if (/(y+)/.test(format)) { format = format.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length)); } for (var k in o) { if (new RegExp("(" + k + ")").test(format)) { format = format.replace(RegExp.$1, RegExp.$1.length == 1 ? o[k] : ("00" + o[k]).substr(("" + o[k]).length)); } } return format; } </script> <style type="text/css"> ul { list-style: none; padding: 0px; margin: 0px; width: 590px; height: 20px; line-height: 20px; border: 1px solid #99CC00; border-top: 0px; font-size: 12px; } ul li { display: block; width: 25%; float: left; text-indent: 2em; } .th { background: #F1FADE; font-weight: bold; border-top: 1px solid #99CC00; } </style> <script type="text/javascript"> var predicates = []; var index = 0; $(document).ready(function () { $("#btnAdd").click(function () { var columnItem = $("#columnItems option:selected"); var operatorItem = $("#operatorItems option:selected"); var value = $("#value").val(); if(value == ""){ alert("请输入值"); return; } var predicate = { index: index, columnItem: columnItem.val(), operatorItem: operatorItem.val(), value: value }; predicates.push(predicate); var html = "<ul><li>" + columnItem.text() + "</li><li>" + operatorItem.text() + "</li><li>" + value + "</li><li><a href='javascript:;' onclick='del(this," + index + ")'>删除</a></li></ul>" $("#predicates ul:last").after(html); index++; }) $("#btnSearch").click(function () { $.ajax({ type: "POST", url: "home/search", data: JSON.stringify(predicates), contentType: "application/json", success: function (data) { if (data.Error != null) { alert(data.Error); return; } $("#list .th").nextAll().remove(); var html = ""; $.each(data, function (index, item) { html += "<ul><li>" + item.AccountId + "</li>"; html += "<li>" + item.RealName + "</li>"; html += "<li>" + item.Age + "</li>"; //转换日期 var dateMilliseconds = parseInt(item.CreateTime.replace(/\D/igm, "")); var date = new Date(dateMilliseconds); html += "<li>" + date.format("yyyy-MM-dd hh:mm:ss") + "</li></ul>"; }); $("#list .th").after(html); } }); }) }) function del(obj,index) { obj.parentNode.parentNode.remove(); for (var i = 0; i < predicates.length; i++) { if (predicates[i].index == index) { predicates.splice(i, 1); } } } </script> </head> <body> <p> 列名:@Html.DropDownList("columnItems") 操纵符:@Html.DropDownList("operatorItems") 值:@Html.TextBox("value") <input id="btnAdd" type="button" value="增添" /> <input id="btnSearch" type="button" value="搜刮" /> </p> <br /> <p id="predicates"> <ul class="th"> <li>列名</li> <li>操纵符</li> <li>值</li> <li>操纵</li> </ul> </p> <br /> <p id="list"> <ul class="th"> <li>账户ID</li> <li>姓名</li> <li>岁数</li> <li>建立时候</li> </ul> </p> </body> </html>
6.末了经由过程DapperExtensions的谓词和反射完成搜刮要领
[HttpPost] public JsonResult Search(List<Predicate> predicates) { if (predicates == null) { return Json(new { Error = "请增添搜刮前提" }); } using (var connection = SqlHelper.GetConnection()) { var pga = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() }; foreach (var p in predicates) { var predicate = Predicates.Field<Account>(GetExpression(p), (Operator)Enum.Parse(typeof(Operator), p.OperatorItem), p.Value); pga.Predicates.Add(predicate); } var list = connection.GetList<Account>(pga); return Json(list); } } private static Expression<Func<Account, object>> GetExpression(Predicate p) { ParameterExpression parameter = Expression.Parameter(typeof(Account), "p"); return Expression.Lambda<Func<Account, object>>(Expression.Convert(Expression.Property(parameter, p.ColumnItem), typeof(object)), parameter); }
终究,经由过程简朴的几行代码,在基于DapperExtensions的功用基础上,我们终究完成了一个能够支撑多个字段、多个前提、多个操纵符的通用查询功用。本文也只是举一反三,只是供应一种思绪,另有更多细节没有斟酌。比方多个前提的组合能够再增添一个逻辑符来衔接、多个前提组合嵌套查询、多表查询等等。
以上就是DapperExtensions和反射完成通用搜刮(ASP.NET)的细致内容,更多请关注ki4网别的相干文章!