/// <summary> /// SQL 'HAVING' phrase extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="joinType">Join type.</param> /// <param name="phrases">The 'HAVING' phrases.</param> /// <returns>The command.</returns> public static APSqlSelectCommand having(this APSqlSelectCommand command, APSqlConditionJoinType joinType, IEnumerable <APSqlWherePhrase> phrases) { if (phrases != null) { command.HavingClause = new APSqlWhereClause(joinType, phrases); } return(command); }
/// <summary> /// SQL 'HAVING' phrase extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="joinType">Join type.</param> /// <param name="phrases">The 'HAVING' phrases.</param> /// <returns>The command.</returns> public static APSqlSelectCommand having(this APSqlSelectCommand command, APSqlConditionJoinType joinType, params APSqlWherePhrase[] phrases) { if (phrases != null && phrases.Length != 0) { command.HavingClause = new APSqlWhereClause(joinType, phrases); } return(command); }
/// <summary> /// Create a new 'WHERE' clause with join type and 'WHERE' phrases. /// </summary> /// <param name="joinType">Join type.</param> /// <param name="phrases">The IEnumerable phrases.</param> public APSqlWhereClause(APSqlConditionJoinType joinType, IEnumerable<APSqlWherePhrase> phrases) { if (joinType == APSqlConditionJoinType.AND) SetNext(new APSqlConditionAndPhrase(phrases)); else SetNext(new APSqlConditionOrPhrase(phrases)); CheckValid(); }
/// <summary> /// Create a new 'WHERE' clause with join type and 'WHERE' phrases. /// </summary> /// <param name="joinType">Join type.</param> /// <param name="phrases">'WHERE' phrases.</param> public APSqlWhereClause(APSqlConditionJoinType joinType, params APSqlWherePhrase[] phrases) { if (joinType == APSqlConditionJoinType.AND) SetNext(new APSqlConditionAndPhrase(phrases)); else SetNext(new APSqlConditionOrPhrase(phrases)); CheckValid(); }
/// <summary> /// SQL 'WHERE' phrase extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="joinType">Join type.</param> /// <param name="phrases">The 'WHERE' phrases.</param> /// <returns>The command.</returns> public static APSqlDeleteCommand where (this APSqlDeleteCommand command, APSqlConditionJoinType joinType, IEnumerable <APSqlWherePhrase> phrases) { if (phrases != null) { command.WhereClause = new APSqlWhereClause(joinType, phrases); } else { command.WhereClause = null; } return(command); }
/// <summary> /// SQL 'WHERE' phrase extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="joinType">Join type.</param> /// <param name="phrases">The 'WHERE' phrases.</param> /// <returns>The command.</returns> public static APSqlDeleteCommand where (this APSqlDeleteCommand command, APSqlConditionJoinType joinType, params APSqlWherePhrase[] phrases) { if (phrases != null && phrases.Length != 0) { command.WhereClause = new APSqlWhereClause(joinType, phrases); } else { command.WhereClause = null; } return(command); }
/// <summary> /// Create a new 'WHERE' clause with join type and 'WHERE' phrases. /// </summary> /// <param name="joinType">Join type.</param> /// <param name="phrases">The IEnumerable phrases.</param> public APSqlWhereClause(APSqlConditionJoinType joinType, IEnumerable <APSqlWherePhrase> phrases) { if (joinType == APSqlConditionJoinType.AND) { SetNext(new APSqlConditionAndPhrase(phrases)); } else { SetNext(new APSqlConditionOrPhrase(phrases)); } CheckValid(); }
/// <summary> /// Create a new 'WHERE' clause with join type and 'WHERE' phrases. /// </summary> /// <param name="joinType">Join type.</param> /// <param name="phrases">'WHERE' phrases.</param> public APSqlWhereClause(APSqlConditionJoinType joinType, params APSqlWherePhrase[] phrases) { if (joinType == APSqlConditionJoinType.AND) { SetNext(new APSqlConditionAndPhrase(phrases)); } else { SetNext(new APSqlConditionOrPhrase(phrases)); } CheckValid(); }
/// <summary> /// Get 'WHERE' phrase. /// </summary> /// <param name="op">Condition operator.</param> /// <param name="joinType">Condition join type.</param> /// <param name="values">Values.</param> /// <returns>An APSqlWherePhrase.</returns> protected virtual APSqlWherePhrase GetQueryWherePhrase(APSqlConditionOperator op, APSqlConditionJoinType joinType, string[] values) { List <APSqlWherePhrase> list = new List <APSqlWherePhrase>(); foreach (string value in values) { list.Add(GetQueryWherePhrase(op, value)); } if (list.Count == 1) { return(list[0]); } if (joinType == APSqlConditionJoinType.AND) { return(new APSqlConditionAndPhrase(list)); } else { return(new APSqlConditionOrPhrase(list)); } }
/// <summary> /// SQL 'WHERE' phrase extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="joinType">Join type.</param> /// <param name="phrases">The 'WHERE' phrases.</param> /// <returns>The command.</returns> public static APSqlUpdateCommand where(this APSqlUpdateCommand command, APSqlConditionJoinType joinType, IEnumerable<APSqlWherePhrase> phrases) { if (phrases != null) command.WhereClause = new APSqlWhereClause(joinType, phrases); else command.WhereClause = null; return command; }
/// <summary> /// SQL 'WHERE' phrase extensions. /// </summary> /// <param name="command">The command.</param> /// <param name="joinType">Join type.</param> /// <param name="phrases">The 'WHERE' phrases.</param> /// <returns>The command.</returns> public static APSqlUpdateCommand where(this APSqlUpdateCommand command, APSqlConditionJoinType joinType, params APSqlWherePhrase[] phrases) { if (phrases != null && phrases.Length != 0) command.WhereClause = new APSqlWhereClause(joinType, phrases); else command.WhereClause = null; return command; }
private void WriteConditionPhrase(ParserWriter writer, APSqlWherePhrase phrase, APSqlConditionJoinType join, OracleCommand dbCmd) { bool isFirst = true; writer.Write("("); while (phrase != null) { if (!isFirst) { writer.Write(join.ToString()); } if (phrase.IsNot) { writer.Write("NOT ("); } if (phrase is APSqlConditionAndPhrase) { WriteConditionPhrase(writer, (phrase as APSqlConditionAndPhrase).Child, APSqlConditionJoinType.AND, dbCmd); } else if (phrase is APSqlConditionOrPhrase) { WriteConditionPhrase(writer, (phrase as APSqlConditionOrPhrase).Child, APSqlConditionJoinType.OR, dbCmd); } else { APSqlConditionPhrase cond = phrase as APSqlConditionPhrase; // for this, if columnDef is null, mean this is a 'EXISTS ( subquery )' phrase // do not // change to check ConditionOperator. if (cond.ConditionOperator != APSqlConditionOperator.Exists && cond.ConditionOperator != APSqlConditionOperator.NotExists) { WriteSelectExpression(writer, cond.Expr); } if (cond.ConditionOperator == APSqlConditionOperator.Equals /*&& !cond.IsRelationDef*/ && (cond.Value == null || cond.Value == DBNull.Value)) { writer.Write("IS NULL"); } else if (cond.ConditionOperator == APSqlConditionOperator.NotEqual /*&& !cond.IsRelationDef*/ && (cond.Value == null || cond.Value == DBNull.Value)) { writer.Write("IS NOT NULL"); } else { switch (cond.ConditionOperator) { case APSqlConditionOperator.Equals: writer.Write("="); break; case APSqlConditionOperator.NotEqual: writer.Write("<>"); break; case APSqlConditionOperator.GreaterThan: writer.Write(">"); break; case APSqlConditionOperator.GreaterThanOrEqual: writer.Write(">="); break; case APSqlConditionOperator.LessThan: writer.Write("<"); break; case APSqlConditionOperator.LessThanOrEqual: writer.Write("<="); break; case APSqlConditionOperator.Between: writer.Write("BETWEEN"); break; case APSqlConditionOperator.NotBetween: writer.Write("NOT BETWEEN"); break; case APSqlConditionOperator.Like: writer.Write("LIKE"); break; case APSqlConditionOperator.NotLike: writer.Write("NOT LIKE"); break; case APSqlConditionOperator.In: writer.Write("IN"); break; case APSqlConditionOperator.NotIn: writer.Write("NOT IN"); break; case APSqlConditionOperator.Exists: writer.Write("EXISTS"); break; case APSqlConditionOperator.NotExists: writer.Write("NOT EXISTS"); break; } object value = cond.Value; if (!TryWriteValue(writer, value)) { if (value is APSqlSelectCommand) { switch (cond.SubQueryScalarRestrict) { case APSqlSubQueryScalarRestrict.All: writer.Write("ALL"); break; case APSqlSubQueryScalarRestrict.Some: writer.Write("SOME"); break; case APSqlSubQueryScalarRestrict.Any: writer.Write("ANY"); break; } writer.Write("("); int idented = writer.Idented++; writer.WriteLine(); ParseSelectInternal(value as APSqlSelectCommand, dbCmd, writer); writer.Idented = idented; writer.Write(")"); } else { if (cond.ConditionOperator == APSqlConditionOperator.In || cond.ConditionOperator == APSqlConditionOperator.NotIn) { writer.Write("("); int i = 0; foreach (object val in value as Array) { if (i != 0) { writer.Write(','); } string paramName = writer.GetSuitableParameterName(cond.ParamName); paramName = String.Format("v_{0}${1}", paramName, i); writer.Write(":" + paramName); AddParameter(dbCmd, paramName, val, ParameterDirection.Input); i++; } writer.Write(")"); } else if (cond.ConditionOperator == APSqlConditionOperator.Between || cond.ConditionOperator == APSqlConditionOperator.NotBetween) { object begin = (value as Array).GetValue(0); object end = (value as Array).GetValue(1); if (!TryWriteValue(writer, begin)) { string paramName = writer.GetSuitableParameterName(cond.ParamName); writer.Write(":" + paramName); AddParameter(dbCmd, paramName, begin, ParameterDirection.Input); } writer.Write("AND"); if (!TryWriteValue(writer, end)) { string paramName = writer.GetSuitableParameterName(cond.ParamName); writer.Write(":" + paramName); AddParameter(dbCmd, paramName, end, ParameterDirection.Input); } } else { string paramName = writer.GetSuitableParameterName(cond.ParamName); writer.Write(":" + paramName); AddParameter(dbCmd, paramName, value, ParameterDirection.Input); } } } } } if (phrase.IsNot) { writer.Write(")"); } isFirst = false; phrase = phrase.Next as APSqlWherePhrase; } writer.Write(")"); }
private void WriteConditionPhrase(ParserWriter writer, APSqlWherePhrase phrase, APSqlConditionJoinType join, SqlCommand dbCmd) { bool isFirst = true; writer.Write("("); while (phrase != null) { if (!isFirst) writer.Write(join.ToString()); if (phrase.IsNot) writer.Write("NOT ("); if (phrase is APSqlConditionAndPhrase) { WriteConditionPhrase(writer, (phrase as APSqlConditionAndPhrase).Child, APSqlConditionJoinType.AND, dbCmd); } else if (phrase is APSqlConditionOrPhrase) { WriteConditionPhrase(writer, (phrase as APSqlConditionOrPhrase).Child, APSqlConditionJoinType.OR, dbCmd); } else { APSqlConditionPhrase cond = phrase as APSqlConditionPhrase; // for this, if columnDef is null, mean this is a 'EXISTS ( subquery )' phrase // do not // change to check ConditionOperator. if (cond.ConditionOperator != APSqlConditionOperator.Exists && cond.ConditionOperator != APSqlConditionOperator.NotExists) WriteSelectExpression(writer, cond.Expr); if (cond.ConditionOperator == APSqlConditionOperator.Equals /*&& !cond.IsRelationDef*/ && (cond.Value == null || cond.Value == DBNull.Value)) { writer.Write("IS NULL"); } else if (cond.ConditionOperator == APSqlConditionOperator.NotEqual /*&& !cond.IsRelationDef*/ && (cond.Value == null || cond.Value == DBNull.Value)) { writer.Write("IS NOT NULL"); } else { switch (cond.ConditionOperator) { case APSqlConditionOperator.Equals: writer.Write("="); break; case APSqlConditionOperator.NotEqual: writer.Write("<>"); break; case APSqlConditionOperator.GreaterThan: writer.Write(">"); break; case APSqlConditionOperator.GreaterThanOrEqual: writer.Write(">="); break; case APSqlConditionOperator.LessThan: writer.Write("<"); break; case APSqlConditionOperator.LessThanOrEqual: writer.Write("<="); break; case APSqlConditionOperator.Between: writer.Write("BETWEEN"); break; case APSqlConditionOperator.NotBetween: writer.Write("NOT BETWEEN"); break; case APSqlConditionOperator.Like: writer.Write("LIKE"); break; case APSqlConditionOperator.NotLike: writer.Write("NOT LIKE"); break; case APSqlConditionOperator.In: writer.Write("IN"); break; case APSqlConditionOperator.NotIn: writer.Write("NOT IN"); break; case APSqlConditionOperator.Exists: writer.Write("EXISTS"); break; case APSqlConditionOperator.NotExists: writer.Write("NOT EXISTS"); break; } object value = cond.Value; if (!TryWriteValue(writer, value)) { if (value is APSqlSelectCommand) { switch (cond.SubQueryScalarRestrict) { case APSqlSubQueryScalarRestrict.All: writer.Write("ALL"); break; case APSqlSubQueryScalarRestrict.Some: writer.Write("SOME"); break; case APSqlSubQueryScalarRestrict.Any: writer.Write("ANY"); break; } writer.Write("("); int idented = writer.Idented++; writer.WriteLine(); ParseSelectInternal(value as APSqlSelectCommand, 0, dbCmd, writer); writer.Idented = idented; writer.Write(")"); } else { if (cond.ConditionOperator == APSqlConditionOperator.In || cond.ConditionOperator == APSqlConditionOperator.NotIn) { writer.Write("("); int i = 0; foreach (object val in value as Array) { if (i != 0) writer.Write(','); string paramName = writer.GetSuitableParameterName(cond.ParamName); paramName = String.Format("@{0}${1}", paramName, i); writer.Write(paramName); AddParameter(dbCmd, paramName, val, ParameterDirection.Input); i++; } writer.Write(")"); } else if (cond.ConditionOperator == APSqlConditionOperator.Between || cond.ConditionOperator == APSqlConditionOperator.NotBetween) { object begin = (value as Array).GetValue(0); object end = (value as Array).GetValue(1); if (!TryWriteValue(writer, begin)) { string paramName = writer.GetSuitableParameterName(cond.ParamName); writer.Write("@" + paramName); AddParameter(dbCmd, paramName, begin, ParameterDirection.Input); } writer.Write("AND"); if (!TryWriteValue(writer, end)) { string paramName = writer.GetSuitableParameterName(cond.ParamName); writer.Write("@" + paramName); AddParameter(dbCmd, paramName, end, ParameterDirection.Input); } } else { string paramName = writer.GetSuitableParameterName(cond.ParamName); writer.Write("@" + paramName); AddParameter(dbCmd, paramName, value, ParameterDirection.Input); } } } } } if (phrase.IsNot) writer.Write(")"); isFirst = false; phrase = phrase.Next as APSqlWherePhrase; } writer.Write(")"); }
/// <summary> /// Get 'WHERE' phrase. /// </summary> /// <param name="op">Condition operator.</param> /// <param name="joinType">Condition join type.</param> /// <param name="values">Values.</param> /// <returns>An APSqlWherePhrase.</returns> protected virtual APSqlWherePhrase GetQueryWherePhrase(APSqlConditionOperator op, APSqlConditionJoinType joinType, string[] values) { List<APSqlWherePhrase> list = new List<APSqlWherePhrase>(); foreach (string value in values) list.Add(GetQueryWherePhrase(op, value)); if (list.Count == 1) return list[0]; if (joinType == APSqlConditionJoinType.AND) return new APSqlConditionAndPhrase(list); else return new APSqlConditionOrPhrase(list); }