/// <summary>Описание конструкции SELECT ... FROM</summary>
public static From SubQuery(string subQuery, string alias)
{
From from = new From();
from.Term = FromTerm.SubQuery(subQuery, alias);
return(from);
}
/// <summary>
/// Renders a row count SELECT statement.
/// </summary>
/// <param name="query">Query definition to count rows for</param>
/// <returns>Generated SQL statement</returns>
/// <remarks>
/// Renders a SQL statement which returns a result set with one row and one cell which contains the number of rows <paramref name="query"/> can generate.
/// The generated statement will work nicely with <see cref="System.Data.IDbCommand.ExecuteScalar"/> method.
/// </remarks>
public override string RenderRowCount(SelectQuery query)
{
string baseSql = RenderSelect(query, false);
SelectQuery countQuery = new SelectQuery();
SelectColumn col = new SelectColumn("*", null, "cnt", SqlAggregationFunction.Count);
countQuery.Columns.Add(col);
countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t");
return(RenderSelect(countQuery));
}
/// <summary>
/// Renders a SELECT statement
/// </summary>
/// <param name="query">Query definition</param>
/// <returns>Generated SQL statement</returns>
public override string RenderSelect(SelectQuery query)
{
if (query.Top > -1 && query.OrderByTerms.Count > 0)
{
string baseSql = RenderSelect(query, -1);
SelectQuery countQuery = new SelectQuery();
SelectColumn col = new SelectColumn("*");
countQuery.Columns.Add(col);
countQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "t");
return(RenderSelect(countQuery, query.Top));
}
else
{
return(RenderSelect(query, query.Top));
}
}
/// <summary>
/// Renders a SELECT statement which a result-set page
/// </summary>
/// <param name="pageIndex">The zero based index of the page to be returned</param>
/// <param name="pageSize">The size of a page</param>
/// <param name="totalRowCount">Total number of rows the query would yeild if not paged</param>
/// <param name="query">Query definition to apply paging on</param>
/// <returns>Generated SQL statement</returns>
/// <remarks>
/// To generate pagination SQL you must supply <paramref name="totalRowCount"/>.
/// To aquire the total number of rows use the <see cref="RenderRowCount"/> method.
/// </remarks>
public virtual string RenderPage(int pageIndex, int pageSize, int totalRowCount, SelectQuery query)
{
if (query.OrderByTerms.Count == 0)
{
throw new InvalidQueryException("OrderBy must be specified for paging to work.");
}
int currentPageSize = pageSize;
if (pageSize * (pageIndex + 1) > totalRowCount)
{
currentPageSize = totalRowCount - pageSize * pageIndex;
}
if (currentPageSize < 0)
{
currentPageSize = 0;
}
SelectQuery baseQuery = query.Clone();
baseQuery.Top = (pageIndex + 1) * pageSize;
//baseQuery.Columns.Add(new SelectColumn("*"));
foreach (OrderByTerm term in baseQuery.OrderByTerms)
{
baseQuery.Columns.Add(
new SelectColumn(term.Field, term.Table, FormatSortFieldName(term.Field),
SqlAggregationFunction.None));
}
string baseSql = RenderSelect(baseQuery);
SelectQuery reverseQuery = new SelectQuery();
reverseQuery.Columns.Add(new SelectColumn("*"));
reverseQuery.Top = currentPageSize;
reverseQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "r");
ApplyOrderBy(baseQuery.OrderByTerms, reverseQuery, false, reverseQuery.FromClause.BaseTable);
string reverseSql = RenderSelect(reverseQuery);
SelectQuery forwardQuery = new SelectQuery();
foreach (SelectColumn originalCol in query.Columns)
{
FromTerm forwardTable = FromTerm.TermRef("f");
SqlExpression expr = null;
if (originalCol.ColumnAlias != null)
{
expr = SqlExpression.Field(originalCol.ColumnAlias, forwardTable);
}
else if (originalCol.Expression.Type == SqlExpressionType.Field ||
originalCol.Expression.Type == SqlExpressionType.Constant)
{
expr = SqlExpression.Field((string)originalCol.Expression.Value, forwardTable);
}
if (expr != null)
{
forwardQuery.Columns.Add(new SelectColumn(expr, originalCol.ColumnAlias));
}
}
forwardQuery.FromClause.BaseTable = FromTerm.SubQuery(reverseSql, "f");
ApplyOrderBy(baseQuery.OrderByTerms, forwardQuery, true, forwardQuery.FromClause.BaseTable);
return(RenderSelect(forwardQuery));
}
internal From(SelectQuery subQuery, string alias)
{
Term = FromTerm.SubQuery(subQuery, alias);
}
FromTerm GetBaseFromTerm()
{
return((baseQuery != null) ? FromTerm.SubQuery(baseQuery, "b") : FromTerm.SubQuery(baseSql, "b"));
}
