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 e