Creating views from enums with DbUp

Dec 30, 2014 • val antonini

DbUp is a great script runner for handling database migrations. This snippet can be used to can an assembly and create views for any public enums inside it. The assembly version needs to be incremented for views to be recreated

namespace valantonini
{
    public static class UpgradeEngineBuilderExtensions
    {
        public static UpgradeEngineBuilder GenerateLookupViewsForEnumsIn(this UpgradeEngineBuilder builder, Assembly assembly)
        {
            var enumLookupScriptProvider = new EnumLookupScriptProvider(assembly);
            builder.Configure(c => c.ScriptProviders.Add(enumLookupScriptProvider));
            return builder;
        }
    }

    public class EnumLookupScriptProvider : IScriptProvider
    {
        private const string DropView = "IF EXISTS(select * FROM sys.views where name = \'{0}\')\n DROP VIEW {0}\n GO\n";
        private const string CreateView = "CREATE VIEW {0} AS {1}\n GO\n";

        private readonly Assembly _assembly;

        public EnumLookupScriptProvider(Assembly assembly)
        {
            _assembly = assembly;
        }

        public IEnumerable<SqlScript> GetScripts(Func<IDbConnection> connectionFactory)
        {
            var enumTypes = _assembly
            .DefinedTypes
            .Where(type => type.IsEnum);

            var scripts = new List<SqlScript>();

            foreach (var type in enumTypes)
            {
                var dropViewScript = String.Format(DropView, type.Name);
                var createViewScript = GenerateCreateView(type);

                var script = String.Format("{0} {1}", dropViewScript, createViewScript);

                var scriptName = String.Format("{0}-{1}",type.Name,_assembly.GetName().Version);
                scripts.Add(new SqlScript(scriptName, script));
            }

            return scripts;
        }

        private string GenerateCreateView(TypeInfo type)
        {
            const string selectStatementFormat = "SELECT {0} AS Id, \'{1}\' AS Value";
            var selectStatements = new List<string>();
            var enumValues = Enum.GetValues(type);

            foreach (var enumValue in enumValues)
            {
                var castValue = enumValue as Enum;

                if (castValue == null) continue;

                var selectStatement = String.Format(selectStatementFormat, castValue.ToString("d"), GetDescription(castValue).Replace("'", "''"));
                selectStatements.Add(selectStatement);
            }

            return String.Format(CreateView, type.Name, String.Join("\nUNION\n", selectStatements));
        }

        public static string GetDescription(Enum e)
        {
            var field = e.GetType().GetField(e.ToString());

            if (field == null) return e.ToString();

            var description = field.GetCustomAttributes(typeof(DescriptionAttribute), false)
            .Cast<DescriptionAttribute>()
            .FirstOrDefault();

            return description == null ? e.ToString() : description.Description;
        }
    }
}

The easiest way to use this is expose the Assembly of the project with the views:

public static class MyProject
{
    public static Assembly Assembly()
    {
        return typeof (MyProject).Assembly;
    }
}

Then add the below to the DbUp program:

DeployChanges.To.SqlDatabase(connectionString).GenerateLookupViewsForEnumsIn(MyProject.Assembly())