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

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 2

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 3

You don't need to do anything if you want to store ints. Just map your property in EF. If you want to store them as strings use converter.

Int (db type is smallint):

public override void Configure(EntityTypeBuilder<MyEfEntity> b)
{
    ...
    b.Property(x => x.EnumStatus);
}

String (db type is varchar(50)):

public override void Configure(EntityTypeBuilder<MyEfEntity> b)
{
    ...
    b.Property(x => x.EnumStatus).HasConversion<EnumToStringConverter>();
}

If you want to save your db data usage use smallint as a column in db. But data won't be human readable and you should set an index against every enum item and never mess with them:

public enum EnumStatus
{
    Active = 0, // Never change this index
    Archived = 1, // Never change this index
}

If you want to make data in db more readable you can save them as strings (e.g. varchar(50)). You don't have to worry about indexes and you just need update strings in db when you change enum names. Cons: column size gets data usage more expensive. It means if you got a table within 1,000,000 rows it might have an impact on db size and performance.

Also as a solution you can use short enum names:

public enum EnumStatus
{
    [Display(Name = "Active")]
    Act,
    [Display(Name = "Archived")]
    Arc,
}

Or use your own converter to make names in db shorter:

public enum EnumStatus
{
    [Display(Name = "Active", ShortName = "Act")]
    Active,
    [Display(Name = "Archived", ShortName = "Arc")]
    Archived,
}
...
public override void Configure(EntityTypeBuilder<MyEfEntity> b)
{
    ...
    b.Property(x => x.EnumStatus).HasConversion<MyShortEnumsConverter>();
}

More info can be found here: EF: https://docs.microsoft.com/en-us/ef/ef6/modeling/code-first/data-types/enums EFCore: https://docs.microsoft.com/en-us/ef/core/modeling/value-conversions

Answer 4

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 5

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

Answer 6

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.