示例#1
0
        /// <summary>
        /// 更新单个模型
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int Update(TElement entity, IDbTransaction transaction = null)
        {
            string tableInDbName;

            System.Reflection.PropertyInfo[] propertys;
            string[] filelds;
            string[] paras;
            var      sqlFieldMapping = ResolveEntity(entity, true, out tableInDbName, out propertys, out filelds, out paras);

            if (filelds.Length <= 1)
            {
                //除主键后 没有其他字段
                return(-1);

                throw new Exception("未指定除主键后其他字段!");
            }

            StringBuilder sb_FiledParaPairs = new StringBuilder("");
            var           settedValueDic    = entity.GetSettedValuePropertyDic();

            foreach (var item in settedValueDic)
            {
                var keyProperty = item.Key;
                //var value = item.Value;
                if (keyProperty != EntityIdentityFiledName)
                {
                    string fieldName = ResolveLambdaTreeToCondition.SearchPropertyMappingField(sqlFieldMapping, keyProperty);
                    sb_FiledParaPairs.AppendFormat("{1}{0}{1}=@{2},", fieldName, this.FieldWrapperChar, keyProperty);
                }
            }

            //移除最后一个逗号
            var str_FiledParaPairs = sb_FiledParaPairs.ToString();

            str_FiledParaPairs = str_FiledParaPairs.Remove(str_FiledParaPairs.Length - 1);

            StringBuilder sb_Sql = new StringBuilder();

            sb_Sql.Append(string.Format("update {0} set ", tableInDbName)); //Set Table
            sb_Sql.Append(str_FiledParaPairs);                              //参数对



            sb_Sql.AppendFormat(" where {0}=@{0}", EntityIdentityFiledName);//主键


            var sqlCmd = sb_Sql.ToString();

            ///清理掉字符串拼接构造器
            sb_FiledParaPairs.Clear();
            sb_FiledParaPairs = null;
            sb_Sql.Clear();
            sb_Sql = null;
            this.SqlOutPutToLogAsync(sqlCmd, entity);
            using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
            {
                var result = conn.Execute(sqlCmd, entity, transaction);
                return(result);
            }
        }
        /// <summary>
        /// 通过特定的条件查询出元素集合
        /// </summary>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public List <TElement> GetElementsByCondition(Expression <Func <TElement, bool> > predicate)
        {
            TElement entity = new TElement();

            string tableInDbName;

            System.Reflection.PropertyInfo[] propertys;
            string[] filelds;
            string[] paras;
            ResolveEntity(entity, false, out tableInDbName, out propertys, out filelds, out paras);
            if (filelds.Length <= 1)
            {
                //除主键后 没有其他字段
                return(null);

                throw new Exception("未指定除主键后其他字段!");
            }
            //获取字段
            string splitor          = string.Format("{0},{0}", this.FieldWrapperChar);
            var    fieldSplitString = string.Concat(this.FieldWrapperChar, string.Join(splitor, filelds), this.FieldWrapperChar);//返回逗号分隔的字符串 例如:`ProvinceCode`,`ProvinceName`
            //解析查询条件
            string whereStr = "1=1";

            if (null != predicate)
            {
                whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, wrapperChar: this.FieldWrapperChar);
            }



            StringBuilder sb_Sql = new StringBuilder();

            sb_Sql.AppendFormat("select  {0} ", fieldSplitString);
            sb_Sql.AppendFormat(" from {0} ", tableInDbName);
            sb_Sql.AppendFormat(" where {0};", whereStr);


            var sqlCmd = sb_Sql.ToString();

            sb_Sql.Clear();
            sb_Sql = null;

            List <TElement> dataLst = null;

            try
            {
                this.SqlOutPutToLogAsync(sqlCmd);

                using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
                {
                    dataLst = conn.Query <TElement>(sqlCmd).AsList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(dataLst);
        }
示例#3
0
        /// <summary>
        /// 删除符合条件的实体
        /// </summary>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public int DeleteByCondition(Expression <Func <TElement, bool> > predicate, IDbTransaction transaction = null)
        {
            TElement entity = new TElement();


            string tableInDbName;

            System.Reflection.PropertyInfo[] propertys;
            string[] filelds;
            string[] paras;
            var      sqlFieldMapping = ResolveEntity(entity, true, out tableInDbName, out propertys, out filelds, out paras);

            if (filelds.Length <= 1)
            {
                //除主键后 没有其他字段
                return(-1);

                throw new Exception("未指定除主键后其他字段!");
            }

            //解析查询条件
            var whereStr = "1=1";

            if (null != predicate)
            {
                whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, sqlFieldMapping, wrapperChar: this.FieldWrapperChar);
            }

            StringBuilder sb_Sql = new StringBuilder();

            sb_Sql.AppendFormat("delete from {0} ", tableInDbName);
            if (null != predicate)
            {
                sb_Sql.AppendFormat("where  {0}  ", whereStr);
            }

            var sqlCmd = sb_Sql.ToString();

            try
            {
                this.SqlOutPutToLogAsync(sqlCmd);

                using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
                {
                    var result = conn.Execute(sqlCmd, null, transaction);
                    return(result);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 分页获取元素集合-ORM
        /// 注意:mysql 的存储过程 ,调用,不能输出参数。BUG.即使关闭了datareader 也不能关闭 依然isclosed =false
        /// </summary>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="totalRecords">总记录数</param>
        /// <param name="totalPages">总页数</param>
        /// <param name="predicate">条件</param>
        /// <param name="sortField">排序字段</param>
        /// <param name="rule">排序规则</param>
        /// <returns></returns>
        public List <TElement> GetElementsByPagerAndCondition(int pageIndex, int pageSize, out int totalRecords, out int totalPages, Expression <Func <TElement, bool> > predicate, string sortField, OrderRule rule = OrderRule.ASC)
        {
            List <TElement> dataLst = new List <TElement>();
            TElement        entity  = new TElement();

            string tableInDbName;

            System.Reflection.PropertyInfo[] propertys;
            string[] filelds;
            string[] paras;
            ResolveEntity(entity, false, out tableInDbName, out propertys, out filelds, out paras);
            if (filelds.Length <= 1)
            {
                totalRecords = -1;
                totalPages   = -1;
                //除主键后 没有其他字段
                return(null);

                throw new Exception("未指定除主键后其他字段!");
            }
            //获取字段
            string splitor          = string.Format("{0},{0}", this.FieldWrapperChar);
            var    fieldSplitString = string.Concat(this.FieldWrapperChar, string.Join(splitor, filelds), this.FieldWrapperChar);//返回逗号分隔的字符串 例如:`ProvinceCode`,`ProvinceName`
            //解析查询条件
            var whereStr = "1=1";

            if (null != predicate)
            {
                whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, wrapperChar: this.FieldWrapperChar);
            }



            //调用分页存储过程
            StringBuilder sb_Sql = new StringBuilder();

            sb_Sql.Append(Contanst.PageSql_Call_Name);

            var sqlCmd = sb_Sql.ToString();

            var sqlParas = new DynamicParameters();

            sqlParas.Add("@PageIndex", pageIndex);                                               //页索引
            sqlParas.Add("@PageSize", pageSize);                                                 //页大小
            sqlParas.Add("@TableName", tableInDbName);                                           //表名称
            sqlParas.Add("@SelectFields", fieldSplitString);                                     //查询的字段
            sqlParas.Add("@PrimaryKey", EntityIdentityFiledName);                                //查询的表的主键
            sqlParas.Add("@ConditionWhere", whereStr);                                           //查询条件
            sqlParas.Add("@SortField", sortField);                                               //排序字段
            sqlParas.Add("@IsDesc", (int)rule);                                                  //倒排序 正排序
            sqlParas.Add("@TotalRecords", DbType.Int32, direction: ParameterDirection.Output);   //总记录数(可选参数)
            sqlParas.Add("@TotalPageCount", DbType.Int32, direction: ParameterDirection.Output); //总页数(输出参数


            try
            {
                this.SqlOutPutToLogAsync(sqlCmd, sqlParas);

                using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
                {
                    dataLst = conn.Query <TElement>(sqlCmd, sqlParas, commandType: CommandType.StoredProcedure).AsList();
                }

                //查询完毕后 根据输出参数 返回总记录数 总页数
                totalRecords = sqlParas.Get <int>("@TotalRecords");
                totalPages   = sqlParas.Get <int>("@TotalPageCount");
            }
            catch (Exception ex)
            {
                //抛出Native 异常信息
                throw ex;
            }
            finally
            {
                //清理字符串构建
                sb_Sql.Clear();
                sb_Sql    = null;
                propertys = null;
                filelds   = null;
                paras     = null;
            }


            return(dataLst);
        }
        /// <summary>
        /// 更新元素 通过  符合条件的
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="predicate"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int UpdateByCondition(TElement entity, Expression <Func <TElement, bool> > predicate, IDbTransaction transaction = null)
        {
            string tableInDbName;

            System.Reflection.PropertyInfo[] propertys;
            string[] filelds;
            string[] paras;
            ResolveEntity(entity, true, out tableInDbName, out propertys, out filelds, out paras);
            if (filelds.Length <= 1)
            {
                //除主键后 没有其他字段
                return(-1);

                throw new Exception("未指定除主键后其他字段!");
            }


            StringBuilder sb_FiledParaPairs = new StringBuilder("");
            ///解析要更新的列
            var settedValueDic = entity.GetSettedValuePropertyDic();

            foreach (var item in settedValueDic)
            {
                var keyProperty = item.Key;
                //var value = item.Value;
                if (keyProperty != EntityIdentityFiledName)
                {
                    sb_FiledParaPairs.AppendFormat("{1}{0}{1}=@{0},", keyProperty, this.FieldWrapperChar);
                }
            }
            //移除最后一个逗号
            var str_FiledParaPairs = sb_FiledParaPairs.ToString();

            str_FiledParaPairs = str_FiledParaPairs.Remove(str_FiledParaPairs.Length - 1);

            StringBuilder sb_Sql = new StringBuilder();

            sb_Sql.Append(string.Format("update {0} set ", tableInDbName)); //Set Table
            sb_Sql.Append(str_FiledParaPairs);                              //参数对



            if (null != predicate)
            {
                string where = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, wrapperChar: this.FieldWrapperChar);
                sb_Sql.Append(" where "); //解析条件
                sb_Sql.Append(where);     //条件中带有参数=值的  拼接字符串
            }


            var sqlCmd = sb_Sql.ToString();

            ///清理字符串构建
            sb_FiledParaPairs.Clear();
            sb_FiledParaPairs = null;
            sb_Sql.Clear();
            sb_Sql = null;

            this.SqlOutPutToLogAsync(sqlCmd, entity);

            using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
            {
                var result = conn.Execute(sqlCmd, entity, transaction);
                return(result);
            }
        }
        /// <summary>
        /// 统计 符合条件的行数
        /// </summary>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public int Count(Expression<Func<TElement, bool>> predicate)
        {
            TElement entity = new TElement();

            string tableInDbName;
            System.Reflection.PropertyInfo[] propertys;
            string[] filelds;
            string[] paras;
            ResolveEntity(entity, false,out tableInDbName, out propertys, out filelds, out paras);
            if (filelds.Length <= 1)
            {
                //除主键后 没有其他字段
                throw new Exception("未指定除主键后其他字段!");
            }

            //解析查询条件
            string whereStr = "1=1";
            if (null != predicate)
            {
                whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition<TElement>(predicate, FieldWrapperChar);
            }

            StringBuilder sb_Sql = new StringBuilder();
            sb_Sql.AppendFormat("SELECT COUNT(*) FROM  {0} ", tableInDbName);
            if (!string.IsNullOrEmpty(whereStr))
            {
                sb_Sql.AppendFormat("WHERE {0} ", whereStr);
            }

            var sqlCmd = sb_Sql.ToString();

            //清理字符串构建
            sb_Sql.Clear();
            sb_Sql = null;
            DbDataReader reader = null;
            try
            {

                 reader = this.ExecuteReader(sqlCmd, null, CommandType.Text);
                if (reader.Read())
                {
                    var colValue = reader[0].ToString().ToInt();
                    return colValue;
                }
                else
                {
                    throw new Exception("can not  excute sql cmd: " + sqlCmd);
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (null!=reader)
                {
                    reader.Close();
                }
            }
        }
示例#7
0
        /// <summary>
        /// 通过特定的条件查询出元素集合
        /// </summary>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public List <TElement> GetElementsByCondition(Expression <Func <TElement, bool> > predicate)
        {
            TElement entity = new TElement();

            string tableInDbName;

            System.Reflection.PropertyInfo[] propertys;
            string[] filelds;
            string[] paras;
            var      sqlFieldMapping = ResolveEntity(entity, false, out tableInDbName, out propertys, out filelds, out paras);

            if (filelds.Length <= 1)
            {
                //除主键后 没有其他字段
                return(null);

                throw new Exception("未指定除主键后其他字段!");
            }
            //获取字段
            //List<string> fieldAlias = new List<string>();
            //foreach (var item in sqlFieldMapping.Filelds)
            //{
            //    var ailasName = string.Format("{0} as {1}", item.FieldColumnName, item.PropertyName);
            //    fieldAlias.Add(ailasName);
            //}
            //获取字段
            var fieldSplitString = "*";//entity.GetSqlQueryFieldsWithAlias();// String.Join(",", fieldAlias);//返回逗号分隔的字符串 例如:ProvinceCode,ProvinceName,Submmary


            //解析查询条件
            string whereStr = "1=1";

            if (null != predicate)
            {
                whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, sqlFieldMapping);
            }



            StringBuilder sb_Sql = new StringBuilder();

            sb_Sql.AppendFormat("select  {0} ", fieldSplitString);
            sb_Sql.AppendFormat(" from {0} ", tableInDbName);
            sb_Sql.AppendFormat(" where {0};", whereStr);


            var sqlCmd = sb_Sql.ToString();

            sb_Sql.Clear();
            sb_Sql = null;

            List <TElement> dataLst = null;

            try
            {
                this.SqlOutPutToLogAsync(sqlCmd);

                using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
                {
                    dataLst = conn.Query <TElement>(sqlCmd).AsList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(dataLst);
        }
示例#8
0
        /// <summary>
        /// 使用指定的条件 汇总列
        /// </summary>
        /// <param name="predicate"></param>
        /// <param name="specialColumn"></param>
        /// <returns></returns>
        public decimal Sum(Expression <Func <TElement, bool> > predicate, Fields <TElement> specialColumn)
        {
            TElement entity = new TElement();


            string tableInDbName;

            System.Reflection.PropertyInfo[] propertys;
            string[] filelds;
            string[] paras;
            var      sqlFieldMapping = ResolveEntity(entity, false, out tableInDbName, out propertys, out filelds, out paras);

            if (filelds.Length <= 1)
            {
                //除主键后 没有其他字段
                throw new Exception("未指定除主键后其他字段!");
            }

            //解析查询条件
            string whereStr = "1=1";

            if (null != predicate)
            {
                whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, sqlFieldMapping, FieldWrapperChar);
            }



            StringBuilder sb_Sql = new StringBuilder();

            sb_Sql.AppendFormat("SELECT SUM({2}{0}{2}) FROM  {1} ", specialColumn.Container_Fileds.FirstOrDefault(), tableInDbName, FieldWrapperChar);
            if (!string.IsNullOrEmpty(whereStr))
            {
                sb_Sql.AppendFormat("WHERE {0} ", whereStr);
            }


            var sqlCmd = sb_Sql.ToString();

            //清理字符串构建
            sb_Sql.Clear();
            sb_Sql = null;
            DbDataReader reader = null;

            try
            {
                reader = this.ExecuteReader(sqlCmd, null, CommandType.Text);
                if (reader.Read())
                {
                    if (reader[0].ToString().IsEmpty())
                    {
                        return(0);
                    }
                    var colValue = reader.GetFieldValue <decimal>(0);
                    return(colValue);
                }
                else
                {
                    throw new Exception("can not  excute sql cmd: " + sqlCmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (null != reader)
                {
                    reader.Close();
                }
            }
        }