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