public override void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount = null)
{
if (!Using)
{
return;
}
Build_If(commandText);
Build_UpdateStatement(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount);
SqlWhereCollection?.BuildCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount);
AddParams_Where(
sqlCommand: sqlCommand,
commandCount: commandCount);
AddParams_Param(
sqlCommand: sqlCommand,
commandCount: commandCount);
AddTermination(commandText: commandText);
Build_EndIf(commandText: commandText);
}
public override void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount = null)
{
if (!Using)
{
return;
}
if (Not)
{
commandText.Append("not ");
}
commandText.Append("exists(select * from ", TableBracket, " ");
SqlJoinCollection?.BuildCommandText(commandText);
SqlWhereCollection?.BuildCommandText(
sqlContainer, sqlCommand, commandText, TableType, commandCount);
commandText.Append(")");
AddTermination(commandText);
AddParams_Where(sqlCommand, commandCount);
switch (TableType)
{
case Sqls.TableTypes.History:
commandText = commandText.Replace(TableBracket, HistoryTableBracket);
break;
case Sqls.TableTypes.Deleted:
commandText = commandText.Replace(TableBracket, DeletedTableBracket);
break;
}
}
public override void BuildCommandText(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount = null)
{
if (!Using)
{
return;
}
Build_If(commandText);
commandText.Append(Statement(commandCount));
SqlWhereCollection?.BuildCommandText(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount);
AddParams_Where(
factory: factory,
sqlCommand: sqlCommand,
commandCount: commandCount);
AddTermination(commandText);
Build_EndIf(commandText);
}
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 string Sql_Or(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
int?commandCount,
bool select)
{
var commandText = new StringBuilder();
Or.Clause = string.Empty;
Or.BuildCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
multiClauseOperator: " or ",
select: select);
return("(" + commandText + ")");
}
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: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
tableType: tableType,
commandCount: commandCount);
return("(" + commandText + ")");
}
public override void BuildCommandText(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount = null)
{
if (!Using)
{
return;
}
Build_If(commandText);
commandText.Append(Statement(commandCount));
SqlWhereCollection?.BuildCommandText(
sqlContainer, sqlCommand, commandText, TableType, commandCount);
AddParams_Where(sqlCommand, commandCount);
AddTermination(commandText);
Build_CountRecord(commandText);
Build_EndIf(commandText);
}
private string Sql_And(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
int?commandCount,
bool select)
{
var commandText = new StringBuilder();
And.Clause = string.Empty;
And.BuildCommandText(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
multiClauseOperator: " and ",
select: select);
return("(" + commandText + ")");
}
private string Sql_Or(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
string tableBracket,
int?commandCount,
bool select)
{
var commandText = new StringBuilder();
Or.Clause = string.Empty;
Or.MultiClauseOperator = " or ";
Or.BuildCommandText(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText,
commandCount: commandCount,
multiClauseOperator: " or ",
select: select);
return("(" + commandText + ")");
}
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);
}
private void Build_UpdateOrInsertStatement(
SqlContainer sqlContainer,
SqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount)
{
var tableBracket = TableBracket;
switch (TableType)
{
case Sqls.TableTypes.History: tableBracket = HistoryTableBracket; break;
case Sqls.TableTypes.Deleted: tableBracket = DeletedTableBracket; break;
}
var updateColumnNameCollection = new List <string>();
if (AddUpdatorParam)
{
updateColumnNameCollection.Add("[Updator] = @_U");
}
if (AddUpdatedTimeParam)
{
updateColumnNameCollection.Add("[UpdatedTime] = getdate()");
}
var insertColumnNameCollection = new List <string>
{
"[Creator]",
"[Updator]"
};
var valueCollection = new List <string> {
"@_U", "@_U"
};
SqlParamCollection
.Where(o => (o as SqlParam).Using)
.ForEach(sqlParam =>
{
insertColumnNameCollection.Add(sqlParam.ColumnBracket);
if (!sqlParam.Raw.IsNullOrEmpty())
{
switch (sqlParam.Raw?.ToString())
{
case "@@identity":
if (sqlParam.Updating)
{
updateColumnNameCollection.Add(
sqlParam.ColumnBracket + "=@_I");
}
valueCollection.Add(
sqlParam.ColumnBracket + "@_I");
break;
default:
if (sqlParam.Updating)
{
updateColumnNameCollection.Add(
sqlParam.ColumnBracket + "=" + sqlParam.Raw
.Replace("#CommandCount#", commandCount.ToString()));
}
valueCollection.Add(sqlParam.Raw
.Replace("#CommandCount#", commandCount.ToString()));
break;
}
}
else if (sqlParam.Sub != null)
{
var sub = sqlParam.Sub.GetCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
prefix: "_sub",
commandCount: commandCount);
if (sqlParam.Updating)
{
updateColumnNameCollection.Add(sqlParam.ColumnBracket +
"=(" + sub + ")");
}
valueCollection.Add("(" + sub + ")");
}
else
{
if (sqlParam.Updating)
{
updateColumnNameCollection.Add(sqlParam.ColumnBracket +
"=@" + sqlParam.VariableName + commandCount.ToStr());
}
valueCollection.Add("@" + sqlParam.VariableName + commandCount.ToStr());
}
});
commandText.Append(
"update ", tableBracket,
" set ", updateColumnNameCollection.Join(), " ");
SqlWhereCollection.BuildCommandText(
sqlContainer, sqlCommand, commandText, TableType, commandCount);
commandText.Append(
" if @@rowcount = 0 insert into ",
tableBracket,
"(", insertColumnNameCollection.Join(), ") values(", valueCollection.Join(), ")");
}
private void Build_UpdateOrInsertStatement(
ISqlObjectFactory factory,
SqlContainer sqlContainer,
ISqlCommand sqlCommand,
StringBuilder commandText,
int?commandCount)
{
var tableBracket = TableBracket;
switch (TableType)
{
case Sqls.TableTypes.History: tableBracket = HistoryTableBracket; break;
case Sqls.TableTypes.Deleted: tableBracket = DeletedTableBracket; break;
}
var updateColumnNameCollection = new List <string>();
if (AddUpdatorParam)
{
updateColumnNameCollection.Add($"\"Updator\" = {Parameters.Parameter.SqlParameterPrefix}U");
}
if (AddUpdatedTimeParam)
{
updateColumnNameCollection.Add($"\"UpdatedTime\" = {factory.Sqls.CurrentDateTime} ");
}
var insertColumnNameCollection = new List <string>
{
"\"Creator\"",
"\"Updator\""
};
var valueCollection = new List <string> {
$"{Parameters.Parameter.SqlParameterPrefix}U", $"{Parameters.Parameter.SqlParameterPrefix}U"
};
SqlParamCollection
.Where(o => (o as SqlParam).Using)
.ForEach(sqlParam =>
{
insertColumnNameCollection.Add(sqlParam.ColumnBracket);
if (!sqlParam.Raw.IsNullOrEmpty())
{
switch (sqlParam.Raw?.ToString())
{
case "@@identity":
if (sqlParam.Updating)
{
updateColumnNameCollection.Add(
sqlParam.ColumnBracket + $"={Parameters.Parameter.SqlParameterPrefix}I");
}
valueCollection.Add(
sqlParam.ColumnBracket + $"{Parameters.Parameter.SqlParameterPrefix}I");
break;
default:
if (sqlParam.Updating)
{
updateColumnNameCollection.Add(
sqlParam.ColumnBracket + "=" + sqlParam.Raw
.Replace("#CommandCount#", commandCount.ToString()));
}
valueCollection.Add(sqlParam.Raw
.Replace("#CommandCount#", commandCount.ToString()));
break;
}
}
else if (sqlParam.Sub != null)
{
var sub = sqlParam.Sub.GetCommandText(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
prefix: "_sub",
commandCount: commandCount);
if (sqlParam.Updating)
{
updateColumnNameCollection.Add(sqlParam.ColumnBracket +
"=(" + sub + ")");
}
valueCollection.Add("(" + sub + ")");
}
else
{
if (sqlParam.Updating)
{
updateColumnNameCollection.Add(sqlParam.ColumnBracket +
"=@" + sqlParam.VariableName + commandCount.ToStr());
}
valueCollection.Add("@" + sqlParam.VariableName + commandCount.ToStr());
}
});
commandText.Append(factory.SqlCommandText.CreateUpdateOrInsert(
tableBracket: tableBracket,
setClause: $" set {updateColumnNameCollection.Join()} ",
sqlWhereAppender: commandText_ =>
SqlWhereCollection.BuildCommandText(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
commandText: commandText_,
commandCount: commandCount),
intoClause: insertColumnNameCollection.Join(),
valueClause: valueCollection.Join()));
}