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