不少公司用的是ADO.NET的訪問方式，估計不少朋友對於sql的拼寫真是深惡痛絕，在沒有一個封裝足夠好的底層的專案，特別是經過許多人接手之後，程式碼那叫一個慘不忍睹，本文藉助【通用查詢設計思想】這篇文章的思想和基於ADO.NET進行通用查詢設計。
【通用查詢設計思想】這篇文章是基於核心方法GenerateQueryExpression來生成表示式的，基於這個思想我們重新寫個方法拼裝成類似sql中的where條件
/// <summary> /// 生成查詢條件 /// </summary> /// <typeparam name="TEntity">要查詢的實體型別</typeparam> public static string GenerateQueryCriterion<TEntity>(this IQuery<TEntity> query) where TEntity : class { var criterion = " where 1 = 1 "; if (query == null) return criterion; var condition = " and "; var returnStr = string.Empty; var queryType = query.GetType(); foreach (PropertyInfo property in queryType.GetProperties()) { var value = property.GetValue(query); if (null == value) continue; if (value is string) { var str = ((string)value).Trim(); value = string.IsNullOrEmpty(str) ? null : str; } //針對QueryMode特性獲取我們指定要查詢的路徑 foreach (var attribute in property.GetAttributes<QueryModeAttribute>()) { var propertyPath = attribute.PropertyPath; if (propertyPath == null) propertyPath = property.Name; var conditionStr = CreateConditionString(value, propertyPath, attribute.Compare); if (string.IsNullOrWhiteSpace(conditionStr)) continue; returnStr += condition + conditionStr; } } return criterion + returnStr; } /// <summary> /// 生成sql條件 /// </summary> /// <param name="value"></param> /// <param name="propertyPath"></param> /// <param name="compare"></param> /// <returns></returns> private static string CreateConditionString(object value, string propertyPath, QueryCompare compare) { switch (compare) { case QueryCompare.Equal: return CreateEqualString(propertyPath, value); case QueryCompare.GreaterThanOrEqual: return CreateGreaterThanOrEqualString(propertyPath, value); case QueryCompare.LessThanOrEqual: return CreateLessThanOrEqualString(propertyPath, value); case QueryCompare.Like: return CreateLikeString(propertyPath, value); default: return null; } } /// <summary> /// 生成sql的等於條件 /// </summary> /// <param name="propertyPath"></param> /// <param name="value"></param> /// <returns></returns> private static string CreateEqualString(string propertyPath, object value) { if (value == null) return string.Empty; if (value is string) { return propertyPath + "='" + value + "'"; } if (value is bool) { if (value.ToString() == "False") return propertyPath + "=0"; return propertyPath + "=1"; } return propertyPath + "=" + value; }
方法返回的就是我們常見的“ 1 = 1 and System='A' ” 這樣的查詢條件，其他的類似 大於，小於，like這些條件，相信以朋友們高超的智慧肯定不是什麼難事：）
為避免新朋友沒看過【通用查詢設計思想】這篇文章，個人把這次的變動的類羅列出來
查詢基類（保留表示式的方法，相容同時使用Linq和ADO.NET的情況：））
public class Query<TEntity> : IQuery<TEntity> where TEntity: class { /// <summary> /// 指定查詢條件 /// </summary> protected Expression<Func<TEntity, bool>> Predicate; /// <summary> /// 建立一個新的 <see cref="Query{TEntity}"/> /// </summary> public Query() { } /// <summary> /// 建立一個指定查詢條件的<see cref="Query{TEntity}"/> /// </summary> /// <param name="predicate">指定的查詢條件</param> public Query(Expression<Func<TEntity, bool>> predicate) { Predicate = predicate; } /// <summary> /// 生成表示式 /// </summary> /// <returns></returns> public Expression<Func<TEntity, bool>> GenerateExpression() { return Predicate.And(this.GetQueryExpression()); } /// <summary> /// 生成sql條件 /// </summary> /// <returns></returns> public string GenerateSqlCriterion() { return this.GenerateQueryCriterion(); } }
查詢模式（保留PropertyPath給表示式，PropertyPath2不是陣列，因為ADO.NET中應該不會涉及到導航屬性）
public class QueryModeAttribute : Attribute { /// <summary> /// 比較方式 /// </summary> public QueryCompare Compare { get; set; } /// <summary> /// 對應屬性路徑(Linq + Expression) /// </summary> public string[] PropertyPath { get; set; } /// <summary> /// 對應屬性路徑(sql + ADO.NET) /// </summary> public string PropertyPath2 { get; set; } /// <summary> /// 查詢欄位 /// </summary> public QueryModeAttribute(params string[] propertyPath) { PropertyPath = propertyPath; } /// <summary> /// 查詢欄位 /// </summary> public QueryModeAttribute(QueryCompare compare,params string[] propertyPath) { PropertyPath = propertyPath; Compare = compare; } /// <summary> /// 查詢欄位 /// </summary> public QueryModeAttribute(QueryCompare compare, string propertyPath2) { PropertyPath2 = propertyPath2; Compare = compare; } }
查詢實體
public class AccountQuery : PageQuery<Account> { /// <summary> /// 姓名 /// </summary> [Query(QueryCompare.Equal, nameof(Account.Name))] public string Name { get; set; } //其他查詢條件... }
這樣的話我們的之前的表示式寫法成了下面這樣
public class AccountQuery : PageQuery<Account> { /// <summary> /// 姓名 /// </summary> [Query(QueryCompare.Equal, new[]{nameof(Account.Name)})] public string Name { get; set; } //其他查詢條件... }
來看一下我們完整的使用例子，很多情況下我們是需要分頁和新增類似於Order By或者Group By這樣的高階條件（本例子前端是使用layui，資料庫是mysql）
public PageResult<AccountDto> GetAccount(AccountQuery query) { var sql = @"select xx,xx,xx from user "; var criterion = query.GenerateSqlCriterion(); sql += criterion; return return MySqlHelper.GetPageResult<AccountDto>(sql, null, query); } /// <summary> /// 獲取分頁資料 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <param name="pageInfo"></param> /// <param name="groupBy"></param> /// <returns></returns> public static PageResult<T> GetPageResult<T>(string cmdText, MySqlParameter[] cmdParms, PageInfo pageInfo, string groupBy = "") where T : class, new() { var result = new PageResult<T> { PageSize = pageInfo.PageSize, PageIndex = pageInfo.PageIndex }; //獲取總數 var sqlCount = $"SELECT COUNT(*) FROM ({cmdText}) TableCount "; var objCount = ExecuteScalar(CommandType.Text, sqlCount, cmdParms); result.TotalCount = Convert.ToInt32(objCount); string pagingSql; //獲取分頁資料 if (string.IsNullOrWhiteSpace(groupBy)) { pagingSql = $"{cmdText} ORDER BY {pageInfo.Field ?? "id"} {pageInfo.Order ?? "desc"} " + $"LIMIT {pageInfo.PageIndex}, {pageInfo.PageSize}"; } else { //Group by pagingSql = $"{cmdText} GROUP BY {groupBy} ORDER BY {pageInfo.Field ?? "id"} {pageInfo.Order ?? "desc"}" + $"LIMIT {pageInfo.PageIndex}, {pageInfo.PageSize}"; } var dt = ExecuteDataTable(CommandType.Text, pagingSql, cmdParms); if (dt != null) { result.Data = dt.ToList<T>(); } return result; }
當然，這只是我們的一般查詢情況，實際情況中我們會遇到更復雜的sql，基於本文的中心主題，不再深入討論其他場景。
讓我知道如果你有更好的想法！