通用查询设计思想（2）- 基于ADO.Net的设计

　　前言　　

　　不少公司用的是ADO.NET的访问方式，估计不少朋友对于sql的拼写真是没太多好感，在没有一个封装足够好的底层的项目（还需要考虑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，基于本文的中心主题，不再深入讨论其他场景。

 

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