private static void BuildSingleValue( StringBuilder outputBuilder, ConnectorBase conn, string firstTableName, object value, ValueObjectType valueType, string otherTableName, object otherValue, ValueObjectType otherType, Query relatedQuery, TableSchema rightTableSchema, string rightTableName) { if (valueType == ValueObjectType.Value) { if (value is Query) { outputBuilder.Append('('); outputBuilder.Append(((Query)value).BuildCommand(conn)); outputBuilder.Append(')'); } else if (value is WhereList) { outputBuilder.Append('('); ((WhereList)value).BuildCommand(outputBuilder, conn, relatedQuery, rightTableSchema, rightTableName); outputBuilder.Append(')'); } else if (value is ICollection) { ICollection collIn = value as ICollection; StringBuilder sbIn = new StringBuilder(); sbIn.Append('('); bool first = true; TableSchema schema = null; if (object.ReferenceEquals(otherTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID)) { schema = rightTableSchema; } else { if (relatedQuery != null) { if (otherTableName == null || !relatedQuery.TableAliasMap.TryGetValue(otherTableName, out schema)) { schema = relatedQuery.Schema; } } } foreach (object objIn in collIn) { if (first) first = false; else sbIn.Append(','); if (schema != null) { sbIn.Append(Query.PrepareColumnValue(schema.Columns.Find((string)otherValue), objIn, conn, relatedQuery)); } else { sbIn.Append(conn.PrepareValue(objIn, relatedQuery)); } } if (first) { sbIn.Append("NULL"); // Avoid exceptions, create a NULL list, where the condition will always return FALSE } sbIn.Append(')'); outputBuilder.Append(sbIn.ToString()); } else if (otherType == ValueObjectType.ColumnName) { TableSchema schema = null; if (object.ReferenceEquals(otherTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID)) { schema = rightTableSchema; } else { if (relatedQuery != null) { if (otherTableName == null || !relatedQuery.TableAliasMap.TryGetValue(otherTableName, out schema)) { schema = relatedQuery.Schema; } } } if (schema != null) { // Try to match value type to the other value type outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)otherValue), value, conn, relatedQuery)); } else { // Format it according to generic rules outputBuilder.Append(conn.PrepareValue(value, relatedQuery)); } } else { outputBuilder.Append(conn.PrepareValue(value, relatedQuery)); } } else if (valueType == ValueObjectType.ColumnName) { if (firstTableName != null) { if (object.ReferenceEquals(firstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID)) { outputBuilder.Append(conn.WrapFieldName(rightTableName)); } else { outputBuilder.Append(conn.WrapFieldName(firstTableName)); } outputBuilder.Append('.'); } outputBuilder.Append(conn.WrapFieldName((string)value)); } else { outputBuilder.Append(value == null ? @"NULL" : value); } }
public void BuildCommand(StringBuilder outputBuilder, bool isFirst, ConnectorBase conn, Query relatedQuery) { BuildCommand(outputBuilder, isFirst, conn, relatedQuery, null, null); }
public void BuildCommand(StringBuilder outputBuilder, bool isFirst, ConnectorBase conn, Query relatedQuery, TableSchema rightTableSchema, string rightTableName) { if (!isFirst) { switch (Condition) { case WhereCondition.AND: outputBuilder.Append(@" AND "); break; case WhereCondition.OR: outputBuilder.Append(@" OR "); break; } } // The list is empty? if (Comparison == WhereComparison.None && (First is WhereList && ((WhereList)First).Count == 0) ) { outputBuilder.Append(@"1"); // dump a dummy TRUE condition to fill the blank return; } if (First is WhereList) { outputBuilder.Append('('); ((WhereList)First).BuildCommand(outputBuilder, conn, relatedQuery, rightTableSchema, rightTableName); outputBuilder.Append(')'); } else { BuildSingleValue( outputBuilder, conn, FirstTableName, First, FirstType, SecondTableName, Second, SecondType, relatedQuery, rightTableSchema, rightTableName); if (Comparison != WhereComparison.None) { switch (Comparison) { case WhereComparison.EqualsTo: if (First == null || Second == null) outputBuilder.Append(@" IS "); else outputBuilder.Append(@" = "); break; case WhereComparison.NotEqualsTo: if (First == null || Second == null) outputBuilder.Append(@" IS NOT "); else outputBuilder.Append(@" <> "); break; case WhereComparison.GreaterThan: outputBuilder.Append(@" > "); break; case WhereComparison.GreaterThanOrEqual: outputBuilder.Append(@" >= "); break; case WhereComparison.LessThan: outputBuilder.Append(@" < "); break; case WhereComparison.LessThanOrEqual: outputBuilder.Append(@" <= "); break; case WhereComparison.Is: outputBuilder.Append(@" IS "); break; case WhereComparison.IsNot: outputBuilder.Append(@" IS NOT "); break; case WhereComparison.Like: outputBuilder.Append(@" LIKE "); break; case WhereComparison.Between: outputBuilder.Append(@" BETWEEN "); break; case WhereComparison.In: outputBuilder.Append(@" IN "); break; case WhereComparison.NotIn: outputBuilder.Append(@" NOT IN "); break; } BuildSingleValue( outputBuilder, conn, SecondTableName, Second, SecondType, FirstTableName, First, FirstType, relatedQuery, rightTableSchema, rightTableName); if (Comparison == WhereComparison.Between) { outputBuilder.Append(@" AND "); BuildSingleValue( outputBuilder, conn, ThirdTableName, Third, ThirdType, FirstTableName, First, FirstType, relatedQuery, rightTableSchema, rightTableName); } else if (Comparison == WhereComparison.Like) { outputBuilder.Append(' '); outputBuilder.Append(conn.LikeEscapingStatement); outputBuilder.Append(' '); } } } }
public Where(WhereCondition condition, Query query, WhereComparison comparison, object value, ValueObjectType valueType = ValueObjectType.Value) { Condition = condition; First = query; FirstType = ValueObjectType.Value; Comparison = comparison; Second = value; SecondType = valueType; }
public Where(WhereCondition condition, Query query, WhereComparison comparison, string tableName, string columnName) { Condition = condition; First = query; FirstType = ValueObjectType.Value; Comparison = comparison; SecondTableName = tableName; Second = columnName; SecondType = ValueObjectType.ColumnName; }
public Query Having(WhereCondition condition, Query query, WhereComparison comparison, string tableName, string columnName) { return Having(new Where(condition, query, comparison, tableName, columnName)); }
public Query Having(WhereCondition condition, Query query, WhereComparison comparison, object value, ValueObjectType valueType = ValueObjectType.Value) { return Having(new Where(condition, query, comparison, value, valueType)); }
public Query AddSelect(Query query, string alias = null) { return Select(query, alias); }
public Query Having(Query query, WhereComparison comparison, string tableName, string columnName) { return Having(WhereCondition.AND, query, comparison, tableName, columnName); }
public Query Select(Query query, string alias = null) { this.QueryMode = QueryMode.Select; if (_ListSelect == null) _ListSelect = new SelectColumnList(); _ListSelect.Add(new SelectColumn(query, alias)); return this; }
public string Build(ConnectorBase conn, Query relatedQuery = null) { string ret = ""; if (Type == ValueObjectType.ColumnName) { if (TableName != null && TableName.Length > 0) { ret += conn.WrapFieldName(TableName); ret += "."; } ret += conn.WrapFieldName(Value.ToString()); } else if (Type == ValueObjectType.Value) { ret += @"(" + conn.PrepareValue(Value, relatedQuery) + @")"; } else { ret += Value; } return ret; }
public void BuildCommand(StringBuilder outputBuilder, bool isFirst, ConnectorBase conn, Query relatedQuery, TableSchema rightTableSchema, string rightTableName) { if (!isFirst) { switch (Condition) { case WhereCondition.AND: outputBuilder.Append(@" AND "); break; case WhereCondition.OR: outputBuilder.Append(@" OR "); break; } } if (Comparision == WhereComparision.None && // Its not a comparison // And there's no list or the list is empty (!(First is WhereList) || ((WhereList)First).Count == 0) && // And it's not a literal expression FirstType != ValueObjectType.Literal && FirstType != ValueObjectType.Value ) { outputBuilder.Append(@"1"); // dump a dummy TRUE condition to fill the blank return; } if (First is WhereList) { outputBuilder.Append('('); ((WhereList)First).BuildCommand(outputBuilder, conn, relatedQuery, rightTableSchema, rightTableName); outputBuilder.Append(')'); } else { if (FirstType == ValueObjectType.Value) { if (SecondType == ValueObjectType.ColumnName) { if (object.ReferenceEquals(SecondTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID)) { outputBuilder.Append(Query.PrepareColumnValue(rightTableSchema.Columns.Find((string)Second), First, conn)); } else { TableSchema schema; if (SecondTableName == null || !relatedQuery.TableAliasMap.TryGetValue(SecondTableName, out schema)) { schema = relatedQuery.Schema; } outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)Second), First, conn)); } } else { outputBuilder.Append(conn.PrepareValue(First)); } } else if (FirstType == ValueObjectType.ColumnName) { if (FirstTableName != null) { if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID)) { outputBuilder.Append(conn.EncloseFieldName(rightTableName)); } else { outputBuilder.Append(conn.EncloseFieldName(FirstTableName)); } outputBuilder.Append('.'); } outputBuilder.Append(conn.EncloseFieldName((string)First)); } else { outputBuilder.Append(First == null ? @"NULL" : First); } if (Comparision != WhereComparision.None) { switch (Comparision) { case WhereComparision.EqualsTo: if (First == null || Second == null) outputBuilder.Append(@" IS "); else outputBuilder.Append(@" = "); break; case WhereComparision.NotEqualsTo: if (First == null || Second == null) outputBuilder.Append(@" IS NOT "); else outputBuilder.Append(@" <> "); break; case WhereComparision.GreaterThan: outputBuilder.Append(@" > "); break; case WhereComparision.GreaterThanOrEqual: outputBuilder.Append(@" >= "); break; case WhereComparision.LessThan: outputBuilder.Append(@" < "); break; case WhereComparision.LessThanOrEqual: outputBuilder.Append(@" <= "); break; case WhereComparision.Is: outputBuilder.Append(@" IS "); break; case WhereComparision.IsNot: outputBuilder.Append(@" IS NOT "); break; case WhereComparision.Like: outputBuilder.Append(@" LIKE "); break; case WhereComparision.Between: outputBuilder.Append(@" BETWEEN "); break; case WhereComparision.In: outputBuilder.Append(@" IN "); break; case WhereComparision.NotIn: outputBuilder.Append(@" NOT IN "); break; } if (Comparision != WhereComparision.In && Comparision != WhereComparision.NotIn) { if (SecondType == ValueObjectType.Value) { if (Second is Query) { outputBuilder.Append('('); outputBuilder.Append(((Query)Second).BuildCommand(conn)); outputBuilder.Append(')'); } else { if (FirstType == ValueObjectType.ColumnName) { // Match SECOND value to FIRST's column type if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID)) { outputBuilder.Append(Query.PrepareColumnValue(rightTableSchema.Columns.Find((string)First), Second, conn)); } else { TableSchema schema; if (FirstTableName == null || !relatedQuery.TableAliasMap.TryGetValue(FirstTableName, out schema)) { schema = relatedQuery.Schema; } outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)First), Second, conn)); } } else { outputBuilder.Append(conn.PrepareValue(Second)); } } } else if (SecondType == ValueObjectType.ColumnName) { if (SecondTableName != null) { if (object.ReferenceEquals(SecondTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID)) { outputBuilder.Append(conn.EncloseFieldName(rightTableName)); } else { outputBuilder.Append(conn.EncloseFieldName(SecondTableName)); } outputBuilder.Append('.'); } outputBuilder.Append(conn.EncloseFieldName((string)Second)); } else { if (Second == null) outputBuilder.Append(@"NULL"); else outputBuilder.Append(Second); } } else { if (Second is Query) outputBuilder.AppendFormat(@"({0})", Second.ToString()); else { ICollection collIn = Second as ICollection; if (collIn != null) { StringBuilder sbIn = new StringBuilder(); sbIn.Append('('); bool first = true; TableSchema schema = null; if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID)) { schema = rightTableSchema; } else { if (FirstTableName == null || !relatedQuery.TableAliasMap.TryGetValue(FirstTableName, out schema)) { schema = relatedQuery.Schema; } } foreach (object objIn in collIn) { if (first) first = false; else sbIn.Append(','); if (schema != null) sbIn.Append(Query.PrepareColumnValue(schema.Columns.Find((string)First), objIn, conn)); else sbIn.Append(conn.PrepareValue(objIn)); } sbIn.Append(')'); outputBuilder.Append(sbIn.ToString()); } else outputBuilder.Append(Second); } } if (Comparision == WhereComparision.Between) { outputBuilder.Append(@" AND "); if (ThirdType == ValueObjectType.Value) { if (FirstType == ValueObjectType.ColumnName) { TableSchema schema = null; if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID)) { schema = rightTableSchema; } else { if (FirstTableName == null || !relatedQuery.TableAliasMap.TryGetValue(FirstTableName, out schema)) { schema = relatedQuery.Schema; } } outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)First), Third, conn)); } else outputBuilder.Append(conn.PrepareValue(Third)); } else if (ThirdType == ValueObjectType.ColumnName) { if (ThirdTableName != null) { if (object.ReferenceEquals(ThirdTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID)) { outputBuilder.Append(conn.EncloseFieldName(rightTableName)); } else { outputBuilder.Append(conn.EncloseFieldName(ThirdTableName)); } outputBuilder.Append('.'); } outputBuilder.Append(conn.EncloseFieldName((string)Third)); } else outputBuilder.Append(Third == null ? @"NULL" : Third); } if (Comparision == WhereComparision.Like) { outputBuilder.Append(' '); outputBuilder.Append(conn.LikeEscapingStatement); outputBuilder.Append(' '); } } } }
public Query Where(Query query, WhereComparison comparison, object value, ValueObjectType valueType = ValueObjectType.Value) { return Where(new Where(WhereCondition.AND, query, comparison, value, valueType)); }
public Query Where(Query query, WhereComparison comparison, string tableName, string columnName) { return Where(new Where(WhereCondition.AND, query, comparison, tableName, columnName)); }