private static void BuildSelectClauseForPagingQuery(MsSqlSelectParser sqlQuery, SqlString limit, SqlStringBuilder result) { result.Add(sqlQuery.Sql.Substring(0, sqlQuery.SelectIndex)); result.Add("SELECT "); if (limit != null) { result.Add("TOP (").Add(limit).Add(") "); } else { // ORDER BY can only be used in subqueries if TOP is also specified. result.Add("TOP (" + int.MaxValue + ") "); } var sb = new StringBuilder(); foreach (var column in sqlQuery.SelectColumns) { if (sb.Length > 0) { sb.Append(", "); } sb.Append(column.Alias); } result.Add(sb.ToString()); }
private static void BuildFromClauseForPagingDistinctQuery(MsSqlSelectParser sqlQuery, SqlStringBuilder result) { result.Add(" FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY "); int orderIndex = 0; foreach (var order in sqlQuery.Orders) { if (orderIndex++ > 0) { result.Add(", "); } if (!order.Column.InSelectClause) { throw new HibernateException( "The dialect was unable to perform paging of a statement that requires distinct results, and " + "is ordered by a column that is not included in the result set of the query."); } result.Add("q_.").Add(order.Column.Alias); if (order.IsDescending) { result.Add(" DESC"); } } if (orderIndex == 0) { result.Add("CURRENT_TIMESTAMP"); } result.Add(") as __hibernate_sort_row FROM (") .Add(sqlQuery.SelectClause) .Add(" ") .Add(sqlQuery.FromAndWhereClause) .Add(") as q_) as query"); }
private SqlString PageByLimitAndOffset(SqlString offset, SqlString limit) { var queryParser = new MsSqlSelectParser(_sourceQuery); if (queryParser.SelectIndex < 0) { return(null); } var result = new SqlStringBuilder(); BuildSelectClauseForPagingQuery(queryParser, limit, result); if (queryParser.IsDistinct) { BuildFromClauseForPagingDistinctQuery(queryParser, result); } else { BuildFromClauseForPagingQuery(queryParser, result); } BuildWhereAndOrderClausesForPagingQuery(offset, result); return(result.ToSqlString()); }
private static void BuildFromClauseForPagingQuery(MsSqlSelectParser sqlQuery, SqlStringBuilder result) { result.Add(" FROM (") .Add(sqlQuery.SelectClause) .Add(", ROW_NUMBER() OVER(ORDER BY "); var orderIndex = 0; foreach (var order in sqlQuery.Orders) { if (orderIndex++ > 0) { result.Add(", "); } if (order.Column.Name != null) { result.Add(order.Column.Name); } else { result.Add(sqlQuery.Sql.Substring(order.Column.SqlIndex, order.Column.SqlLength).Trim()); } if (order.IsDescending) { result.Add(" DESC"); } } if (orderIndex == 0) { result.Add("CURRENT_TIMESTAMP"); } result.Add(") as __hibernate_sort_row ") .Add(sqlQuery.FromAndWhereClause) .Add(") as query"); }