/// <summary> /// Creates a FromTerm which represents a sub-query. /// </summary> /// <param name="query">A SelectQuery instance representing the sub query</param> /// <param name="alias">term alias</param> /// <returns>A FromTerm which represents a sub-query.</returns> public static FromTerm SubQuery(SelectQuery query, string alias) { FromTerm term = new FromTerm(); term.QueryValue = query; term.Alias = alias; term.Type = FromTermType.SubQueryObj; return(term); }
/// <summary> /// Creates a FromTerm which represents a UNION. /// </summary> public static FromTerm Union(OmUnion union, string alias) { FromTerm term = new FromTerm(); term.UnionValue = union; term.Alias = alias; term.Type = FromTermType.Union; return(term); }
/// <summary> /// Joins two tables using on an arbitrary join condition /// </summary> /// <param name="type">The type of join to be created.</param> /// <param name="leftTable">The left table</param> /// <param name="rightTable">The right table</param> /// <param name="conditions">Specifies how the join should be performed</param> /// <remarks> /// Use this overload to create complex join conditions. /// Note that not all <see cref="WhereClause"/> operators and expressions are supported in joins. /// </remarks> /// <example> /// WhereClause condition = new WhereClause(WhereClauseRelationship.Or); /// condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId", tOrders), SqlExpression.Field("productId", tProducts), CompareOperator.Equal)); /// condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productName", tOrders), SqlExpression.Field("productName", tProducts), CompareOperator.Equal)); /// query.FromClause.Join(JoinType.Left, tOrders, tProducts, condition); /// </example> public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, WhereClause conditions) { if (conditions.IsEmpty && type != JoinType.Cross) { throw new InvalidQueryException("A join must have at least one condition."); } joins.Add(new Join(leftTable, rightTable, conditions, type)); }
/// <summary> /// Creates a FromTerm which represents a sub-query. /// </summary> /// <param name="query">sub-query sql</param> /// <param name="alias">term alias</param> /// <returns>A FromTerm which represents a sub-query.</returns> public static FromTerm SubQuery(string query, string alias) { FromTerm term = new FromTerm(); term.StringValue = query; term.Alias = alias; term.Type = FromTermType.SubQuery; return(term); }
/// <summary> /// Creates a SqlExpression which represents a field in a database table. /// </summary> /// <param name="fieldName">Name of a field</param> /// <param name="table">The table this field belongs to</param> /// <returns></returns> public static OmExpression Field(string fieldName, FromTerm table) { OmExpression expr = new OmExpression(); expr.StringValue = fieldName; expr.ValueCode = ExprValCode.String; expr.Table = table; expr.Type = OmExpressionType.Field; return(expr); }
/// <summary> /// Creates a FromTerm which represents a database table or view. /// </summary> /// <param name="tableName">Name of the table or view</param> /// <param name="alias">Alias of the FromTerm</param> /// <param name="ns1">First table namespace</param> /// <param name="ns2">Second table namespace</param> /// <returns>A FromTerm which represents a database table or view</returns> /// <remarks>Use the <paramref name="ns1"/> parameter to set table database and <paramref name="ns2"/> to set table owner.</remarks> public static FromTerm Table(string tableName, string alias, string ns1, string ns2) { FromTerm term = new FromTerm(); term.StringValue = tableName; term.Alias = alias; term.Type = FromTermType.Table; term.Ns1 = ns1; term.Ns2 = ns2; return(term); }
/// <summary> /// Joins two tables using on an array join condition /// </summary> /// <param name="type">The type of join to be created.</param> /// <param name="leftTable">The left table</param> /// <param name="rightTable">The right table</param> /// <param name="conditions">An array of equality condition to be applied on the join</param> /// <remarks> /// A logical AND will be applied on the conditions. /// Schematically, the resulting SQL will be ... x join y on (cond1 and cond2 and cond3 and ... and condN) ... /// </remarks> public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, JoinCondition[] conditions) { WhereClause clause = new WhereClause(WhereRel.And); foreach (JoinCondition cond in conditions) { clause.Terms.Add(WhereTerm.CreateCompare(OmExpression.Field(cond.LeftField, leftTable), OmExpression.Field(cond.RightField, rightTable), CompCond.Equal)); } Join(type, leftTable, rightTable, clause); }
/// <summary> /// Creates a SelectColumn with a column name, table, column alias and optional aggregation function /// </summary> /// <param name="columnName">Name of a column</param> /// <param name="table">The table this field belongs to</param> /// <param name="columnAlias">Alias of the column</param> /// <param name="function">Aggregation function to be applied to the column. Use SqlAggregationFunction.None to specify that no function should be applied.</param> public SelectColumn(string columnName, FromTerm table, string columnAlias, AggFunc function) { if (function == AggFunc.None) { Expression = OmExpression.Field(columnName, table); } else { Expression = OmExpression.Function(function, OmExpression.Field(columnName, table)); } this.ColumnAlias = columnAlias; }
/// <summary> /// Creates an uncoditional join /// </summary> /// <param name="type">Must be JoinType.CrossJoin</param> /// <param name="leftTable"></param> /// <param name="rightTable"></param> public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable) { Join(type, leftTable, rightTable, new JoinCondition[] {}); }
/// <overloads>Use the following methods to define a join between two FromTerms.</overloads> /// <summary> /// Joins two tables using on a single join condition /// </summary> /// <param name="type">The type of join to be created.</param> /// <param name="leftTable">The left table</param> /// <param name="rightTable">The right table</param> /// <param name="leftField">Name of the field in the left table to join on</param> /// <param name="rightField">Name of the field in the right table to join on</param> /// <example> /// <code> /// query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId"); /// </code> /// </example> public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, string leftField, string rightField) { Join(type, leftTable, rightTable, new JoinCondition(leftField, rightField)); }
/// <summary> /// Joins two tables using on a triple join condition /// </summary> /// <param name="type">The type of join to be created.</param> /// <param name="leftTable">The left table</param> /// <param name="rightTable">The right table</param> /// <param name="cond1">First equality condition to be applied on the join</param> /// <param name="cond2">First equality condition to be applied on the join</param> /// <param name="cond3">First equality condition to be applied on the join</param> /// <remarks> /// A logical AND will be applied on all conditions. /// Schematically, the resulting SQL will be ... x join y on (cond1 and cond2 and cond3) ... /// </remarks> public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, JoinCondition cond1, JoinCondition cond2, JoinCondition cond3) { Join(type, leftTable, rightTable, new JoinCondition[] { cond1, cond2, cond3 }); }
/// <summary> /// Creates a SelectColumn with a column name, table and column alias /// </summary> /// <param name="columnName">Name of a column</param> /// <param name="table">The table this field belongs to</param> /// <param name="columnAlias">Alias of the column</param> public SelectColumn(string columnName, FromTerm table, string columnAlias) : this(columnName, table, columnAlias, AggFunc.None) { }
/// <summary> /// Creates a SelectColumn with a column name, table, no column alias and no function /// </summary> /// <param name="columnName">Name of a column</param> /// <param name="table">The table this field belongs to</param> public SelectColumn(string columnName, FromTerm table) : this(columnName, table, null) { }