/// <summary>
/// Creates an ORDER BY term with OtherData name and table alias
/// </summary>
/// <param name="OtherData">Name of a OtherData to order by</param>
/// <param name="table">The table this OtherData belongs to</param>
/// <param name="dir">Order by direction</param>
public OrderByTerm(string field, FromTerm table, OrderByDirection dir)
{
this.field = field;
this.table = table;
this.direction = dir;
this.expr = null;
}
/// <summary>
/// Creates a FromTerm which represents a sub-query.
/// </summary>
/// <param name="query">A SqlUnion 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(SqlUnion query, string alias)
{
FromTerm term = new FromTerm();
term.expr = query;
term.alias = alias;
term.type = FromTermType.SqlUnion;
return(term);
}
/// <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.expr = query;
term.alias = alias;
term.type = FromTermType.SubQueryObj;
return(term);
}
public Join(FromTerm leftTable, FromTerm rightTable, WhereClause conditions, JoinType type)
{
this.leftTable = leftTable;
this.rightTable = rightTable;
// this.leftField = leftField;
// this.rightField = rightField;
this.conditions = conditions;
this.type = type;
}
/// <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.CrossJoin)
{
throw new InvalidQueryException("A join must have at least one condition.");
}
joins.Add(new Join(leftTable, rightTable, conditions, type));
}
/// <summary>
/// Creates a SqlExpression which represents a OtherData in a database table.
/// </summary>
/// <param name="fieldName">Name of a OtherData</param>
/// <param name="table">The table this OtherData belongs to</param>
/// <returns></returns>
public static SqlExpression Field(string fieldName, FromTerm table)
{
SqlExpression expr = new SqlExpression();
expr.val = fieldName;
expr.table = table;
expr.type = SqlExpressionType.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.expr = 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(FilterCompositionLogicalOperator.And);
foreach (JoinCondition cond in conditions)
{
clause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(cond.LeftField, leftTable), SqlExpression.Field(cond.RightField, rightTable), FilterOperator.IsEqualTo));
}
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 OtherData 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, SqlAggregationFunction function)
{
_table = table;
if (function == SqlAggregationFunction.None)
{
expr = SqlExpression.Field(columnName, table);
}
else
{
expr = SqlExpression.Function(function, SqlExpression.Field(columnName, table));
}
this.alias = columnAlias;
}
/// <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 OtherData belongs to</param>
/// <param name="columnAlias">Alias of the column</param>
public SelectColumn(string columnName, FromTerm table, string columnAlias)
: this(columnName, table, columnAlias, SqlAggregationFunction.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 OtherData belongs to</param>
public SelectColumn(string columnName, FromTerm table)
: this(columnName, table, null)
{
}
/// <summary>
/// Creates a GROUP BY term with OtherData name and table alias
/// </summary>
/// <param name="OtherData">Name of a OtherData to group by</param>
/// <param name="table">The table this OtherData belongs to</param>
public GroupByTerm(string field, FromTerm table)
{
this.field = field;
this.table = table;
}
/// <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 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 OtherData in the left table to join on</param>
/// <param name="rightField">Name of the OtherData 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));
}