Automatically create an Enum based on values in a database lookup table?

I'm doing this exact thing, but you need to do some kind of code generation for this to work.

In my solution, I added a project "EnumeratedTypes". This is a console application which gets all of the values from the database and constructs the enums from them. Then it saves all of the enums to an assembly.

The enum generation code is like this:

// Get the current application domain for the current thread
AppDomain currentDomain = AppDomain.CurrentDomain;

// Create a dynamic assembly in the current application domain,
// and allow it to be executed and saved to disk.
AssemblyName name = new AssemblyName("MyEnums");
AssemblyBuilder assemblyBuilder = currentDomain.DefineDynamicAssembly(name,
                                      AssemblyBuilderAccess.RunAndSave);

// Define a dynamic module in "MyEnums" assembly.
// For a single-module assembly, the module has the same name as the assembly.
ModuleBuilder moduleBuilder = assemblyBuilder.DefineDynamicModule(name.Name,
                                  name.Name + ".dll");

// Define a public enumeration with the name "MyEnum" and an underlying type of Integer.
EnumBuilder myEnum = moduleBuilder.DefineEnum("EnumeratedTypes.MyEnum",
                         TypeAttributes.Public, typeof(int));

// Get data from database
MyDataAdapter someAdapter = new MyDataAdapter();
MyDataSet.MyDataTable myData = myDataAdapter.GetMyData();

foreach (MyDataSet.MyDataRow row in myData.Rows)
{
    myEnum.DefineLiteral(row.Name, row.Key);
}

// Create the enum
myEnum.CreateType();

// Finally, save the assembly
assemblyBuilder.Save(name.Name + ".dll");

My other projects in the solution reference this generated assembly. As a result, I can then use the dynamic enums in code, complete with intellisense.

Then, I added a post-build event so that after this "EnumeratedTypes" project is built, it runs itself and generates the "MyEnums.dll" file.

By the way, it helps to change the build order of your project so that "EnumeratedTypes" is built first. Otherwise, once you start using your dynamically generated .dll, you won't be able to do a build if the .dll ever gets deleted. (Chicken and egg kind of problem -- your other projects in the solution need this .dll to build properly, and you can't create the .dll until you build your solution...)

I got most of the above code from this msdn article.

Hope this helps!

Enums must be specified at compile time, you can't dynamically add enums during run-time - and why would you, there would be no use/reference to them in the code?

From Professional C# 2008:

The real power of enums in C# is that behind the scenes they are instantiated as structs derived from the base class, System.Enum . This means it is possible to call methods against them to perform some useful tasks. Note that because of the way the .NET Framework is implemented there is no performance loss associated with treating the enums syntactically as structs. In practice, once your code is compiled, enums will exist as primitive types, just like int and float .

So, I'm not sure you can use Enums the way you want to.

Does it have to be an actual enum? How about using a Dictionary<string,int> instead?

for example

Dictionary<string, int> MyEnum = new Dictionary(){{"One", 1}, {"Two", 2}};
Console.WriteLine(MyEnum["One"]);

Let's say you have the following in your DB:

table enums
-----------------
| id | name     |
-----------------
| 0  | MyEnum   |
| 1  | YourEnum |
-----------------

table enum_values
----------------------------------
| id | enums_id | value | key    |
----------------------------------
| 0  | 0        | 0     | Apple  |
| 1  | 0        | 1     | Banana |
| 2  | 0        | 2     | Pear   |
| 3  | 0        | 3     | Cherry |
| 4  | 1        | 0     | Red    |
| 5  | 1        | 1     | Green  |
| 6  | 1        | 2     | Yellow |
----------------------------------

Construct a select to get the values you need:

select * from enums e inner join enum_values ev on ev.enums_id=e.id where e.id=0

Construct the source code for the enum and you'll get something like:

String enumSourceCode = "enum " + enumName + "{" + enumKey1 + "=" enumValue1 + "," + enumKey2 + ... + "}";

(obviously this is constructed in a loop of some kind.)

Then comes the fun part, Compiling your enum and using it:

CodeDomProvider provider = CodeDomProvider.CreateProvider("CSharp");
CompilerParameters cs = new CompilerParameters();
cp.GenerateInMemory = True;

CompilerResult result = provider.CompileAssemblyFromSource(cp, enumSourceCode);

Type enumType = result.CompiledAssembly.GetType(enumName);

Now you have the type compiled and ready for use.
To get a enum value stored in the DB you can use:

[Enum].Parse(enumType, value);

where value can be either the integer value (0, 1, etc.) or the enum text/key (Apple, Banana, etc.)

Just showing the answer of Pandincus with "of the shelf" code and some explanation: You need two solutions for this example ( I know it could be done via one also ; ), let the advanced students present it ...

So here is the DDL SQL for the table :

USE [ocms_dev]
    GO

CREATE TABLE [dbo].[Role](
    [RoleId] [int] IDENTITY(1,1) NOT NULL,
    [RoleName] [varchar](50) NULL
) ON [PRIMARY]

So here is the console program producing the dll:

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Reflection.Emit;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace DynamicEnums
{
    class EnumCreator
    {
        // after running for first time rename this method to Main1
        static void Main ()
        {
            string strAssemblyName = "MyEnums";
            bool flagFileExists = System.IO.File.Exists (
                   AppDomain.CurrentDomain.SetupInformation.ApplicationBase + 
                   strAssemblyName + ".dll"
            );

            // Get the current application domain for the current thread
            AppDomain currentDomain = AppDomain.CurrentDomain;

            // Create a dynamic assembly in the current application domain,
            // and allow it to be executed and saved to disk.
            AssemblyName name = new AssemblyName ( strAssemblyName );
            AssemblyBuilder assemblyBuilder = 
                    currentDomain.DefineDynamicAssembly ( name,
                            AssemblyBuilderAccess.RunAndSave );

            // Define a dynamic module in "MyEnums" assembly.
            // For a single-module assembly, the module has the same name as
            // the assembly.
            ModuleBuilder moduleBuilder = assemblyBuilder.DefineDynamicModule (
                    name.Name, name.Name + ".dll" );

            // Define a public enumeration with the name "MyEnum" and
            // an underlying type of Integer.
            EnumBuilder myEnum = moduleBuilder.DefineEnum (
                    "EnumeratedTypes.MyEnum",
                    TypeAttributes.Public,
                    typeof ( int )
            );

            #region GetTheDataFromTheDatabase
            DataTable tableData = new DataTable ( "enumSourceDataTable" );

            string connectionString = "Integrated Security=SSPI;Persist " +
                    "Security Info=False;Initial Catalog=ocms_dev;Data " +
                    "Source=ysg";

            using (SqlConnection connection = 
                    new SqlConnection ( connectionString ))
            {

                SqlCommand command = connection.CreateCommand ();
                command.CommandText = string.Format ( "SELECT [RoleId], " + 
                        "[RoleName] FROM [ocms_dev].[dbo].[Role]" );

                Console.WriteLine ( "command.CommandText is " + 
                        command.CommandText );

                connection.Open ();
                tableData.Load ( command.ExecuteReader ( 
                        CommandBehavior.CloseConnection
                ) );
            } //eof using

            foreach (DataRow dr in tableData.Rows)
            {
                myEnum.DefineLiteral ( dr[1].ToString (),
                        Convert.ToInt32 ( dr[0].ToString () ) );
            }
            #endregion GetTheDataFromTheDatabase

            // Create the enum
            myEnum.CreateType ();

            // Finally, save the assembly
            assemblyBuilder.Save ( name.Name + ".dll" );
        } //eof Main 
    } //eof Program
} //eof namespace 

Here is the Console programming printing the output ( remember that it has to reference the dll ). Let the advance students present the solution for combining everything in one solution with dynamic loading and checking if there is already build dll.

// add the reference to the newly generated dll
use MyEnums ; 

class Program
{
    static void Main ()
    {
        Array values = Enum.GetValues ( typeof ( EnumeratedTypes.MyEnum ) );

        foreach (EnumeratedTypes.MyEnum val in values)
        {
            Console.WriteLine ( String.Format ( "{0}: {1}",
                    Enum.GetName ( typeof ( EnumeratedTypes.MyEnum ), val ),
                    val ) );
        }

        Console.WriteLine ( "Hit enter to exit " );
        Console.ReadLine ();
    } //eof Main 
} //eof Program

You want System.Web.Compilation.BuildProvider

I also doubt the wisdom of doing this, but then there maybe a good use case that I can't think of.

What you're looking for are Build Providers i.e. System.Web.Compilation.BuildProvider

They're used very effectively by SubSonic, you can download the source and see how they use them, you won't need anything half as intricate as what they're doing.

Hope this helps.

Word up, I as well got tired of writing out enumerations based on Id / Name db table columns, copying and pasting stuff from queries in SSMS.

Below is a super dirty stored procedure that takes as input a table name, the column name you want to use for the c# enumeration name, and the column name that you want to use for the c# enumeration value.

Most of theses table names I work with a) end with "s" b) have a [TABLENAME]Id column and c) have a [TABLENAME]Name column, so there are a couple if statements that will assume that structure, in which case, the column name parameters are not required.

A little context for these examples - "Stonk" here doesn't really mean "stock" but kinda, the way I'm using "stonk" it means "a thing that has some numbers associated to it for a time period" But that's not important, it's just an example of table with this Id / Name schema. It looks like this:

CREATE TABLE StonkTypes (
    StonkTypeId TINYINT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    StonkTypeName VARCHAR(200) NOT NULL CONSTRAINT UQ_StonkTypes_StonkTypeName UNIQUE (StonkTypeName)
)

After I create the proc, this statement:

EXEC CreateCSharpEnum 'StonkTypes'

Selects this string:

public enum StonkTypes { Stonk = 1, Bond = 2, Index = 3, Fund = 4, Commodity = 5, 
PutCallRatio = 6, }

Which I can copy and paste into a C# file.

I have a Stonks table and it has StonkId and StonkName columns so this exec:

EXEC CreateCSharpEnum 'Stonks'

Spits out:

public enum Stonks { SP500 = 1, DowJonesIndustrialAverage = 2, ..... }

But for that enum I want to use the "Symbol" column for the enum name values so this:

EXEC CreateCSharpEnum 'Stonks', 'Symbol'

Does the trick and renders:

public enum Stonks { SPY = 1, DIA = 2, ..... }

Without further ado, here is this dirty piece of craziness. Yeah, very dirty, but I'm kind of pleased with myself - it's SQL code that constructs SQL code that constructs C# code. Couple layers involved.

CREATE OR ALTER PROCEDURE CreateCSharpEnum
@TableName VARCHAR(MAX),
@EnumNameColumnName VARCHAR(MAX) = NULL,
@EnumValueColumnName VARCHAR(MAX) = NULL
AS

DECLARE @LastCharOfTableName VARCHAR(1)
SELECT @LastCharOfTableName = RIGHT(@TableName, 1)

PRINT 'Last char = [' + @LastCharOfTableName + ']'

DECLARE @TableNameWithoutS VARCHAR(MAX)
IF UPPER(@LastCharOfTableName) = 'S'
    SET @TableNameWithoutS = LEFT(@TableName, LEN(@TableName) - 1)
ELSE
    SET @TableNameWithoutS = @TableName

PRINT 'Table name without trailing s = [' + @TableNameWithoutS + ']'

IF @EnumNameColumnName IS NULL
    BEGIN
        SET @EnumNameColumnName = @TableNameWithoutS + 'Name'
    END

PRINT 'name col name = [' + @EnumNameColumnName + ']'

IF @EnumValueColumnName IS NULL
    SET @EnumValueColumnName = @TableNameWithoutS + 'Id'

PRINT 'value col name = [' + @EnumValueColumnName + ']'

-- replace spaces and punctuation
SET @EnumNameColumnName  = 'REPLACE(' + @EnumNameColumnName + ', '' '', '''')'
SET @EnumNameColumnName  = 'REPLACE(' + @EnumNameColumnName + ', ''&'', '''')'
SET @EnumNameColumnName  = 'REPLACE(' + @EnumNameColumnName + ', ''.'', '''')'
SET @EnumNameColumnName  = 'REPLACE(' + @EnumNameColumnName + ', ''('', '''')'
SET @EnumNameColumnName  = 'REPLACE(' + @EnumNameColumnName + ', '')'', '''')'

PRINT 'name col name with replace sql = [' + @EnumNameColumnName + ']'

DECLARE @SqlStr VARCHAR(MAX) = 'SELECT ' + @EnumNameColumnName  
+ ' + '' = ''' 
+ ' + LTRIM(RTRIM(STR(' + @EnumValueColumnName + '))) + '','' FROM ' + @TableName + ' ORDER BY ' + @EnumValueColumnName

PRINT 'sql that gets rows for enum body = [' + @SqlStr + ']'

CREATE TABLE #EnumRowsTemp (s VARCHAR(MAX))

INSERT 
INTO #EnumRowsTemp
EXEC(@SqlStr)

--SELECT * FROM #EnumRowsTemp

DECLARE @csharpenumbody VARCHAR(MAX) 
SELECT @csharpenumbody = COALESCE(@csharpenumbody + ' ', '') + s FROM #EnumRowsTemp

--PRINT @csharpenumbody

DECLARE @csharpenum VARCHAR(MAX) = 'public enum ' + @TableName + ' { ' + @csharpenumbody + ' }'

PRINT @csharpenum

SELECT @csharpenum

DROP TABLE #EnumRowsTemp

Please, be critical. One funky thing I didn't understand, how come I have to create and drop this #EnumRowsTemp table and not just "SELECT INTO #EnumRowsTemp" to create the temp table on the fly? I don't know the answer, I tried that and it didn't work. That's probably the least of the problems of this code...

As dirty as it may be... I hope this saves some of you fellow dorks a little bit of time.

enum builder class

public class XEnum
{
    private EnumBuilder enumBuilder;
    private int index;
    private AssemblyBuilder _ab;
    private AssemblyName _name;
    public XEnum(string enumname)
    {
        AppDomain currentDomain = AppDomain.CurrentDomain;
        _name = new AssemblyName("MyAssembly");
        _ab = currentDomain.DefineDynamicAssembly(
            _name, AssemblyBuilderAccess.RunAndSave);

        ModuleBuilder mb = _ab.DefineDynamicModule("MyModule");

        enumBuilder = mb.DefineEnum(enumname, TypeAttributes.Public, typeof(int));


    }
    /// <summary>
    /// adding one string to enum
    /// </summary>
    /// <param name="s"></param>
    /// <returns></returns>
    public FieldBuilder add(string s)
    {
        FieldBuilder f = enumBuilder.DefineLiteral(s, index);
        index++;
        return f;
    }
    /// <summary>
    /// adding array to enum
    /// </summary>
    /// <param name="s"></param>
    public void addRange(string[] s)
    {
        for (int i = 0; i < s.Length; i++)
        {
            enumBuilder.DefineLiteral(s[i], i);
        }
    }
    /// <summary>
    /// getting index 0
    /// </summary>
    /// <returns></returns>
    public object getEnum()
    {
        Type finished = enumBuilder.CreateType();
        _ab.Save(_name.Name + ".dll");
        Object o1 = Enum.Parse(finished, "0");
        return o1;
    }
    /// <summary>
    /// getting with index
    /// </summary>
    /// <param name="i"></param>
    /// <returns></returns>
    public object getEnum(int i)
    {
        Type finished = enumBuilder.CreateType();
        _ab.Save(_name.Name + ".dll");
        Object o1 = Enum.Parse(finished, i.ToString());
        return o1;
    }
}

create an object

string[] types = { "String", "Boolean", "Int32", "Enum", "Point", "Thickness", "long", "float" };
XEnum xe = new XEnum("Enum");
        xe.addRange(types);
        return xe.getEnum();

You could use CodeSmith to generate something like this:

http://www.csharping.com/PermaLink,guid,cef1b637-7d37-4691-8e49-138cbf1d51e9.aspx