public string UpdateStatements(UpdateStatementModel updateStatements, out List <string> columnValues)
{
columnValues = new List <string>();
List <string> columnResults = new List <string>();
string result = this.BuildUpdateSql(updateStatements, out columnResults);
columnValues.AddRange(columnResults.Distinct());
return(result);
}
internal static string UpdateStatements(UpdateStatementModel updateStatements, out List <string> columnValues)
{
DatabaseTypeEnum buildType = Connections.Instance.DatabaseModel.DatabaseType;
IDataToSQL sql = null;
switch (buildType)
{
case DatabaseTypeEnum.SQL:
default:
sql = new MsSQL();
return(sql.UpdateStatements(updateStatements, out columnValues));
}
}
internal void UpdateUpdateStatement(UpdateStatementModel updateStatement)
{
if (updateStatement == null)
{
return;
}
string itemKey = this.BuildItemKey(updateStatement.TriggerTable, updateStatement.TriggerColumn);
if (this.updateStatements.ContainsKey(itemKey))
{
this.updateStatements.Remove(itemKey);
}
if (updateStatement.Values.Count == 0 ||
updateStatement.WhereValus.Count == 0)
{
return;
}
this.updateStatements.Add(itemKey, updateStatement);
}
private string BuildUpdateSql(UpdateStatementModel statement, out List <string> columnValues)
{
columnValues = new List <string>();
StringBuilder whereCaluse = new StringBuilder();
foreach (UpdateValueModel item in statement.WhereValus)
{
if (item.IsDatabaseValue)
{
string[] columnSplit = item.UpdateValue.Split('.', StringSplitOptions.None);
whereCaluse.Append($"{item.ColumnName} = '[[{columnSplit[1]}]]' AND ");
columnValues.Add(columnSplit[1]);
}
else
{
whereCaluse.Append($"{item.ColumnName} = '{item.UpdateValue}' AND ");
}
}
whereCaluse.Remove(whereCaluse.Length - 4, 4);
StringBuilder result = new StringBuilder();
result.AppendLine($"IF EXISTS (SELECT 1");
result.AppendLine($" FROM [{statement.UpdateTableName}]");
result.AppendLine($" WHERE {whereCaluse.ToString()})");
result.AppendLine("BEGIN");
result.AppendLine($" UPDATE [{statement.UpdateTableName}]");
result.Append(" SET ");
foreach (UpdateValueModel valueItem in statement.Values)
{
if (valueItem.IsDatabaseValue)
{
string[] columnSplit = valueItem.UpdateValue.Split('.', StringSplitOptions.None);
result.Append($"{valueItem.ColumnName} = '[[{columnSplit[1]}]]', ");
columnValues.Add(columnSplit[1]);
}
else
{
if (valueItem.UpdateValue == Constants.SqlGetDate)
{ // No Single Quates
result.Append($"{valueItem.ColumnName} = {valueItem.UpdateValue}, ");
}
else
{
result.Append($"{valueItem.ColumnName} = '{valueItem.UpdateValue}', ");
}
}
}
result.Remove(result.Length - 2, 2);
result.AppendLine($" WHERE {whereCaluse.ToString()}");
result.AppendLine("END");
result.AppendLine("ELSE");
result.AppendLine("BEGIN");
result.AppendLine($" INSERT INTO {statement.UpdateTableName} (");
foreach (UpdateValueModel valueItem in statement.Values)
{
result.Append($"{valueItem.ColumnName}, ");
}
result.Remove(result.Length - 2, 2);
result.AppendLine(")");
result.AppendLine($" VALUES (");
foreach (UpdateValueModel valueItem in statement.Values)
{
if (valueItem.IsDatabaseValue)
{
string[] columnSplit = valueItem.UpdateValue.Split('.', StringSplitOptions.None);
result.Append($"'[[{columnSplit[1]}]]', ");
columnValues.Add(columnSplit[1]);
}
else
{
if (valueItem.UpdateValue == Constants.SqlGetDate)
{ // No Single Quates
result.Append($"{valueItem.UpdateValue}, ");
}
else
{
result.Append($"'{valueItem.UpdateValue}', ");
}
}
}
result.Remove(result.Length - 2, 2);
result.AppendLine(")");
result.AppendLine("END");
return(result.ToString());
}
internal string SQLUpdates(UpdateStatementModel updateStement, out List <string> columnValues)
{
return(ObjectCreator.UpdateStatements(updateStement, out columnValues));
}
public void UpdateUpdateStatement(UpdateStatementModel updateStatement)
{
this.SqlManager.UpdateUpdateStatement(updateStatement);
}
