3

At the moment I have a SQL Server 2005 table that looks a bit like:

ID | name  | desc
----------------------
1  | ONE   | Value One
3  | THREE | Value Three
5  | FIVE  | Value Five

This table corresponds with an enum in C# that looks like:

enum MsgTypes{
  <summary>Value One</summary>
  ONE = 1,
  <summary>Value Three</summary>
  THREE = 3,
  <summary>Value Five</summary>
  FIVE = 5
}

So my question is this: Is there a good way to associate the enum to the SQL table so that any changes/additions to the values in the table don't need to be manually made in the c# code?

chezy525
  • 4,025
  • 6
  • 28
  • 41
  • What kinds of changes in the table would need to be mirrored in the C# code? Are you looking at potentially changing the ID associated with `MsgTypes.ONE`? – StriplingWarrior Dec 01 '10 at 20:25
  • In my situation, I think the ID will be the least likely to change (though I don't want to say it's impossible). More probable situations are that the name changes or a new one is added. i.e. `MsgTypes.ONE` is changed to `MsgTypes.UNO` or `MsgTypes.TWO` is created. – chezy525 Dec 01 '10 at 20:49

5 Answers5

11

If you want it to be somewhat dynamic, why make it an enum to start with? Just fetch the details from the table on app startup, and remember them in (say) a Dictionary<int, string>. You could always encapsulate the value within your own value type which enforced the range, if you wanted to.

Alternatively, if you don't mind recompiling, you could fetch it at build time and autogenerate the enum source code.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
3

I had to have a think about something similar recently (refactoring an enum) -- basically I considered using a Dictionary<A, B> to store the enum values in. You could dynamically load from the table to populate the dictionary if you wanted to.

One thing I'd add -- is if you're replacing an enum that already exists with something dynamic you'll have to think about what you're going to do with exceptions raised as part of populating it dynamically.

Tim Barrass
  • 4,813
  • 2
  • 29
  • 55
1

To me it depends on how often the enums/DB lookup tables change. We have about a half dozen enum/lookups like this in our system, and i don't mind recompiling to add an emum option + DB row becuase:

  1. It doesn't happen very often - probably twice in the past year that i can think of
  2. There is usually new business logic surrounding the new option so coding is necessary anyway.
n8wrl
  • 19,439
  • 4
  • 63
  • 103
0

Another alternative would to implement a custom object with ID, Name, and Desc properties that would encapsulate the database table.

dparker
  • 1,082
  • 1
  • 9
  • 14
0

I accept with what Jon is suggesting, but then if you prefer to have your Enum list in the DB and want to use them in your code, you could use TypeTable feature from nHydrate for your project.