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

User · Answer

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

User · Answer

You could use CodeSmith to generate something like this   http   www csharping com PermaLink guid cef1b637-7d37-4691-8e49-138cbf1d51e9 aspx

User · Answer

Just showing the  answer of Pandincus with  of the shelf  code and some explanation  You need two solutions for this example   I know it could be done via one also      let the advanced students present it      So here is the DDL SQL for the table     USE  ocms dev      GO  CREATE TABLE  dbo   Role        RoleId   int  IDENTITY 1 1  NOT NULL       RoleName   varchar  50  NULL   ON  PRIMARY    So here is the console program producing the dll    using System  using System Collections Generic  using System Text  using System Reflection  using System Reflection Emit  using System Data Common  using System Data  using System Data SqlClient   namespace DynamicEnums       class EnumCreator                  after running for first time rename this method to Main1         static void Main                          string strAssemblyName    MyEnums               bool flagFileExists   System IO File Exists                      AppDomain CurrentDomain SetupInformation ApplicationBase                       strAssemblyName     dll                                 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   strAssemblyName                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                                region GetTheDataFromTheDatabase             DataTable tableData   new DataTable    enumSourceDataTable                  string connectionString    Integrated Security SSPI Persist                          Security Info False Initial Catalog ocms dev Data                          Source ysg                using  SqlConnection connection                        new SqlConnection   connectionString                                   SqlCommand command   connection CreateCommand                     command CommandText   string Format    SELECT  RoleId                                  RoleName  FROM  ocms dev   dbo   Role                       Console WriteLine    command CommandText is                              command CommandText                     connection Open                     tableData Load   command ExecuteReader                            CommandBehavior CloseConnection                                      eof using              foreach  DataRow dr in tableData Rows                                myEnum DefineLiteral   dr 1  ToString                             Convert ToInt32   dr 0  ToString                                    endregion GetTheDataFromTheDatabase                 Create the enum             myEnum CreateType                     Finally  save the assembly             assemblyBuilder Save   name Name     dll                 eof Main          eof Program     eof namespace    Here is the Console programming printing the output   remember that it has to reference the dll    Let the advance students present the solution for combining everything in one solution with dynamic loading and checking if there is already build dll       add the reference to the newly generated dll use MyEnums     class Program       static void Main                  Array values   Enum GetValues   typeof   EnumeratedTypes MyEnum               foreach  EnumeratedTypes MyEnum val in values                        Console WriteLine   String Format     0    1                        Enum GetName   typeof   EnumeratedTypes MyEnum    val                        val                         Console WriteLine    Hit enter to exit              Console ReadLine             eof Main      eof Program

User · Answer

Aren t we coming to this from the wrong direction   If the data is likely to change at all during the lifetime of the deployed release then an enum is just not appropriate  and you need to use a dictionary  hash or other dynamic collection   If you know the set of possible values is fixed for the life of the deployed release  then an enum is preferable   If you must have something in your database that replicates the enumerated set  then why not add a deployment step to clear and repopulate the database table with the definitive set of enum values

User · Answer

I don t think there is a good way of doing what you want  And if you think about it I don t think this is what you really want   If you would have a dynamic enum  it also means you have to feed it with a dynamic value when you reference it  Maybe with a lot of magic you could achieve some sort of IntelliSense that would take care of this and generate an enum for you in a DLL file  But consider the amount of work it would take  how uneffective it would be to access the database to fetch IntelliSense information as well as the nightmare of version controlling the generated DLL file   If you really don t want to manually add the enum values  you ll have to add them to the database anyway  use a code generation tool instead  for example T4 templates  Right click run and you got your enum statically defined in code and you get all the benefits of using enums

User · Answer

Word up  I as well got tired of writing out enumerations based on Id   Name db table columns  copying and pasting stuff from queries in SSMS  Below is a super dirty stored procedure that takes as input a table name  the column name you want to use for the c  enumeration name  and the column name that you want to use for the c  enumeration value  Most of theses table names I work with a  end with  quot s quot  b  have a  TABLENAME Id column and c  have a  TABLENAME Name column  so there are a couple if statements that will assume that structure  in which case  the column name parameters are not required  A little context for these examples -  quot Stonk quot  here doesn t really mean  quot stock quot  but kinda  the way I m using  quot stonk quot  it means  quot a thing that has some numbers associated to it for a time period quot  But that s not important  it s just an example of table with this Id   Name schema  It looks like this  CREATE TABLE StonkTypes       StonkTypeId TINYINT IDENTITY 1 1  PRIMARY KEY NOT NULL      StonkTypeName VARCHAR 200  NOT NULL CONSTRAINT UQ StonkTypes StonkTypeName UNIQUE  StonkTypeName     After I create the proc  this statement  EXEC CreateCSharpEnum  StonkTypes  Selects this string  public enum StonkTypes   Stonk   1  Bond   2  Index   3  Fund   4  Commodity   5   PutCallRatio   6     Which I can copy and paste into a C  file  I have a Stonks table and it has StonkId and StonkName columns so this exec  EXEC CreateCSharpEnum  Stonks   Spits out  public enum Stonks   SP500   1  DowJonesIndustrialAverage   2          But for that enum I want to use the  quot Symbol quot  column for the enum name values so this  EXEC CreateCSharpEnum  Stonks    Symbol   Does the trick and renders  public enum Stonks   SPY   1  DIA   2          Without further ado  here is this dirty piece of craziness  Yeah  very dirty  but I m kind of pleased with myself - it s SQL code that constructs SQL code that constructs C  code  Couple layers involved   CREATE OR ALTER PROCEDURE CreateCSharpEnum  TableName VARCHAR MAX    EnumNameColumnName VARCHAR MAX    NULL   EnumValueColumnName VARCHAR MAX    NULL AS  DECLARE  LastCharOfTableName VARCHAR 1  SELECT  LastCharOfTableName   RIGHT  TableName  1   PRINT  Last char         LastCharOfTableName        DECLARE  TableNameWithoutS VARCHAR MAX  IF UPPER  LastCharOfTableName     S      SET  TableNameWithoutS   LEFT  TableName  LEN  TableName  - 1  ELSE     SET  TableNameWithoutS    TableName  PRINT  Table name without trailing s         TableNameWithoutS        IF  EnumNameColumnName IS NULL     BEGIN         SET  EnumNameColumnName    TableNameWithoutS    Name      END  PRINT  name col name         EnumNameColumnName        IF  EnumValueColumnName IS NULL     SET  EnumValueColumnName    TableNameWithoutS    Id   PRINT  value col name         EnumValueColumnName        -- replace spaces and punctuation SET  EnumNameColumnName     REPLACE      EnumNameColumnName                    SET  EnumNameColumnName     REPLACE      EnumNameColumnName         amp            SET  EnumNameColumnName     REPLACE      EnumNameColumnName                    SET  EnumNameColumnName     REPLACE      EnumNameColumnName                    SET  EnumNameColumnName     REPLACE      EnumNameColumnName                     PRINT  name col name with replace sql         EnumNameColumnName        DECLARE  SqlStr VARCHAR MAX     SELECT      EnumNameColumnName                         LTRIM RTRIM STR      EnumValueColumnName                FROM      TableName     ORDER BY      EnumValueColumnName  PRINT  sql that gets rows for enum body         SqlStr        CREATE TABLE  EnumRowsTemp  s VARCHAR MAX    INSERT  INTO  EnumRowsTemp EXEC  SqlStr   --SELECT   FROM  EnumRowsTemp  DECLARE  csharpenumbody VARCHAR MAX   SELECT  csharpenumbody   COALESCE  csharpenumbody              s FROM  EnumRowsTemp  --PRINT  csharpenumbody  DECLARE  csharpenum VARCHAR MAX     public enum      TableName            csharpenumbody         PRINT  csharpenum  SELECT  csharpenum  DROP TABLE  EnumRowsTemp  Please  be critical  One funky thing I didn t understand  how come I have to create and drop this  EnumRowsTemp table and not just  quot SELECT INTO  EnumRowsTemp quot  to create the temp table on the fly  I don t know the answer  I tried that and it didn t work  That s probably the least of the problems of this code    As dirty as it may be    I hope this saves some of you fellow dorks a little bit of time

User · Answer

One way to keep the Enums and to create a Dynamic list of values at the same time is to use the Enums that you currently have with a Dynamically created Dictionary   Since most Enums are used in the context that they are defined to be used  and the  dynamic enums  will be supported by dynamic processes  you can distinguish the 2   The first step is to create a table collection that houses the IDs and References for the Dynamic Entries   In the table you will autoincrement much larger than your largest Enum value   Now comes the part for your dynamic Enums  I am assuming that you will be using the Enums to create a set of conditions that apply a set of rules  some are dynamically generated   Get integer from database If Integer is in Enum - gt  create Enum - gt  then run Enum parts If Integer is not a Enum - gt  create Dictionary from Table - gt  then run Dictionary parts

User · Answer

Using dynamic enums is bad no matter which way  You will have to go through the trouble of  duplicating  the data to ensure clear and easy code easy to maintain in the future   If you start introducing automatic generated libraries  you are for sure causing more confusion to future developers having to upgrade your code than simply making your enum coded within the appropriate class object   The other examples given sound nice and exciting  but think about the overhead on code maintenance versus what you get from it  Also  are those values going to change that frequently

User · Answer

Does it have to be an actual enum  How about using a Dictionary lt string int gt  instead   for example   Dictionary lt string  int gt  MyEnum   new Dictionary     One   1     Two   2    Console WriteLine MyEnum  One

User · Answer

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

User · Answer

You want System Web Compilation BuildProvider  I also doubt the wisdom of doing this  but then there maybe a good use case that I can t think of   What you re looking for are Build Providers i e  System Web Compilation BuildProvider  They re used very effectively by SubSonic  you can download the source and see how they use them  you won t need anything half as intricate as what they re doing   Hope this helps

User · Answer

I always like to write my own  custom enum   Than I have one class that is a little bit more complex  but I can reuse it   public abstract class CustomEnum       private readonly string  name      private readonly object  id       protected CustomEnum  string name  object id                  name   name           id   id             public string Name               get   return  name               public object Id               get   return  id               public override string ToString                 return  name           public abstract class CustomEnum lt TEnumType  TIdType gt    CustomEnum     where TEnumType   CustomEnum lt TEnumType  TIdType gt        protected CustomEnum  string name  TIdType id             base  name  id                public new TIdType Id               get   return  TIdType base Id               public static TEnumType FromName  string name                 try                       return FromDelegate  entry   gt  entry Name Equals  name                        catch  ArgumentException ae                        throw new ArgumentException   Illegal name for custom enum      typeof  TEnumType   Name        ae                         public static TEnumType FromId  TIdType id                 try                       return FromDelegate  entry   gt  entry Id Equals  id                        catch  ArgumentException ae                        throw new ArgumentException   Illegal id for custom enum      typeof  TEnumType   Name        ae                         public static IEnumerable lt TEnumType gt  GetAll                 var elements   new Collection lt TEnumType gt             var infoArray   typeof  TEnumType   GetFields  BindingFlags Public   BindingFlags Static             foreach  var info in infoArray                        var type   info GetValue  null   as TEnumType              elements Add  type                       return elements             protected static TEnumType FromDelegate  Predicate lt TEnumType gt  predicate                 if predicate    null              throw new ArgumentNullException   predicate              foreach  var entry in GetAll                          if  predicate  entry                    return entry                     throw new ArgumentException   Element not found while using predicate               Now I just need to create my enum I want to use    public sealed class SampleEnum   CustomEnum lt SampleEnum  int gt                public static readonly SampleEnum Element1   new SampleEnum   Element1   1   foo             public static readonly SampleEnum Element2   new SampleEnum   Element2   2   bar              private SampleEnum  string name  int id  string additionalText                 base  name  id                         AdditionalText   additionalText                     public string AdditionalText   get  private set            At last I can use it like I want    static void Main  string   args                         foreach  var element in SampleEnum GetAll                                  Console WriteLine    0    1    element  element AdditionalText                    Console WriteLine   Is  Element2    0    element    SampleEnum Element2                    Console WriteLine                               Console ReadKey                And my output would be   Element1  foo Is  Element2   False  Element2  bar Is  Element2   True

User · Answer

enum builder class   public class XEnum       private EnumBuilder enumBuilder      private int index      private AssemblyBuilder  ab      private AssemblyName  name      public XEnum string enumname                AppDomain currentDomain   AppDomain CurrentDomain           name   new AssemblyName  MyAssembly             ab   currentDomain DefineDynamicAssembly               name  AssemblyBuilderAccess RunAndSave            ModuleBuilder mb    ab DefineDynamicModule  MyModule             enumBuilder   mb DefineEnum enumname  TypeAttributes Public  typeof int                     lt summary gt          adding one string to enum          lt  summary gt           lt param name  s  gt  lt  param gt           lt returns gt  lt  returns gt      public FieldBuilder add string s                FieldBuilder f   enumBuilder DefineLiteral s  index           index            return f                 lt summary gt          adding array to enum          lt  summary gt           lt param name  s  gt  lt  param gt      public void addRange string   s                for  int i   0  i  lt  s Length  i                          enumBuilder DefineLiteral s i   i                            lt summary gt          getting index 0          lt  summary gt           lt returns gt  lt  returns gt      public object getEnum                 Type finished   enumBuilder CreateType             ab Save  name Name     dll            Object o1   Enum Parse finished   0            return o1                 lt summary gt          getting with index          lt  summary gt           lt param name  i  gt  lt  param gt           lt returns gt  lt  returns gt      public object getEnum int i                Type finished   enumBuilder CreateType             ab Save  name Name     dll            Object o1   Enum Parse finished  i ToString             return o1            create an object  string   types      String    Boolean    Int32    Enum    Point    Thickness    long    float     XEnum xe   new XEnum  Enum            xe addRange types           return xe getEnum

User · Answer

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

User · Answer

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

[c#] Automatically create an Enum based on values in a database lookup table?

Examples related to c#

Examples related to database

Examples related to dynamic

Examples related to enums