public static List <Row> GetTableRows(SqlConnection conn, EnumInfo info)
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = SqlCreator.CreateTableAndSelect(info);
using (var rdr = cmd.ExecuteReader())
{
SchemaOrdinals ordinals;
if (!TryGetOrdinals(info, rdr, out ordinals))
{
throw new InvalidOperationException($"Table {info.Schema}.{info.Table} does not match the expected schema.");
}
var rows = new List <Row>();
var idSize = info.IdColumn.Size;
while (rdr.Read())
{
var id = ReadIdColumn(rdr, ordinals.Id, idSize);
var name = ordinals.Name == -1 ? null : rdr.GetString(ordinals.Name);
var displayName = ordinals.DisplayName == -1 ? null : rdr.GetString(ordinals.DisplayName);
var description = ordinals.Description == -1 ? null : rdr.GetString(ordinals.Description);
var isActive = ordinals.IsActive == -1 ? false : rdr.GetBoolean(ordinals.IsActive);
rows.Add(new Row(id, name, displayName, description, isActive));
}
return(rows);
}
}
}
EnumInfo(Type enumType, AttributeInfo attrInfo, EnumValue[] values)
{
Schema = SqlCreator.TrimSqlName(attrInfo.Schema);
SqlSchema = SqlCreator.BracketSqlName(Schema);
Table = SqlCreator.TrimSqlName(attrInfo.Table);
SqlTable = SqlCreator.BracketSqlName(Table);
if (string.IsNullOrEmpty(Schema))
{
throw new EnumsToSqlException($"Schema name cannot be null or empty. Enum: {FullName}");
}
if (string.IsNullOrEmpty(Table))
{
throw new EnumsToSqlException($"Table name cannot be null or empty. Enum: {FullName}");
}
DeletionMode mode;
if (Enum.TryParse(attrInfo.DeletionMode, true, out mode))
{
DeletionMode = mode;
}
else
{
throw new EnumsToSqlException($"DeletionMode \"{attrInfo.DeletionMode}\" is not valid. Enum: {FullName}");
}
Type = enumType;
BackingTypeInfo = BackingTypeInfo.Get(enumType);
Values = values;
SetupColumns(attrInfo);
}
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);
}
}
}
void SetupColumns(AttributeInfo attrInfo)
{
var columns = new List <ColumnInfo>();
var idSize = attrInfo.IdColumnSize == 0 ? BackingTypeInfo.Size : attrInfo.IdColumnSize;
if (idSize < BackingTypeInfo.Size)
{
throw new EnumsToSqlException($"IdColumnSize is smaller than the enum's backing type. Enum: {FullName}");
}
switch (idSize)
{
case 1:
case 2:
case 4:
case 8:
break;
default:
throw new InvalidOperationException($"{idSize} is not a valid IdColumnSize. Must be 0 (default), 1, 2, 4, or 8. Enum: {FullName}");
}
IdColumn = new ColumnInfo(ID, attrInfo.IdColumn, idSize, SqlCreator.GetIntegerColumnType(idSize));
columns.Add(IdColumn);
if (string.IsNullOrEmpty(IdColumn.Name))
{
throw new EnumsToSqlException($"Id column name cannot be null or empty. Enum: {FullName}");
}
if (attrInfo.NameColumnEnabled)
{
var name = attrInfo.NameColumn;
var size = attrInfo.NameColumnSize;
NameColumn = new ColumnInfo(NAME, name, size, "nvarchar");
columns.Add(NameColumn);
if (string.IsNullOrEmpty(NameColumn.Name))
{
throw new EnumsToSqlException($"NameColumn property cannot be null or empty. Enum: {FullName}");
}
if (size < 1)
{
throw new EnumsToSqlException($"NameColumnSize cannot be less than 1. Enum: {FullName}");
}
foreach (var value in Values)
{
if (value.Name.Length > size)
{
throw new Exception($"Enum value name exceeds the maximum length of {size}.\n Enum: {FullName}\n Value: {value.Name}");
}
}
}
if (attrInfo.DisplayNameColumnEnabled)
{
var name = attrInfo.DisplayNameColumn;
var size = attrInfo.DisplayNameColumnSize;
DisplayNameColumn = new ColumnInfo(DISPLAY_NAME, name, size, "nvarchar");
columns.Add(DisplayNameColumn);
if (string.IsNullOrEmpty(name))
{
throw new EnumsToSqlException($"DisplayNameColumn property cannot be null or empty. Enum: {FullName}");
}
if (size < 1)
{
throw new EnumsToSqlException($"DisplayNameColumnSize cannot be less than 1. Enum: {FullName}");
}
foreach (var value in Values)
{
if (value.DisplayName.Length > size)
{
throw new Exception($"Enum value display name exceeds the maximum length of {size}.\n Enum: {FullName}\n Value: {value.Name}");
}
}
}
if (attrInfo.DescriptionColumnEnabled)
{
var name = attrInfo.DescriptionColumn;
var size = attrInfo.DescriptionColumnSize;
DescriptionColumn = new ColumnInfo(DESCRIPTION, name, size, "nvarchar");
columns.Add(DescriptionColumn);
if (string.IsNullOrEmpty(DescriptionColumn.Name))
{
throw new EnumsToSqlException($"DescriptionColumn property cannot be null or empty. Enum: {FullName}");
}
if (size < 1)
{
throw new EnumsToSqlException($"DescriptionColumnSize cannot be less than 1. Enum: {FullName}");
}
foreach (var value in Values)
{
if (value.Description.Length > size)
{
throw new Exception($"Enum value description exceeds the maximum length of {size}.\n Enum: {FullName}\n Value: {value.Name}");
}
}
}
if (attrInfo.IsActiveColumnEnabled)
{
IsActiveColumn = new ColumnInfo(IS_ACTIVE, attrInfo.IsActiveColumn, 1, "bit");
columns.Add(IsActiveColumn);
if (string.IsNullOrEmpty(IsActiveColumn.Name))
{
throw new EnumsToSqlException($"IsActiveColumn property cannot be null or empty. Enum: {FullName}");
}
}
else if (DeletionMode == DeletionMode.MarkAsInactive)
{
throw new EnumsToSqlException($"DeletionMode is {DeletionMode.MarkAsInactive}, but the {IS_ACTIVE} column is disabled. Enum: {FullName}");
}
// make sure there are no duplicate column names
foreach (var a in columns)
{
foreach (var b in columns)
{
if (a != b && a.Name == b.Name)
{
throw new EnumsToSqlException($"Duplicate column name \"{a.Name}\". Enum: {FullName}");
}
}
}
Columns = columns;
}