前言
搜索功能是一個(gè)很常用的功能,當(dāng)然這個(gè)搜索不是指全文檢索,是指網(wǎng)站的后臺(tái)管理系統(tǒng)或ERP系統(tǒng)列表的搜索功能。常見(jiàn)做法一般就是在搜索欄上加上幾個(gè)常用字段來(lái)搜索。代碼可能一般這樣實(shí)現(xiàn)
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)
};
這段代碼如果遇到下面幾個(gè)需求,又該如何處理?
再加一個(gè)查詢字段
RealName需要改成模糊查詢
Age需要支持范圍查詢
可能大多數(shù)程序猿想法,這是新的需求,那么就直接改代碼,簡(jiǎn)單粗暴。然后在前臺(tái)加個(gè)age范圍文本框,后臺(tái)再加個(gè)if判斷,realname的=號(hào)就直接改成like,就這樣輕松搞定了。但需求總是不斷變化,如果一張表有50個(gè)字段,同時(shí)需要支持其中40個(gè)字段查詢。我想大都數(shù)人第一反應(yīng):臥槽,神經(jīng)病!難道就沒(méi)有一個(gè)通用的辦法來(lái)解決這種搜索的問(wèn)題?我想說(shuō)當(dāng)然有,本文接下來(lái)就用DapperExtensions和反射來(lái)解決這個(gè)問(wèn)題,最終于實(shí)現(xiàn)的效果如下圖:
DapperExtensions介紹
DapperExtensions是基于Dapper的一個(gè)擴(kuò)展,主要在Dapper基礎(chǔ)上實(shí)現(xiàn)了CRUD的操作。它還提供了一個(gè)謂詞系統(tǒng),可以實(shí)現(xiàn)更多復(fù)雜的高級(jí)查詢功能。還可以通過(guò)ClassMapper來(lái)定義實(shí)體類和表的映射。
通用搜索功能實(shí)現(xiàn)
1.首先創(chuàng)建一個(gè)account表,然后增加一個(gè)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>
/// 創(chuàng)建時(shí)間
/// </summary>
[Mark("創(chuàng)建時(shí)間")]
public DateTime CreateTime { get; set; }
}
2.為了獲取字段對(duì)應(yīng)的中文名稱,我們?cè)黾右粋€(gè)MarkAttribute類。因?yàn)橛袕?qiáng)大的反射功能,我們可以通過(guò)反射動(dòng)態(tài)獲取每張表實(shí)體類的屬性和中文名稱。
[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.通用搜索思路主要是把搜索功能抽象出一個(gè)對(duì)象,本質(zhì)上也就列名、操作符、值組成的一個(gè)對(duì)象集合,這樣就可以實(shí)現(xiàn)多個(gè)搜索條件的組合。我們?cè)黾右粋€(gè)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.然后通過(guò)反射Account類的屬性加載到前臺(tái)列名的DropDownList,再增加一個(gè)操作符的DropDownList
var columnItems = new List<SelectListItem>(); //通過(guò)反射來(lái)獲取類的屬性 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.前臺(tái)界面實(shí)現(xiàn)代碼
<!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("請(qǐng)輸入值"); 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>"; //轉(zhuǎn)換日期 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> <div> 列名:@Html.DropDownList("columnItems") 操作符:@Html.DropDownList("operatorItems") 值:@Html.TextBox("value") <input id="btnAdd" type="button" value="增加" /> <input id="btnSearch" type="button" value="搜索" /> </div> <br /> <div id="predicates"> <ul class="th"> <li>列名</li> <li>操作符</li> <li>值</li> <li>操作</li> </ul> </div> <br /> <div id="list"> <ul class="th"> <li>賬戶ID</li> <li>姓名</li> <li>年齡</li> <li>創(chuàng)建時(shí)間</li> </ul> </div> </body> </html>
6.最后通過(guò)DapperExtensions的謂詞和反射實(shí)現(xiàn)搜索方法
[HttpPost] public JsonResult Search(List<Predicate> predicates) { if (predicates == null) { return Json(new { Error = "請(qǐng)?jiān)黾铀阉鳁l件" }); } 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); }