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.

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 Contact
where 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.

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.

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:

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

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.

Write a Comment

Take a moment to comment and tell us what you think. Some basic HTML is allowed for formatting.

Name (required)

E-mail (required)

Website

Allow comment box to float next to comments.

Reader Comments

Comment Number:

1

Written by:

Jeff Odell

Posted on:

July 14, 2008 at 6:50 pm

Swwet - a great solution to a problem we know and love.

Comment Number:

2

Written by:

Justin Taylor

Posted on:

October 14, 2008 at 8:11 pm

Why wouldn’t you have a ContactType table that had a foreign key from Contact which stored all your id’s against names? Surely this would be better practice in terms of optimising the database and it allows you to change th names of the the Contact types without affecting your stored procedures?

Comment Number:

3

Written by:

Oleg Sych

Posted on:

October 15, 2008 at 12:40 pm

I agree that adding a ContactType table and a foreign key to help enforcing values in the Type column of the Contact table is a good idea. However, it doesn’t help you to write T-SQL code that needs to use a particular ContactType value. Consider having the following code in a stored procedure:

select * from Contact
where Type = (select Id from ContactType where Name = “Organization”)

If the name of the “Organization” contact type changes to “Company”, this code will start failing silently by returning 0 rows. You could solve this problem by changing the code to look like this:

select * from Contact
where Type = 1

But this requires you to know that 1 means “Organization”. Aside from making this code difficult to read, if the meaning of contact type 1 changes to “Individual”, this code will also fail silently.

I’ve done something similar to this - I got around the DB project problem by adding my table definition (id & name) to the DB project, and using the generated SQL (re)populate it with the correct data.

The generated SQL can then be included in a DB project’s post deployment script using something like

:r “..\ProjectWithT4\GeneratedEnums.sql”

I’m also using T4 to generate both my SQL and enum definition (.NET) code. With your method, I believe the SQL is not regenerated unless the template file is touched - this way a change causes both to be regenerated.

Error 1 Running transformation: System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.VisualStudio.TextTemplatingBB88E5E873511E8E0604412A18F7F962.GeneratedTextTransformation.EnumSqlViewTemplate.GetEnum()
at Microsoft.VisualStudio.TextTemplatingBB88E5E873511E8E0604412A18F7F962.GeneratedTextTransformation.EnumSqlViewTemplate.TransformText()
at T4Toolbox.Template.Transform()
at T4Toolbox.Template.Render()
at Microsoft.VisualStudio.TextTemplatingBB88E5E873511E8E0604412A18F7F962.GeneratedTextTransformation.TransformText()
at Microsoft.VisualStudio.TextTemplating.TransformationRunner.RunTransformation(TemplateProcessingSession session, String source, ITextTemplatingEngineHost host, String& result) 1 1

Comment Number:

11

Written by:

Oleg Sych

Posted on:

July 13, 2010 at 7:03 am

Justin,

Does your script specify a valid path to the enum file? I can see in how it could throw a NullReferenceException when the file cannot be found.