Click here to Skip to main content
11,629,614 members (76,834 online)
Click here to Skip to main content

Creating Enums from Database Lookup Tables

, 3 Nov 2012 CPOL 41.4K 1.9K 20
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

You may also be interested in...

Comments and Discussions

 
QuestionQuestions in implementation Pin
jaish00712-Mar-15 17:55
memberjaish00712-Mar-15 17:55 
QuestionConcern about Application has config file which already contains connection string Pin
Chaturvedi Dewashish4-Apr-14 0:01
memberChaturvedi Dewashish4-Apr-14 0:01 
GeneralMy vote of 5 Pin
Kostas Robotis15-Mar-14 5:19
memberKostas Robotis15-Mar-14 5:19 
GeneralMy vote of 5 Pin
Mahmud Mustafa6-Feb-13 8:13
memberMahmud Mustafa6-Feb-13 8:13 
Questiondownload problem Pin
saber_solomon12-Oct-12 22:06
membersaber_solomon12-Oct-12 22:06 
AnswerRe: download problem Pin
Ronen Rabinovitz3-Nov-12 19:51
memberRonen Rabinovitz3-Nov-12 19:51 
QuestionDownloading the template Pin
Member 15395448-Aug-12 4:36
memberMember 15395448-Aug-12 4:36 
GeneralThoughts Pin
PIEBALDconsult13-Jul-12 4:01
memberPIEBALDconsult13-Jul-12 4:01 
GeneralRe: Thoughts Pin
Marcus Kramer13-Jul-12 5:28
memberMarcus Kramer13-Jul-12 5:28 
GeneralRe: Thoughts Pin
Ronen Rabinovitz13-Jul-12 20:06
memberRonen 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 | Terms of Use | Mobile
Web02 | 2.8.150723.1 | Last Updated 4 Nov 2012
Article Copyright 2012 by Ronen Rabinovitz
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid