Beispiel #1
0
        void ISqlStatementElement.Render(SqlTextWriter writer, SqlBuildArguments args)
        {
            writer.WriteBeginGroup();

            writer.WriteColumnName(this.ColumnName, args.TableName(this.Table));
            writer.WriteSpace();

            if (this.Condition == SubQueryMode.NotInList)
            {
                writer.Write(SqlConstants.NOT);
                writer.WriteSpace();
            }

            writer.Write(SqlConstants.IN);

            writer.WriteSpace();

            args.AssignAlias(this.SubQueryTable);

            var builder        = new SqlSelectBuilder(this.SubQueryTable);
            var subQueryColumn = new SqlColumn(this.SubQueryTable, this.SubQueryColumn);

            builder.Columns.Add(subQueryColumn);

            foreach (var filter in this.SubQueryFilters)
            {
                builder.Filters.Add(filter);
            }

            writer.WriteBeginGroup();
            builder.Render(writer, args);
            writer.WriteEndGroup();

            writer.WriteEndGroup();
        }
Beispiel #2
0
        void ISqlStatementElement.Render(SqlTextWriter writer, SqlBuildArguments args)
        {
            string[] resolvedArguments = new string[0];

            if (this.Arguments != null)
            {
                resolvedArguments = this.Arguments.Select(arg =>
                {
                    if (arg is ISqlTable table)
                    {
                        return(writer.FormatQualifiedIdentifierName(args.TableName(table)));
                    }
                    else if (arg is System.Data.Common.DbParameter dbParam)
                    {
                        return(string.Concat("@", dbParam.ParameterName));
                    }
                    else if (arg is DbParameterValue paramValue)
                    {
                        return(string.Concat("@", args.CreateParameter(paramValue.ParameterName, paramValue.DbType, paramValue.Size)));
                    }
                    else if (arg is string)
                    {
                        return(string.Concat("@", args.CreateParameter(arg.ToString(), System.Data.DbType.String)));
                    }
                    else
                    {
                        return(string.Concat("@", args.CreateParameter(arg, System.Data.DbType.String)));
                    }
                }).ToArray();
            }

            writer.WriteBeginGroup();
            writer.Write(string.Format(this.SqlText, resolvedArguments));
            writer.WriteEndGroup();
        }
Beispiel #3
0
        void ISqlStatementElement.Render(SqlTextWriter writer, SqlBuildArguments args)
        {
            args.AssignAlias(this.SubQueryTable);

            var subquery       = new SqlSelectBuilder(this.SubQueryTable);
            var subQueryColumn = new SqlColumn(this.SubQueryTable, this.SubQueryColumn);

            subQueryColumn.Aggregate = SqlAggregate.Count;
            subQueryColumn.Alias     = "Cnt";
            subquery.Columns.Add(subQueryColumn);

            foreach (var filter in this.SubQueryFilters)
            {
                subquery.Filters.Add(filter);
            }

            writer.WriteBeginGroup();

            writer.WriteBeginGroup();
            subquery.Render(writer, args);
            writer.WriteEndGroup();

            writer.WriteSpace();
            writer.WriteOperator(this.CountOperator);
            writer.WriteSpace();
            writer.WriteParameter(args.CreateParameter(this.CountValue, System.Data.DbType.Int32));

            writer.WriteEndGroup();
        }
Beispiel #4
0
 protected override void RenderInternal(SqlTextWriter writer, SqlBuildArguments args)
 {
     //TODO: Make this impl agnostic
     writer.WriteBeginGroup();
     writer.WriteColumnName(this.ColumnName, args.TableName(this.Table));
     writer.Write((this.IsNull) ? " IS NULL" : " IS NOT NULL");
     writer.WriteEndGroup();
 }
 public void Render(SqlTextWriter writer, SqlBuildArguments args)
 {
     writer.WriteBeginGroup();
     writer.WriteColumnName(this.ColumnName, args.TableName(this.Table));
     writer.WriteOperator(this.Operator);
     writer.Write(this.ParameterName);
     writer.WriteEndGroup();
 }
 void ISqlStatementElement.Render(SqlTextWriter writer, SqlBuildArguments args)
 {
     writer.WriteBeginGroup();
     writer.WriteColumnName(this.LeftColumnName, args.TableName(this.LeftTable));
     writer.WriteOperator(this.Operator);
     writer.WriteColumnName(this.RightColumnName, args.TableName(this.RightTable));
     writer.WriteEndGroup();
 }
 /// <summary>
 /// Renders the entire SQL statement.
 /// </summary>
 /// <param name="writer"></param>
 /// <param name="args"></param>
 protected override void Render(SqlTextWriter writer, SqlBuildArguments args)
 {
     this.RenderValueExpression(writer, args);
     writer.WriteSpace();
     writer.Write(SqlConstants.AS);
     writer.WriteSpace();
     writer.WriteColumnName(this.Alias);
 }
Beispiel #8
0
 void ISqlStatementElement.Render(SqlTextWriter writer, SqlBuildArguments args)
 {
     writer.WriteBeginGroup();
     writer.Write("EXISTS ");
     writer.WriteBeginGroup();
     this.Statement.Render(writer, args);
     writer.WriteEndGroup();
     writer.WriteEndGroup();
 }
Beispiel #9
0
 protected override void RenderInternal(SqlTextWriter writer, SqlBuildArguments args)
 {
     writer.WriteBeginGroup();
     writer.Write(this.CommandText.TrimEnd(new char[] { '\r', '\n', ';', ' ', '\t' }));
     writer.WriteEndGroup();
     writer.WriteSpace();
     writer.Write(SqlConstants.AS);
     writer.WriteSpace();
     writer.Write(SqlDataColumn.Format(args.TableName(this)));
 }
Beispiel #10
0
 public void Render(SqlTextWriter writer, SqlBuildArguments args)
 {
     writer.WriteLiteralValue(this.Value);
     if (this.Alias != null)
     {
         writer.WriteSpace();
         writer.Write(SqlConstants.AS);
         writer.WriteSpace();
         writer.WriteColumnName(this.Alias);
     }
 }
Beispiel #11
0
 /// <summary>
 /// Renders the column name, table expression reference, and alias.
 /// </summary>
 /// <param name="writer"></param>
 /// <param name="args"></param>
 protected override void Render(SqlTextWriter writer, SqlBuildArguments args)
 {
     if (this.Aggregate == SqlAggregate.None)
     {
         writer.WriteColumnName(this.ColumnName, args.TableName(this.Table), this.Alias);
     }
     else
     {
         writer.WriteAggregateColumn(this.ColumnName, args.TableName(this.Table), this.Aggregate, this.Alias);
     }
 }
Beispiel #12
0
 void ISqlStatementElement.Render(SqlTextWriter writer, SqlBuildArguments args)
 {
     this.CompileInternal(args);
     //writer.WriteBeginGroup();
     this.RenderInternal(writer, args);
     //writer.WriteEndGroup();
     //writer.WriteSpace();
     //writer.Write(SqlConstants.AS);
     //writer.WriteSpace();
     //writer.Write(SqlDataColumn.Format(args.TableName(this)));
 }
Beispiel #13
0
 /// <summary>
 /// Compiles the tables used in the query into the given build arguments object.
 /// </summary>
 /// <param name="args"></param>
 protected void CompileInternal(SqlBuildArguments args)
 {
     this.Table.Compile(args);
     if (this.Joins.Count > 0)
     {
         foreach (var join in this.Joins)
         {
             join.JoinedTable.Compile(args);
         }
     }
 }
Beispiel #14
0
        /// <summary>
        /// Renders the raw value to the writer with its alias, if it exists
        /// </summary>
        /// <param name="writer"></param>
        /// <param name="args"></param>
        public void Render(SqlTextWriter writer, SqlBuildArguments args)
        {
            RenderValueExpression(writer, args);

            if (this.Alias != null)
            {
                writer.WriteSpace();
                writer.Write(SqlConstants.AS);
                writer.WriteSpace();
                writer.WriteColumnName(this.Alias);
            }
        }
Beispiel #15
0
 void ISqlStatementElement.Render(SqlTextWriter writer, SqlBuildArguments args)
 {
     writer.WriteBeginGroup();
     this.WriteColumnName(writer, args);
     writer.WriteOperator(SqlOperator.GreaterThanOrEqual);
     writer.WriteParameter(args.CreateParameter(this.GetBeginDate(), System.Data.DbType.DateTime));
     writer.WriteSpace();
     writer.Write(SqlConstants.AND);
     writer.WriteSpace();
     this.WriteColumnName(writer, args);
     writer.WriteOperator(SqlOperator.LessThanOrEqual);
     writer.WriteParameter(args.CreateParameter(this.GetEndDate(), System.Data.DbType.DateTime));
     writer.WriteEndGroup();
 }
Beispiel #16
0
        void ISqlTable.Compile(SqlBuildArguments args)
        {
            args.AssignAlias(this);

            if (_joins == null)
            {
                return;
            }

            foreach (var join in _joins)
            {
                join.JoinedTable.Compile(args);
            }
        }
Beispiel #17
0
        public void RenderFrom(ISqlTable table, SqlBuildArguments args)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table");
            }
            if (args == null)
            {
                throw new ArgumentNullException("args");
            }

            this.WriteFrom();
            table.Render(this, args);
            this.WriteNewLine();
        }
Beispiel #18
0
        public void RenderOffsetLimit(SqlPagingOptions options, SqlBuildArguments args)
        {
            //https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#using-offset-and-fetch-to-limit-the-rows-returned
            //e.g. OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

            if (options.Offset > 0 || options.Limit > 0)
            {
                this.Write($" OFFSET {options.Offset} ROWS");
            }

            if (options.Limit > 0)
            {
                this.Write($" FETCH NEXT {options.Limit} ROWS ONLY");
            }
        }
        /// <summary>
        /// Renders the portion of the SQL statement that will be used as the value.
        /// </summary>
        /// <param name="writer"></param>
        /// <param name="args"></param>
        protected override void RenderValueExpression(SqlTextWriter writer, SqlBuildArguments args)
        {
            var expr = this.Expression;

            expr = expr.Replace("{0}", args.TableName(this.Table));
            if (_referenceTables != null && _referenceTables.Count > 0)
            {
                for (var i = 0; i < _referenceTables.Count; i++)
                {
                    expr = expr.Replace(string.Concat("{", i + 1, "}"), args.TableName(_referenceTables[i]));
                }
            }
            writer.WriteBeginGroup();
            writer.Write(expr);
            writer.WriteEndGroup();
        }
Beispiel #20
0
        /// <summary>
        /// Renders each of the given elements into a single statement. This can be used to execute a batch.
        /// </summary>
        /// <param name="elements"></param>
        /// <returns></returns>
        public static SqlStatementBatch RenderBatch(this IEnumerable <ISqlStatementElement> elements)
        {
            var writer = new SqlTextWriter();
            var args   = new SqlBuildArguments();
            int count  = 0;

            foreach (var element in elements)
            {
                count++;
                element.Render(writer, args);
                writer.WriteEndStatement();
                writer.WriteLine();
            }

            return(new SqlStatementBatch(count, writer.ToString(), args.Parameters));
        }
        void ISqlStatementElement.Render(SqlTextWriter writer, SqlBuildArguments args)
        {
            writer.WriteBeginGroup();
            writer.WriteColumnName(this.ColumnName, args.TableName(this.Table));
            writer.WriteOperator(this.Operator);

            if (this.EncodeValueAsLiteral)
            {
                writer.WriteLiteralValue(this.Value);
            }
            else
            {
                writer.WriteParameter(args.CreateParameter(this.Value, this.DataType));
            }

            writer.WriteEndGroup();
        }
Beispiel #22
0
        protected override void RenderInternal(SqlTextWriter writer, SqlBuildArguments args)
        {
            string s = this.Value;

            if (s == null)
            {
                s = string.Empty;
            }

            writer.WriteBeginGroup();
            writer.WriteColumnName(this.ColumnName, args.TableName(this.Table));
            writer.WriteSpace();
            writer.Write(SqlConstants.LIKE);
            writer.WriteSpace();
            writer.WriteParameter(args.CreateParameter(SqlStringMatchFilter.DecorateValue(this.Value, this.Operator), this.DataType));
            writer.WriteEndGroup();
        }
Beispiel #23
0
        public virtual void RenderAll <T>(IEnumerable <T> items, SqlBuildArguments args, string seperator, Action <T, SqlTextWriter, SqlBuildArguments> renderAction) where T : ISqlStatementElement
        {
            bool first = true;

            foreach (var item in items)
            {
                if (first)
                {
                    first = false;
                }
                else
                {
                    this.Write(seperator);
                }
                renderAction(item, this, args);
            }
        }
Beispiel #24
0
        public void RenderWhere(IEnumerable <ISqlFilter> filters, SqlLogic logic, SqlBuildArguments args)
        {
            if (filters == null)
            {
                throw new ArgumentNullException("filters");
            }
            if (args == null)
            {
                throw new ArgumentNullException("args");
            }

            var items = filters.ToArray();

            if (items.Length > 0)
            {
                this.WriteWhere();
                this.RenderAll(items, args, string.Concat(SqlConstants.SPACE, ConvertSqlLogicToString(logic), SqlConstants.SPACE));
            }
        }
Beispiel #25
0
        public void Render(SqlTextWriter writer, SqlBuildArguments args)
        {
            if (_innerList.Count <= 0)
            {
                return;
            }

            if (this.Count > 1)
            {
                writer.WriteBeginGroup();
            }

            writer.RenderAll(this, args, string.Concat(" ", ((this.Logic == SqlLogic.And) ? SqlConstants.AND : SqlConstants.OR).ToString().ToUpper(), " "));

            if (this.Count > 1)
            {
                writer.WriteEndGroup();
            }
        }
Beispiel #26
0
        public void RenderGroupBy(IEnumerable <ISqlColumn> columns, SqlBuildArguments args)
        {
            if (columns == null)
            {
                throw new ArgumentNullException("columns");
            }
            if (args == null)
            {
                throw new ArgumentNullException("args");
            }

            var items = columns.ToArray();

            if (items.Length > 0)
            {
                this.WriteGroupBy();
                this.RenderAll(items, args, COLUMN_SEPERATOR, (a, b, c) => { a.RenderValueExpression(b, c); });
            }
            this.WriteNewLine();
        }
Beispiel #27
0
        public void RenderOrderBy(IEnumerable <SqlOrderColumn> columns, SqlBuildArguments args)
        {
            if (columns == null)
            {
                throw new ArgumentNullException("columns");
            }
            if (args == null)
            {
                throw new ArgumentNullException("args");
            }

            var items = columns.ToArray();

            if (items.Length > 0)
            {
                this.WriteOrderBy();
                this.RenderAll(items, args, COLUMN_SEPERATOR);
            }
            this.WriteNewLine();
        }
Beispiel #28
0
        /// <summary>
        /// Renders the query to the given text writer.
        /// </summary>
        /// <param name="writer"></param>
        /// <param name="args"></param>
        protected void RenderInternal(SqlTextWriter writer, SqlBuildArguments args)
        {
            writer.RenderSelect(this.Columns, args, this.SelectDistinct);
            writer.RenderFrom(this.Table, args);
            if (this.Joins.Count > 0)
            {
                writer.RenderJoins(this.Joins, args);
            }
            writer.RenderWhere(this.Filters, SqlLogic.And, args);

            if (this.Columns.Count(x => x.IsAggregate) > 0)
            {
                writer.RenderGroupBy(this.Columns.Where(x => !x.IsAggregate), args);
            }

            writer.RenderOrderBy(this.OrderBy, args);
            if (this.PagingOptions.HasValue)
            {
                writer.RenderOffsetLimit(this.PagingOptions.Value, args);
            }
        }
Beispiel #29
0
        public void RenderSelect(IEnumerable <ISqlColumn> columns, SqlBuildArguments args, bool distinct)
        {
            if (columns == null)
            {
                throw new ArgumentNullException("columns");
            }
            if (args == null)
            {
                throw new ArgumentNullException("args");
            }

            var items = columns.ToArray();

            this.WriteSelect();
            this.WriteSpace();

            if (distinct)
            {
                this.Write(SqlConstants.DISTINCT);
                this.WriteSpace();
            }

            if (items.Length > 0)
            {
                if (this.Format)
                {
                    this.RenderAll(items, args, string.Concat(COLUMN_SEPERATOR, "\r\n"));
                }
                else
                {
                    this.RenderAll(items, args, COLUMN_SEPERATOR);
                }
            }
            else
            {
                this.Write("*");
            }
            this.WriteNewLine();
        }
Beispiel #30
0
        /// <summary>
        /// Renders the query.
        /// </summary>
        /// <param name="supressEndStatement">True if you want to supress statement terminating characters (semicolon)</param>
        /// <returns></returns>
        public SqlStatement Render(bool supressEndStatement)
        {
            var writer = new SqlTextWriter()
            {
                Format = this.GenerateFormattedSql
            };
            var args = new SqlBuildArguments();

            this.CompileInternal(args);

            if (this.Prefix != null)
            {
                writer.Write(this.Prefix);
                if (!supressEndStatement)
                {
                    writer.WriteEndStatement();
                }
            }

            this.RenderInternal(writer, args);

            if (!supressEndStatement)
            {
                writer.WriteEndStatement();
            }

            if (this.Suffix != null)
            {
                writer.Write(this.Suffix);
                if (!supressEndStatement)
                {
                    writer.WriteEndStatement();
                }
            }

            return(new SqlStatement(writer.ToString(), args.Parameters));
        }