Ejemplo n.º 1
0
        private void ConvertQueryModel(QueryModel queryModel, PageQuerySqlEntity sqlEntity)
        {
            //1、Fields——fields
            if (queryModel.Fields.IsEmpty())
            {
                throw new Exception("查询字段不能为空!");
            }
            //重建表名——将bd.dict_code转换为bd.dict_code 'bd.dict_code',bd.dict_code bd_dict_code
            //var fields = queryModel.Fields.ToList();
            var fields = new List <string>();

            queryModel.Fields.ToList().ForEach(f => fields.Add(string.Format("{0} {1}", f, f.Replace('.', '_'))));
            sqlEntity.fields = string.Join(",", fields);
            //2、TableRelations——tables
            if (queryModel.TableRelation == null || queryModel.TableRelation.PrimeTable.IsEmpty())
            {
                throw new Exception("查询表不能为空!");
            }
            StringBuilder sb = new StringBuilder();

            ConvertTableRelation(queryModel, sb);
            sqlEntity.tables = sb.ToString();
            //3、Conditions——where
            ConvertConditions(queryModel.Conditions, sqlEntity.where);
            //4、Sorts——order
            ConvertSorts(queryModel.Sorts, sqlEntity.order);
        }
Ejemplo n.º 2
0
        public OptResult Query(QueryModel queryModel)
        {
            OptResult rst = null;

            if (queryModel == null)
            {
                rst = OptResult.Build(ResultCode.ParamError, Msg_ExeQuery + ",查询模型参数不能为空!");
                return(rst);
            }
            try
            {
                PageQuerySqlEntity sqlEntity = _cqRep.GetPageQuerySql(SqlName_PageQuery);
                if (sqlEntity == null)
                {
                    rst = OptResult.Build(ResultCode.ParamError, Msg_ExeQuery + ",未能获取sql配置!");
                    return(rst);
                }
                //1、查询模型转换
                ConvertQueryModel(queryModel, sqlEntity);
                //2、执行查询
                IEnumerable <dynamic> data = _cqRep.PageQueryBySp <dynamic>(sqlEntity: sqlEntity, page: queryModel.Page);
                rst = OptResult.Build(ResultCode.Success, Msg_ExeQuery, new
                {
                    total     = queryModel.Page.total,
                    pagecount = queryModel.Page.pageTotal,
                    rows      = data
                });
            }
            catch (Exception ex)
            {
                LogHelper.LogError(Msg_ExeQuery, ex);
                rst = OptResult.Build(ResultCode.DbError, Msg_ExeQuery);
            }
            return(rst);
        }
Ejemplo n.º 3
0
        public OptResult QueryByPage(PageQuery page)
        {
            OptResult rst = null;

            if (page == null)
            {
                rst = OptResult.Build(ResultCode.ParamError, Msg_QueryByPage + ",分页参数不能为空!");
                return(rst);
            }
            PageQuerySqlEntity sqlEntity = _perRep.GetPageQuerySql(SqlName_PageQuery);

            if (sqlEntity == null)
            {
                rst = OptResult.Build(ResultCode.ParamError, Msg_QueryByPage + ",未能获取sql配置!");
                return(rst);
            }
            //构造where
            #region where条件
            if (page.conditions != null && page.conditions.Count > 0)
            {
                if (page.conditions.ContainsKey("per_code") && !page.conditions["per_code"].IsEmpty())
                {
                    sqlEntity.where.AppendFormat(" and p.per_code='{0}' ", page.conditions["per_code"]);
                }
                if (page.conditions.ContainsKey("per_name") && !page.conditions["per_name"].IsEmpty())
                {
                    sqlEntity.where.AppendFormat(" and p.per_name like '%{0}%' ", page.conditions["per_name"]);
                }
                if (page.conditions.ContainsKey("per_type") && !page.conditions["per_type"].IsEmpty())
                {
                    sqlEntity.where.AppendFormat(" and p.per_type = '{0}' ", page.conditions["per_type"]);
                }
                if (page.conditions.ContainsKey("per_parent") && !page.conditions["per_parent"].IsEmpty())
                {
                    sqlEntity.where.AppendFormat(" and p.per_parent = '{0}' ", page.conditions["per_parent"]);
                }
                else if (page.conditions.ContainsKey("per_parent_name") && !page.conditions["per_parent_name"].IsEmpty())
                {
                    sqlEntity.where.AppendFormat(" and pp.per_name like '%{0}%' ", page.conditions["per_parent_name"]);
                }
            }
            #endregion

            try
            {
                var pers = _perRep.PageQueryBySp <dynamic>(sqlEntity: sqlEntity, page: page);
                rst = OptResult.Build(ResultCode.Success, Msg_QueryByPage, new
                {
                    total     = page.total,
                    pagecount = page.pageTotal,
                    rows      = pers
                });
            }
            catch (Exception ex)
            {
                LogHelper.LogError(Msg_QueryByPage, ex);
                rst = OptResult.Build(ResultCode.DbError, Msg_QueryByPage);
            }
            return(rst);
        }
Ejemplo n.º 4
0
        public OptResult QueryByPage(PageQuery page)
        {
            OptResult rst = null;

            if (page == null)
            {
                rst = OptResult.Build(ResultCode.ParamError, Msg_QueryByPage + ",分页参数不能为空!");
                return(rst);
            }
            PageQuerySqlEntity sqlEntity = _fieldRep.GetPageQuerySql(SqlName_PageQuery);

            if (sqlEntity == null)
            {
                rst = OptResult.Build(ResultCode.ParamError, Msg_QueryByPage + ",未能获取sql配置!");
                return(rst);
            }
            //构造where
            #region where条件
            if (page.conditions != null && page.conditions.Count > 0)
            {
                if (page.conditions.ContainsKey("tbname") && !page.conditions["tbname"].IsEmpty())
                {
                    sqlEntity.where.AppendFormat(" and qt.tbname like '%{0}%' ", page.conditions["tbname"]);
                }
                if (page.conditions.ContainsKey("tbalias") && !page.conditions["tbalias"].IsEmpty())
                {
                    sqlEntity.where.AppendFormat(" and qt.alias like '%{0}%' ", page.conditions["tbalias"]);
                }
                if (page.conditions.ContainsKey("tbid") && !page.conditions["tbid"].IsEmpty())
                {
                    sqlEntity.where.AppendFormat(" and qf.tbid = '{0}' ", page.conditions["tbid"]);
                }
                if (page.conditions.ContainsKey("fieldname") && !page.conditions["fieldname"].IsEmpty())
                {
                    sqlEntity.where.AppendFormat(" and qf.fieldname like '%{0}%' ", page.conditions["fieldname"]);
                }
                if (page.conditions.ContainsKey("displayname") && !page.conditions["displayname"].IsEmpty())
                {
                    sqlEntity.where.AppendFormat(" and qf.displayname like '%{0}%' ", page.conditions["displayname"]);
                }
            }
            #endregion
            try
            {
                var fields = _fieldRep.PageQueryBySp <FieldDto>(sqlEntity: sqlEntity, page: page);
                rst = OptResult.Build(ResultCode.Success, Msg_QueryByPage, new
                {
                    total     = page.total,
                    pagecount = page.pageTotal,
                    rows      = fields
                });
            }
            catch (Exception ex)
            {
                LogHelper.LogError(Msg_QueryByPage, ex);
                rst = OptResult.Build(ResultCode.DbError, Msg_QueryByPage);
            }
            return(rst);
        }
Ejemplo n.º 5
0
        private DynamicParameters BuildParamsForPageQueryBySp(PageQuerySqlEntity sqlEntity, PageQuery page)
        {
            var param = new DynamicParameters();

            param.Add("_fields", sqlEntity.fields);
            param.Add("_tables", sqlEntity.tables);
            param.Add("_where", sqlEntity.where.ToString());
            param.Add("_orderby", sqlEntity.order.ToString());
            param.Add("_pageindex", page.pageIndex, dbType: System.Data.DbType.Int32);
            param.Add("_pageSize", page.pageSize, dbType: System.Data.DbType.Int32);
            param.Add("_totalcount", page.total, dbType: System.Data.DbType.Int32, direction: ParameterDirection.Output);
            param.Add("_pagecount", page.pageTotal, dbType: System.Data.DbType.Int32, direction: ParameterDirection.Output);
            return(param);
        }
Ejemplo n.º 6
0
 public IEnumerable <TReturn> PageQueryBySp <TFirst, TSecond, TReturn>(PageQuerySqlEntity sqlEntity, PageQuery page, Func <TFirst, TSecond, TReturn> map, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int?commandTimeout = null)
 {
     try
     {
         var param = BuildParamsForPageQueryBySp(sqlEntity, page);
         var lst   = this.DbSession.Connection.Query <TFirst, TSecond, TReturn>(sql: sqlEntity.sp_name, map: map, param: param,
                                                                                transaction: transaction, buffered: buffered, splitOn: splitOn, commandTimeout: commandTimeout, commandType: CommandType.StoredProcedure);
         page.total     = param.Get <Int32>("_totalcount");
         page.pageTotal = param.Get <Int32>("_pagecount");
         return(lst);
     }
     catch
     {
         Dispose();
         throw;
     }
 }
Ejemplo n.º 7
0
        public OptResult QueryByPage(PageQuery page)
        {
            OptResult rst = null;

            if (page == null)
            {
                rst = OptResult.Build(ResultCode.ParamError, Msg_PageQuery + ",分页参数不能为空!");
                return(rst);
            }

            PageQuerySqlEntity sqlEntity = _cardRep.GetPageQuerySql(SqlName_PageQuery);

            if (sqlEntity == null)
            {
                rst = OptResult.Build(ResultCode.ParamError, Msg_PageQuery + ",未能获取sql配置!");
                return(rst);
            }
            if (page.conditions != null && page.conditions.Count > 0)
            {
                if (page.conditions.ContainsKey("idcard") && page.conditions["idcard"].IsNotEmpty())
                {
                    sqlEntity.where.AppendFormat(" and cn.idcard='{0}' ", page.conditions["idcard"]);
                }
            }
            try
            {
                var cards = _cardRep.PageQueryBySp <dynamic>(sqlEntity: sqlEntity, page: page);
                rst = OptResult.Build(ResultCode.Success, Msg_PageQuery, new
                {
                    total     = page.total,
                    pagecount = page.pageTotal,
                    rows      = cards
                });
            }
            catch (Exception ex)
            {
                LogHelper.LogError(Msg_PageQuery, ex);
                rst = OptResult.Build(ResultCode.DbError, Msg_PageQuery);
            }
            return(rst);
        }