How to keep a C# Enum in sync with a table in database

This is a somewhat simplified example (I changed it around to hide the actual code). I have a database-powered app and a small tool that is being developed separately that is meant to work with the app. There is a table that defines an enumeration, but it could potentially change over time. Suppose some app (medical?) needed to track the sex of a person rather precisely.

select * from sex order by id;

id | mnemonic | description
0  | U        | Unknown sex
1  | M        | Male
2  | F        | Female
3  | T        | Trans-gender

And my C# enum:

public enum SexType
{
    /// <summary>Unknown sex.</summary>
    [Description("U")]
    Unknown = 0,

    /// <summary>Male sex.</summary>
    [Description("M")]
    Male = 1,

    /// <summary>Female sex.</summary>
    [Description("F")]
    Female = 2

    /// <summary>Trans-gender sex.</summary>
    [Description("T")]
    TransGender = 3,
}

Here I should not assume that the id is a continuous sequence; neither are these enums flags that can be combined, even though it might look that way.

Some of the logic is done in SQL; some is done in C# code. For instance, I might have a function:

// Here we get id for some record from the database.
public static void IsMaleOrFemale(int sexId)
{
    if (!Enum.IsDefined(typeof(SexType), sexId))
    {
        string message = String.Format("There is no sex type with id {0}.", 
            sexId);
        throw new ArgumentException(message, "sexId");
    }

    var sexType = (SexType) sexId;
    return sexType == SexType.Male || sexType == SexType.Female;
}

This can work fairly well as long as neither the table nor the enum definitions change. But the table might. I cannot rely on the id column or the mnemonic column maintaining their values. I think the best I can to is have a unit test which makes sure that the table is in sync with my definition of an enum. I am trying to match the value to id, and the description attribute to mnemonic column.

So, how to I get a list of all pairs (programmatically, in C#): (0, "U"), (1, "M"), (2, "F"), (3, "T") by looking at the enum SexType?

The idea is to tightly compare this ordered list with select id, mnemonic from sex order by is asc;.

Why not change SexType from an enum to a class (or Struct) and populate a List from your database at runtime?

Your struct (or class) would look something like this:

public struct SexType
{
  public string Type;
  public string Code;
  public int Value;
}

Then you could populate from your database a List<SexType> (or, if you're using EF, you could pull down a list of Entities of type SexType, or whatever your solution allows).

Presuming you're using Linq and EF, do eager loading when the application starts, and you should be good-to-go.

Check out Tangible T4Editor.

I use it to do just that.

Install it and then add this file to your project (more information on this blog post):

EnumGenerator.ttinclude

<#@ template debug="true" hostSpecific="true" #>
<#@ output extension=".generated.cs" #>
<#@ Assembly Name="System.Data" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#  
    string tableName = Path.GetFileNameWithoutExtension(Host.TemplateFile);
    string path = Path.GetDirectoryName(Host.TemplateFile);
    string columnId = "ID";
    string columnName = "NAME";
    string connectionString = "[your connection string]";
#>
using System; 
using System.CodeDom.Compiler;  

namespace Your.NameSpace.Enums
{     
    /// <summary>
    /// <#= tableName #> auto generated enumeration
    /// </summary>
    [GeneratedCode("TextTemplatingFileGenerator", "10")]
    public enum <#= tableName #>
    { 
<#
    SqlConnection conn = new SqlConnection(connectionString);
    string command = string.Format("select {0}, {1} from {2} order by {0}", columnId, columnName, tableName);
    SqlCommand comm = new SqlCommand(command, conn);

    conn.Open();

    SqlDataReader reader = comm.ExecuteReader();
    bool loop = reader.Read(); 

    while(loop)
    { 
#>
        /// <summary>
        /// <#= reader[columnName] #> configuration setting.
        /// </summary>
        <#= reader[columnName] #> = <#= reader[columnId] #><# loop = reader.Read(); #><#= loop ? ",\r\n" : string.Empty #> 
<#  } 
#>  }
} 
<#+     private string Pascalize(object value)
        {
            Regex rx = new Regex(@"(?:^|[^a-zA-Z]+)(?<first>[a-zA-Z])(?<reminder>[a-zA-Z0-9]+)");
            return rx.Replace(value.ToString(), m => m.Groups["first"].ToString().ToUpper() + m.Groups["reminder"].ToString().ToLower());
        }      

        private string GetSubNamespace()
        {
            Regex rx = new Regex(@"(?:.+Services\s)");
            string path = Path.GetDirectoryName(Host.TemplateFile);
            return rx.Replace(path, string.Empty).Replace("\\", ".");
        }
#>

(fill in the namespace of your class and the connection string)

Then, you can just add a blank TT file with a single line <#@ include file="EnumGenerator.ttinclude" #>`. The name of this file should be identical to the table's name, and the columns of this table must be named "ID" and "NAME" unless you change it in the enum generator class.

Whenever you save the TT file, the Enum will be auto generated.

var choices = Enumerable.Zip(
    Enum.GetNames(typeof(SexType)),
    Enum.GetValues(typeof(SexType)).Cast<SexType>(),
    (name, value) => Tuple.Create(name, value));

It would be easier if you just named the enum values U, M, F and T. If you did that, the Enum class's static methods do all the work for you.

Barring that, you need to use a bit of reflection to dig out the Description attributes

public IEnumerable<Tuple<string, int>> GetEnumValuePairs(Type enumType)
{
    if(!enumType.IsEnum)
    {
        throw new ArgumentException();
    }

    List<Tuple<string, int>> result = new List<Tuple<string, int>>();

    foreach (var value in Enum.GetValues(enumType))
    {
        string fieldName = Enum.GetName(enumType, value);

        FieldInfo fieldInfo = enumType.GetField(fieldName);
        var descAttribute = fieldInfo.GetCustomAttributes(false).Where(a => a is DescriptionAttribute).Cast<DescriptionAttribute>().FirstOrDefault();

        // ideally check if descAttribute is null here
        result.Add(Tuple.Create(descAttribute.Description, (int)value));
    }

    return result;
}

I would use a T4 Text Template which would dynamically generate the enumeration for you when the template is processed (on save and/or build).

Then, this template (which includes C# code) will generate the enumeration definition for you into a .cs file based on the contents of your database.

Of course, it means you will have to have access to a database at save/compile time, but this is the only way to guarantee that these two are in line at compile time.

At runtime, you might want to perform a one-time check against the database to make sure that the enumeration and the values in the database are in line.

List<Tuple<int, string>> pairs = new List<Tuple<int,string>>();
     Type enumType = typeof(SexType);
     foreach (SexType enumValue in Enum.GetValues(enumType))
     {
        var customAttributes = enumType.GetField(Enum.GetName(enumType, enumValue)).
           GetCustomAttributes(typeof(DescriptionAttribute), false);

        DescriptionAttribute descriptionAttribute = 
           (DescriptionAttribute)customAttributes.FirstOrDefault(attr => 
              attr is DescriptionAttribute);

        if (descriptionAttribute != null)
           pairs.Add(new Tuple<int, string>((int)enumValue, descriptionAttribute.Description));
     }