private string Sql_Sub(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
IEnumerable <string> left,
Sqls.TableTypes tableType,
int?commandCount)
{
var commandText = Sub.GetCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
prefix: "_sub",
commandCount: commandCount);
return(left != null
? left.Select(columnBracket =>
Sqls.TableAndColumnBracket(
tableName : TableName,
tableType : tableType,
columnBracket : columnBracket) +
Operator +
"(" + commandText + ")")
.Join(MultiColumnOperator)
: Value == null
? "(" + commandText + ")"
: "(" + commandText + Operator + Value + ")");
}
public void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int pageSize,
Sqls.TableTypes tableType,
int?commandCount)
{
if (Count > 0)
{
commandText.Append(
"order by ",
this
.GroupBy(o => o.ColumnBracket)
.Select(o => o.FirstOrDefault())
.Select(o => o.Sql(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
tableBracket: Sqls.GetTableBracket(o.TableName),
tableType: tableType))
.Join(),
" ");
if (pageSize != 0)
{
commandText.Append(
$"offset {Parameters.Parameter.SqlParameterPrefix}Offset", commandCount.ToString(),
$" rows fetch next {Parameters.Parameter.SqlParameterPrefix}PageSize", commandCount.ToString(),
" rows only ");
}
}
}
public void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount,
bool distinct,
int top)
{
commandText.Append("select ");
Build_DistinctClause(commandText, distinct);
Build_TopClause(
commandText: commandText,
top: top);
if (this.Any())
{
commandText.Append(this
.Select(o => o.CommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
tableBracket: Sqls.GetTableBracket(o.TableName),
commandCount: commandCount))
.Join(), " ");
}
else
{
commandText.Append("* ");
}
RemoveAll(o => o.AdHoc);
}
public string Sql(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
int?commandCount,
string multiClauseOperator = " and ",
bool select = false)
{
if (!select)
{
this.Where(o => o?.ColumnBrackets != null)
.ForEach(where => where.ColumnBrackets =
where.ColumnBrackets.Select(o => o.Split('.').Last()).ToArray());
}
return(this.Where(o => o != null).Any(o => o.Using)
? Clause + this
.Where(o => o != null)
.Where(o => o.Using)
.Select(o => o.Sql(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
tableBracket: Sqls.GetTableBracket(o.TableName),
commandCount: commandCount,
select: select))
.Join(multiClauseOperator) + " "
: string.Empty);
}
private string Functions(string tableBracket)
{
return(Function.ToString().ToLower() +
"(" + Sqls.TableAndColumnBracket(
tableBracket: tableBracket,
columnBracket: ColumnBracket) + ")");
}
private string TableAndColumnBracket(
string tableBracket, string columnBracket, bool select)
{
return(select
? Sqls.TableAndColumnBracket(tableBracket, columnBracket)
: columnBracket);
}
private string CommandText(
ISqlObjectFactory factory,
string tableBracket)
{
var columnBracket = Sqls.TableAndColumnBracket(
tableBracket: tableBracket, columnBracket: ColumnBracket);
switch (Function)
{
case Sqls.Functions.Count:
return("count(*)");
case Sqls.Functions.Sum:
return("sum(" + columnBracket + ")");
case Sqls.Functions.Min:
return("min(" + columnBracket + ")");
case Sqls.Functions.Max:
return("max(" + columnBracket + ")");
case Sqls.Functions.Avg:
return($"avg({factory.Sqls.IsNull}({columnBracket}, 0))");
default:
return(columnBracket);
}
}
public void BuildCommandText(
StringBuilder commandText,
int pageSize,
Sqls.TableTypes tableType,
int?commandCount)
{
if (Count > 0)
{
commandText.Append(
"order by ",
this
.GroupBy(o => o.ColumnBracket)
.Select(o => o.FirstOrDefault())
.Select(o => o.Sql(
tableBracket: Sqls.GetTableBracket(o.TableName),
tableType: tableType))
.Join(),
" ");
if (pageSize != 0)
{
commandText.Append(
"offset @_Offset", commandCount.ToString(),
" rows fetch next @_PageSize", commandCount.ToString(),
" rows only ");
}
}
}
private string CommandText(string tableBracket)
{
var columnBracket = Sqls.TableAndColumnBracket(
tableBracket: tableBracket, columnBracket: ColumnBracket);
switch (Function)
{
case Sqls.Functions.Count:
return("count(*)");
case Sqls.Functions.Sum:
return("sum(" + columnBracket + ")");
case Sqls.Functions.Min:
return("min(" + columnBracket + ")");
case Sqls.Functions.Max:
return("max(" + columnBracket + ")");
case Sqls.Functions.Avg:
return("avg(" + columnBracket + ")");
default:
return(columnBracket);
}
}
private string Functions(Sqls.TableTypes tableType)
{
return(Function.ToString().ToLower() +
"(" + Sqls.TableAndColumnBracket(
tableName: TableName,
tableType: tableType,
columnBracket: ColumnBracket) + ")");
}
private string Sql_General(
string columnBracket, Sqls.TableTypes tableType, int?commandCount)
{
return
(Sqls.TableAndColumnBracket(
tableName: TableName,
tableType: tableType,
columnBracket: columnBracket) +
Operator +
Variable(commandCount: commandCount));
}
public void BuildCommandText(StringBuilder commandText)
{
if (this.Any())
{
commandText.Append("group by ", this.Select(o =>
Sqls.TableAndColumnBracket(
tableBracket: Sqls.GetTableBracket(o.TableName),
columnBracket: o.ColumnBracket))
.Join(), " ");
}
}
public void BuildCommandText(StringBuilder commandText, Sqls.TableTypes tableType)
{
if (this.Any())
{
commandText.Append("group by ", this.Select(o =>
Sqls.TableAndColumnBracket(
tableName: o.TableName,
tableType: tableType,
columnBracket: o.ColumnBracket))
.Join(), " ");
}
}
public string Sql(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
string tableBracket,
Sqls.TableTypes tableType)
{
var orderType = " " + OrderType.ToString().ToLower();
if (!Raw.IsNullOrEmpty())
{
return(Raw);
}
else if (Sub != null)
{
return(Sql_Sub(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
orderType: orderType));
}
else
{
string columnBracket = Sqls.TableAndColumnBracket(
tableBracket: tableType == Sqls.TableTypes.NormalAndHistory
? string.Empty
: tableBracket,
columnBracket: ColumnBracket);
switch (Function)
{
case Sqls.Functions.Count:
case Sqls.Functions.Sum:
case Sqls.Functions.Min:
case Sqls.Functions.Max:
return
(Function.ToString().ToLower() +
"(" +
columnBracket +
")" +
orderType);
case Sqls.Functions.Avg:
return($"avg({factory.Sqls.IsNull}({columnBracket}, 0)) {orderType}");
default:
return(IsNullValue.IsNullOrEmpty()
? columnBracket + orderType
: $"{factory.Sqls.IsNull}({columnBracket}, {IsNullValue}) {orderType}");
}
}
}
public static SqlWhereCollection SqlWhereLike(
this SqlWhereCollection self,
string searchText,
params string[] clauseCollection)
{
var searchTextCollection = Sqls.SearchTextCollection(searchText);
return(self.Add(
name: "SearchText",
value: searchTextCollection,
raw: "(@SearchText#ParamCount#_#CommandCount# = '' or (" +
clauseCollection.Join(" or ") + "))",
_using: searchTextCollection.Any()));
}
public string Sql(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
int?commandCount)
{
return(this.Any(o => o.Using)
? Clause + this
.Where(o => o.Using)
.Select(o => o.Sql(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
tableBracket: Sqls.GetTableBracket(o.TableName),
commandCount: commandCount))
.Join(MultiClauseOperator) + " "
: string.Empty);
}
private string ReplacedSql(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
IEnumerable <string> left,
Sqls.TableTypes tableType,
int?commandCount)
{
return(left != null
? left.Select(columnBracket =>
Sqls.TableAndColumnBracket(
tableName : TableName,
tableType : tableType,
columnBracket : columnBracket) +
ReplacedRaw(commandCount : commandCount))
.Join(MultiColumnOperator)
: Raw.Replace("#CommandCount#", commandCount.ToString()));
}
private string Sql_General(
string columnBracket,
Sqls.TableTypes tableType,
int?commandCount,
IEnumerable <string> valueCollection)
{
return(valueCollection
.Select((o, i) =>
Sqls.TableAndColumnBracket(
tableName: TableName,
tableType: tableType,
columnBracket: columnBracket) +
Operator +
Variable(
commandCount: commandCount,
paramCount: i.ToString() + "_"))
.Join(MultiParamOperator));
}
public string Sql(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
string tableBracket,
Sqls.TableTypes tableType)
{
var orderType = " " + OrderType.ToString().ToLower();
if (Sub != null)
{
return(Sql_Sub(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
orderType: orderType));
}
else
{
string columnBracket = Sqls.TableAndColumnBracket(
tableBracket: tableType == Sqls.TableTypes.NormalAndHistory
? string.Empty
: tableBracket,
columnBracket: ColumnBracket);
switch (Function)
{
case Sqls.Functions.Count:
case Sqls.Functions.Sum:
case Sqls.Functions.Min:
case Sqls.Functions.Max:
case Sqls.Functions.Avg:
return
(Function.ToString().ToLower() +
"(" +
columnBracket +
")" +
orderType);
default:
return(columnBracket + orderType);
}
}
}