Example #1
0
 public Query Join(JoinType joinType,
     TableSchema leftTableSchema, string leftColumn, string leftTableAlias,
     object rightTableSql, string rightColumn, string rightTableAlias)
 {
     if (_ListJoin == null) _ListJoin = new JoinList();
     Join join = new Join(joinType, leftTableSchema, leftColumn, leftTableAlias, rightTableSql, rightColumn, rightTableAlias);
     _ListJoin.Add(join);
     return this;
 }
Example #2
0
 public Query Join(JoinType joinType,
     TableSchema rightTableSchema, string rightTableAlias,
     params JoinColumnPair[] pairs)
 {
     if (_ListJoin == null) _ListJoin = new JoinList();
     Join join = new Join(joinType, rightTableSchema, rightTableAlias, pairs);
     _ListJoin.Add(join);
     TableAliasMap[join.RightTableAlias] = join.RightTableSchema;
     return this;
 }
Example #3
0
 /// <summary>
 /// Adds a foreign key.
 /// </summary>
 /// <param name="foreignKey">Key to add</param>
 /// <returns>Current <typeparamref name="Query"/> object</returns>
 public Query CreateForeignKey(TableSchema.ForeignKey foreignKey)
 {
     ClearSelect();
     ClearOrderBy();
     ClearGroupBy();
     ClearInsertAndUpdate();
     ClearStoredProcedureParameters();
     this.QueryMode = QueryMode.CreateIndex;
     this._CreateIndexObject = foreignKey;
     return this;
 }
Example #4
0
 /// <summary>
 /// Adds a column.
 /// </summary>
 /// <param name="column">Column to add</param>
 /// <returns>Current <typeparamref name="Query"/> object</returns>
 public Query AddColumn(TableSchema.Column column)
 {
     ClearSelect();
     ClearOrderBy();
     ClearGroupBy();
     ClearInsertAndUpdate();
     ClearStoredProcedureParameters();
     _AlterColumn = column;
     this.QueryMode = QueryMode.AddColumn;
     return this;
 }
Example #5
0
        public void BuildColumnPropertiesDataType(StringBuilder sb, ConnectorBase connection, TableSchema.Column column, out bool isTextField)
        {
            if (column.LiteralType != null && column.LiteralType.Length > 0)
            {
                isTextField = column.ActualDataType == DataType.VarChar ||
                    column.ActualDataType == DataType.Char ||
                    column.ActualDataType == DataType.Text ||
                    column.ActualDataType == DataType.MediumText ||
                    column.ActualDataType == DataType.LongText ||
                    column.Type == typeof(string);

                sb.Append(column.LiteralType);
                return;
            }

            isTextField = false;
            DataType dataType = column.ActualDataType;
            if (!column.AutoIncrement || (connection.TYPE != ConnectorBase.SqlServiceType.MSACCESS && connection.TYPE != ConnectorBase.SqlServiceType.POSTGRESQL))
            {
                if (dataType == DataType.VarChar)
                {
                    if (column.MaxLength < 0)
                    {
                        if (connection.varchar_MAX != null)
                        {
                            sb.Append(connection.type_VARCHAR);
                            sb.AppendFormat(@"({0})", connection.varchar_MAX);
                        }
                        else
                        {
                            sb.Append(connection.type_VARCHAR);
                            sb.AppendFormat(@"({0})", connection.varchar_MAX_VALUE);
                        }
                    }
                    else if (column.MaxLength == 0)
                    {
                        sb.Append(connection.type_TEXT);
                        isTextField = true;
                    }
                    else if (column.MaxLength <= connection.varchar_MAX_VALUE)
                    {
                        sb.Append(connection.type_VARCHAR);
                        sb.AppendFormat(@"({0})", column.MaxLength);
                    }
                    else if (column.MaxLength < 65536)
                    {
                        sb.Append(connection.type_TEXT);
                        isTextField = true;
                    }
                    else if (column.MaxLength < 16777215)
                    {
                        sb.Append(connection.type_MEDIUMTEXT);
                        isTextField = true;
                    }
                    else
                    {
                        sb.Append(connection.type_LONGTEXT);
                        isTextField = true;
                    }
                }

                if (dataType == DataType.Char)
                {
                    if (column.MaxLength < 0)
                    {
                        if (connection.varchar_MAX != null)
                        {
                            sb.Append(connection.type_CHAR);
                            sb.AppendFormat(@"({0})", connection.varchar_MAX);
                        }
                        else
                        {
                            sb.Append(connection.type_CHAR);
                            sb.AppendFormat(@"({0})", connection.varchar_MAX_VALUE);
                        }
                    }
                    else if (column.MaxLength == 0 || column.MaxLength >= connection.varchar_MAX_VALUE)
                    {
                        sb.Append(connection.type_CHAR);
                        sb.AppendFormat(@"({0})", connection.varchar_MAX_VALUE);
                    }
                    else
                    {
                        sb.Append(connection.type_CHAR);
                        sb.AppendFormat(@"({0})", column.MaxLength);
                    }
                }
                else if (dataType == DataType.Text)
                {
                    sb.Append(connection.type_TEXT);
                    isTextField = true;
                }
                else if (dataType == DataType.MediumText)
                {
                    sb.Append(connection.type_MEDIUMTEXT);
                    isTextField = true;
                }
                else if (dataType == DataType.LongText)
                {
                    sb.Append(connection.type_LONGTEXT);
                    isTextField = true;
                }
                else if (dataType == DataType.Boolean)
                {
                    sb.Append(connection.type_BOOLEAN);
                }
                else if (dataType == DataType.DateTime)
                {
                    sb.Append(connection.type_DATETIME);
                }
                else if (dataType == DataType.Numeric)
                {
                    if (column.NumberPrecision > 0)
                    {
                        sb.Append(connection.type_NUMERIC);
                        sb.AppendFormat(@"({0}, {1})", column.NumberPrecision, column.NumberScale);
                    }
                    else
                    {
                        sb.Append(connection.type_NUMERIC);
                    }
                }
                else if (dataType == DataType.Float)
                {
                    if (column.NumberPrecision > 0 && connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
                    {
                        sb.Append(connection.type_FLOAT);
                        sb.AppendFormat(@"({0}, {1})", column.NumberPrecision, column.NumberScale);
                    }
                    else
                    {
                        sb.Append(connection.type_FLOAT);
                    }
                }
                else if (dataType == DataType.Double)
                {
                    if (column.NumberPrecision > 0 && connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
                    {
                        sb.Append(connection.type_DOUBLE);
                        sb.AppendFormat(@"({0}, {1})", column.NumberPrecision, column.NumberScale);
                    }
                    else
                    {
                        sb.Append(connection.type_DOUBLE);
                    }
                }
                else if (dataType == DataType.Decimal)
                {
                    if (column.NumberPrecision > 0)
                    {
                        sb.Append(connection.type_DECIMAL);
                        sb.AppendFormat(@"({0}, {1})", column.NumberPrecision, column.NumberScale);
                    }
                    else
                    {
                        sb.Append(connection.type_DECIMAL);
                    }
                }
                else if (dataType == DataType.Money)
                {
                    if (column.NumberPrecision > 0)
                    {
                        sb.Append(connection.type_MONEY);
                        if (connection.TYPE != ConnectorBase.SqlServiceType.MSSQL)
                        {
                            sb.AppendFormat(@"({0}, {1})", column.NumberPrecision, column.NumberScale);
                        }
                    }
                    else
                    {
                        sb.Append(connection.type_MONEY);
                    }
                }
                else if (dataType == DataType.TinyInt)
                {
                    sb.Append(connection.type_TINYINT);
                }
                else if (dataType == DataType.UnsignedTinyInt)
                {
                    sb.Append(connection.type_UNSIGNEDTINYINT);
                }
                else if (dataType == DataType.SmallInt)
                {
                    sb.Append(connection.type_SMALLINT);
                }
                else if (dataType == DataType.UnsignedSmallInt)
                {
                    sb.Append(connection.type_UNSIGNEDSMALLINT);
                }
                else if (dataType == DataType.Int)
                {
                    sb.Append(connection.type_INT);
                }
                else if (dataType == DataType.UnsignedInt)
                {
                    sb.Append(connection.type_UNSIGNEDINT);
                }
                else if (dataType == DataType.BigInt)
                {
                    sb.Append(connection.type_BIGINT);
                }
                else if (dataType == DataType.UnsignedBigInt)
                {
                    sb.Append(connection.type_UNSIGNEDBIGINT);
                }
                else if (dataType == DataType.Json)
                {
                    sb.Append(connection.type_JSON);
                }
                else if (dataType == DataType.JsonBinary)
                {
                    sb.Append(connection.type_JSON_BINARY);
                }
                else if (dataType == DataType.Blob)
                {
                    sb.Append(connection.type_BLOB);
                }
                else if (dataType == DataType.Guid)
                {
                    sb.Append(connection.type_GUID);
                }
                else if (dataType == DataType.Geometry)
                {
                    sb.Append(connection.type_GEOMETRY);
                }
                else if (dataType == DataType.GeometryCollection)
                {
                    sb.Append(connection.type_GEOMETRYCOLLECTION);
                }
                else if (dataType == DataType.Point)
                {
                    sb.Append(connection.type_POINT);
                }
                else if (dataType == DataType.LineString)
                {
                    sb.Append(connection.type_LINESTRING);
                }
                else if (dataType == DataType.Polygon)
                {
                    sb.Append(connection.type_POLYGON);
                }
                else if (dataType == DataType.Line)
                {
                    sb.Append(connection.type_LINE);
                }
                else if (dataType == DataType.Curve)
                {
                    sb.Append(connection.type_CURVE);
                }
                else if (dataType == DataType.Surface)
                {
                    sb.Append(connection.type_SURFACE);
                }
                else if (dataType == DataType.LinearRing)
                {
                    sb.Append(connection.type_LINEARRING);
                }
                else if (dataType == DataType.MultiPoint)
                {
                    sb.Append(connection.type_MULTIPOINT);
                }
                else if (dataType == DataType.MultiLineString)
                {
                    sb.Append(connection.type_MULTILINESTRING);
                }
                else if (dataType == DataType.MultiPolygon)
                {
                    sb.Append(connection.type_MULTIPOLYGON);
                }
                else if (dataType == DataType.MultiCurve)
                {
                    sb.Append(connection.type_MULTICURVE);
                }
                else if (dataType == DataType.MultiSurface)
                {
                    sb.Append(connection.type_MULTISURFACE);
                }
                else if (dataType == DataType.Geographic)
                {
                    sb.Append(connection.type_GEOGRAPHIC);
                }
                else if (dataType == DataType.GeographicCollection)
                {
                    sb.Append(connection.type_GEOGRAPHICCOLLECTION);
                }
                else if (dataType == DataType.GeographicPoint)
                {
                    sb.Append(connection.type_GEOGRAPHIC_POINT);
                }
                else if (dataType == DataType.GeographicLineString)
                {
                    sb.Append(connection.type_GEOGRAPHIC_LINESTRING);
                }
                else if (dataType == DataType.GeographicPolygon)
                {
                    sb.Append(connection.type_GEOGRAPHIC_POLYGON);
                }
                else if (dataType == DataType.GeographicLine)
                {
                    sb.Append(connection.type_GEOGRAPHIC_LINE);
                }
                else if (dataType == DataType.GeographicCurve)
                {
                    sb.Append(connection.type_GEOGRAPHIC_CURVE);
                }
                else if (dataType == DataType.GeographicSurface)
                {
                    sb.Append(connection.type_GEOGRAPHIC_SURFACE);
                }
                else if (dataType == DataType.GeographicLinearRing)
                {
                    sb.Append(connection.type_GEOGRAPHIC_LINEARRING);
                }
                else if (dataType == DataType.GeographicMultiPoint)
                {
                    sb.Append(connection.type_GEOGRAPHIC_MULTIPOINT);
                }
                else if (dataType == DataType.GeographicMultiLineString)
                {
                    sb.Append(connection.type_GEOGRAPHIC_MULTILINESTRING);
                }
                else if (dataType == DataType.GeographicMultiPolygon)
                {
                    sb.Append(connection.type_GEOGRAPHIC_MULTIPOLYGON);
                }
                else if (dataType == DataType.GeographicMultiCurve)
                {
                    sb.Append(connection.type_GEOGRAPHIC_MULTICURVE);
                }
                else if (dataType == DataType.GeographicMultiSurface)
                {
                    sb.Append(connection.type_GEOGRAPHIC_MULTISURFACE);
                }
                sb.Append(' ');
            }

            if (column.AutoIncrement)
            {
                if (dataType == DataType.BigInt || dataType == DataType.UnsignedBigInt)
                { // Specifically for PostgreSQL
                    sb.Append(connection.type_AUTOINCREMENT_BIGINT);
                }
                else
                {
                    sb.Append(connection.type_AUTOINCREMENT);
                }
                sb.Append(' ');
            }

            if (connection.TYPE != ConnectorBase.SqlServiceType.POSTGRESQL && !string.IsNullOrEmpty(column.Charset))
            {
                sb.Append(@"COLLATE ");
                sb.Append(column.Collate);
                sb.Append(' ');
            }

            if (!string.IsNullOrEmpty(column.Charset))
            {
                sb.Append(@"CHARACTER SET ");
                sb.Append(column.Charset);
                sb.Append(' ');
            }
        }
Example #6
0
        public void BuildColumnProperties(StringBuilder sb, ConnectorBase connection, TableSchema.Column column, bool NoDefault)
        {
            sb.Append(connection.WrapFieldName(column.Name));
            sb.Append(' ');

            bool isTextField;
            BuildColumnPropertiesDataType(sb, connection, column, out isTextField);

            if (!column.Nullable)
            {
                sb.Append(@"NOT NULL ");
            }
            if (!NoDefault && column.Default != null && (!(isTextField && connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)))
            {
                sb.Append(@"DEFAULT ");
                Query.PrepareColumnValue(column, column.Default, sb, connection, this);
                sb.Append(' ');
            }
        }
Example #7
0
        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);
            }
        }
Example #8
0
        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(' ');
                    }
                }
            }
        }
Example #9
0
 /// <summary>
 /// Alters a column.
 /// </summary>
 /// <param name="oldColumnName">The column's old name</param>
 /// <param name="column">The updated column's definition</param>
 /// <returns>Current <typeparamref name="Query"/> object</returns>
 public Query ChangeColumn(string oldColumnName, TableSchema.Column column)
 {
     ClearSelect();
     ClearOrderBy();
     ClearGroupBy();
     ClearInsertAndUpdate();
     ClearStoredProcedureParameters();
     _AlterColumn = column;
     _AlterColumnOldName = oldColumnName;
     this.QueryMode = QueryMode.ChangeColumn;
     return this;
 }
Example #10
0
 /// <summary>
 /// Alters a column.
 /// Will use the same column name for old and updated column.
 /// </summary>
 /// <param name="column">The column to alter</param>
 /// <returns>Current <typeparamref name="Query"/> object</returns>
 public Query ChangeColumn(TableSchema.Column column)
 {
     return ChangeColumn(null, column);
 }
Example #11
0
        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(' ');
                    }
                }
            }
        }
Example #12
0
 public Query LeftOuterJoin(
     TableSchema rightTableSchema, string rightTableAlias,
     params JoinColumnPair[] pairs)
 {
     return Join(JoinType.LeftOuterJoin, rightTableSchema, rightTableAlias, pairs);
 }