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

Question

How do I automatically create an enum and subsequently use its values in C# based on values in a database lookup table (using enterprise library data layer)?

For example, If I add a new lookup value in the database, I don't want to have to manually add the extra static enum value declaration in code - I'd like to keep the enum in sync with the database.

Is there such a thing as this?

---

I don't want to create a code generated static enum (as per The Code Project article Enum Code Generator - Generating enum code automatically from database look up tables) and would prefer it to be completely automatic.

Solution 1

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!

Solution 2

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.

Solution 3

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"]);

Solution 4

I've done this with a T4 template. It is fairly trivial to drop a .tt file into your project, and set up Visual Studio to run the T4 template as a pre-build step.

The T4 generates a .cs file, which means you can have it just query the database and build an enum in a .cs file from the result. Wired up as a pre-build task, it would re-create your enum on every build, or you can run the T4 manually as needed instead.

Solution 5

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.)

View more solutions

Comments

[Daniel Brückner]

I would not try to do it this way. You loose your compile time checks and become prone to typing errors. All benefits of enums gone. You could introduce string constants, but then you are back where you started.

[Sandeep Datta]

I agree. But remember mistyped strings will be caught at runtime. Just add a test case to cover all the enum members.

[Nicolas Fall]

mistyping isn't an issue if you use constants instead of literals

[Runeborg]

In what way would this actually help? There's no type safety and no intellisense. Basically it's only a more complicated way of using a constant since he has to provide the value anyway.

[Michael Kniskern]

@YordanGeorgiev -Why do you declare flagFileExists when it is not used anywhere else in the application?

[Yordan Georgiev]

I guess it is a bug than ; I)

[Pandincus]

not sure what billfredtom's reasoning is, but mine was that I could avoid doing manual string-lookups for certain keys, instead having them built into my code. I just prefer to be able to perform logic on strongly-typed values instead of weak strings. A caveat would be that, since we now have code that relies on a dynamically-generated Enum, if we delete the value from the database, the next time we try to compile our code it will fail.

[Matt Mitchell]

Poster and 18 upvotes kinda missed his point. It sounds like he wants generated enums, not runtime dynamic enums.

[Matt Mitchell]

@Maslow Assume you mean enums, not string constants.

[Admin]

Sani - perfect! This was exactly what I needed. For those who question the reason for something like this, I'm using a vendor library that requires a property to be set to the name of an enumeration. The enumeration restricts the valid value range for a different property of the same object. In my case I'm loading metadata, including the valid value range from a database; and no, the vendor code does not support passing a collection of any type to the property. Thanks

[Olivier Jacot-Descombes]

+1. An enum is basically just another way of defining integer constants (even if System.Enum has some additional functionality). Instead of writing const int Red=0, Green=1, Blue=3; You write enum { Red, Green, Blue }. A constant is by definition constant and not dynamic.

[Olivier Jacot-Descombes]

+1. Using a dictionary or a HashSet comes closest to what could be a dynamic enum. Fully dynamic means that it happens at runtime and therefore error checking will have to occur at runtime.

[Pandincus]

@Oliver If you want to argue semantics, yes, you are correct. But I agree with the comment by Graphain -- I believe the OP is looking for generated enums. He wants the enum values to come from the database and not have to hard-code them.

[PositiveGuy]

Or...lets say I allow someone in my web.config define token types for email templates for my email templating code. It would be nice if my existing enum called EmailTokens which represents those string types would be gened based off those types defined in my web.config. So if anyone adds a new email token in the webconfig via my key value e.g. "Email, FName" and I have an enum already that I'm gonna use to represent these tokens such as EmailTemplate.Email it would be nice if anyone could just add a new string token in that key in the web.config and my enum would automatically add the const

[Miguel]

For those who don't know how to run the resulting executable on post-build: 1) Right click the project 2) Click on properties 3) Click on Build Events 4) On the "Post-build event command lines" text box type $(TargetPath)

[traveler3468]

This is a great answer, would much prefer to do it this way than having to compile a dll at start up

[Ken]

Yes and no, Yes because you are correct the whole point is enum is static. You can avoid typing errors and also know what is available. With dictionary and db - could be anything. But sometimes you want the fruit of both trees when you are only allowed to pick from one.

[Dudeman3000]

Ha I misread the question, was just looking for some place to post this. But yeah, you can do the reflection stuff, but... how are you going to use that when you are writing code? You'd have to compile the new code to use it. If I had to do this... You could have a db trigger or scheduled task that would then run the reflection stuff, compile a nuget package, etc, etc, I agree with some of these dudes, that's not really how to use an enum. This code will write the c# for you based on the table but you still have to copy and paste that into a c# file. Still manual process.