public void BuildCommandText(
StringBuilder commandText,
int pageSize,
Sqls.TableTypes tableType,
Sqls.UnionTypes unionType,
int?commandCount)
{
if (Count > 0)
{
commandText.Append(
"order by ",
this
.GroupBy(o => o.ColumnBracket)
.Select(o => o.FirstOrDefault().Sql(
tableType: tableType,
unionType: unionType))
.Join(),
" ");
if (pageSize != 0)
{
commandText.Append(
"offset @_Offset", commandCount.ToString(),
" rows fetch next @_PageSize", commandCount.ToString(),
" rows only ");
}
}
}
public string Sql(Sqls.TableTypes tableType, Sqls.UnionTypes unionType)
{
var columnBracket = Sqls.TableAndColumnBracket(
tableName: unionType == Sqls.UnionTypes.None
? TableName
: null,
tableType: tableType,
columnBracket: ColumnBracket);
var orderType = " " + OrderType.ToString().ToLower();
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);
}
}
private void BuildCommandText(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
StringBuilder commandText,
Sqls.TableTypes tableType,
Sqls.UnionTypes unionType,
bool orderBy,
int?commandCount)
{
if (!Using)
{
return;
}
AddUnion(commandText, unionType);
SqlColumnCollection?.BuildCommandText(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
distinct: Distinct,
top: Top);
var from = From(tableType, As);
commandText.Append(from);
SqlJoinCollection?.BuildCommandText(commandText: commandText);
SqlWhereCollection?.BuildCommandText(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
select: true);
SqlGroupByCollection?.BuildCommandText(
commandText: commandText);
SqlHavingCollection?.BuildCommandText(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount);
if (orderBy)
{
SqlOrderByCollection?.BuildCommandText(
commandText: commandText,
pageSize: PageSize,
tableType: TableType,
commandCount: commandCount);
}
commandText.Append(
factory.SqlCommandText.CreateLimitClause(limit: Top));
AddTermination(commandText);
AddParams_Where(factory, sqlCommand, commandCount);
AddParams_Having(factory, sqlCommand, commandCount);
AddParams_Paging(factory, sqlCommand, commandCount);
AddParams_Param(factory, sqlCommand, commandCount);
}
private void BuildHistoryWithoutFlag(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount,
Sqls.UnionTypes unionType)
{
BuildCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
tableType: Sqls.TableTypes.History,
unionType: unionType,
orderBy: true,
commandCount: commandCount);
}
protected void AddUnion(StringBuilder commandText, Sqls.UnionTypes unionType)
{
if (unionType != Sqls.UnionTypes.None && commandText.ToString().EndsWith(";"))
{
commandText.Length -= 1;
}
switch (unionType)
{
case Sqls.UnionTypes.Union:
commandText.Append(" union ");
break;
case Sqls.UnionTypes.UnionAll:
commandText.Append(" union all ");
break;
}
}
private void BuildDeleted(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount,
Sqls.UnionTypes unionType)
{
AddTableTypeColumn("Deleted");
BuildCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
tableType: Sqls.TableTypes.Deleted,
unionType: unionType,
orderBy: true,
commandCount: commandCount);
}
private void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
Sqls.TableTypes tableType,
Sqls.UnionTypes unionType,
bool orderBy,
bool countRecord,
int?commandCount)
{
if (!Using)
{
return;
}
if (!DataTableName.IsNullOrEmpty())
{
sqlContainer.DataTableNames.Add(DataTableName);
}
AddUnion(commandText, unionType);
SqlColumnCollection?.BuildCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
distinct: Distinct,
top: Top);
var from = From(tableType, As);
commandText.Append(from);
SqlJoinCollection?.BuildCommandText(commandText: commandText);
SqlWhereCollection?.BuildCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
select: true);
SqlGroupByCollection?.BuildCommandText(
commandText: commandText);
SqlHavingCollection?.BuildCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount);
if (orderBy)
{
SqlOrderByCollection?.BuildCommandText(
commandText: commandText,
pageSize: PageSize,
tableType: TableType,
commandCount: commandCount);
}
AddTermination(commandText);
if (countRecord)
{
sqlContainer.DataTableNames.Add("Count");
commandText.Append("select count(*) from ( select 1 as [c] ");
commandText.Append(from);
SqlJoinCollection?.BuildCommandText(commandText: commandText);
SqlWhereCollection?.BuildCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
select: true);
SqlGroupByCollection?.BuildCommandText(
commandText: commandText);
SqlHavingCollection?.BuildCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount);
commandText.Append(") as [table_count]");
AddTermination(commandText);
}
AddParams_Where(sqlCommand, commandCount);
AddParams_Having(sqlCommand, commandCount);
AddParams_Paging(sqlCommand, commandCount);
AddParams_Param(sqlCommand, commandCount);
}
