/// <summary>
/// Updates the enum tables for a single database.
/// </summary>
/// <param name="conn">An open SQL connection to the database you want to update.</param>
/// <param name="logger">The stream to send logging information to.</param>
public void UpdateDatabase(SqlConnection conn, Logger logger)
{
if (logger == null)
{
throw new ArgumentNullException(nameof(logger));
}
if (Enums.Count == 0)
{
return;
}
using (logger.OpenBlock($"Updating database {conn.Database} on {conn.DataSource}"))
{
try
{
foreach (var enumInfo in Enums)
{
var existingRows = SqlExecutor.GetTableRows(conn, enumInfo);
var updatePlan = TableUpdatePlan.Create(enumInfo, existingRows);
SqlExecutor.UpdateTable(conn, enumInfo, updatePlan, logger);
}
}
catch (Exception ex)
{
logger.Exception(ex);
throw new EnumsToSqlException("Unable to update database", ex, isLogged: true);
}
}
}
public static void UpdateTable(SqlConnection conn, EnumInfo info, TableUpdatePlan plan, Logger logger)
{
if (plan.Add.Count == 0 && plan.Update.Count == 0 && plan.Delete.Count == 0)
{
return;
}
using (logger.OpenBlock($"Updating {info.Schema}.{info.Table}"))
{
try
{
if (plan.Add.Count > 0)
{
var sql = SqlCreator.GetInsertSql(info);
foreach (var row in plan.Add)
{
ExecuteUpdate(conn, sql, info, row);
logger.Info($"Added {row.Name}");
}
}
if (plan.Update.Count > 0)
{
var sql = SqlCreator.GetUpdateSql(info);
foreach (var row in plan.Update)
{
ExecuteUpdate(conn, sql, info, row);
logger.Info($"Updated {row.Name}");
}
}
if (plan.Delete.Count > 0)
{
string sql, successMessage;
if (plan.DeletionMode == DeletionMode.MarkAsInactive)
{
sql = $"update {info.SqlSchema}.{info.SqlTable} set {info.IsActiveColumn.SqlName} = 0 where {info.IdColumn.SqlName} = @{EnumInfo.ID};";
successMessage = "Marked deleted value \"{0}\" as inactive";
}
else
{
sql = $"delete from {info.SqlSchema}.{info.SqlTable} where {info.IdColumn.SqlName} = @{EnumInfo.ID};";
successMessage = "Deleted {0}";
}
var ignoreConstraintViolations = plan.DeletionMode == DeletionMode.TryDelete;
foreach (var row in plan.Delete)
{
if (ExecuteDelete(conn, sql, row.Id, ignoreConstraintViolations))
{
logger.Info(string.Format(successMessage, row.Name));
}
else
{
logger.Warning($"Attempted to delete {row.Name}, but failed due to SQL constraints (probably a foreign key)");
}
}
}
}
catch (Exception ex)
{
logger.Exception(ex);
throw new EnumsToSqlException($"Failed to update table {info.Schema}.{info.Table}", isLogged: true);
}
}
}
