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(); }
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(); }
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(); }
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); }
void ISqlStatementElement.Render(SqlTextWriter writer, SqlBuildArguments args) { writer.WriteBeginGroup(); writer.Write("EXISTS "); writer.WriteBeginGroup(); this.Statement.Render(writer, args); writer.WriteEndGroup(); writer.WriteEndGroup(); }
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))); }
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); } }
/// <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); } }
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))); }
/// <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); } } }
/// <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); } }
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(); }
void ISqlTable.Compile(SqlBuildArguments args) { args.AssignAlias(this); if (_joins == null) { return; } foreach (var join in _joins) { join.JoinedTable.Compile(args); } }
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(); }
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(); }
/// <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(); }
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(); }
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); } }
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)); } }
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(); } }
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(); }
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(); }
/// <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); } }
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(); }
/// <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)); }