Click here to Skip to main content
Click here to Skip to main content

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

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

License

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#

Comments and Discussions

 
QuestionDefining the literal PinprofessionalGeorge Hendrickson23-May-14 5:19 
AnswerRe: Defining the literal PinprofessionalGeorge Hendrickson23-May-14 6:14 
QuestionHow to access the enums in the .dll? PinmemberMember 904971930-May-12 3:21 
AnswerRe: How to access the enums in the .dll? PinmemberGreg Osborne19-Jul-12 5:48 
GeneralVery Very Cool. Thx a mil PinmemberThepo27-Nov-08 10:25 
GeneralRe: Very Very Cool. Thx a mil PinmemberGreg Osborne1-Dec-08 2:16 
GeneralMy vote of 1 PinmemberProJester124-Nov-08 11:08 
GeneralRe: My vote of 1 PinmemberGreg Osborne1-Dec-08 2:14 
GeneralNo source code and/or binary to download PinmemberDaniel M. Camenzind19-Nov-08 19:37 
GeneralRe: No source code and/or binary to download PinmemberGreg Osborne20-Nov-08 2:19 
GeneralGood work Pinmemberkissdznuts19-Nov-08 2:10 
QuestionGetting error while accessing enum value PinmemberPatel Pranav18-Nov-08 22:58 
AnswerRe: Getting error while accessing enum value PinmemberGreg Osborne19-Nov-08 3:02 
QuestionWould it be possible to add multiple tables in the single dll? Pinmemberjoe_sabado17-Nov-08 14:16 
AnswerRe: Would it be possible to add multiple tables in the single dll? PinmemberGreg Osborne18-Nov-08 2:15 
AnswerRe: Would it be possible to add multiple tables in the single dll? PinmemberGreg Osborne18-Nov-08 2:16 
GeneralBrilliant! PinmemberGer Rietman17-Nov-08 0:53 
GeneralThis is good idea... Pinmemberlee kim wee15-Nov-08 14:21 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 19 Nov 2008
Article Copyright 2008 by Greg Osborne
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid