public string ApplyTo(DbPagerContext context) { if (context == null) throw new ArgumentNullException("context"); /*SELECT * FROM Orders ORDER BY OrderID OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;*/ return string.Format("{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", context.Sql, ((context.PageNumber - 1)*context.PageSize), context.PageSize); }
public string ApplyTo(DbPagerContext context) { if (context == null) { throw new ArgumentNullException("context"); } /*SELECT * * FROM Orders * ORDER BY OrderID * OFFSET 20 ROWS * FETCH NEXT 10 ROWS ONLY;*/ return(string.Format("{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", context.Sql, ((context.PageNumber - 1) * context.PageSize), context.PageSize)); }
private static string ApplyConstraints(IQueryConstraints<User> constraints, string sql) { if (!string.IsNullOrEmpty(constraints.SortPropertyName)) { sql += " ORDER BY " + constraints.SortPropertyName; if (constraints.SortOrder == SortOrder.Descending) sql += " DESC"; } if (constraints.PageNumber != -1) { var context = new DbPagerContext(sql, constraints.PageNumber, constraints.PageSize); var pager = new SqlServerCePager(); sql = pager.ApplyTo(context); } return sql; }
/// <summary> /// Page /// </summary> /// <param name="originalSql">Original SQL statement</param> /// <param name="pageNumber">Page to get</param> /// <param name="pageSize">Number of items per page</param> /// <param name="pkColumn">The primary key column</param> /// <returns>Paged SQL query</returns> public string ApplyTo(DbPagerContext context) { var sqlServerContext = (SqlServerPagerContext)context; var fromIndex = context.Sql.IndexOf("FROM", StringComparison.OrdinalIgnoreCase); var whereIndex = context.Sql.IndexOf("WHERE", fromIndex, StringComparison.OrdinalIgnoreCase); var groupByIndex = context.Sql.IndexOf("GROUP BY", StringComparison.OrdinalIgnoreCase); var orderByIndex = context.Sql.IndexOf("ORDER BY", StringComparison.OrdinalIgnoreCase); var selectColumns = context.Sql.Substring(7, fromIndex - 7).TrimEnd(); string tables = ""; if (fromIndex == -1) { throw new InvalidOperationException("Failed to find a FROM clause"); } else { var startPos = fromIndex + 5; var endPos = whereIndex != -1 ? whereIndex :groupByIndex != -1 ? groupByIndex : orderByIndex != -1 ? orderByIndex : context.Sql.Length; tables = context.Sql.Substring(startPos, endPos - startPos).TrimEnd(); } // where var where = ""; if (whereIndex != -1) { var startPos = whereIndex + 6; var endPos = groupByIndex != -1 ? groupByIndex : orderByIndex != -1 ? orderByIndex : context.Sql.Length; where = context.Sql.Substring(startPos, endPos - startPos).TrimEnd(); } // group by var groupByColumns = ""; if (groupByIndex != -1) { var startPos = groupByIndex + 9; var endPos = orderByIndex != -1 ? orderByIndex : context.Sql.Length; groupByColumns = context.Sql.Substring(startPos, endPos - startPos).TrimEnd(); } // order by var orderByColumns = ""; if (orderByIndex == -1) { orderByColumns = sqlServerContext.PkColumn; } else { var startPos = orderByIndex + 9; var endPos = context.Sql.Length; orderByColumns = context.Sql.Substring(startPos, endPos - startPos); } var firstRow = (context.PageNumber - 1) * context.PageSize; var lastRow = firstRow + context.PageSize; var innerFrom = tables; if (!string.IsNullOrEmpty(where)) { innerFrom += "\r\n WHERE " + where + "\r\n"; } /*WITH Members AS * ( * SELECT M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY, * ROW_NUMBER() OVER (ORDER BY M_POSTS DESC) AS RowNumber * FROM dbo.FORUM_MEMBERS * ) * SELECT RowNumber, M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY * FROM Members * WHERE RowNumber BETWEEN 1 AND 20 * ORDER BY RowNumber ASC; */ var sql = string.Format(@"WITH Paged AS ( SELECT {0}, ROW_NUMBER() OVER (ORDER BY {1}) AS RowNumber FROM {2} ) SELECT RowNumber, {0} FROM Paged WHERE RowNumber BETWEEN {3} AND {4} ", selectColumns, orderByColumns, innerFrom, firstRow, lastRow); if (groupByColumns != "") { sql += "GROUP BY " + groupByColumns + "\r\n"; } sql += "ORDER BY " + orderByColumns; return(sql); }
/// <summary> /// Page /// </summary> /// <param name="originalSql">Original SQL statement</param> /// <param name="pageNumber">Page to get</param> /// <param name="pageSize">Number of items per page</param> /// <param name="pkColumn">The primary key column</param> /// <returns>Paged SQL query</returns> public string ApplyTo(DbPagerContext context) { var sqlServerContext = (SqlServerPagerContext) context; var fromIndex = context.Sql.IndexOf("FROM", StringComparison.OrdinalIgnoreCase); var whereIndex = context.Sql.IndexOf("WHERE", fromIndex, StringComparison.OrdinalIgnoreCase); var groupByIndex = context.Sql.IndexOf("GROUP BY", StringComparison.OrdinalIgnoreCase); var orderByIndex = context.Sql.IndexOf("ORDER BY", StringComparison.OrdinalIgnoreCase); var selectColumns = context.Sql.Substring(7, fromIndex - 7).TrimEnd(); string tables = ""; if (fromIndex == -1) throw new InvalidOperationException("Failed to find a FROM clause"); else { var startPos = fromIndex + 5; var endPos = whereIndex != -1 ? whereIndex :groupByIndex != -1 ? groupByIndex : orderByIndex != -1 ? orderByIndex : context.Sql.Length; tables = context.Sql.Substring(startPos, endPos - startPos).TrimEnd(); } // where var where = ""; if (whereIndex != -1) { var startPos = whereIndex + 6; var endPos = groupByIndex != -1 ? groupByIndex : orderByIndex != -1 ? orderByIndex : context.Sql.Length; where = context.Sql.Substring(startPos, endPos - startPos).TrimEnd(); } // group by var groupByColumns = ""; if (groupByIndex != -1) { var startPos = groupByIndex + 9; var endPos = orderByIndex != -1 ? orderByIndex : context.Sql.Length; groupByColumns = context.Sql.Substring(startPos, endPos - startPos).TrimEnd(); } // order by var orderByColumns = ""; if (orderByIndex == -1) orderByColumns = sqlServerContext.PkColumn; else { var startPos = orderByIndex + 9; var endPos = context.Sql.Length; orderByColumns = context.Sql.Substring(startPos, endPos - startPos); } var firstRow = (context.PageNumber - 1)*context.PageSize; var lastRow = firstRow + context.PageSize; var innerFrom = tables; if (!string.IsNullOrEmpty(where)) innerFrom += "\r\n WHERE " + where + "\r\n"; /*WITH Members AS ( SELECT M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY, ROW_NUMBER() OVER (ORDER BY M_POSTS DESC) AS RowNumber FROM dbo.FORUM_MEMBERS ) SELECT RowNumber, M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY FROM Members WHERE RowNumber BETWEEN 1 AND 20 ORDER BY RowNumber ASC; */ var sql = string.Format(@"WITH Paged AS ( SELECT {0}, ROW_NUMBER() OVER (ORDER BY {1}) AS RowNumber FROM {2} ) SELECT RowNumber, {0} FROM Paged WHERE RowNumber BETWEEN {3} AND {4} ", selectColumns, orderByColumns, innerFrom, firstRow, lastRow); if (groupByColumns != "") sql += "GROUP BY " + groupByColumns + "\r\n"; sql += "ORDER BY " + orderByColumns; return sql; }