public GroupConcat(bool distinct, string tableName, string columnName, string separator, OrderByList orderBy)
{
this.Distinct = distinct;
this.Value = new ValueWrapper(tableName, columnName);
this.Separator = separator;
this.OrderBy = orderBy;
}
public GroupConcat(bool distinct, ValueWrapper value, string separator, OrderByList orderBy)
{
this.Distinct = distinct;
this.Value = value;
this.Separator = separator;
this.OrderBy = orderBy;
}
public void OrderByDESCSimple2()
{
OrderByList o = new OrderByList(global::SqlBuilder.Format.MsSQL);
o.Descending("a", "b", "c");
string result = o.GetSql();
string sql = "[a] DESC, [b] DESC, [c] DESC";
Assert.AreEqual(sql, result);
}
public void OrderByASCSimple1()
{
OrderByList o = new OrderByList(global::SqlBuilder.Format.MsSQL);
o.Ascending("a");
string result = o.GetSql();
string sql = "[a] ASC";
Assert.AreEqual(sql, result);
}
public Select(IFormatter parameters, string tableAlias = "")
{
Query = SqlQuery.Select;
Formatter = parameters;
TableAlias = tableAlias;
Columns = new ColumnsListAggregation(Formatter);
Join = new JoinList(Formatter);
Where = new WhereList(Formatter);
OrderBy = new OrderByList(Formatter);
GroupBy = new GroupByList(Formatter, Columns);
}
public void OrderByASCAndDESC2()
{
OrderByList o = new OrderByList(global::SqlBuilder.Format.MsSQL);
o.Ascending("a", "b", "c");
o.Descending("d", "e", "f");
string result = o.GetSql();
string sql = "[a] ASC, [b] ASC, [c] ASC, [d] DESC, [e] DESC, [f] DESC";
Assert.AreEqual(sql, result);
}
public void OrderBy(string tableName, string fieldName, bool desc = false)
{
var order = Adapter.Field(tableName, fieldName);
if (desc)
{
order += " DESC";
}
OrderByList.Add(order);
}
public void OrderByRaw2()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
OrderByList o = new OrderByList(SqlBuilder.DefaultFormatter);
o.Raw("[c] ASC").Raw("[d] DESC");
string result = o.GetSql("t");
string sql = "[c] ASC, [d] DESC";
Assert.AreEqual(sql, result);
}
public void OrderByASCSimple1()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
OrderByList o = new OrderByList(SqlBuilder.DefaultFormatter);
o.Ascending("a");
string result = o.GetSql();
string sql = "[a] ASC";
Assert.AreEqual(sql, result);
}
public void OrderByDESCSimple2()
{
NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
OrderByList o = new OrderByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter);
o.Descending("a", "b", "c");
string result = o.GetSql();
string sql = "[a] DESC, [b] DESC, [c] DESC";
Assert.Equal(sql, result);
}
public Select(Format parameters, string tableName, string tableAlias = "")
{
this.Query = Enums.SqlQuery.Select;
this.Format = parameters;
this.TableName = tableName;
this.TableAlias = tableAlias;
this.Columns = new ColumnsListAggregation(this.Format);
this.Join = new JoinList(this.Format);
this.Where = new WhereList(this.Format);
this.OrderBy = new OrderByList(this.Format);
this.GroupBy = new GroupByList(this.Format, this.Columns);
}
public void OrderByRaw2()
{
NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
OrderByList o = new OrderByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter);
o.Raw("[c] ASC").Raw("[d] DESC");
string result = o.GetSql("t");
string sql = "[c] ASC, [d] DESC";
Assert.Equal(sql, result);
}
public void OrderByDESCSimple2()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
OrderByList o = new OrderByList(SqlBuilder.DefaultFormatter);
o.Descending("a", "b", "c");
string result = o.GetSql();
string sql = "[a] DESC, [b] DESC, [c] DESC";
Assert.AreEqual(sql, result);
}
public void OrderByASCAndDESC2()
{
NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
OrderByList o = new OrderByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter);
o.Ascending("a", "b", "c");
o.Descending("d", "e", "f");
string result = o.GetSql();
string sql = "[a] ASC, [b] ASC, [c] ASC, [d] DESC, [e] DESC, [f] DESC";
Assert.Equal(sql, result);
}
public void OrderByASCAndDESC2()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
OrderByList o = new OrderByList(SqlBuilder.DefaultFormatter);
o.Ascending("a", "b", "c");
o.Descending("d", "e", "f");
string result = o.GetSql();
string sql = "[a] ASC, [b] ASC, [c] ASC, [d] DESC, [e] DESC, [f] DESC";
Assert.AreEqual(sql, result);
}
public void OrderByASCAndDESCAlias()
{
OrderByList o = new OrderByList(global::SqlBuilder.Format.MsSQL);
o.Ascending("a");
o.Descending("b");
o.Ascending("c");
o.Descending("d");
string result = o.GetSql("t");
string sql = "[t].[a] ASC, [t].[b] DESC, [t].[c] ASC, [t].[d] DESC";
Assert.AreEqual(sql, result);
}
public void OrderByASCAndDESCAlias()
{
NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
OrderByList o = new OrderByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter);
o.Ascending("a");
o.Descending("b");
o.Ascending("c");
o.Descending("d");
string result = o.GetSql("t");
string sql = "[t].[a] ASC, [t].[b] DESC, [t].[c] ASC, [t].[d] DESC";
Assert.Equal(sql, result);
}
public void OrderByASCAndDESCAlias()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
OrderByList o = new OrderByList(SqlBuilder.DefaultFormatter);
o.Ascending("a");
o.Descending("b");
o.Ascending("c");
o.Descending("d");
string result = o.GetSql("t");
string sql = "[t].[a] ASC, [t].[b] DESC, [t].[c] ASC, [t].[d] DESC";
Assert.AreEqual(sql, result);
}
public string BuildSQL(string condition, string having, params string[] sorts)
{
string sqlstr = SelectClause + " " + FromClause;
if (condition != null && condition.Trim().Length > 0)
{
if (WhereClause == null || WhereClause.Trim().Length <= 0)
{
sqlstr += " where " + condition;
}
else
{
sqlstr += " where ( " + condition + " ) and ( " + WhereClause + " )";
}
}
else if (WhereClause != null && WhereClause.Trim().Length > 0)
{
sqlstr += " where " + WhereClause;
}
if (GroupByList != null && GroupByList.Trim().Length > 0)
{
sqlstr += " group by " + GroupByList;
if (having != null && having.Trim().Length > 0)
{ //用户指定了条件并且有分组,则用户指定条件合并到HAVING子句中
if (HavingClause == null || HavingClause.Trim().Length <= 0)
{
sqlstr += " having " + having;
}
else
{
sqlstr += " having ( " + having + " ) and ( " + HavingClause + " )";
}
}
else if (HavingClause != null && HavingClause.Trim().Length > 0)
{
sqlstr += " having " + HavingClause;
}
}
if (sorts.Length > 0)
{
sqlstr += " " + string.Join(",", sorts);
}
else if (OrderByList != null && OrderByList.Trim().Length > 0)
{
sqlstr += " order by " + OrderByList;
}
return(sqlstr);
}
public void OrderByTableAlias2()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
OrderByList o = new OrderByList(SqlBuilder.DefaultFormatter);
o.Raw("[a] ASC");
o.Ascending("as1");
o.Descending("ds2");
o.SetTableAlias("ttt");
o.Ascending("at1");
o.SetTableAlias("ddd");
o.Descending("at2");
o.SetTableAlias();
o.Descending("b");
string result = o.GetSql();
string sql = "[a] ASC, [as1] ASC, [ds2] DESC, [ttt].[at1] ASC, [ddd].[at2] DESC, [b] DESC";
Assert.AreEqual(sql, result);
}
public void OrderByTableAlias2()
{
NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
OrderByList o = new OrderByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter);
o.Raw("[a] ASC");
o.Ascending("as1");
o.Descending("ds2");
o.SetTableAlias("ttt");
o.Ascending("at1");
o.SetTableAlias("ddd");
o.Descending("at2");
o.SetTableAlias();
o.Descending("b");
string result = o.GetSql();
string sql = "[a] ASC, [as1] ASC, [ds2] DESC, [ttt].[at1] ASC, [ddd].[at2] DESC, [b] DESC";
Assert.Equal(sql, result);
}
public GroupConcat(ValueWrapper value, OrderByList orderBy)
{
this.Value = value;
this.OrderBy = orderBy;
}
public GroupConcat(ValueWrapper value, string separator, OrderByList orderBy)
{
this.Value = value;
this.Separator = separator;
this.OrderBy = orderBy;
}
public static GroupConcat GroupConcat(ValueWrapper value, OrderByList orderBy)
{
return new GroupConcat(value, orderBy);
}
public static GroupConcat GroupConcat(ValueWrapper value, string separator, OrderByList orderBy)
{
return new GroupConcat(value, separator, orderBy);
}
public static GroupConcat GroupConcat(string tableName, string columnName, OrderByList orderBy)
{
return new GroupConcat(tableName, columnName, orderBy);
}
public static GroupConcat GroupConcat(bool distinct, ValueWrapper value, OrderByList orderBy)
{
return new GroupConcat(distinct, value, orderBy);
}
public static GroupConcat GroupConcat(bool distinct, string tableName, string columnName, OrderByList orderBy)
{
return new GroupConcat(distinct, tableName, columnName, orderBy);
}
public Query OrderBy(string ColumnName, SortDirection SortDirection, bool ColumnNameIsLiteral)
{
if (_ListOrderBy == null) _ListOrderBy = new OrderByList();
_ListOrderBy.Add(new OrderBy(ColumnName, SortDirection, ColumnNameIsLiteral));
return this;
}
public GroupConcat(bool distinct, ValueWrapper value, OrderByList orderBy)
{
this.Distinct = distinct;
this.Value = value;
this.OrderBy = orderBy;
}
public override void WriteToStream(IndentStream stream)
{
stream.Write("SELECT");
if (TopCount != null)
{
stream.Write(" ");
TopCount.WriteToStream(stream);
}
stream.Write(" ");
foreach (var column in Columns.Select((value, idx) => new { value, idx }))
{
if (column.idx != 0)
{
stream.Write(", ");
}
column.value.WriteToStream(stream);
}
if (IntoTable != null)
{
stream.WriteLine();
stream.Write("INTO ");
IntoTable.WriteToStream(stream);
}
if (FromSourceList.Count > 0)
{
stream.WriteLine();
stream.Write("FROM ");
stream.Indent++;
for (int i = 0; i < FromSourceList.Count; i++)
{
if (i != 0)
{
stream.WriteLine(", ");
}
var fromSource = FromSourceList[i];
fromSource.WriteToStream(stream);
}
stream.Indent--;
}
if (ForXmlExpr != null)
{
stream.WriteLine();
ForXmlExpr.WriteToStream(stream);
}
if (PivotExpr != null)
{
stream.WriteLine();
PivotExpr.WriteToStream(stream);
}
if (WhereExpr != null)
{
stream.WriteLine();
stream.Write("WHERE ");
WhereExpr.WriteToStream(stream);
}
if (GroupByList.Count > 0)
{
stream.WriteLine();
stream.Write("GROUP BY ");
GroupByList.WriteToStreamWithComma(stream);
}
if (Having != null)
{
stream.WriteLine();
Having.WriteToStream(stream);
}
if (OrderByList.Count > 0)
{
stream.WriteLine();
stream.Write("ORDER BY ");
OrderByList.WriteToStreamWithComma(stream);
}
if (OptionExpr != null)
{
stream.WriteLine();
OptionExpr.WriteToStream(stream);
}
if (UnionSelectList != null && UnionSelectList.Count > 0)
{
stream.WriteLine();
UnionSelectList.WriteToStream(stream);
}
if (IsSemicolon)
{
stream.Write(" ;");
}
}
public Query OrderBy(string columnName, SortDirection sortDirection)
{
if (_ListOrderBy == null) _ListOrderBy = new OrderByList();
_ListOrderBy.Add(new OrderBy(columnName, sortDirection));
return this;
}
public Query OrderBy(string TableName, string ColumnName, SortDirection SortDirection)
{
if (_ListOrderBy == null) _ListOrderBy = new OrderByList();
_ListOrderBy.Add(new OrderBy(TableName, ColumnName, SortDirection));
return this;
}
internal OrderByList ProcessOrderBy()
{
var list = new OrderByList();
if (!string.IsNullOrWhiteSpace(orderByClause))
{
var orderByItems = orderByClause.Split(new [] { "," }, StringSplitOptions.RemoveEmptyEntries);
foreach (var item in orderByItems)
{
var parts = item.Split(new [] { " " }, StringSplitOptions.RemoveEmptyEntries);
if (parts.Length == 1)
list.Add(new OrderByNode(parts[0], Direction.Asc));
if (parts.Length == 2)
{
var direction = string.Equals(parts[1], "desc", StringComparison.OrdinalIgnoreCase) ? Direction.Desc : Direction.Asc;
list.Add(new OrderByNode(parts[0], direction));
}
}
}
return list;
}
public GroupConcat(string tableName, string columnName, OrderByList orderBy)
{
this.Value = new ValueWrapper(tableName, columnName);
this.OrderBy = orderBy;
}
private Query OrderBy(OrderBy OrderBy)
{
if (_ListOrderBy == null) _ListOrderBy = new OrderByList();
_ListOrderBy.Add(OrderBy);
return this;
}