Using T4 to Sync Enums with Database Lookup Tables in C#
Using T4 to Generate Enums from Database Lookup Tables
Enums are a useful way to represent a set of predefined values in code. They can make the code more readable, maintainable, and type-safe. However, sometimes we need to create enums that match the values in a database lookup table, such as a table that stores the names and codes of countries, currencies, or statuses. Manually creating and updating these enums can be tedious and error-prone, especially if the database lookup table changes frequently.
Using T4 to generate enums from database lookup tables
Fortunately, there is a way to automate this process by using T4 text templates. T4 stands for Text Template Transformation Toolkit, and it is a feature of Visual Studio that allows us to generate code or text files from templates. We can use T4 to generate enums from database lookup tables by writing a template that queries the database and creates an enum definition based on the results.
How to Use T4 to Generate Enums from Database Lookup Tables
To use T4 to generate enums from database lookup tables, we need to follow these steps:
Create a new C# class library project in Visual Studio.
Add a reference to System.Data.SqlClient and System.Configuration assemblies.
Add a connection string to the app.config file that points to the database that contains the lookup table.
Add a new item of type Text Template to the project and name it EnumGenerator.tt.
Edit the EnumGenerator.tt file and write the template code that queries the database and generates an enum.
Save the EnumGenerator.tt file and run the template by right-clicking on it and choosing Run Custom Tool.
A new file named EnumGenerator.cs will be created in the project that contains the generated enum.
Example of a T4 Template for Generating Enums from Database Lookup Tables
Here is an example of a T4 template that generates an enum named CountryCode from a database lookup table named Country that has two columns: Code and Name.
<#@ template debug=\"false\" hostspecific=\"false\" language=\"C#\" #>
<#@ assembly name=\"System.Data\" #>
<#@ assembly name=\"System.Configuration\" #>
<#@ import namespace=\"System.Data\" #>
<#@ import namespace=\"System.Data.SqlClient\" #>
<#@ import namespace=\"System.Configuration\" #>
<#@ output extension=\".cs\" #>
using System.ComponentModel.DataAnnotations;
namespace EnumGenerator
public enum CountryCode
<#
// Get the connection string from app.config
string connectionString = ConfigurationManager.ConnectionStrings[\"DefaultConnection\"].ConnectionString;
// Create a SQL command to query the Country table
string commandText = \"SELECT Code, Name FROM Country ORDER BY Code\";
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(commandText, connection))
// Open the connection and execute the command
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
// Loop through the rows and generate an enum literal for each row
while (reader.Read())
// Get the code and name values from the row
string code = reader[\"Code\"].ToString();
string name = reader[\"Name\"].ToString();
// Write an enum literal with the code as the name and the name as the display attribute
Write(\" [Display(Name = \\\"\");
Write(name);
Write(\"\\\")]\\r\\n\");
Write(\" \");
Write(code);
Write(\",\\r\\n\");
#>
The output of this template will look something like this:
using System.ComponentModel.DataAnnotations;
namespace EnumGenerator
public enum CountryCode
[Display(Name = \"Afghanistan\")]
AF,
[Display(Name = \"Albania\")]
AL,
[Display(Name = \"Algeria\")]
DZ,
// ... more countries ...
[Display(Name = \"Zambia\")]
ZM,
[Display(Name = \"Zimbabwe\")]
ZW,
Advantages of Using T4 to Generate Enums from Database Lookup Tables
Using T4 to generate enums from database lookup tables has several advantages, such as:
It saves time and effort by automating the enum creation process.
It reduces errors and inconsistencies by ensuring that the enum values match the database values.
It makes the code more maintainable by keeping the enum in sync with the database changes.
It allows customization and flexibility by letting us write our own template logic and formatting.
Conclusion
T4 text templates are a powerful tool for generating code or text files from templates. We can use them to generate enums from database lookup tables by writing a template that queries the database and creates an enum definition based on the results. This way, we can create enums that are consistent with the database values, save time and effort, reduce errors and inconsistencies, and make our code more maintainable. Using T4 to generate enums from database lookup tables is a simple and effective technique for working with predefined values in code.
How to Use the Generated Enums in Code
Once we have generated the enums from the database lookup tables using T4, we can use them in our code as we would use any other enum. For example, we can use them to assign values to variables, compare them with other values, pass them as parameters, or switch on them. We can also use the [Display] attribute to get the name of the enum value for display purposes.
Here is an example of how to use the CountryCode enum that we generated from the Country table in code:
using EnumGenerator;
namespace ConsoleApp
class Program
static void Main(string[] args)
// Assign a value to a variable
CountryCode myCountry = CountryCode.US;
// Compare with another value
if (myCountry == CountryCode.CA)
Console.WriteLine(\"You are from Canada.\");
else
Console.WriteLine(\"You are not from Canada.\");
// Pass as a parameter
PrintCountryName(myCountry);
// Switch on it
switch (myCountry)
case CountryCode.US:
Console.WriteLine(\"You are from United States.\");
break;
case CountryCode.UK:
Console.WriteLine(\"You are from United Kingdom.\");
break;
default:
Console.WriteLine(\"You are from somewhere else.\");
break;
// Get the name of the enum value using the [Display] attribute
static void PrintCountryName(CountryCode countryCode)
var displayAttribute = countryCode.GetAttribute<DisplayAttribute>();
if (displayAttribute != null)
Console.WriteLine(\"The name of your country is \" + displayAttribute.Name + \".\");
Limitations and Challenges of Using T4 to Generate Enums from Database Lookup Tables
Using T4 to generate enums from database lookup tables is a convenient and effective technique, but it also has some limitations and challenges that we need to be aware of. Some of them are:
We need to make sure that the database lookup table has a unique identifier column that can be used as the enum name. If the identifier column contains spaces or special characters, we need to replace them with valid C# identifiers.
We need to make sure that the database lookup table does not have too many rows, otherwise we might end up with a very large enum that can affect the performance and readability of our code.
We need to make sure that the database lookup table does not change too frequently, otherwise we might have to regenerate and recompile the enum every time there is a change.
We need to make sure that the database connection string is correct and secure, otherwise we might not be able to access the database or expose sensitive information.
We need to make sure that the T4 template is well-written and tested, otherwise we might generate invalid or incorrect code.
Conclusion
T4 text templates are a powerful tool for generating code or text files from templates. We can use them to generate enums from database lookup tables by writing a template that queries the database and creates an enum definition based on the results. This way, we can create enums that are consistent with the database values, save time and effort, reduce errors and inconsistencies, and make our code more maintainable. Using T4 to generate enums from database lookup tables is a simple and effective technique for working with predefined values in code.
How to Customize the T4 Template for Generating Enums from Database Lookup Tables
The T4 template that we used to generate enums from database lookup tables is reusable and flexible, but we might want to customize it to suit our specific needs. For example, we might want to change the namespace, the enum name, the attribute, or the formatting of the generated code. We can do that by modifying the template code and adding or changing some parameters.
Here are some examples of how to customize the T4 template for generating enums from database lookup tables:
To change the namespace of the generated enum, we can change the line namespace EnumGenerator to namespace MyNamespace.
To change the name of the generated enum, we can change the line EnumBuilder myEnum = moduleBuilder.DefineEnum (\"EnumeratedTypes.MyEnum\", TypeAttributes.Public, typeof (int)); to EnumBuilder myEnum = moduleBuilder.DefineEnum (\"EnumeratedTypes.MyNewEnum\", TypeAttributes.Public, typeof (int));.
To change the attribute of the generated enum values, we can change the line Write(\" [Display(Name = \\\"\"); to Write(\" [Description(\\\"\");.
To change the formatting of the generated code, we can change the indentation, spacing, or line breaks in the template code.
How to Use T4 Templates for Other Purposes
T4 templates are not only useful for generating enums from database lookup tables, but also for other purposes that involve generating code or text files from templates. For example, we can use T4 templates to generate:
Data access layer classes from database tables or stored procedures.
Model classes from XML schemas or JSON files.
HTML pages from Razor views or Markdown files.
Documentation files from code comments or annotations.
Test cases from scenarios or specifications.
To use T4 templates for other purposes, we need to follow these steps:
Create a new C# class library project in Visual Studio.
Add references to any assemblies that are needed for accessing or processing the data source.
Add a connection string or a file path to the app.config file that points to the data source.
Add a new item of type Text Template to the project and name it according to the purpose.
Edit the template file and write the template code that accesses or processes the data source and generates the output file.
Save the template file and run it by right-clicking on it and choosing Run Custom Tool.
A new file with the same name as the template but with a different extension will be created in the project that contains the generated output.
Conclusion
T4 text templates are a powerful tool for generating code or text files from templates. We can use them to generate enums from database lookup tables by writing a template that queries the database and creates an enum definition based on the results. We can also customize the template to suit our specific needs by modifying some parameters. We can also use T4 templates for other purposes that involve generating code or text files from different data sources. Using T4 templates is a simple and effective technique for automating code or text generation tasks.
Conclusion
T4 text templates are a powerful tool for generating code or text files from templates. We can use them to generate enums from database lookup tables by writing a template that queries the database and creates an enum definition based on the results. This way, we can create enums that are consistent with the database values, save time and effort, reduce errors and inconsistencies, and make our code more maintainable. We can also customize the template to suit our specific needs by modifying some parameters. We can also use T4 templates for other purposes that involve generating code or text files from different data sources. Using T4 templates is a simple and effective technique for automating code or text generation tasks. ca3e7ad8fd