public string GetSql(string tableAlias = "")
{
StringBuilder sb = new StringBuilder();
bool sep = false;
foreach (IOrderBy expression in this._expressions)
{
if (sep)
{
sb.Append(", ");
}
else
{
sep = true;
}
if (expression.IsRaw)
{
sb.Append(expression.Column);
}
else
{
sb.Append(SuperSql.FormatColumn(expression.Column, this.Parameters, string.IsNullOrEmpty(expression.TableAlias) ? tableAlias : expression.TableAlias) + " " + expression.GetDirection());
}
}
return(sb.ToString());
}
public IColumnsListAggregation SubQuery(IStatementSelect select, string alias = "")
{
if (!string.IsNullOrEmpty(alias))
{
alias = " as " + SuperSql.FormatColumnAlias(alias);
}
this.Raw('(' + select.GetSql(false) + ')' + alias);
return(this);
}
public void FormatAliasColumn()
{
var alias = "text for alias";
SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
var result1 = SuperSql.FormatColumnAlias(alias);
Assert.Equal('\'' + alias + '\'', result1);
// SuperSql.DefaultFormatter = FormatterLibrary.MySql;
// var result2 = SuperSql.FormatColumnAlias(alias);
// Assert.Equal('\"' + alias + '\"', result2);
}
public void FormatAliasTable()
{
var alias = "tbl";
SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
var result1 = SuperSql.FormatTableAlias(alias);
Assert.Equal('[' + alias + ']', result1);
// SuperSql.DefaultFormatter = FormatterLibrary.MySql;
// var result2 = SuperSql.FormatTableAlias(alias);
// Assert.Equal('`' + alias + '`', result2);
}
public void FormatParameter()
{
var name = "login";
SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
var result1 = SuperSql.FormatParameter(name);
Assert.Equal('@' + name, result1);
// SuperSql.DefaultFormatter = FormatterLibrary.MySql;
// var result2 = SuperSql.FormatParameter(name);
// Assert.Equal('?' + name, result2);
}
public string GetSql(string tableAlias = "")
{
StringBuilder sb = new StringBuilder();
foreach (ISet value in this._expressions)
{
if (sb.Length > 0)
{
sb.Append(", ");
}
sb.Append(SuperSql.FormatColumn(value.Name, this.Parameters, tableAlias));
sb.Append('=');
sb.Append(value.Value);
}
return(sb.ToString());
}
public string GetSql(string sourceTable)
{
StringBuilder sb = new StringBuilder();
sb.Append(this.GetJoinType());
sb.Append(' ');
sb.Append(SuperSql.FormatTable(this.Table, this.Parameters));
if (!string.IsNullOrEmpty(this.TableAlias))
{
sb.Append(" as ");
sb.Append(SuperSql.FormatTableAlias(this.TableAlias, this.Parameters));
}
sb.Append(" ON ");
StringBuilder ex = new StringBuilder();
foreach (IJoinItem item in this._expressions)
{
if (ex.Length > 0)
{
ex.Append(" AND ");
}
if (item.IsRaw)
{
ex.Append(item.Value);
}
else
{
ex.Append(SuperSql.FormatTableAlias(sourceTable, this.Parameters));
ex.Append('.');
ex.Append(SuperSql.FormatColumn(item.Column, this.Parameters));
ex.Append('=');
if (string.IsNullOrEmpty(this.TableAlias))
{
ex.Append(SuperSql.FormatTable(this.Table, this.Parameters));
}
else
{
ex.Append(SuperSql.FormatTableAlias(this.TableAlias, this.Parameters));
}
ex.Append('.');
ex.Append(SuperSql.FormatColumn(item.Value, this.Parameters));
}
}
sb.Append(ex);
return(sb.ToString());
}
public void FormatTable()
{
var table = "tab_users";
SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
var result1 = SuperSql.FormatTable(table);
Assert.Equal('[' + table + ']', result1);
// SuperSql.DefaultFormatter = FormatterLibrary.MySql;
// var result2 = SuperSql.FormatTable(table);
// Assert.Equal('`' + table + '`', result2);
//
// SuperSql.DefaultFormatter.EscapeEnabled = false;
// var result3 = SuperSql.FormatTable(table);
// Assert.Equal(table, result3);
}
public string GetSql(string tableAlias = "")
{
var sb = new StringBuilder();
bool logic = false, lastparenthesis = false;
foreach (var expression in _expressions)
{
if (logic && !lastparenthesis && expression.Parenthesis != Parenthesis.CloseParenthesis)
{
sb.Append(' ');
sb.Append(GetSqlCurrentLogic(expression.Logic));
sb.Append(' ');
}
else
{
logic = true;
}
if (expression.Parenthesis == Parenthesis.OpenParenthesis)
{
sb.Append('(');
lastparenthesis = true;
}
else if (expression.Parenthesis == Parenthesis.CloseParenthesis)
{
sb.Append(')');
lastparenthesis = false;
}
else
{
if (expression.IsColumn)
{
sb.Append(SuperSql.FormatColumn(expression.Column, string.IsNullOrEmpty(expression.TableAlias) ? tableAlias : expression.TableAlias));
}
sb.Append(expression.Value);
lastparenthesis = false;
}
}
return(sb.ToString());
}
public string GetSql(string tableAlias = "")
{
if (this.Count == 0)
{
return(string.IsNullOrEmpty(tableAlias)
? "*"
: SuperSql.FormatTableAlias(tableAlias, this.Parameters) + ".*");
}
string table;
StringBuilder sb = new StringBuilder();
foreach (IColumn column in this.Expressions)
{
table = string.IsNullOrEmpty(column.TableAlias) ? tableAlias : column.TableAlias;
if (sb.Length > 0)
{
sb.Append(", ");
}
if (!string.IsNullOrEmpty(table) && !column.IsRaw && !column.IsAggregation)
{
sb.Append(SuperSql.FormatTableAlias(table, this.Parameters) + '.');
}
if (column.IsRaw)
{
sb.Append(column.Value);
}
else
{
sb.Append(column.Prefix);
sb.Append(SuperSql.FormatColumn(column.Value, this.Parameters));
sb.Append(column.Postfix);
}
if (!string.IsNullOrEmpty(column.Alias))
{
sb.Append(" as ");
sb.Append(this.Parameters.AliasEscape);
sb.Append(column.Alias);
sb.Append(this.Parameters.AliasEscape);
}
}
return(sb.ToString());
}
public void FormatColumns()
{
var column = "date";
SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
var result1 = SuperSql.FormatColumn(column);
Assert.Equal('[' + column + ']', result1);
SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
var result4 = SuperSql.FormatColumn(column, "t");
Assert.Equal("[t].[" + column + ']', result4);
// SuperSql.DefaultFormatter = FormatterLibrary.MySql;
// var result2 = SuperSql.FormatColumn(column);
// Assert.Equal('`' + column + '`', result2);
//
// SuperSql.DefaultFormatter.EscapeEnabled = false;
// var result3 = SuperSql.FormatColumn(column);
// Assert.Equal(column, result3);
}
