/// <summary>
/// Creates a SqlExpression representing a NULL value
/// </summary>
/// <returns></returns>
public static OmExpression Null()
{
OmExpression expr = new OmExpression();
expr.Type = OmExpressionType.Null;
return(expr);
}
/// <summary>
/// Creates a SqlExpression with a CASE statement.
/// </summary>
/// <param name="caseClause"></param>
/// <returns></returns>
public static OmExpression Case(CaseClause caseClause)
{
OmExpression expr = new OmExpression();
expr.Type = OmExpressionType.Case;
expr.caseClause = caseClause;
return(expr);
}
/// <summary>
/// Creates a WhereTerm which returns TRUE if an expression is NOT NULL
/// </summary>
/// <param name="expr"></param>
/// <returns></returns>
public static WhereTerm CreateIsNotNull(OmExpression expr)
{
WhereTerm term = new WhereTerm();
term.Expr1 = expr;
term.Type = WhereTermType.IsNotNull;
return(term);
}
/// <summary>
/// Creates a SqlExpression which represents a subquery.
/// </summary>
/// <param name="query">A SelectQuery object</param>
/// <returns>A new SqlExpression</returns>
public static OmExpression SubQuery(SelectQuery query)
{
OmExpression expr = new OmExpression();
expr.ValueCode = ExprValCode.SelQuery;
expr.QueryValue = query;
expr.Type = OmExpressionType.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 OmExpression SubQuery(string queryText)
{
OmExpression expr = new OmExpression();
expr.ValueCode = ExprValCode.String;
expr.StringValue = queryText;
expr.Type = OmExpressionType.SubQueryText;
return(expr);
}
/// <summary></summary>
public static OmExpression PseudoField(string fieldName)
{
OmExpression expr = new OmExpression();
expr.ValueCode = ExprValCode.String;
expr.StringValue = fieldName;
expr.Type = OmExpressionType.PseudoField;
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 OmExpression Function(AggFunc func, OmExpression param)
{
OmExpression expr = new OmExpression();
expr.Type = OmExpressionType.Function;
expr.SubExpr1 = param;
expr.AggFunction = 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 OmExpression IfNull(OmExpression test, OmExpression val)
{
OmExpression expr = new OmExpression();
expr.Type = OmExpressionType.IfNull;
expr.SubExpr1 = test;
expr.SubExpr2 = val;
return(expr);
}
/// <summary>
/// Create a parameter placeholder.
/// </summary>
/// <param name="paramName"></param>
/// <returns></returns>
/// <remarks>
/// Correct parameter name depends on your specifc data provider. OLEDB expects
/// all parameters to be '?' and matches parameters to values based on their index.
/// SQL Server Native driver matches parameters by names and expects to find "@paramName"
/// parameter placeholder in the query.
/// </remarks>
public static OmExpression Parameter(string paramName)
{
OmExpression expr = new OmExpression();
expr.ValueCode = ExprValCode.String;
expr.StringValue = paramName;
expr.Type = OmExpressionType.Parameter;
return(expr);
}
/// <summary>
/// Creates a SqlExpression with raw SQL
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static OmExpression Raw(string sql)
{
OmExpression expr = new OmExpression();
expr.ValueCode = ExprValCode.String;
expr.StringValue = sql;
expr.Type = OmExpressionType.Raw;
return(expr);
}
/// <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(OmExpression expr, string sql)
{
WhereTerm term = new WhereTerm();
term.Expr1 = expr;
term.SubQuery = sql;
term.Type = WhereTermType.InSubQuery;
return(term);
}
/// <summary>
/// Creates a WhereTerm which represents SQL NOT IN clause
/// </summary>
/// <param name="expr">Expression to be looked up</param>
/// <param name="values"></param>
/// <returns></returns>
public static WhereTerm CreateNotIn(OmExpression expr, OmConstantCollection values)
{
WhereTerm term = new WhereTerm();
term.Expr1 = expr;
term.values = values;
term.Type = WhereTermType.NotIn;
return(term);
}
/// <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 OmExpression Constant(OmConstant val)
{
OmExpression expr = new OmExpression();
expr.ValueCode = ExprValCode.SqlConst;
expr.ConstantValue = val;
expr.Type = OmExpressionType.Constant;
return(expr);
}
/// <summary>
/// Creates a WhereTerm which represents SQL NOT IN clause
/// </summary>
/// <param name="expr">Expression to be looked up</param>
/// <param name="subQuery">Sub query</param>
/// <returns></returns>
public static WhereTerm CreateNotIn(OmExpression expr, SelectQuery subQuery)
{
WhereTerm term = new WhereTerm();
term.Expr1 = expr;
term.SubQuery = subQuery;
term.Type = WhereTermType.NotInSubQuery;
return(term);
}
/// <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 OmExpression Constant(DataType dataType, object val)
{
OmExpression expr = new OmExpression();
expr.ValueCode = ExprValCode.SqlConst;
expr.ConstantValue = new OmConstant(dataType, val);
expr.Type = OmExpressionType.Constant;
return(expr);
}
/// <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(OmExpression expr1, OmExpression expr2, CompCond op)
{
WhereTerm term = new WhereTerm();
term.Expr1 = expr1;
term.Expr2 = expr2;
term.Op = op;
term.Type = WhereTermType.Compare;
return(term);
}
/// <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(OmExpression expr, OmExpression lowBound, OmExpression highBound)
{
WhereTerm term = new WhereTerm();
term.Expr1 = expr;
term.Expr2 = lowBound;
term.Expr3 = highBound;
term.Type = WhereTermType.Between;
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>
///
/// </summary>
/// <param name="expr1"></param>
/// <param name="expr2"></param>
/// <param name="escapeChar"></param>
/// <returns></returns>
public static WhereTerm CreateNotLikeCompare(OmExpression expr1, OmExpression expr2, char escapeChar)
{
WhereTerm term = new WhereTerm();
term.Expr1 = expr1;
term.Expr2 = expr2;
term.Expr3 = OmExpression.Constant(DataType.String, new string(escapeChar, 1));
term.Op = CompCond.NotLike;
term.Type = WhereTermType.Compare;
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 a SelectColumn
/// </summary>
/// <param name="expr">Expression</param>
/// <param name="columnAlias">Column alias</param>
public SelectColumn(OmExpression expr, string columnAlias)
{
this.Expression = expr;
this.ColumnAlias = columnAlias;
}
/// <summary>
/// Creates an UpdateTerm
/// </summary>
/// <param name="fieldName">The name of the field to be updated</param>
/// <param name="val">New field value</param>
public UpdateTerm(string fieldName, OmExpression val)
{
this.fieldName = fieldName;
this.val = val;
}
/// <summary>
/// Creates a new CaseTerm
/// </summary>
/// <param name="condition">Condition for the WHEN clause</param>
/// <param name="val">Value for the THEN clause</param>
public CaseTerm(WhereClause condition, OmExpression val)
{
this.Condition = condition;
this.Value = val;
}
