Click here to Skip to main content
Click here to Skip to main content
Go to top

Creating Enums from Database Lookup Tables

, 3 Nov 2012
Rate this:
Please Sign up or sign in to vote.
Using T4 template an easy one-click synchronization between the enum in the code and the database values

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:

Phone_Type_Id Description Some_Column_Name Some_Column_Name2
1 Samsung some value some value

We need to change it to:

Phone_Type_Id Description Some_Column_Name Some_Column_Name2 Enum_Desc
1 Samsung some value some value Samsung

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:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ronen Rabinovitz
Team Leader
Israel Israel
No Biography provided

Comments and Discussions

 
QuestionConcern about Application has config file which already contains connection string PinmemberChaturvedi Dewashish4-Apr-14 0:01 
GeneralMy vote of 5 PinmemberKostas Robotis15-Mar-14 5:19 
GeneralMy vote of 5 PinmemberMahmud Mustafa6-Feb-13 8:13 
Questiondownload problem Pinmembersaber_solomon12-Oct-12 22:06 
AnswerRe: download problem PinmemberRonen Rabinovitz3-Nov-12 19:51 
QuestionDownloading the template PinmemberMember 15395448-Aug-12 4:36 
GeneralThoughts PinmemberPIEBALDconsult13-Jul-12 4:01 
GeneralRe: Thoughts PinmemberMarcus Kramer13-Jul-12 5:28 
GeneralRe: Thoughts PinmemberRonen Rabinovitz13-Jul-12 20:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140921.1 | Last Updated 4 Nov 2012
Article Copyright 2012 by Ronen Rabinovitz
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid