private void Build_InsertStatement(
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 columnNameCollection = new List <string>();
var valueCollection = new List <string>();
if (AddUpdatorParam)
{
columnNameCollection.Add("[Creator]");
columnNameCollection.Add("[Updator]");
valueCollection.Add("@_U");
valueCollection.Add("@_U");
}
SqlParamCollection?
.Where(o => o.Using)
.ForEach(sqlParam =>
{
columnNameCollection.Add(sqlParam.ColumnBracket);
if (!sqlParam.Raw.IsNullOrEmpty())
{
switch (sqlParam.Raw?.ToString())
{
case "@@identity":
valueCollection.Add("@_I");
break;
default:
valueCollection.Add(sqlParam.Raw);
break;
}
}
else if (sqlParam.Sub != null)
{
valueCollection.Add("(" + sqlParam.Sub.GetCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
prefix: "_sub",
commandCount: commandCount) + ")");
}
else
{
valueCollection.Add("@" + sqlParam.VariableName + commandCount
.ToString());
}
});
commandText.Append(
"insert into ",
tableBracket,
"(", columnNameCollection.Join(), ") ",
Values(
valueCollection,
sqlContainer,
sqlCommand,
commandCount));
}
private void Build_UpdateStatement(
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 columnNameCollection = new List <string>();
if (AddUpdatorParam)
{
columnNameCollection.Add("[Updator] = @_U");
}
if (AddUpdatedTimeParam)
{
columnNameCollection.Add("[UpdatedTime] = getdate()");
}
SqlParamCollection
.Where(o => (o as SqlParam).Using)
.Where(o => (o as SqlParam).Updating)
.ForEach(sqlParam =>
{
if (!sqlParam.Raw.IsNullOrEmpty())
{
switch (sqlParam.Raw?.ToString())
{
case "@@identity":
columnNameCollection.Add(
sqlParam.ColumnBracket + "=@_I");
break;
default:
columnNameCollection.Add(
sqlParam.ColumnBracket + "=" + sqlParam.Raw
.Replace("#CommandCount#", commandCount.ToString()));
break;
}
}
else if (sqlParam.Sub != null)
{
columnNameCollection.Add(sqlParam.ColumnBracket + "=(" +
sqlParam.Sub.GetCommandText(
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
prefix: "_sub",
commandCount: commandCount) + ")");
}
else if (!sqlParam.ColumnBracket.IsNullOrEmpty())
{
columnNameCollection.Add(
sqlParam.ColumnBracket + "=@" + sqlParam.VariableName + commandCount);
}
});
if (SaveHistoryCommandText != string.Empty)
{
Build_CopyToHistoryStatement(commandText, SaveHistoryCommandText, commandCount);
columnNameCollection.Add("{0}.[Ver]={0}.[Ver]+1"
.Params(tableBracket));
}
commandText.Append("update ", tableBracket,
" set ", columnNameCollection.Join(), " ");
}
private void Build_UpdateStatement(
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 columnNameCollection = new List <string>();
if (AddUpdatorParam)
{
columnNameCollection.Add($"\"Updator\" = {Parameters.Parameter.SqlParameterPrefix}U");
}
if (AddUpdatedTimeParam)
{
columnNameCollection.Add($"\"UpdatedTime\" = {factory.Sqls.CurrentDateTime} ");
}
SqlParamCollection?
.Where(o => (o as SqlParam).Using)
.Where(o => (o as SqlParam).Updating)
.ForEach(sqlParam =>
{
if (!sqlParam.Raw.IsNullOrEmpty())
{
switch (sqlParam.Raw?.ToString())
{
case "@@identity":
columnNameCollection.Add(
sqlParam.ColumnBracket + $"={Parameters.Parameter.SqlParameterPrefix}I");
break;
default:
columnNameCollection.Add(
sqlParam.ColumnBracket + "=" + sqlParam.Raw
.Replace("#CommandCount#", commandCount.ToString()));
break;
}
}
else if (sqlParam.Sub != null)
{
columnNameCollection.Add(sqlParam.ColumnBracket + "=(" +
sqlParam.Sub.GetCommandText(
factory: factory,
sqlContainer: sqlContainer,
sqlCommand: sqlCommand,
prefix: "_sub",
commandCount: commandCount) + ")");
}
else if (!sqlParam.ColumnBracket.IsNullOrEmpty())
{
columnNameCollection.Add(
sqlParam.ColumnBracket + "=@" + sqlParam.VariableName + commandCount);
}
});
commandText.Append("update ", tableBracket,
" set ", columnNameCollection.Join(), " ");
}
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()));
}
