Best method to store Enum in Database

Question

What is the best method of storing an Enum in a Database using C# And Visual Studio and MySQL Data Connector.

I am going to be creating a new project with over 100 Enums, and majority of them will have to be stored in the database. Creating converters for each one would be a long winded process therefore I'm wondering if visual studio or someone has any methods for this that I haven't heard off.

Answer 1

In the end you will need a great way to deal with repetitious coding tasks such as enum converters. You can use a code generator such as MyGeneration or CodeSmith among many others or perhaps an ORM mapper like nHibernate to handle everything for you.

As for the structure... with hundreds of enums I would first consider trying to organize the data into a single table that might look something like this: (pseudo sql)

MyEnumTable(
EnumType as int,
EnumId as int PK,
EnumValue as int )

that would allow you to store your enum info in a single table. EnumType could also be a foreign key to a table that defines the different enums.

Your biz objects would be linked to this table via EnumId. The enum type is there only for organization and filtering in the UI. Utilizing all of this of course depends on your code structure and problem domain.

Btw, in this scenario you would want to set a clustered index on EnumType rather than leaving the default cluster idx that is created on the PKey.

Answer 2

We store ours as ints or longs and then we can just cast 'em back and forth. Probably not the most robust solution, but that what we do.

we are using typed DataSets, so, for example:

eunum BlockTreatmentType 
{
    All = 0
};

// blockTreatmentType is an int property
blockRow.blockTreatmentType = (int)BlockTreatmentType.All;
BlockTreatmentType btt = (BlockTreatmentType)blockRow.blocktreatmenttype;

Answer 3

Some things you should take in consideration.

Is the enumeration column going to be used directly by other applications like for example reports. This will limit the possibility of the enumeration being stored in it's integer format because that value will have no meaning when present in a report unless the reports have custom logic.

What are the i18n needs for your application? If it only supports one language you can save the enumeration as text and create a helper method to convert from a description string. You can use [DescriptionAttribute] for this and methods for the conversion can probably be found by searching SO.

If on the other hand you have the need to support multiple language and external application access to your data you can start considering if enumeration are really the answer. Other option like lookup tables can be considered if the scenario is more complex.

Enumerations are excellent when they are self contained in code... when they cross that border, things tend to get a bit messy.

---

Update:

You can convert from an integer using Enum.ToObject method. This implies that you know the type of the enumeration when converting. If you want to make it completely generic you need to store the type of the enumeration alongside it's value in the database. You could create data dictionary support tables to tell you which columns are enumerations and what type are them.

Answer 4

I'm not sure if it is the most flexible, but you could simply store the string versions of them. It is certainly readable, but maybe difficult to maintain. Enums convert from strings and back pretty easily:

  public enum TestEnum
     {
     MyFirstEnum,
     MySecondEnum
     }

  static void TestEnums()
     {
     string str = TestEnum.MyFirstEnum.ToString();
     Console.WriteLine( "Enum = {0}", str );
     TestEnum e = (TestEnum)Enum.Parse( typeof( TestEnum ), "MySecondEnum", true );
     Console.WriteLine( "Enum = {0}", e );
     }

Answer 5

If you want a store of all of your enums values, you can try the below tables to store enums and their members, and the code snippet to add those values. I'd only do this at install time, however, since those values will never change until you recompile!

DB Table:

   create table EnumStore (
    EnumKey int NOT NULL identity primary key,
    EnumName varchar(100)
);
GO

create table EnumMember (
    EnumMemberKey int NOT NULL identity primary key,
    EnumKey int NOT NULL,
    EnumMemberValue int,
    EnumMemberName varchar(100)
);
GO
--add code to create foreign key between tables, and index on EnumName, EnumMemberValue, and EnumMemberName

C# Snippet:

void StoreEnum<T>() where T: Enum
    {
        Type enumToStore = typeof(T);
        string enumName = enumToStore.Name;

        int enumKey = DataAccessLayer.CreateEnum(enumName);
        foreach (int enumMemberValue in Enum.GetValues(enumToStore))
        {
            string enumMemberName = Enum.GetName(enumToStore, enumMemberValue);
            DataAccessLayer.AddEnumMember(enumKey, enumMemberValue, enumMemberName);
        }
    }

Answer 6

Hi,

Why not try separating the enums altogether from the DB? I found this article to be a great reference while working on something similar:

http://stevesmithblog.com/blog/reducing-sql-lookup-tables-and-function-properties-in-nhibernate/

The ideas in it should apply regardless of what DB you use. For example, in MySQL you can use the "enum" data type to enforce compliance with your coded enums:

http://dev.mysql.com/doc/refman/5.0/en/enum.html

Cheers