How to create ENUM using values from the database C#

Question

I have a status table in database whose values I am using across the application. Status Table will have (ID, NAME). I want to create a StatusEnum which I can use in my code in the application. How can I create ENUM using values from database ?

Currently I have enum like this

enum StatusCode: int
{
    Open = 20,
    Received = 21,
    Delivered= 22,
    Cancelled = 23
}

But I want to set the values from database.

Answer 1

You can

• Manually keep your enum definition in sync with the database. This is the easiest way.

• You could write a single file generator for Visual Studio (also known as a "Custom Tool") and have that generate the enum definition from some reference database. Single File Generators take some source file (e.g., *.aspx) and from it generate code (e.g. *.designer.cs). Very useful to be able to do this.

• A third technique would be a hermaphrodite variant of the preceding two techniques: write a stand-alone tool to generate your enum definitions from the database. Run the tool against your reference DB to regenerate the file and check it back in.

Either way, you can't change the lookup tables in the database will-nilly without affecting your application. Newly-added values will be unknown to the app; removed or changed values will likely break things.

But presumably, the sorts of things you would want an enum for are all relatively stable sorts of things.

Answer 2

I am going to attempt an answer.

If the idea is that you have magic numbers in your database, which are const in nature, meaning they rarely change, I can see why you would want to represent them as something readable in code.

Depending on how you would query the data, there are different ways of representing this data as an enum value: For a SqlDataReader just upcast the integer value coming from the record to the enum:

int statusCodeValue = row["status"];
// But BEWARE as undefined values also upcast to the enum just fine
// but just won't have any of the defined values
if (!Enum.IsDefined(typeof(StatusCode), statusCodeValue)) throw new Exception();
StatusCode statusCode = (StatusCode)statusCodeValue;

If you're using an ORM like nHibernate or EF, you get this mapping out of the box. Just define the enum property on your data model, and it will correctly map.

Note

You need to think about who is the owner of the data. Is this data coming from another system, or does your application generate it?

If it's foreign, you'll need to keep the data and your application enum in sync manually. If it's local, just make sure to assign an integer value to each enum member, and never change that integer value from what it should semantically represent.

Answer 3

You really can't do this. You will need some helper functions to help you along.

enum StatusCode
{
   Open,
   Received,
   Delivered,
   Cancelled
}

private Dictionary<StatusCode, int> storedCodes = new Dictionary<StatusCode, int>();

public static int GetValue(StatusCode code)
{
    //Return code from database
    return storedCodes[code];
}

public static void SetValue(StatusCode code, int value)
{
    storedCodes[code] = value;
    //Set values from database
    //Note:  you can't set the value of your enums here
    //Just a place to set some other variables to remember what the values are.
}

Answer 4

What you are asking is technically possible with text templating (an example is after the fold). However, as has been pointed out, good text templating isn't easy and usually requires just as much maintenance as your application code.

Instead, take a look and determine whether your Enum is really a constrained list of all possible states. If you are storing your statuses in a database table, I would say that it is not. Being in a table makes it technically possible that these values will change (new ones can be inserted, old ones deleted or even the names changed such that they no longer reflect their original meaning). Only user convention will prevent this, and it won't be immediately apparent in your application code.

You might be better off rethinking your database design. The next statement is tricky for me to explain, but I prefer not to infer entity status based on a child relationship. Instead I prefer to make code decisions based individual column value of a given data row.

For example, rather than two tables, as per:

CREATE TABLE status_codes (
  key INT PRIMARY KEY, 
  value VARCHAR(32)
);
CREATE TABLE entity (
  id INT PRIMARY KEY, 
  name VARCHAR(32), 
  status INT 
  CONSTRAINT fk_status FOREIGN KEY status REFERENCES status_codes(id)
);
INSERT INTO status_codes 
VALUES (20, 'Open')
      ,(21, 'Received')
      ,(22, 'Delivered')
      ,(23, 'Cancelled');

I would prefer:

CREATE TABLE entity (
  id INT,
  name VARCHAR(32),
  is_received BIT NOT NULL,
  is_delivered BIT NOT NULL,
  is_cancelled BIT NOT NULL,
  is_closed BIT NOT NULL
);

For me, I find it easier to code the logic when there are no magic numbers.

This way you are no longer doing things like:

/* SQL */
SELECT * FROM entity WHERE status = 20;

/* C# */
if (entity.Status == StatusCodes.Open) { /* do something */ }
if (entity.Status == 20) { /* do something */ }

and instead doing:

/* SQL */
SELECT * FROM entity WHERE is_closed = 0;

/* C# */
if (!entity.IsClosed) { /* do something */ }

You can also employ database constraints for process (e.g. ensuring an item cannot be marked received before it is delivered, and cannot be cancelled if it has already been delivered).

I've also changed 'is open' semantics to 'is closed', but that is just a personal preference to make doing something (i.e. closing) be more significant than not done anything (i.e. is open).

I also note that sometimes you do need 'soft', user-maintainable states for things. However, I would suggest that these should be for display purposes only and that you shouldn't write 'hard' code around these 'soft' states.

(If your application is intended to be a highly-customisable, off-the-shelf product, you might look at making soft states available to a scripting or rules engine, so the users who define the states can also define the business rules around them, but this is well outside of scope.)

---

With all that said, if you really, really feel that you need to do what you are asking, one possible approach is below...

Firstly, you need a canonical source for your enum values. Either your C# Enum is correct, or the SQL definition is correct.

Once this is decided, I would probably use some form of text templating, like T4 or a custom script file/compiled exe to generate the one that isn't the canonical source.

C# code is canonical

Reflect your Enum using Enum.GetValues(typeof(StatusCode)) and Enum.GetName(typeof(StatusCode), value), then use this to generate a CHECK constraint in your target table. Store the base int type in your database table (e.g. ALTER TABLE my_table ADD status_code INT).

// (untested, pseudo-ish code -- WATCH FOR INJECTION!)

StringBuilder sql = new StringBuilder();
sql.Append(@"ALTER TABLE [my_table] ADD CONSTRAINT chk_status_code CHECK (status_code IN (");

bool first = true;
foreach (var v in Enum.GetValues(typeof(StatusCode)) {
    if (!first) sql.Append(", ");
    sql.Append(v);
    first = false;
}

sql.Append("));");

// write sql to file, or run against the development database

This would get you very close to a SQL statement that could be run on build / install. Note that this is not intended to be run during the normal operation of your database.

If you need the functionality, you may also want to generate an inline table function to map number to name, e.g.:

// (untested, pseudo-ish code -- WATCH FOR INJECTION!)

StringBuilder sql = new StringBuilder();
sql.AppendLine(@"IF OBJECT_ID('dbo.tf_status_codes') IS NULL EXECUTE('
                     CREATE FUNCTION dbo.tf_status_codes RETURNS TABLE AS RETURN (
                         SELECT ''not yet built'' AS err
                     )
                 ')");
sql.AppendLine(@"ALTER FUNCTION dbo.tf_status_codes RETURNS TABLE AS RETURN (")
   .AppendLine(@"  SELECT value, name FROM (VALUES ")

bool first = true;
foreach (var v in Enum.GetValues(typeof(StatusCode)) {
    if (!first) sql.AppendLine(",");
    sql.Append(@"    ({0}, '{1}')", 
               v,
               Enum.GetName(typeof(StatusCode), v));
    first = false;
}

sql.AppendLine(@"  ) e(value, name);")
   .AppendLine(@")";

// write sql to file, or run against the development database

Run the build tool as a Post-Build event, so the code is updated before the constraint / table.

SQL table is canonical

This one is more straight-forward, although I would make sure that the table source cannot change during the lifetime of the production iteration (i.e. only defined at deployment time). For this, I would define my enum values as an inline table function, rather than a table:

CREATE FUNCTION dbo.status_codes 
RETURNS TABLE 
AS RETURN (
    SELECT value, name
    FROM (VALUES (20, 'Open')
                ,(21, 'Received')
                ,(22, 'Delivered')
                ,(23, 'Cancelled')) AS v(value, name)
)

Then, in my build tool, connect to the database, retrieve the values and generate the enum:

// untested, pseudo, assumes an existing database connection routine 
IDataReader reader = DB.GetReader("SELECT value, name FROM dbo.status_codes()");

StringBuilder code = new StringBuilder();
code.AppendLine("namespace MyApp {")
    .AppendLine("  public enum StatusCodes : int {");

bool first = true
while (reader.Read()) {
    if (!first) code.AppendLine(",");
    code.Append("    {0} = {1}", reader["name"], reader["value"]);
    first = false;
}

code.AppendLine("  }")
    .AppendLine("}");

// ...write the code to the Enum class file, and exit with 0 code

Run the build tool as a Pre-Build event (so the code is generated before it is built).

(As I said, the code above is untested and no attempt has been made to secure it for injection. Use at your own risk and test thoroughly)