/// <summary>
        /// 条件筛选 例如:expression 为 it=>it.a  inValues值为 new list《string》{"a" ,"b"} 生成的SQL就是  a in('a','b')
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static Queryable <T> In <T, FieldType>(this Queryable <T> queryable, Expression <Func <T, object> > expression, List <FieldType> inValues)
        {
            ResolveExpress re          = new ResolveExpress();
            var            InFieldName = re.GetExpressionRightField(expression);

            return(In <T, FieldType>(queryable, InFieldName, inValues));
        }
Beispiel #2
0
        /// <summary>
        /// 假删除,根据表达示
        /// 使用说明::
        /// FalseDelete《T》(new int[]{1,2,3})或者Delete《T》(3)
        /// </summary>
        /// <param name="field">更新删除状态字段</param>
        /// <param name="expression">筛选表达示</param>
        public bool FalseDelete <T>(string field, Expression <Func <T, bool> > expression)
        {
            Type   type     = typeof(T);
            string typeName = type.Name;

            typeName = GetTableNameByClassType(typeName);
            //属性缓存
            string cachePropertiesKey     = "db." + type.FullName + ".GetProperties";
            var    cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();

            PropertyInfo[] props = null;
            if (cachePropertiesManager.ContainsKey(cachePropertiesKey))
            {
                props = cachePropertiesManager[cachePropertiesKey];
            }
            else
            {
                props = type.GetProperties();
                cachePropertiesManager.Add(cachePropertiesKey, props, cachePropertiesManager.Day);
            }
            bool           isSuccess = false;
            ResolveExpress re        = new ResolveExpress();

            re.ResolveExpression(re, expression);
            string sql            = string.Format("UPDATE  {0} SET {1}=1 WHERE  1=1 {2}", typeName, field, re.SqlWhere);
            int    deleteRowCount = ExecuteCommand(sql, re.Paras.ToArray());

            isSuccess = deleteRowCount > 0;
            return(isSuccess);
        }
        /// <summary>
        /// 获取最小值
        /// </summary>
        /// <typeparam name="TResult"></typeparam>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="minField">列</param>
        /// <returns></returns>
        public static object Min <T>(this Queryable <T> queryable, Expression <Func <T, object> > expression)
        {
            ResolveExpress re       = new ResolveExpress();
            var            minField = re.GetExpressionRightField(expression);

            return(Min <T, object>(queryable, minField));
        }
        /// <summary>
        /// 分组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="expression">分组字段 it=>it.fieldName</param>
        /// <returns></returns>
        public static Queryable <T> GroupBy <T>(this Queryable <T> queryable, Expression <Func <T, object> > expression)
        {
            ResolveExpress re    = new ResolveExpress();
            var            field = re.GetExpressionRightField(expression);
            var            pre   = queryable.GroupBy.IsValuable() ? "," : "";

            queryable.GroupBy += pre + field;
            return(queryable);
        }
        /// <summary>
        /// 排序
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="expression">例如 (s1,s2)=>s1.id,相当于 order by s1.id</param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static Queryable <T> OrderBy <T, T2>(this Queryable <T> queryable, Expression <Func <T, T2, object> > expression, OrderByType type = OrderByType.asc)
        {
            ResolveExpress re    = new ResolveExpress();
            var            field = re.GetExpressionRightFieldByNT(expression);
            var            pre   = queryable.OrderBy.IsValuable() ? "," : "";

            queryable.OrderBy += pre + field + " " + type.ToString().ToUpper();
            return(queryable);
        }
        /// <summary>
        /// 条件筛选 例如:InFieldName 为 a inValues 值为 new string[]{"a" ,"b"} 生成的SQL就是  a in('a','b')
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static Queryable <T> In <T, FieldType>(this Queryable <T> queryable, string InFieldName, params FieldType[] inValues)
        {
            var type = queryable.Type;

            queryable.WhereIndex = queryable.WhereIndex + 100;
            ResolveExpress re = new ResolveExpress(queryable.WhereIndex);

            queryable.Where.Add(string.Format(" AND {0} IN ({1})", InFieldName, inValues.ToJoinSqlInVal()));
            return(queryable);
        }
        /// <summary>
        ///  返回序列的唯一元素;如果该序列并非恰好包含一个元素,则会引发异常。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static bool Any <T>(this  Queryable <T> queryable, Expression <Func <T, bool> > expression)
        {
            var type = queryable.Type;

            queryable.WhereIndex = queryable.WhereIndex + 100;
            ResolveExpress re = new ResolveExpress(queryable.WhereIndex);

            re.ResolveExpression(re, expression);
            queryable.Where.Add(re.SqlWhere);
            queryable.Params.AddRange(re.Paras);
            return(queryable.Count() > 0);
        }
        /// <summary>
        /// 返回序列中的第一个元素,如果序列为NULL返回default(T)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static T FirstOrDefault <T>(this  Queryable <T> queryable, Expression <Func <T, bool> > expression)
        {
            var type = queryable.Type;

            queryable.WhereIndex = queryable.WhereIndex + 100;
            ResolveExpress re = new ResolveExpress(queryable.WhereIndex);

            re.ResolveExpression(re, expression);
            queryable.Where.Add(re.SqlWhere);
            queryable.Params.AddRange(re.Paras);
            return(FirstOrDefault <T>(queryable));
        }
Beispiel #9
0
        /// <summary>
        /// 解析表达式
        /// </summary>
        /// <param name="re">当前解析对象</param>
        /// <param name="exp">要解析的表达式</param>
        /// <param name="db">数据库访问对象</param>
        public void ResolveExpression(ResolveExpress re, Expression exp, SqlSugarClient db)
        {
            DB = db;
            //初始化表达式
            Init(re, exp);

            //设置PageSize
            foreach (var par in Paras)
            {
                SqlSugarTool.SetParSize(par);
            }
        }
        /// <summary>
        /// 条件筛选
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static Queryable <T> Where <T, T2, T3>(this Queryable <T> queryable, Expression <Func <T, T2, T3, object> > expression)
        {
            var type = queryable.Type;

            queryable.WhereIndex = queryable.WhereIndex + 100;
            ResolveExpress re = new ResolveExpress(queryable.WhereIndex);

            re.Type = ResolveExpressType.nT;
            re.ResolveExpression(re, expression);
            queryable.Params.AddRange(re.Paras);
            queryable.Where.Add(re.SqlWhere);
            return(queryable);
        }
Beispiel #11
0
        /// <summary>
        /// 删除,根据表达示
        /// 使用说明:
        /// Delete《T》(it=>it.id=100) 或者Delete《T》(3)
        /// </summary>
        /// <param name="expression">筛选表达示</param>
        public bool Delete <T>(Expression <Func <T, bool> > expression)
        {
            Type   type     = typeof(T);
            string typeName = type.Name;

            typeName = GetTableNameByClassType(typeName);
            ResolveExpress re = new ResolveExpress();

            re.ResolveExpression(re, expression);
            string sql       = string.Format("DELETE FROM {0} WHERE 1=1 {1}", typeName, re.SqlWhere);
            bool   isSuccess = ExecuteCommand(sql, re.Paras.ToArray()) > 0;

            return(isSuccess);
        }
Beispiel #12
0
 /// <summary>
 /// 初始化表达式
 /// </summary>
 /// <param name="re"></param>
 /// <param name="exp"></param>
 private void Init(ResolveExpress re, Expression exp)
 {
     ResolveExpress.MemberType type = ResolveExpress.MemberType.None;
     //解析表达式
     this.SqlWhere = string.Format(" AND {0} ", re.CreateSqlElements(exp, ref type, true));
     //还原bool值
     foreach (var item in ConstantBoolDictionary)
     {
         if (this.SqlWhere.IsValuable())
         {
             this.SqlWhere = this.SqlWhere.Replace(item.Key.ToString(), item.ConditionalValue);
         }
     }
 }
        /// <summary>
        /// 条件筛选
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static Queryable <T> Where <T>(this Queryable <T> queryable, Expression <Func <T, bool> > expression)
        {
            var type = queryable.Type;

            queryable.WhereIndex = queryable.WhereIndex + 100;
            ResolveExpress re = new ResolveExpress(queryable.WhereIndex);

            if (queryable.JoinTable.IsValuable())
            {
                re.Type = ResolveExpressType.nT;
            }
            re.ResolveExpression(re, expression);
            queryable.Params.AddRange(re.Paras);
            queryable.Where.Add(re.SqlWhere);
            return(queryable);
        }
Beispiel #14
0
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="FiledType">whereIn里面元素的类型</typeparam>
        /// <param name="expression">in 的字段名称</param>
        /// <param name="whereIn">需要删除条件值的数组集合</param>
        /// <returns></returns>
        public bool Delete <T, FiledType>(Expression <Func <T, object> > expression, params FiledType[] whereIn)
        {
            ResolveExpress re        = new ResolveExpress();
            var            fieldName = re.GetExpressionRightField(expression);
            Type           type      = typeof(T);
            string         typeName  = type.Name;

            typeName = GetTableNameByClassType(typeName);
            //属性缓存
            string cachePropertiesKey     = "db." + type.FullName + ".GetProperties";
            var    cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();

            PropertyInfo[] props     = SqlSugarTool.GetGetPropertiesByCache(type, cachePropertiesKey, cachePropertiesManager);
            bool           isSuccess = false;

            if (whereIn != null && whereIn.Length > 0)
            {
                string sql            = string.Format("DELETE FROM {0} WHERE {1} IN ({2})", typeName, fieldName, whereIn.ToJoinSqlInVal());
                int    deleteRowCount = ExecuteCommand(sql);
                isSuccess = deleteRowCount > 0;
            }
            return(isSuccess);
        }
        /// <summary>
        /// 联表查询
        /// </summary>
        /// <typeparam name="T">第一个表的对象</typeparam>
        /// <typeparam name="T2">联接的表对象</typeparam>
        /// <param name="queryable"></param>
        /// <param name="expression">表达示</param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static Queryable <T> JoinTable <T, T2, T3>(this Queryable <T> queryable, Expression <Func <T, T2, T3, object> > expression, JoinType type = JoinType.LEFT)
        {
            ResolveExpress re = new ResolveExpress();

            queryable.WhereIndex = queryable.WhereIndex + 100;
            re.Type = ResolveExpressType.nT;
            var exLeftStr   = Regex.Match(expression.ToString(), @"\((.+?)\).+").Groups[1].Value;
            var exLeftArray = exLeftStr.Split(',');
            var shortName1  = exLeftArray[1];
            var shortName2  = exLeftArray[2];

            re.ResolveExpression(re, expression);
            string joinTableName = type.ToString();
            string joinStr       = string.Format(" {0} JOIN {1} {2} ON {3}  ",
                                                 /*0*/ queryable.JoinTable.Count == 0 ? (" " + shortName1 + " " + joinTableName) : joinTableName.ToString(),
                                                 /*1*/ typeof(T3).Name,
                                                 /*2*/ shortName2,
                                                 /*3*/ re.SqlWhere.Trim().TrimStart('A').TrimStart('N').TrimStart('D')
                                                 );

            queryable.JoinTable.Add(joinStr);
            queryable.Params.AddRange(re.Paras);
            return(queryable);
        }
Beispiel #16
0
        public void ResolveExpression(ResolveExpress re, Expression exp)
        {
            ResolveExpress.MemberType type = ResolveExpress.MemberType.None;
            var expStr = exp.ToString();
            var isNotBool = !expStr.Contains("True") && !expStr.Contains("False");
            var isContainsNot = expStr.Contains("Not");
            if (isContainsNot && expStr.IsMatch(@" => Not\(.+?\)") && !expStr.Contains("Contains"))
            {
                this.SqlWhere = string.Format(" AND {0}=0 or {0} is null ", Regex.Match(expStr, @" => Not\(.+\.(.+?)\)").Groups[1].Value);
            }
            else if (isNotBool)
            {
                this.SqlWhere = string.Format(" AND {0} ", re.CreateSqlElements(exp, ref type));
            }
            else
            {
                var isTrue = Regex.IsMatch(expStr, @"\=\> True$");
                var isFalse = Regex.IsMatch(expStr, @"\=\> False$");
                if (isFalse)
                {
                    this.SqlWhere = string.Format(" AND 1<>1 ");
                }
                else if (isTrue)
                {

                }
                else
                {
                    this.SqlWhere = string.Format(" AND {0} ", re.CreateSqlElements(exp, ref type));
                }
            }
            foreach (var par in Paras)
            {
                SqlSugarTool.SetParSize(par);
            }
        }
Beispiel #17
0
        /// <summary>
        /// 更新
        /// 注意:rowObj为T类型将更新该实体的非主键所有列,如果rowObj类型为匿名类将更新指定列
        /// 使用说明:sqlSugar.Update《T》(rowObj,whereObj);
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="rowObj">new T(){name="张三",sex="男"}或者new {name="张三",sex="男"}</param>
        /// <param name="expression">it.id=100</param>
        /// <returns></returns>
        public bool Update <T>(object rowObj, Expression <Func <T, bool> > expression) where T : class
        {
            if (rowObj == null)
            {
                throw new ArgumentNullException("SqlSugarClient.Update.rowObj");
            }
            if (expression == null)
            {
                throw new ArgumentNullException("SqlSugarClient.Update.expression");
            }


            Type   type     = typeof(T);
            string typeName = type.Name;

            typeName = GetTableNameByClassType(typeName);
            var rows      = SqlSugarTool.GetParameters(rowObj);
            var isDynamic = rowObj.GetType() != type;
            var isClass   = !isDynamic;

            //sql语句缓存
            string cacheSqlKey     = "db.update." + type.FullName + rows.Length;
            var    cacheSqlManager = CacheManager <StringBuilder> .GetInstance();



            string pkName        = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName);
            var    identityNames = SqlSugarTool.GetIdentitiesKeyByTableName(this, typeName);


            ResolveExpress re = new ResolveExpress();

            re.ResolveExpression(re, expression);


            StringBuilder sbSql = new StringBuilder();

            if (cacheSqlManager.ContainsKey(cacheSqlKey) && isClass)
            {
                sbSql = cacheSqlManager[cacheSqlKey];
            }
            else
            {
                sbSql = new StringBuilder(string.Format(" UPDATE {0} SET ", typeName));
                foreach (var r in rows)
                {
                    var name                   = r.ParameterName.TrimStart(':');
                    var isPk                   = pkName != null && pkName.ToLower() == name.ToLower();
                    var isIdentity             = identityNames.Any(it => it.Value.ToLower() == name.ToLower());
                    var isDisableUpdateColumns = DisableUpdateColumns != null && DisableUpdateColumns.Any(it => it.ToLower() == name.ToLower());

                    if (this.IsIgnoreErrorColumns)
                    {
                        if (!SqlSugarTool.GetColumnsByTableName(this, typeName).Any(it => it.ToLower() == name.ToLower()))
                        {
                            continue;
                        }
                    }

                    if (isPk || isIdentity || isDisableUpdateColumns)
                    {
                        if (isClass)
                        {
                            continue;
                        }
                    }
                    sbSql.Append(string.Format(" {0} =:{0}  ,", name));
                }
                sbSql.Remove(sbSql.Length - 1, 1);
                sbSql.Append(" WHERE  1=1  ");
                sbSql.Append(re.SqlWhere);
                cacheSqlManager.Add(cacheSqlKey, sbSql, cacheSqlManager.Day);
            }

            List <OracleParameter> parsList = new List <OracleParameter>();

            parsList.AddRange(re.Paras);
            var pars = rows;

            if (pars != null)
            {
                foreach (var par in pars)
                {
                    SqlSugarTool.SetParSize(par);
                    parsList.Add(par);
                }
            }
            try
            {
                var updateRowCount = ExecuteCommand(sbSql.ToString(), parsList.ToArray());
                return(updateRowCount > 0);
            }
            catch (Exception ex)
            {
                throw new Exception("sql:" + sbSql.ToString() + "\n" + ex.Message);
            }
        }