public string Sql(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
string tableBracket,
int?commandCount,
bool select)
{
if (Using)
{
var left = Left(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandCount: commandCount);
if (!Raw.IsNullOrEmpty())
{
return(Sql_Raw(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
left: left,
tableBracket: tableBracket,
commandCount: commandCount,
select: select));
}
else if (Sub != null)
{
return(Sql_Sub(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
left: left,
tableBracket: tableBracket,
commandCount: commandCount,
select: select));
}
else if (Or != null)
{
return(Sql_Or(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
tableBracket: tableBracket,
commandCount: commandCount,
select: select));
}
else
{
return(Sql_General(
left: left,
tableBracket: tableBracket,
commandCount: commandCount,
select: select));
}
}
else
{
return(string.Empty);
}
}
private string Sql_Sub(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
string orderType)
{
return("(" + Sub.GetCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand) + ")"
+ orderType);
}
private void Build_RestoreStatement(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount)
{
commandText.Append(CommandText
.Params(SqlWhereCollection.Sql(
sqlContainer, sqlCommand, TableType, commandCount)));
}
public string CommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
Sqls.TableTypes tableType,
int?commandCount)
{
return(Sub != null
? Sql_Sub(sqlContainer, sqlCommand, commandCount)
: CommandText(tableType) + AsBracket(tableType));
}
public void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
Sqls.TableTypes tableType,
int?commandCount,
bool select = false)
{
commandText.Append(Sql(sqlContainer, sqlCommand, tableType, commandCount, select));
}
private void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
Sqls.TableTypes tableType,
Sqls.UnionTypes unionType,
bool orderBy,
int?commandCount)
{
if (!Using)
{
return;
}
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(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
pageSize: PageSize,
tableType: TableType,
commandCount: commandCount);
}
AddTermination(commandText);
AddParams_Where(sqlCommand, commandCount);
AddParams_Having(sqlCommand, commandCount);
AddParams_Paging(sqlCommand, commandCount);
AddParams_Param(sqlCommand, commandCount);
}
public void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount)
{
commandText.Append(Sql(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandCount: commandCount));
}
private string ReplacedRaw(SqlContainer sqlContainer, int?commandCount)
{
switch (Raw)
{
case "@@identity":
return(Operator + $"{Parameters.Parameter.SqlParameterPrefix}I");
default:
return(Operator + Raw.Replace("#CommandCount#", commandCount.ToString()));
}
}
private string Sql_Sub(SqlContainer sqlContainer, SqlCommand sqlCommand, int?commandCount)
{
return("(" + Sub.GetCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
prefix: "_sub",
commandCount: commandCount) + ")" +
(As != null
? " as [" + As + "]"
: string.Empty));
}
private string Sql_Sub(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
string orderType)
{
return("(" + Sub.GetCommandText(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand) + ")"
+ orderType);
}
private void Build_DeleteStatement(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount)
{
commandText.Append(CommandText
.Params(SqlWhereCollection.Sql(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandCount: commandCount)));
}
public void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount,
bool select = false)
{
commandText.Append(Sql(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandCount: commandCount,
select: select));
}
public virtual void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount = null)
{
if (!Using)
{
return;
}
commandText.Append(CommandText);
AddParams_Param(sqlCommand, commandCount);
}
public void BuildCommandText(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount)
{
commandText.Append(Sql(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandCount: commandCount));
}
private string Sql_Or(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
Sqls.TableTypes tableType,
int?commandCount)
{
var commandText = new StringBuilder();
Or.Clause = string.Empty;
Or.MultiClauseOperator = " or ";
Or.BuildCommandText(sqlContainer, sqlCommand, commandText, tableType, commandCount);
return("(" + commandText + ")");
}
public string CommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
string tableBracket,
int?commandCount)
{
return(Sub != null
? Sql_Sub(
sqlContainer : sqlContainer,
sqlCommand : sqlCommand,
commandCount : commandCount)
: CommandText(tableBracket: tableBracket) + AsBracket());
}
public string Sql(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
Sqls.TableTypes tableType,
int?commandCount)
{
return(this.Any(o => o.Using)
? Clause + this
.Where(o => o.Using)
.Select(o => o.Sql(sqlContainer, sqlCommand, tableType, commandCount))
.Join(MultiClauseOperator) + " "
: string.Empty);
}
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 string Sql(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
Sqls.TableTypes tableType,
int?commandCount)
{
if (Using)
{
var left = Left(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandCount: commandCount);
if (!Raw.IsNullOrEmpty())
{
return(Sql_Raw(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
left: left,
tableType: tableType,
commandCount: commandCount));
}
else if (Sub != null)
{
return(Sql_Sub(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
left: left,
tableType: tableType,
commandCount: commandCount));
}
else if (Or != null)
{
return(Sql_Or(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
tableType: tableType,
commandCount: commandCount));
}
else
{
return(Sql_General(
left: left,
tableType: tableType,
commandCount: commandCount));
}
}
else
{
return(string.Empty);
}
}
public string GetCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
string prefix = "",
int?commandCount = null)
{
var commandText = new StringBuilder();
Terminate = false;
SqlWhereCollection?.Prefix(prefix);
SqlParamCollection?.Prefix(prefix);
BuildCommandText(sqlContainer, sqlCommand, commandText, commandCount);
return(commandText.ToString());
}
private void Build_RestoreStatement(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount)
{
commandText.Append(factory.SqlCommandText.CreateRestore(
template: CommandText
.Params(SqlWhereCollection.Sql(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandCount: commandCount))));
}
public string CommandText(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
string tableBracket,
int?commandCount)
{
return(Sub != null
? Sql_Sub(
factory : factory,
sqlContainer : sqlContainer,
sqlCommand : sqlCommand,
commandCount : commandCount)
: CommandText(tableBracket: tableBracket) + AsBracket());
}
private string Sql_Raw(
SqlContainer sqlContainer, Sqls.TableTypes tableType, int?commandCount)
{
if (Value.IsCollection())
{
var valueCollection = Value.ToStringEnumerable();
return(valueCollection
.Select((o, i) => ReplacedSql(tableType, commandCount, i.ToString()))
.Join(MultiParamOperator));
}
else
{
return(ReplacedSql(sqlContainer, tableType, commandCount));
}
}
private void BuildNormal(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount)
{
BuildCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
tableType: Sqls.TableTypes.Normal,
unionType: UnionType,
orderBy: true,
commandCount: commandCount);
}
public void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount,
string multiClauseOperator = " and ",
bool select = false)
{
commandText.Append(Sql(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
multiClauseOperator: multiClauseOperator,
commandCount: commandCount,
select: select));
}
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);
}
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);
}
public override void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount = null)
{
if (!Using)
{
return;
}
Build_If(commandText);
Build_InsertStatement(sqlContainer, sqlCommand, commandText, commandCount);
Build_SetIdentity(commandText, SetIdentity, commandCount);
AddParams_Param(sqlCommand, commandCount);
AddTermination(commandText);
Build_EndIf(commandText);
}
public void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
Sqls.TableTypes tableType,
int?commandCount,
bool distinct,
int top)
{
commandText.Append("select ");
Build_DistinctClause(commandText, distinct);
Build_TopClause(commandText, top);
commandText.Append(this
.Select(o => o.CommandText(sqlContainer, sqlCommand, tableType, commandCount))
.Join(), " ");
RemoveAll(o => o.AdHoc);
}
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);
}