c# handling lookup values from database

Question

When you have a lookup table in a sql database. If you wanted to use the value in an expression would you use the primary key (integer) or the actual value of the record. Sorry if thats a bit vague. Ill show you an example to explain.

sql table

id | name
1  |  incomplete
2  |  submitted
3  |  approved

in the c# code which would be more correct

if(id == 1){
  //do something
}

or

if(name == 'incomplete'){
  //do something
}

or

if(id == (int)states.incomplete){
 //do something
}

generally ive used the enum example but would like some clarrification if thats correct

Thanks in advance

Answer 1

Your second and third examples give the reader the most information about what the condition is trying to check for.
I would tend to agree with Daniel that you should use an enum, however, there is no guarantee that in the future, someone else without your knowledge will come and do something like

public enum state
{
    incomplete,
    someNEWState,
    submitted,
    approved
}

In these scenarios, I prefer to go with a string enum approach for paranoid safety. You never know what you might do in 3 months time when you come back to fix the code.
If you want to keep it simple, i'd go with the string checking. It's better to get an error than incorrectly trigger the statement.

Answer 2

Because it is a lookup table, it is safe to assume, that the value doesn't change (much).
Personally, in scenarios like that, I use the last way with the enum and document, that the values of the enum have to correspond with the values in the table.

Answer 3

i'd go with 3 but using a switch

switch (id)
{
    case (int)states.incomplete: 
        Console.WriteLine("incomplete");
        break;
    default:
        Console.WriteLine("Default case");
        break;
}

Answer 4

You know, if it comes to efficiency comapring integers is faster than comparing string values, the approach with enums is OK, it clarifies the code.

Answer 5

All of them are correct but
1) other person when look at your code will have no idea what 1 means in your code
2) string comapre is slower than numbers
3) the best way + you have auto switch snippet :)

Answer 6

[academic theory disclaimer] :-)

not that i've ever tried it, however, you could create a dictionary that was keyed on the id and contained an action delegate. this would save the need to use case statements or if's etc, etc.

let the madness begin:

class Program
{
    // example methods
    static void Method1(string message)
    {
        Console.WriteLine(message);
    }
    static void Method2(int id, string message)
    {
        Console.WriteLine(string.Format("id: {0} message: {1}", id, message));
    }
    static void Method3()
    {
        Console.WriteLine("submitted");
    }

    static void Main(string[] args)
    {
        int targetId = 2; // or passed in id etc

        // add the actions with examples of params
        // and non param methods
        var actions = 
            new Dictionary<int, Action>
                  {
                      {1, () => Method1("incomplete")}, 
                      {2, () => Method2(targetId, "approved")},
                      {3, () => Method3()}
                  };

        if (actions.ContainsKey(targetId))
        {
            actions[targetId].Invoke();
        }
        else
        {
            // no method defined for this action
            // do something about it
            Console.WriteLine("no action defined here yet");
        }
        Console.ReadKey();
    }
}

obviously, the Methodn 'methods' would be your proper 'bespoke' methods in each case.

[edit] - here's a little SO link in support of such a theory:

C# switch statement limitations - why?