Dynamic Enumerations from Database Tables

Introduction

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

Background

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
Try
    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, _
        AssemblyBuilderAccess.RunAndSave)
    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)), _
                             dr.GetValue(dr.GetOrdinal(valueField)))
        Loop
    End If
    dr.Close()
    eb.CreateType()

    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.

History

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