Click here to Skip to main content
13,593,266 members
Click here to Skip to main content
Add your own
alternative version


54 bookmarked
Posted 14 Nov 2008
Licenced CPOL

Dynamic Enumerations from Database Tables

, 19 Nov 2008
Rate this:
Please Sign up or sign in to vote.
Create an assembly containing enumerations defined in your database


Dynamic enumerations can easily be generated by your application by using the code given in this article.


A vital part of database application programming is the identification and use of lookup items similar in functionality to constants. Prior to .NET, developers could define their lookup values in database tables, but to use them as constants in their applications, they would have a separate step of defining them in their applications in the form of CONST values or as enumerations. This poses a problem at times - how to ensure that the database values stay synchronized with the application values. Using the EnumBuilder class and a few lines of code in your application, this is all possible without any intervention on your part.

Using the Code

First, set a reference in your code to System.Reflection.Emit.

Imports System.Reflection.Emit

The code below is amazingly simple to use. Just set a few variables at the start of the code to your values and you are off. This code will create a new assembly, designated by the assemblyName variable, with a DLL extension, in the application folder.

OpenDatabase() 'you provide this
    Dim assemblyName As String = "DynEnum"
    Dim lookupSQL As String = "SELECT ID, Name FROM AllMessageTypes"
    Dim nameField As String = "Name"
    Dim valueField As String = "ID"
    Dim enumerationName As String = "MessageTypes"

    Dim currentDomain As AppDomain = AppDomain.CurrentDomain
    Dim aName As AssemblyName = New AssemblyName(assemblyName)
    Dim ab As AssemblyBuilder = _
        currentDomain.DefineDynamicAssembly(aName, _
    Dim mb As ModuleBuilder = ab.DefineDynamicModule(aName.Name, _
                              aName.Name & ".dll")

    Dim eb As EnumBuilder = mb.DefineEnum(enumerationName, _
              TypeAttributes.Public, GetType(Integer))

    'your saved connection
    Dim cmd As New SqlCommand(lookupSQL, _Connection)
    Dim dr As SqlDataReader = cmd.ExecuteReader
    If dr.HasRows Then
        Do While dr.Read
            eb.DefineLiteral(dr.GetValue(dr.GetOrdinal(nameField)), _
    End If

    ab.Save(aName.Name & ".dll")
Catch ex As Exception
    Throw ex
End Try

From this point, all you have to do after you've run your application once and created the resulting DLL, is add the DLL to the references of your application. If you add or remove any values in the lookup table, it is automatically reflected in the enumeration, as it is generated by the application.

I place this code in the constructor of the class that will consume the enumerations, although I don't believe that it matters where it's placed in relation to the rest of the application. If you find that's not the case, please let me know.

Possible Problems with this Approach

I haven't verified this yet, but there may be instances where anti-virus applications would see a change in a DLL during application execution as an infected file, so you may wish to keep an eye out for such a situation.

Points of Interest

Interestingly enough, even though you may have a reference in the application to the newly created DLL, a file in use exception is not thrown when the application is run and the DLL is regenerated. I would have thought this would be the case, but it goes right through the code and creates the new DLL. If someone can explain this to me, it would be appreciated. Also, if you place a new value in the table and run your application, the new values are available to the application immediately. Of course, the new values are not available to Visual Studio for intellisense until after you've stopped the application.

UPDATE: I think I have determined why we don't get a file in use error when regenerating the DLL. As I understand it, a .NET DLL is not actually loaded until it is used. In this case, our dynamic DLL will be regenerated as long as there have been no uses of it up to the point of regeneration. So, if no other classes in the application use the enumerations that are being generated prior to the regeneration code, our DLL will be regenerated with any new values inserted into the tables. But alas, this also means that we cannot have Private, Friend, Protected, or Public variables of our enumeration types in the class where the regeneration is occurring, as those are initialized prior to the class constructor code being run. So watch the scoping of variables of the enumerations you are creating, or you will get a file in use exception.


  • Original submission - Friday, November 14, 2008
  • Update - Wednesday, November 19, 2008


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Greg Osborne
United States United States
Visual Basic Developer since version 1.0
Java web developer
Currently developing in vb and c#

You may also be interested in...

Comments and Discussions

GeneralNo source code and/or binary to download Pin
Daniel M. Camenzind19-Nov-08 19:37
memberDaniel M. Camenzind19-Nov-08 19:37 
GeneralRe: No source code and/or binary to download Pin
Greg Osborne20-Nov-08 2:19
memberGreg Osborne20-Nov-08 2:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web02 | 2.8.180618.1 | Last Updated 19 Nov 2008
Article Copyright 2008 by Greg Osborne
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid