Database Constants in a Class

Question

If I have, say, a table of films which amongs other things has a int FilmTypeId field and a table of film types, with the id and a meaningful description along the lines of:

• 1 - horror
• 2 - comedy
• ...

Whats the best way of using that information in a C# class?

currently I would have them as Constants in a helper class (unsuprisingly air code):

public class FilmHelper
{
    public const int HorrorFilmType = 1;
    public const int ComedyFilmType = 2;
    ...
}

but this doesn't seem that maintainable. But I would like to avoid a database call everytime I came to use the constant or an additional db call everytime I used either the helper or the main entity.

Answer 1

Is the list of types fixed or does it change?

If fixed, I would encapsulate it in an enum:

public enum FilmType {
   Horror = 1,
   Comedy = 2
}

Then just cast. You can use attributes (and a few lines of bespoke code) to store an extra description per enum item.

If the list changes I would probably read it once early on in the app, and cache the list in a lookup somewhere (perhaps static, perhaps in a specific cache). Then just do the lookups from the cached copy.

You can take this further by adding properties that change between the two representations.

Answer 2

You can use a strong type without strongly-typing the values.

class FilmType : Dictionary<int, string> {}

Then just load the values at app load time, as Joel suggested.

If you're more concerned about the actual values at compile-time, then you're not going to do better than an enum.

Answer 3

My answer assumes you want to explicitly code against those values in your database.

What I've done in the past is to build a script that creates my constants files. (I also had a SQL script that would output C# code when run with a text output so I could just paste it into the file and use that constant right away.)

If you do this, you can make it part of your continuous integration build and just get latest on that file when a build is done.

One thing to be aware of here is that when you delete rows from that table, some code may become unreachable/dead and, depending on how you implement your script, builds will break (this is a good thing to force you to remove the dead code).