Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / C#

Creating Enums from Database Lookup Tables

Rate me:
Please Sign up or sign in to vote.
4.70/5 (9 votes)
3 Nov 2012CPOL3 min read 104.1K   3.5K   30   12
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:

C#
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:

C#
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:
    C#
    //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:
    C#
     //Enum value column name:
    var enumDescriptionColumnName = "Enum_Description";
  3. Set the Enum's NameSpace:
    C#
    //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]:

C#
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.

C#
//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)


Written By
Chief Technology Officer Consist Systems
Israel Israel
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionNot very clear. Pin
sunayv28-Apr-17 4:50
sunayv28-Apr-17 4:50 
QuestionSchemas Pin
Tod Palin18-Jan-16 0:49
Tod Palin18-Jan-16 0:49 
QuestionQuestions in implementation Pin
jaish00712-Mar-15 17:55
jaish00712-Mar-15 17:55 
QuestionConcern about Application has config file which already contains connection string Pin
Chaturvedi Dewashish4-Apr-14 0:01
Chaturvedi Dewashish4-Apr-14 0:01 
GeneralMy vote of 5 Pin
Kostas Robotis15-Mar-14 5:19
Kostas Robotis15-Mar-14 5:19 
GeneralMy vote of 5 Pin
Mahmud Mostafa6-Feb-13 8:13
Mahmud Mostafa6-Feb-13 8:13 
Questiondownload problem Pin
saber_solomon12-Oct-12 22:06
saber_solomon12-Oct-12 22:06 
AnswerRe: download problem Pin
Ronen Rabinovitz3-Nov-12 19:51
Ronen Rabinovitz3-Nov-12 19:51 
I fixed the links
QuestionDownloading the template Pin
Member 15395448-Aug-12 4:36
Member 15395448-Aug-12 4:36 
GeneralThoughts Pin
PIEBALDconsult13-Jul-12 4:01
mvePIEBALDconsult13-Jul-12 4:01 
GeneralRe: Thoughts Pin
fjdiewornncalwe13-Jul-12 5:28
professionalfjdiewornncalwe13-Jul-12 5:28 
GeneralRe: Thoughts Pin
Ronen Rabinovitz13-Jul-12 20:06
Ronen Rabinovitz13-Jul-12 20:06 

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

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