/// <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; }