private string RenderSelect(SelectQuery query, int limitRows) { query.Validate(); StringBuilder builder = new StringBuilder(); this.Select(builder, query.Distinct); this.SelectColumns(builder, query.Columns); this.FromClause(builder, query.FromClause, query.TableSpace); WhereClause group = new WhereClause(WhereClauseRelationship.And); group.SubClauses.Add(query.WherePhrase); if (limitRows > -1) { group.Terms.Add(WhereTerm.CreateCompare(SqlExpression.PseudoField("rownum"), SqlExpression.Number(limitRows), CompareOperator.LessOrEqual)); } this.Where(builder, group); this.WhereClause(builder, group); this.GroupBy(builder, query.GroupByTerms); if (query.GroupByWithCube) { builder.Append(" cube ("); } else if (query.GroupByWithRollup) { builder.Append(" rollup ("); } this.GroupByTerms(builder, query.GroupByTerms); if (query.GroupByWithCube || query.GroupByWithRollup) { builder.Append(" )"); } this.Having(builder, query.HavingPhrase); this.WhereClause(builder, query.HavingPhrase); this.OrderBy(builder, query.OrderByTerms); this.OrderByTerms(builder, query.OrderByTerms); return builder.ToString(); }
public static FromTerm SubQuery(SelectQuery query, string alias) { FromTerm term = new FromTerm(); term.expr = query; term.alias = alias; term.type = FromTermType.SubQueryObj; return term; }
public override string RenderRowCount(SelectQuery query) { string str = this.RenderSelect(query, -1); SelectQuery query2 = new SelectQuery(); SelectColumn column = new SelectColumn("*", null, "cnt", SqlAggregationFunction.Count); query2.Columns.Add(column); query2.FromClause.BaseTable = FromTerm.SubQuery(str, "t"); return this.RenderSelect(query2); }
public override string RenderSelect(SelectQuery query) { if ((query.Top > -1) && (query.OrderByTerms.Count > 0)) { string str = this.RenderSelect(query, -1); SelectQuery query2 = new SelectQuery(); SelectColumn column = new SelectColumn("*"); query2.Columns.Add(column); query2.FromClause.BaseTable = FromTerm.SubQuery(str, "t"); return this.RenderSelect(query2, query.Top); } return this.RenderSelect(query, query.Top); }
private string RenderSelect(SelectQuery query, bool forRowCount, int offset, int limitRows) { query.Validate(); StringBuilder builder = new StringBuilder(); this.Select(builder, query.Distinct); if (forRowCount) { this.SelectColumn(builder, new SelectColumn("*", null, "cnt", SqlAggregationFunction.Count)); } else { this.SelectColumns(builder, query.Columns); } this.FromClause(builder, query.FromClause, query.TableSpace); this.Where(builder, query.WherePhrase); this.WhereClause(builder, query.WherePhrase); this.GroupBy(builder, query.GroupByTerms); this.GroupByTerms(builder, query.GroupByTerms); if (query.GroupByWithCube) { throw new InvalidQueryException("MySql does not support WITH CUBE modifier."); } if (query.GroupByWithRollup) { builder.Append(" with rollup"); } this.Having(builder, query.HavingPhrase); this.WhereClause(builder, query.HavingPhrase); this.OrderBy(builder, query.OrderByTerms); this.OrderByTerms(builder, query.OrderByTerms); if (limitRows > -1) { builder.AppendFormat(" limit {0}, {1}", offset, limitRows); } return builder.ToString(); }
public SelectQuery Clone() { SelectQuery query = new SelectQuery(); query.columns = new SelectColumnCollection(this.columns); query.orderByTerms = new OrderByTermCollection(this.orderByTerms); query.groupByTerms = new GroupByTermCollection(this.groupByTerms); query.wherePhrase = this.wherePhrase.Clone(); query.fromClause = this.fromClause.Clone(); query.top = this.top; query.groupByWithRollup = this.groupByWithRollup; query.groupByWithCube = this.groupByWithCube; query.distinct = this.distinct; query.tableSpace = this.tableSpace; return query; }
public void Add(SelectQuery query) { this.Add(query, DistinctModifier.Distinct); }
public void Add(SelectQuery query, DistinctModifier repeatingAction) { this.items.Add(new SqlUnionItem(query, repeatingAction)); }
public static SqlExpression SubQuery(SelectQuery query) { SqlExpression expression = new SqlExpression(); expression.val = query; expression.type = SqlExpressionType.SubQueryObject; return expression; }
public override string RenderSelect(SelectQuery query) { return this.RenderSelect(query, false, 0, query.Top); }
public override string RenderPage(int pageIndex, int pageSize, int totalRowCount, SelectQuery query) { return this.RenderSelect(query, false, pageIndex * pageSize, pageSize); }
public SqlUnionItem(SelectQuery query, DistinctModifier repeatingAction) { this.Query = query; this.RepeatingAction = repeatingAction; }
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 on SqlServer."); } int num = pageSize; if ((pageSize * (pageIndex + 1)) > totalRowCount) { num = totalRowCount - (pageSize * pageIndex); } if (num < 0) { num = 0; } SelectQuery query2 = query.Clone(); query2.Top = (pageIndex + 1) * pageSize; foreach (SkyMap.Net.SqlOM.OrderByTerm term in query2.OrderByTerms) { query2.Columns.Add(new SkyMap.Net.SqlOM.SelectColumn(term.Field, term.Table, this.FormatSortFieldName(term.Field), SqlAggregationFunction.None)); } string str = this.RenderSelect(query2); SelectQuery orderQuery = new SelectQuery(); orderQuery.Columns.Add(new SkyMap.Net.SqlOM.SelectColumn("*")); orderQuery.Top = num; orderQuery.FromClause.BaseTable = FromTerm.SubQuery(str, "r"); this.ApplyOrderBy(query2.OrderByTerms, orderQuery, false, orderQuery.FromClause.BaseTable); string str2 = this.RenderSelect(orderQuery); SelectQuery query4 = new SelectQuery(); query4.Columns.AddRange(query.Columns); query4.FromClause.BaseTable = FromTerm.SubQuery(str2, "f"); this.ApplyOrderBy(query2.OrderByTerms, query4, true, query4.FromClause.BaseTable); return this.RenderSelect(query4); }
private void ApplyOrderBy(OrderByTermCollection terms, SelectQuery orderQuery, bool forward, FromTerm table) { foreach (SkyMap.Net.SqlOM.OrderByTerm term in terms) { OrderByDirection dir = term.Direction; if (!(forward || (dir != OrderByDirection.Ascending))) { dir = OrderByDirection.Descending; } else if (!(forward || (dir != OrderByDirection.Descending))) { dir = OrderByDirection.Ascending; } orderQuery.OrderByTerms.Add(new SkyMap.Net.SqlOM.OrderByTerm(this.FormatSortFieldName(term.Field.ToString()), table, dir)); } }
public abstract string RenderSelect(SelectQuery query);
public abstract string RenderRowCount(SelectQuery query);