Enum Bound to Database

Question

When writing code for existing applications, often times the development database environment does not match the production environment - and even worse, there are times still where overlaying the environments is just not an option.

One idea I had in mind to code for all environments would be to use a databound enum, whose values would be bound to the ID of the data item they represent. I couldn't get that to work with an Enum but I was able to solve it via abstract classes. For example:

public abstract class Colors
{
    private static readonly string c_red    = "red";
    private static readonly string c_blue   = "blue";
    private static readonly string c_yellow = "yellow";
    private static readonly string c_green  = "green";

    private static int? _red    = null;
    private static int? _blue   = null;
    private static int? _yellow = null;
    private static int? _green  = null;

    public static int Red
    {
        get
        {
            if (_red == null)
                _red = GetColorID(c_red);

            return (int)_red;
        }
    }
    public static int Blue
    {
        get
        {
            if (_blue == null)
                _blue = GetColorID(c_blue);

            return (int)_blue;
        }
    }
    public static int Yellow
    {
        get
        {
            if (_yellow == null)
                _yellow = GetColorID(c_yellow);

            return (int)_yellow;
        }
    }
    public static int Green
    {
        get
        {
            if (_green == null)
                _green = GetColorID(c_green);

            return (int)_green;
        }
    }

    private static int GetColorID(string identifier)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Demo"].ConnectionString))
        {
            conn.Open();

            using (SqlCommand cmd = new SqlCommand("spGetColorId", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("Name", identifier);

                return Convert.ToInt32(cmd.ExecuteScalar());
            }
        }
    }
}

By doing it this way, I'm able to call Colors.Red in this example to get the ID of Red regardless of if I'm in Dev, Testing, or Production.

My question is: is this really the ideal method of accomplishing this? Is there a native-to-C# method of databinding enums, or something equivalent to what I'm doing above?

Answer 1

Having an enum implies that the values are rarely, if ever, changes. you can think of it as a closed list of values (like days of the week etc.).

Because of this nature of enums, I find it acceptable to have this redundancy of having the enum underlying value being specified twice (once in the DB and the other in the enum itself).

If you are worried about discrepancies, you can run a validation for the values when your application starts and check that the values has the correct corresponding ids and that the number of values in the enum matches the number of values in the DB.