Beispiel #1
0
        public static Statement CreateStatement(Insert insert, IEntityMapping mapping)
        {
            var writer = new SqlWriter();
            IDictionary <string, object> parameters = null;

            writer.InsertInto.QuotedName(insert.Into.Name);

            var resultType = Statement.ResultType.None;

            switch (insert.Type)
            {
            case Insert.SetType.Values:
                writer.OpenBlock.Trim().FieldList(x => x.Comma.Flush(), insert.Values.Keys).Trim().CloseBlock.Flush();
                parameters = new Dictionary <string, object>();
                writer.Values.OpenBlock.Trim().ParameterList(x => x.Comma.Flush(), insert.Values.Values.Select(x => parameters.AddWithUniquelyNamedKey(x))).
                Trim().CloseBlock.Flush();
                if (insert.HasIdentityKey)
                {
                    writer.Trim().QuerySeperator.Select.ScopeIdentity(typeof(int));
                    resultType = Statement.ResultType.Scalar;
                }
                break;

            case Insert.SetType.Query:
                var select = SelectWriter <TEntity> .CreateStatement(insert.Query, mapping);

                parameters = select.Parameters;
                writer.OpenBlock.Trim().FieldList(x => x.Comma.Flush(), SelectWriter <TEntity> .BuildProjection(insert.Query, mapping, parameters)).Trim().CloseBlock.Flush();
                writer.Write(select.Text);
                break;
            }

            return(new Statement(writer.ToString(), Statement.StatementType.Text, resultType, parameters));
        }
Beispiel #2
0
        private void VisitExpression(Operator @operator, Operator parent)
        {
            var isNotNull = @operator.Type == Operator.OperatorType.NotEqual && !IsRightOperandNullConstant(@operator);

            if (IsComparisonOperator(parent) && !IsMathOperator(@operator))
            {
                _sql.Case.When.Flush();
            }

            _sql.OpenBlock.Trim();
            if (isNotNull)
            {
                _sql.OpenBlock.Trim();
            }

            VisitOperand(@operator.LeftOperand, @operator);
            switch (@operator.Type)
            {
            case Operator.OperatorType.And: _sql.And.Flush(); break;

            case Operator.OperatorType.Or: _sql.Or.Flush(); break;

            case Operator.OperatorType.Equal: if (IsRightOperandNullConstant(@operator))
                {
                    _sql.Is.Flush();
                }
                else
                {
                    _sql.Equal.Flush();
                } break;

            case Operator.OperatorType.NotEqual: if (IsRightOperandNullConstant(@operator))
                {
                    _sql.Is.Not.Flush();
                }
                else
                {
                    _sql.NotEqual.Flush();
                } break;

            case Operator.OperatorType.GreaterThan: _sql.GreaterThan.Flush(); break;

            case Operator.OperatorType.GreaterThanOrEqual: _sql.GreaterThanOrEqual.Flush(); break;

            case Operator.OperatorType.LessThan: _sql.LessThan.Flush(); break;

            case Operator.OperatorType.LessThanOrEqual: _sql.LessThanOrEqual.Flush(); break;

            case Operator.OperatorType.Add: _sql.Plus.Flush(); break;

            case Operator.OperatorType.Subtract: _sql.Minus.Flush(); break;

            case Operator.OperatorType.Multiply: _sql.Multiply.Flush(); break;

            case Operator.OperatorType.Divide: _sql.Divide.Flush(); break;

            case Operator.OperatorType.Modulo: _sql.Modulo.Flush(); break;
            }
            VisitOperand(@operator.RightOperand, @operator);
            _sql.Trim().CloseBlock.Flush();

            if (isNotNull)
            {
                _sql.Or.Flush();
                VisitOperand(@operator.LeftOperand, @operator);
                _sql.Is.Null.Trim().CloseBlock.Flush();
            }

            if (IsComparisonOperator(parent) && !IsMathOperator(@operator))
            {
                _sql.Then.True.Else.False.End.Flush();
            }
        }
Beispiel #3
0
        public static Statement CreateStatement(Select select, IEntityMapping mapping, IEnumerable <string> projectionOverride = null, bool noLock = false)
        {
            var sql        = new SqlWriter();
            var parameters = new Dictionary <string, object>();

            var projection    = projectionOverride ?? BuildProjection(select, mapping, parameters);
            var whereClause   = BuildWhereClause(select, mapping, parameters);
            var orderByClause = select.HasOrderBy ? BuildOrderBy(select.OrderBy, mapping, parameters) : null;

            Action <SqlWriter> writeProjection = x => x.Do(projection != null, y => y.FieldList(z => z.Comma.Flush(), projection), y => y.Wildcard.Flush());

            if (select.Any)
            {
                sql.Select.Cast().Trim().OpenBlock.Trim().Case.When.Exists.OpenBlock.Trim();
            }

            if (select.From.IsTable || select.HasConditions)
            {
                sql.Select.Flush();

                if (select.First || select.FirstOrDefault)
                {
                    sql.Top(1);
                }
                else if (select.Single)
                {
                    sql.Top(2);
                }
                else if (select.HasTop && !select.HasStart)
                {
                    sql.Do(select.TopType == Select.TopValueType.Count, x => x.Top(select.Top), x => x.TopPercent(select.Top));
                }

                if (select.Count)
                {
                    sql.CountWildcard.Flush();
                }
                else
                {
                    sql.Do(writeProjection);
                }

                sql.From.Flush();

                if (select.HasStart)
                {
                    sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma.
                    RowNumber().Over.OpenBlock.Trim().OrderBy.
                    Do(select.HasOrderBy, x => x.Write(orderByClause), x => x.Do(projection != null, writeProjection, y => y.QuotedName(mapping.Key.GetColumnName()))).Trim().
                    CloseBlock.As.RowNumberAlias.From.Flush();
                }

                if (select.HasDuplicates)
                {
                    var duplicateProjection = BuildProjection(select.Duplicates.Distinct, mapping, parameters);
                    sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma.
                    RowNumber().Over.OpenBlock.Trim().Partition.By.Write(duplicateProjection).OrderBy.Write(BuildOrderBy(select.Duplicates.OrderBy, mapping, parameters)).
                    Trim().CloseBlock.As.PartitionAlias.From.Flush();
                }
                else if (select.HasDistinct)
                {
                    var distinctProjection = BuildProjections(select.Distinct.Select(x => x.Projection), mapping, parameters);
                    sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma.
                    RowNumber().Over.OpenBlock.Trim().Partition.By.ExpressionList(z => z.Comma.Flush(), distinctProjection).OrderBy.
                    Write(BuildOrderBy(select.Distinct.Any(x => x.HasOrder) ? select.Distinct.Where(x => x.HasOrder).Select(x => x.Order) :
                                       select.Distinct.Select(x => new OrderBy {
                        Type       = OrderBy.SourceType.Projection,
                        Projection = x.Projection,
                        Order      = Order.Ascending
                    }), mapping, parameters)).Trim().
                    CloseBlock.As.PartitionAlias.From.Flush();
                }
            }

            switch (select.From.Type)
            {
            case Data.DataType.Table:
                sql.QuotedName(select.From.Table.Name).Write(select.From.Alias);
                if (noLock)
                {
                    sql.With(x => x.NoLock.Flush());
                }
                break;

            case Data.DataType.Query:
                var first = true;
                if (select.HasConditions)
                {
                    sql.OpenBlock.Trim().Flush();
                }
                foreach (var subQuery in select.From.Queries.Select(x => CreateStatement(x, mapping, projection, noLock)))
                {
                    sql.Do(!first, x => x.Union.Flush()).Write(subQuery.Text).Flush();
                    parameters.AddRange(subQuery.Parameters);
                    first = false;
                }
                if (select.HasConditions)
                {
                    sql.Trim().CloseBlock.As.Write(select.From.Alias).Flush();
                }
                break;
            }

            if (select.From.IsTable || select.HasConditions)
            {
                if (select.HasWhere || select.HasSetOperations)
                {
                    sql.Where.Write(whereClause).Flush();
                }

                if (select.Randomize)
                {
                    sql.OrderBy.NewId();
                }
                // The reason why we dont do an order by if there is a start is because the order by is
                // already specified in the row number definition. So we dont need to specify it again.
                else if (select.HasOrderBy && !select.HasStart && !select.HasDistinct)
                {
                    sql.OrderBy.Write(orderByClause);
                }

                if (select.HasDuplicates)
                {
                    sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.PartitionAlias.GreaterThan.Value(1, SqlDbType.Int).Flush();
                }
                else if (select.HasDistinct)
                {
                    sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.PartitionAlias.Equal.Value(1, SqlDbType.Int).Flush();
                }

                if (select.HasStart)
                {
                    sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.RowNumberAlias.Flush();
                    if (select.HasTop && select.HasStart)
                    {
                        sql.Between(select.Start, select.Start + (select.Top - 1));
                    }
                    else
                    {
                        sql.GreaterThanOrEqual.Value(select.Start, SqlDbType.Int);
                    }
                }
            }

            if (select.Any)
            {
                sql.Trim().CloseBlock.Then.Value(1, SqlDbType.Bit).Else.Value(0, SqlDbType.Bit).End.As.Write(DataTypes.Bit.SqlName).Trim().CloseBlock.Flush();
            }

            return(new Statement(sql.ToString(), Statement.StatementType.Text, GetResultType(select), parameters));
        }
Beispiel #4
0
        private void VisitFunction(Function function)
        {
            switch (function.Type)
            {
            case Function.FunctionType.Coalesce:
                _sql.Coalesce(x => VisitProjection(function.Coalesce.First),
                              x => VisitProjection(function.Coalesce.Second));
                break;

            case Function.FunctionType.Convert:
                _sql.Cast(x => VisitProjection(function.Convert.Value), function.Convert.Type, 0, null, null);
                break;

            case Function.FunctionType.IndexOf:
                _sql.IndexOf(x => VisitProjection(function.IndexOf.Text),
                             x => VisitProjection(function.IndexOf.Value));
                break;

            case Function.FunctionType.IndexOfAt:
                _sql.IndexOf(x => VisitProjection(function.IndexOfAt.Text),
                             x => VisitProjection(function.IndexOfAt.Value),
                             x => VisitProjection(function.IndexOfAt.Start));
                break;

            case Function.FunctionType.Insert:
                _sql.Insert(x => VisitProjection(function.Insert.Text),
                            x => VisitProjection(function.Insert.Value),
                            x => VisitProjection(function.Insert.Start));
                break;

            case Function.FunctionType.Length:
                _sql.Length(x => VisitProjection(function.Length.Text));
                break;

            case Function.FunctionType.Replace:
                _sql.Replace(x => VisitProjection(function.Replace.Text),
                             x => VisitProjection(function.Replace.SearchValue),
                             x => VisitProjection(function.Replace.ReplaceValue));
                break;

            case Function.FunctionType.Substring:
                _sql.Substring(x => VisitProjection(function.Substring.Text),
                               x => VisitProjection(function.Substring.Start));
                break;

            case Function.FunctionType.SubstringFixed:
                _sql.Substring(x => VisitProjection(function.SubstringFixed.Text),
                               x => VisitProjection(function.SubstringFixed.Start),
                               x => VisitProjection(function.SubstringFixed.Length));
                break;

            case Function.FunctionType.ToLower:
                _sql.ToLower(x => VisitProjection(function.ToLower.Text));
                break;

            case Function.FunctionType.ToString:
                _sql.Cast(x => VisitProjection(function.ToString.Value), typeof(string), 0, null, null);
                break;

            case Function.FunctionType.ToUpper:
                _sql.ToUpper(x => VisitProjection(function.ToUpper.Text));
                break;

            case Function.FunctionType.Trim:
                _sql.Trim(x => VisitProjection(function.Trim.Text));
                break;

            case Function.FunctionType.TrimEnd:
                _sql.RightTrim(x => VisitProjection(function.TrimEnd.Text));
                break;

            case Function.FunctionType.TrimStart:
                _sql.LeftTrim(x => VisitProjection(function.TrimStart.Text));
                break;

            case Function.FunctionType.Hash:
                _sql.Hash(x => VisitProjection(function.Hash.Value), function.Hash.Type == Function.HashParameters.HashType.Md5 ?
                          SqlWriter.HashAlgorithm.Md5 : SqlWriter.HashAlgorithm.Sha1);
                break;

            case Function.FunctionType.ToHex:
                _sql.ToHex(x => VisitProjection(function.ToHex.Value));
                break;
            }
        }
Beispiel #5
0
 public SqlWriter SubQueryColumn(string name)
 {
     return(SubQueryAlias.Trim().Period.Trim().QuotedName(name));
 }