Creating Enums from Database Lookup Tables

• Sample project EnumFromDBProject.zip - 40.4 KB
• T4 Template - DBEnums.zip - 1.4 KB

When developing code with a database, we usually need to use some values from lookup tables in the code and use them for compare or set some properties.

For example, If you use the following line in code:

if(Car.Car_Type == 5){...

Then First, nobody will understand what is the number-5 is meaning, and if we change the value in the database from 5 to something else, we will get a bug.

So the right way is to use Enum:

 if(Car.Car_Type ==  (byte)CarTypesEnum.SmallCars ){...

The problem is the difficulty on maintenance on this Enum, when database values are changing. This is when the T4 template help as:

If you're using Entity Framework you can generate the Enum properties in the entities to avoid the casting in the condition (see this tip, it a start points to write your one code)

Background

So, I was needed to create Enums from lookup tables. I find some solutions(like this link and this link) for that, including some T4 solutions, but they all have some problems: they don't support non-English language's and they required some changes in the database table columns names.

My T4 template has these advantages:

1. Sporting non-English language's values in the Enum description column, In my case the lookup tables description was in Hebrew, and I definitely do not want some Hebrew values in my Enums code.
2. No need to write hard coded names of the enum tables that you need to generate.
3. No need to change the column names in the table.
4. Easy one-click synchronization between the code and the database values.
5. Minor changes on the code to insert and use this temple.

The Solution

You need to add some, fix name, column in yore lookup tables and fill, in this new column ,the enum values, then run the template and all the Enum will be created.

For Example:

Table before the change:

We need to change it to:

Using the Code

To insert the temple to your project you need to follow these steps:

In .NET solution:

1. Open the template (.tt extension) and set the connection string to the database:

   //Database connection string
   var connectionString = "data source=localhost;initial catalog=EF_Extention_Sample;integrated security=True;multipleactiveresultsets=True;";

2. Set the column name that indicates that the table is an Enum table:

    //Enum value column name:
   var enumDescriptionColumnName = "Enum_Description";

3. Set the Enum's NameSpace:

   //Enum namespace
   var enumNameSpace = "MyNamespace";

   In Database:

4. Add the column (in section 2) to all the tables in your database that need to generate to Enum.
5. Insert the Enum description values in the new column [Enum_Description], for all the Enum tables. If the value of the [Enum_Description] column is null than the value from the lookup table row will not be generated in the Enum (That allow you to insert only relative rows to the Enum).

Last, Go back to the Template file and right click->Run Custom Tools - This will generate the Enums.

Do this last step whenever you need to synchronize between the database values and the code.

The Template

The steps to create the database Enums are:

I Select all the tables with the [Enum_Column_Name]:

command.CommandText = string.Format(@"SELECT DISTINCT t.NAME 
					FROM SYS.tables as t
					INNER JOIN SYS.columns AS c ON t.object_id = c.object_id
					AND c.name = '{0}'",enumDescriptionColumnName); 

For each table in the SELECT result, I create the Enum.

Then, run on the values of each lookup table to fill the Enum values.

* I am assuming that the first column in the lookup table is the primary key of the table.

//Start write enum file:		
#>
namespace <#=enumNameSpace#>
{
	public enum <#=enumName#>Enum
	{
	<#
	command.CommandText = string.Format("SELECT * FROM {0}",codeGenerationTools.Escape(tableName));
    var columnReader = command.ExecuteReader();
    while (columnReader.Read())
    {
		//Fill only the values that the field "enumDescriptionColumnName" have value
		if(!string.IsNullOrEmpty(columnReader[enumDescriptionColumnName].ToString().Trim()))
		{
	#>	<#=columnReader[enumDescriptionColumnName].ToString()#> = <#=columnReader[0].ToString()#>,
	<#	}
	}#>
}
}
<#

For more information about T4 template and Entity Framework use of them, you can see these links:

• Code Generation and T4 Text Templates
• Generated Code Overview - Entity Framework

Enjoy