internal static string AddPagination(this string rawSql, IPagedFilter pagedFilter, IDataSource dataSource) { if (pagedFilter.IsNull()) { return(rawSql); } var recordStart = pagedFilter.Page * pagedFilter.PageSize; if (recordStart < 0) { recordStart = 0; } switch (dataSource.Provider) { case Provider.PostgreSqL: return($"{rawSql} OFFSET ({recordStart}) LIMIT @PageSize "); case Provider.Oracle: pagedFilter.Page = pagedFilter.Page <= 0 ? 1 : pagedFilter.Page; return ($" SELECT * FROM (SELECT a.*, rownum r_ FROM ({rawSql}) a WHERE rownum < (({pagedFilter.Page} * {pagedFilter.PageSize}) + 1) ) WHERE r_ >= ((({pagedFilter.Page} - 1) * {pagedFilter.PageSize}) + 1)"); case Provider.MsSql: return($"{rawSql} OFFSET {recordStart} ROWS FETCH NEXT @PageSize ROWS ONLY"); default: return($"{rawSql} OFFSET {recordStart} ROWS FETCH NEXT @PageSize ROWS ONLY"); } }
internal static string AddPagination(this string rawSql, IPagedFilter pagedFilter, IDataSource dataSource) { if (pagedFilter.IsNull()) { return(rawSql); } var recordStart = (pagedFilter.Page) * pagedFilter.PageSize; if (recordStart < 0) { recordStart = 0; } return(dataSource.Provider switch { Provider.PostgreSqL => $"{rawSql} OFFSET ({recordStart}) LIMIT @PageSize ", _ => $"{rawSql} OFFSET {recordStart} ROWS FETCH NEXT @PageSize ROWS ONLY" });