不少公司用的是ADO.NET的访问方式,估计不少朋友对于sql的拼写真是深恶痛绝,在没有一个封装足够好的底层的项目,特别是经过许多人接手之后,代码那叫一个惨不忍睹,本文借助
【通用查询设计思想】 <https://www.cnblogs.com/lex-wu/p/10389236.html>
这篇文章的思想和基于ADO.NET进行通用查询设计。

  【通用查询设计思想】 <https://www.cnblogs.com/lex-wu/p/10389236.html>
这篇文章是基于核心方法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这些条件,相信以朋友们高超的智慧肯定不是什么难事:)

   

  为避免新朋友没看过【通用查询设计思想】 <https://www.cnblogs.com/lex-wu/p/10389236.html>
这篇文章,个人把这次的变动的类罗列出来

  查询基类(保留表达式的方法,兼容同时使用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,基于本文的中心主题,不再深入讨论其他场景。

 

  让我知道如果你有更好的想法!

  

友情链接
ioDraw流程图
API参考文档
OK工具箱
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:ixiaoyang8@qq.com
QQ群:637538335
关注微信