获取分页数据 电脑版发表于:2022/11/16 16:57 #region 获取符合条件的数据(分页) /// <summary> /// 获取符合条件的数据(分页) /// </summary> [Route("Select")] [HttpPost] [AdviceWebApi] public HttpResponseMessage Select() { var filter = Request.GetPagingFilter(); var service = new TaxRegisterMasterService(); var result = service.GetPage(filter); return Request.CreateSuccessResponse(result); } #endregion #region 获取符合条件的数据(分页) /// <summary> /// 获取符合条件的数据(分页) /// </summary> public PagingResultDataTable GetPage(PagingFilter filter) { if (filter == null) return null; return dal.GetPage(filter); } #endregion #region 获取符合条件的数据(分页) /// <summary> /// 获取符合条件的数据(分页) /// </summary> public PagingResultDataTable GetPage(PagingFilter filter, JCPX.DB.Database db = null) { if (filter == null) return null; string order; if (!string.IsNullOrEmpty(filter.Order) && !string.IsNullOrEmpty(filter.Direction)) { order = $"{filter.Order} {filter.Direction}"; } else { order = "Id DESC"; } var datapermissions = GetDataPermissions(PermissionSource.GetPage); var sqlPub = GetSQL(XmlPath); var sqlCreator = new SQLCreator(XmlPath, "GetPageList"); var sql = sqlCreator.GetSql(); var where = GetWhereString(filter.Wheres, sqlCreator.GetRenameDic()); var newSql = sql.Replace("--{sql}", sqlPub.Replace("--{into}", "INTO #temp")) .Replace("--{where}", where) .Replace("--{order}", order) .Replace("--{datapermissions}", datapermissions); newSql = GetNewSQL(newSql, PermissionSource.GetPage); var totalRows = JCPX.DAL.DbUtility.CreateDatabase(db) .CreateSqlCommand(newSql) .AddInParameter("@PageIndex", filter.PageIndex) .AddInParameter("@PageSize", filter.PageSize) .AddOutParameter("@TotalRows", DbType.Int32, null) .FetchDataTableByOutputScale(db, out var result) .JCPXConvert().ToInt32(); return new PagingResultDataTable(totalRows, result); } #endregion SELECT --{top} M.* ,dbo.fns_GetStatusName(M.Status) AS StatusName ,DT1.Name AS CompanyNatureName --公司性质(CompanyNature) FROM tbd_TaxRegisterMaster M LEFT JOIN tbs_Dict DT1 ON DT1.Code=M.CompanyNature --公司性质(CompanyNature) WHERE 1=1 XML <DataCommands Database="YNRF" ConfigDescrip="云南人防"> <DataCommand Name="GetDictField" CommandType="Text" CommandDescrip="数据字典字段语句"> <CommandText> <![CDATA[ ,DT1.Name AS CompanyNatureName --公司性质(CompanyNature) ]]> </CommandText> </DataCommand> <DataCommand Name="GetDictTable" CommandType="Text" CommandDescrip="数据字典关联表语句"> <CommandText> <![CDATA[ LEFT JOIN tbs_Dict DT1 ON DT1.Code=M.CompanyNature --公司性质(CompanyNature) ]]> </CommandText> </DataCommand> <DataCommand Name="GetSQL" CommandType="Text" CommandDescrip="获取符合条件的数据(公用SQL语句)"> <CommandText> <![CDATA[ SELECT --{top} M.* ,dbo.fns_GetStatusName(M.Status) AS StatusName --{dictfield} --{into} FROM tbd_TaxRegisterMaster M --{dicttable} ]]> </CommandText> </DataCommand> <DataCommand Name="GetList" CommandType="Text" CommandDescrip="获取符合条件的数据(不分页)"> <CommandText> <![CDATA[ --{sql} WHERE 1=1 --{where} --{datapermissions} --{order} ]]> </CommandText> <Renames> <Rename OldName="M.Status" NewName="Status"></Rename> <Rename OldName="M.BillDate" NewName="BillDate"></Rename> <Rename OldName="M.BillCode" NewName="BillCode"></Rename> </Renames> </DataCommand> <DataCommand Name="GetPageList" CommandType="Text" CommandDescrip="获取符合条件的数据(分页)"> <CommandText> <![CDATA[ --{sql} WHERE 1=1 --{where} --{datapermissions} SELECT *, ROW_NUMBER() OVER (ORDER BY --{order}) ROWS INTO #result FROM #temp WHERE 1 = 1 --{1}; SELECT @TotalRows=COUNT(0) FROM #result; SELECT * FROM #result WHERE ROWS BETWEEN @PageIndex * @PageSize + 1 AND (@PageIndex + 1) * @PageSize; TRUNCATE TABLE #temp;TRUNCATE TABLE #result; DROP TABLE #temp;DROP TABLE #result; ]]> </CommandText> <Renames> <Rename OldName="M.Status" NewName="Status"></Rename> <Rename OldName="M.BillDate" NewName="BillDate"></Rename> <Rename OldName="M.BillCode" NewName="BillCode"></Rename> </Renames> </DataCommand>