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));
}