T4 template for generating SQL view from C# enumeration

Database tables often contain columns that store “codes”, or values with special meaning. In application code, these special values can be encapsulated as enumerations. This article demonstrates how to use T4 code generation templates and Visual Studio CodeModel API to generate SQL views that encapsulate the special values for use in SQL code.

Overview

In relational database design, tables often contain columns that store values with special meaning,  often referred to as “codes”. For example, a CRM system may use the following tables to store information about contacts and their addresses.

create table Contact(    Id int not null,    Type tinyint not null,    Name varchar(50) not null    constraint PK_Contact primary key (Id))

create table Address(    Id int not null,    Type tinyint not null,    ContactId int not null,    Line1 varchar(50) not null,    Line2 varchar(50),    City varchar(50) not null,    State varchar(50) not null,    Zip varchar(10) not null    constraint PK_Address primary key (Id)    constraint FK_Address_Contact foreign key (ContactId) references Contact (Id))

In this database design, Type column of the Contact table stores codes 0 or 1 to to indicate whether a contact is an individual or an organization. In the Address table, the Type column also stores codes 0 or 1 to indicate whether an address is for billing or shipping. 

Special values in application code

Although we can use literal values 0 and 1 when accessing Contact and Address data in application code, C# and other modern languages allow us to define enumerations that encapsulate these codes and refer to them by name.

public enum ContactType{    Individual = 0,    Organization = 1}

public enum AddressType{    Billing = 0,    Shipping = 1}

With enumerations defined above, we would use ContactType.Organization instead of special code 1 when accessing Type column of the Contact table. Using enumerations instead of hard-coded literals makes the application code easier to read, but more importantly, it makes the application code easier to maintain. If, for whatever reason, we need to change value of the Shipping code from 1 to 2, we can simply change definition of the AddressType enumeration. On the other hand, if we need to change meaning of the code from Shipping to Mailing, we can change definition of the AddressType enumeration and let the compiler help us find all places in the application code where it was referenced and modify them to use the new name.

Special values in SQL scripts

Unfortunately, there is no direct support for enumerations in SQL, often forcing developers to hard-code special values in SQL scripts - stored procedures, triggers, functions and reports. For example, a report that prints a list of  corporate customers (contacts of with Type of Organization), could use the following SQL query.

select * from Contactwhere Type = 1

This code is difficult to read - unless you know that Type 1 means Organization, you will not be able to understand it. A select statement that uses both Contact type and Address type codes would be even more confusing because the same code values mean different things for different tables, making it easy to use one instead of the other.

This code is also difficult to maintain. To change Organization code value we would need to review all SQL code in stored procedures, triggers, scripts and reports that accesses the Contact table and update all places where the special value is being used. If we forget to update a particular SQL script to use the new value, it will fail silently. For example the select statement above will return no rows or a wrong set of rows and we will need to test the application in order to find the error.

Encapsulating special values with SQL views

We can emulate enumerations in SQL code using views. The following SQL view emulates ContactType enumeration by returning a single row of values where each column represents a single value of the enumeration.

create view enum.ContactTypeas    select        0 as Individual,        1 as Organization

Using this view, we can change the select statement from previous example like so:

select * from Contactwhere Type = (select Organization from enum.ContactType)

In this code, we no longer have the special value (1) hard-coded. Instead, we are now using its name (Organization). We can now change Organization code value in the view itself, without affecting the SQL scripts that use it. If we need to change the meaning (or name) of the code from Organization to Company, we can modify definition of the view and rely on the SQL engine to help us verify that all SQL scripts referencing it have been updated. If a particular SQL script still refers to the renamed Organization column of the ContactType view, SQL engine will raise an error and we will find out about it quickly.

Performance

Retrieving special values from a SQL view compared to using literal values does have a slight negative effect on performance. Jane Dallaway provides detailed analysis in her article TSQL: Enumerations and constants and describes an alternative approach based on UDF (user-defined SQL functions).

Keeping enumeration and SQL view definitions in synch

Encapsulating special values using SQL views is a great step toward making SQL scripts more maintainable. However, as the number of “codes” grows, the number of enumeration types and SQL views that encapsulate them increases. Keeping enumeration definitions and SQL view definitions becomes increasingly difficult.

Ideally, we would like to have a single definition of a particular list of special values and generate all constructs that encapsulate it in our application and SQL code. If you are using a modeling tool, such as Enterprise Architect by Sparx Systems, you may want to define this list in your tool’s repository and then generate the enumeration definition for your application code and the SQL view definition for your database code.  A simpler alternative is to define the list of values as an enumeration in the application code and use it to generate the corresponding SQL view.

Implementation

We will use a T4 code generation template and Visual Studio extensibility APIs to parse enumeration definition from the application source code and generate a corresponding SQL view definition. Visual Studio provides Code Model framework that allows code running inside of the IDE to discover code definitions in a project and manipulate them. The following T4 template illustrates usage of the Code Model API.

<#@ template hostspecific=“True” #><#@ output extension=“txt” #><#@ include file=“T4Toolbox.tt” #><#@ import namespace=“EnvDTE” #><#  CodeEnum enumeration = GetEnum(”ContactType.cs”);  WriteLine(”Found enumeration ” + enumeration.Name);  foreach (CodeElement element in enumeration.Children)  {    CodeVariable value = element as CodeVariable;    if (value != null)      WriteLine(”… found value ” + value.Name);  }#><#+  private CodeEnum GetEnum(string enumFile)  {    ProjectItem projectItem = TransformationContext.FindProjectItem(enumFile);    FileCodeModel codeModel = projectItem.FileCodeModel;    return FindEnum(codeModel.CodeElements);  }  private CodeEnum FindEnum(CodeElements elements)  {    foreach (CodeElement element in elements)    {      CodeEnum enumeration = element as CodeEnum;      if (enumeration != null)        return enumeration;      enumeration = FindEnum(element.Children);      if (enumeration != null)        return enumeration;    }    return null;  }#>

 

When this template is included in the same project with ContactType.cs, which contains definition of the ContactType enumeration shown above, it produces the following output.

Found enumeration ContactType... found value Individual... found value Organization

This template imports EnvDTE namespace that defines Code Model interfaces, such as CodeEnum, which represents an enumeration defined in a source file and CodeVariable, which represents an individual value defined in the enumeration. This template also includes T4Toolbox.tt file, that provides a set of utility classes, such as TransformationContext.

Execution starts when the main code bock calls GetEnum method, passing it name of the file where enumeration is defined. GetEnum uses TransformationContext to retrieve a Visual Studio ProjectItem object for the enumeration file. Through the FileCodeModel property of this object, the code obtains a FileCodeModel object that represents all of the code in the enumeration source file. The code then uses FindEnum method to iterate through the code elements in the file recursively to find the CodeEnum object that represents the enumeration. Having the CodeEnum object, the main code block of the template iterates through its children to find all CodeVariable objects that represent values defined in the enumeration.

Having access to the definition of the enumeration, creating actual T4 template to generate the SQL view is very straightforward. You can find the complete code in EnumSqlView.tt installed by T4 Toolbox.

Usage

• Download and install T4 Toolbox from CodePlex. It installs EnumSqlView.tt and supporting templates in the %ProgramFiles%/T4 Toolbox directory on your computer.
• In a C# project, select Project -> Add New Item from the main menu.

• In the Add New Item dialog, select Code Generation -> “Enumeration with a SQL view” template. Use name of the enumeration, such as ContactType, as the file name.
• In Solution Explorer, you should see two new files added to your project.
• Define the enumeration in ContactType.cs.
• ContactTypeView.tt is a T4 code generation template that contains code similar to the following:

<#@ template language=“C#” hostspecific=“True” #><#@ output extension=“sql” #><#@ include file=“T4Toolbox.tt” #><#@ include file=“T4Toolbox/EnumSqlView.tt” #><#    // Generate a SQL view that encapsulates a C# enumeration for accessing    // special values in T-SQL code    using (EnumSqlViewTemplate template = new EnumSqlViewTemplate())    {        template.EnumFileName = “ContactType.cs”;        template.Render();    }#>

• EnumSqlViewTemplate is a class defined in EnumSqlView.tt
• EnumFileName property of the EnumSqlViewTemplate is a required parameter that specifies the source file where enumeration is defined.
• ContactTypeView.sql is generated by ContactTypeView.tt template and contains a SQL script that creates ContactType view in the enum schema.
• SchemaName property of the EnumSqlViewTemplate is an optional parameter that can be used to change the name of the schema where SQL views encapsulating enumerations will be created.
• Add the generated SQL file to your database creation scripts.

Wish list

Here are some of the features that didn’t make the cut.

• EnumSqlView.tt currently generates a single file that contains code that creates the schema, drops and recreates the view. This does not allow to include the generated SQL file in a Visual Studio DB Pro database project, which stores a single object per SQL file. It would be nice to make the template generate code compatible with DB Pro database projects.
• On the other hand, some developers may want to generate all of their SQL views in a single SQL file. This would require EnumSqlView.tt to allow developer to specify multiple enum files, such as a list of files or all files in a given path.
• It may not be possible to encapsulate non-numeric codes with enumerations. It would be nice to generate SQL view based on a static class that contains string constants that define special values.
• In addition to the SQL view, it would be nice to generate CHECK constraints for a database column that stores codes defined in the enumeration.

Download

T4 Toolbox
Source Code

About T4

T4 (Text Template Transformation Toolkit) is a template-based code generation engine. It is available in Visual Studio 2008 and as a download in DSL and GAT toolkits for Visual Studio 2005. T4 engine allows you to use ASP.NET-like template syntax to generate C#, T-SQL, XML or any other text files.

T4 Toolbox is a library of code generation templates for Visual Studio. For more information about T4, check out my previous article.