/// <summary>
/// Creates a WhereTerm which represents SQL IN clause
/// </summary>
/// <param name="expr">Expression to be looked up</param>
/// <param name="sql">Sub query</param>
/// <returns></returns>
public static WhereTerm CreateIn(SqlExpression expr, string sql)
{
WhereTerm term = new WhereTerm();
term.expr1 = expr;
term.subQuery = sql;
term.type = WhereTermType.InSubQuery;
return term;
}
/// <summary>
/// Creates a comparison WhereTerm.
/// </summary>
/// <param name="expr1">Expression on the left side of the operator</param>
/// <param name="expr2">Expression on the right side of the operator</param>
/// <param name="op">Conditional operator to be applied on the expressions</param>
/// <returns>A new conditional WhereTerm</returns>
/// <remarks>
/// A comparison term compares two expression on the basis of their values. Expressions can be of any type but their results must be of comparible types.
/// For instance, you can not compare a database field of type 'date' and a static value of type 'int'.
/// </remarks>
/// <example>
/// <code>
/// ...
/// query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("J%"), CompareOperator.Like));
/// </code>
/// </example>
public static WhereTerm CreateCompare(SqlExpression expr1, SqlExpression expr2, CompareOperator op)
{
WhereTerm term = new WhereTerm();
term.expr1 = expr1;
term.expr2 = expr2;
term.op = op;
term.type = WhereTermType.Compare;
return term;
}
/// <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, SqlAggregationFunction function)
{
if (function == SqlAggregationFunction.None)
{
expr = SqlExpression.Field(columnName, table);
}
else
{
expr = SqlExpression.Function(function, SqlExpression.Field(columnName, table));
}
alias = columnAlias;
}
/// <summary>
/// Creates a WhereTerm which returns TRUE if an expression is NOT NULL
/// </summary>
/// <param name="expr"></param>
/// <returns></returns>
public static WhereTerm CreateIsNotNull(SqlExpression expr)
{
WhereTerm term = new WhereTerm();
term.expr1 = expr;
term.type = WhereTermType.IsNotNull;
return term;
}
public void AddCompare(string column, SqlExpression expression, CompareOperator compareOperator)
{
WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(column, fromClause.BaseTable),
expression, compareOperator));
}
public WhereTerm CreateCompare(Enum column, SqlExpression expression, CompareOperator compareOperator)
{
return WhereTerm.CreateCompare(SqlExpression.Field(column, fromClause.BaseTable),
expression, compareOperator);
}
public void AddCompare(Enum column, SqlExpression expression, CompareOperator compareOperator)
{
WherePhrase.Terms.Add(CreateCompare(column, expression, compareOperator));
}
/// <summary>
/// Creates a SqlExpression which represents a constant typed value
/// </summary>
/// <param name="dataType">Value's data type</param>
/// <param name="val">The value</param>
/// <returns></returns>
public static SqlExpression Constant(SqlDataType dataType, object val)
{
SqlExpression expr = new SqlExpression();
expr.val = new SqlConstant(dataType, val);
expr.type = SqlExpressionType.Constant;
return expr;
}
/// <summary>
/// Creates a SqlExpression which represents a subquery.
/// </summary>
/// <param name="query">A SelectQuery object</param>
/// <returns>A new SqlExpression</returns>
public static SqlExpression SubQuery(SelectQuery query)
{
SqlExpression expr = new SqlExpression();
expr.val = query;
expr.type = SqlExpressionType.SubQueryObject;
return expr;
}
/// <summary>
/// Creates a SqlExpression which represents a subquery.
/// </summary>
/// <param name="queryText">Text of the subquery.</param>
/// <returns>A new SqlExpression</returns>
/// <remarks>
/// In many cases you can use an inner or outer JOIN instead of a subquery.
/// If you prefer using subqueries it is recomended that you construct the subquery
/// using another instance of <see cref="SelectQuery"/>, render it using the correct
/// renderer and pass the resulting SQL statement to the <paramref name="queryText"/> parameter.
/// </remarks>
public static SqlExpression SubQuery(string queryText)
{
SqlExpression expr = new SqlExpression();
expr.val = queryText;
expr.type = SqlExpressionType.SubQueryText;
return expr;
}
public static SqlExpression PseudoField(string fieldName)
{
SqlExpression expr = new SqlExpression();
expr.val = fieldName;
expr.type = SqlExpressionType.PseudoField;
return expr;
}
/// <summary>
/// Creates a SqlExpression representing a NULL value
/// </summary>
/// <returns></returns>
public static SqlExpression Null()
{
SqlExpression expr = new SqlExpression();
expr.type = SqlExpressionType.Null;
return expr;
}
/// <summary>
/// Creates a SqlExpression with an aggergation function
/// </summary>
/// <param name="func">Aggregation function to be applied on the supplied expression</param>
/// <param name="param">Parameter of the aggregation function</param>
/// <returns></returns>
public static SqlExpression Function(SqlAggregationFunction func, SqlExpression param)
{
SqlExpression expr = new SqlExpression();
expr.type = SqlExpressionType.Function;
expr.subExpr1 = param;
expr.func = func;
return expr;
}
/// <summary>
/// Creates a SqlExpression with IfNull function.
/// </summary>
/// <param name="test">Expression to be checked for being NULL</param>
/// <param name="val">Substitution</param>
/// <returns></returns>
/// <remarks>
/// Works as SQL Server's ISNULL() function.
/// </remarks>
public static SqlExpression IfNull(SqlExpression test, SqlExpression val)
{
SqlExpression expr = new SqlExpression();
expr.type = SqlExpressionType.IfNull;
expr.subExpr1 = test;
expr.subExpr2 = val;
return expr;
}
/// <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 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 WhereTerm which checks weather a value is in a specifed range.
/// </summary>
/// <param name="expr">Expression which yeilds the value to be checked</param>
/// <param name="lowBound">Expression which yeilds the low bound of the range</param>
/// <param name="highBound">Expression which yeilds the high bound of the range</param>
/// <returns>A new WhereTerm</returns>
/// <remarks>
/// CreateBetween only accepts expressions which yeild a 'Date' or 'Number' values.
/// All expressions must be of compatible types.
/// </remarks>
public static WhereTerm CreateBetween(SqlExpression expr, SqlExpression lowBound, SqlExpression highBound)
{
WhereTerm term = new WhereTerm();
term.expr1 = expr;
term.expr2 = lowBound;
term.expr3 = highBound;
term.type = WhereTermType.Between;
return term;
}
public static SqlExpression LikeExpressionParameter(string paramName)
{
SqlExpression expr = new SqlExpression();
expr.val = paramName;
expr.type = SqlExpressionType.LikeExpressionParameter;
return expr;
}
public static SqlExpression LikeExpressionParameter()
{
SqlExpression expr = new SqlExpression();
expr.val = "?";
expr.type = SqlExpressionType.LikeExpressionParameter;
return expr;
}
/// <summary>
/// Creates a WhereTerm which represents SQL IN clause
/// </summary>
/// <param name="expr">Expression to be looked up</param>
/// <param name="values">List of values</param>
/// <returns></returns>
public static WhereTerm CreateIn(SqlExpression expr, SqlConstantCollection values)
{
WhereTerm term = new WhereTerm();
term.expr1 = expr;
term.values = values;
term.type = WhereTermType.In;
return term;
}
/// <summary>
/// Creates a SqlExpression with raw SQL
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlExpression Raw(string sql)
{
SqlExpression expr = new SqlExpression();
expr.val = sql;
expr.type = SqlExpressionType.Raw;
return expr;
}
/// <summary>
/// Creates a SelectColumn
/// </summary>
/// <param name="expr">Expression</param>
/// <param name="columnAlias">Column alias</param>
public SelectColumn(SqlExpression expr, string columnAlias)
{
this.expr = expr;
alias = columnAlias;
}
/// <summary>
/// Creates a SqlExpression which represents a constant typed value.
/// </summary>
/// <param name="val">SqlConstant instance</param>
/// <returns>A SqlExpression which represents a date value</returns>
public static SqlExpression Constant(SqlConstant val)
{
SqlExpression expr = new SqlExpression();
expr.val = val;
expr.type = SqlExpressionType.Constant;
return expr;
}
