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); }
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); }
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); }
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); }
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); }
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; } }
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); }