Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / Visual Basic
Article

Dynamic Enumerations from Database Tables

Rate me:
Please Sign up or sign in to vote.
4.82/5 (11 votes)
19 Nov 2008CPOL3 min read 52K   54   18
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.

VB.NET
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.

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


Written By
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 Pin
George Hendrickson23-May-14 5:19
professionalGeorge Hendrickson23-May-14 5:19 
AnswerRe: Defining the literal Pin
George Hendrickson23-May-14 6:14
professionalGeorge Hendrickson23-May-14 6:14 
QuestionHow to access the enums in the .dll? Pin
Member 904971930-May-12 3:21
Member 904971930-May-12 3:21 
AnswerRe: How to access the enums in the .dll? Pin
Greg Osborne19-Jul-12 5:48
Greg Osborne19-Jul-12 5:48 
GeneralVery Very Cool. Thx a mil Pin
Thepo27-Nov-08 10:25
Thepo27-Nov-08 10:25 
GeneralRe: Very Very Cool. Thx a mil Pin
Greg Osborne1-Dec-08 2:16
Greg Osborne1-Dec-08 2:16 
GeneralMy vote of 1 Pin
ProJester124-Nov-08 11:08
ProJester124-Nov-08 11:08 
GeneralRe: My vote of 1 Pin
Greg Osborne1-Dec-08 2:14
Greg Osborne1-Dec-08 2:14 
That's the point! Shucks | :-\

Please rate my articles!
Greg Osborne

GeneralNo source code and/or binary to download Pin
Daniel M. Camenzind19-Nov-08 19:37
Daniel M. Camenzind19-Nov-08 19:37 
GeneralRe: No source code and/or binary to download Pin
Greg Osborne20-Nov-08 2:19
Greg Osborne20-Nov-08 2:19 
GeneralGood work Pin
Jon_Boy19-Nov-08 2:10
Jon_Boy19-Nov-08 2:10 
QuestionGetting error while accessing enum value Pin
Patel Pranav18-Nov-08 22:58
Patel Pranav18-Nov-08 22:58 
AnswerRe: Getting error while accessing enum value Pin
Greg Osborne19-Nov-08 3:02
Greg Osborne19-Nov-08 3:02 
QuestionWould it be possible to add multiple tables in the single dll? Pin
joe_sabado17-Nov-08 14:16
joe_sabado17-Nov-08 14:16 
AnswerRe: Would it be possible to add multiple tables in the single dll? Pin
Greg Osborne18-Nov-08 2:15
Greg Osborne18-Nov-08 2:15 
AnswerRe: Would it be possible to add multiple tables in the single dll? Pin
Greg Osborne18-Nov-08 2:16
Greg Osborne18-Nov-08 2:16 
GeneralBrilliant! Pin
Ger Rietman17-Nov-08 0:53
Ger Rietman17-Nov-08 0:53 
GeneralThis is good idea... Pin
laptop developer15-Nov-08 14:21
laptop developer15-Nov-08 14:21 

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.