Oleg Sych

This post is a part of the series that introduces code generation with Text Templates (also known as T4 Templates) in Visual Studio using C# and Visual Basic; explains how to create reusable templates and combine them in complex code generators. In order to follow examples in this article, you need to have Visual Studio 2008 or 2010 Standard Edition or higher, SQL Server 2005 or later, T4 Toolbox and T4 Editor installed on your computer.

Introduction

In the first article of this series, we created the following text template to generate a DELETE stored procedure for a given database table using SMO. This stored procedure is one of the CRUD procedures that would need to be created to encapsulate access to this table. Accessing data exclusively through CRUD stored procedures helps to eliminate SQL injection vulnerabilities, prevent accidental data corruption during casual browsing and may help to improve performance of SQL queries.

C#

<#@ template language="C#v3.5" #>
<#@ output extension="SQL" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#
    Server server = new Server();
    Database database = new Database(server, "Northwind");
    Table table = new Table(database, "Products");
    table.Refresh();
#>
create procedure <#= table.Name #>_Delete
<#
    PushIndent("\t");
    foreach (Column column in table.Columns)
    {
        if (column.InPrimaryKey)
            WriteLine("@" + column.Name + " " + column.DataType.Name);
    }
    PopIndent();
#>
as
    delete from <#= table.Name #>
    where
<#
    PushIndent("\t\t");
    foreach (Column column in table.Columns)
    {
        if (column.InPrimaryKey)
            WriteLine(column.Name + " = @" + column.Name);
    }
    PopIndent();
#>    

Visual Basic

<#@ template language="VBv3.5" #>
<#@ output extension="SQL" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ import namespace="Microsoft.VisualBasic" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#
    Dim server As Server = New Server()
    Dim database As Database = New Database(server, "Northwind")
    Dim table As Table = New Table(database, "Products")
    table.Refresh()
#>
create procedure <#= table.Name #>_Delete
<#
    PushIndent(VbTab)
    For Each column As Column In table.Columns
        If column.InPrimaryKey Then
            WriteLine("@" & column.Name & " " & column.DataType.Name)
        End If
    Next
    PopIndent()
#>
as
    delete from <#= table.Name #>
    where
<#
    PushIndent(VbTab & VbTab)
    For Each column As Column In table.Columns
        If column.InPrimaryKey then
            WriteLine(column.Name & " = @" & column.Name)
        End If
    Next
    PopIndent()
#>

Note that this file has code generation parameters (database name and table name) embedded in its source code. In order to reuse this code generator in its current form, we would need to create a copy of this file and modify the embedded parameters to specify another database and table names. While this approach certainly works for simple code generators that need to be used a limited number of times, it creates code duplication and makes maintaining code generators more and more difficult as their number and complexity increases.

We also need to extend this code generator to create additional types of stored procedures to encapsulate INSERT, UPDATE and SELECT SQL statements. We could accomplish that by extending this file to generate all of these stored procedures. Again, this approach works well for a simple code generator, but as the number of procedure types this template generates grows, it becomes more and more difficult to manage.

Let’s see how we can extend this code generator without introducing code duplication and additional complexity.

Create a new code generation template

Let’s define code generation template (or simply template) as a reusable code generator, which produces a single logical unit of code. In our example, a template will generate a delete stored procedure for a single database table.

• Create a new Class Library (C# or Visual Basic) project in Visual Studio.
• Click Project->Add New Item in the main menu and select Template item from the Code Generation folder in the dialog. If you don’t see this folder or this item, make sure you have the latest version of T4 Toolbox installed.

• Enter DeleteProcedureTemplate.tt as the item name and click the Add button.
• Double-click DeleteProcedureTemplate.tt in the Solution Explorer. You will see the following text in the editor.

C#

<#+
// <copyright file="DeleteProcedureTemplate.tt" company="Your Company">
//  Copyright © Your Company. All Rights Reserved.
// </copyright>

public class DeleteProcedureTemplate : Template
{
    public override string TransformText()
    {

        return this.GenerationEnvironment.ToString();
    }
}
#>

Visual Basic

<#+
‘ <copyright file="DeleteProcedureTemplate.tt" company="Your Company">
‘  Copyright © Your Company. All Rights Reserved.
‘ </copyright>

Public Class DeleteProcedureTemplate
    Inherits Template

    Public Overrides Function TransformText() As String

        Return Me.GenerationEnvironment.ToString()
    End Function

End Class
#>

This file contains a class feature block that defines a template class called DeleteProcedureTemplate. It inherits from a base class called Template, which is defined in T4 Toolbox. Although this file has a .tt extension, it is not intended to be used for code generation directly. Instead, it will be reused by other code generation files, as we will see shortly.

TextTemplatingFileGenerator is a custom tool that invokes the code generation Engine to run or transform the code generation file. Notice that newly created DeleteProcedureTemplate.tt doesn’t have the Custom Tool property assigned which prevents Visual Studio from trying to process the file and displaying the unnecessary errors.

Encapsulate code generation logic in the template class

Template base class descends from TextTransformation which defines an abstract method called TransformText. Each concrete template must override this method and implement code generation logic inside of it.

• Copy the contents of the original file inside of the TransformText method in DeleteProcedureTemplate.tt and modify it to look like this:

C#

<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#+
public class DeleteProcedureTemplate : Template
{
    public string DatabaseName;
    public string TableName; 

    public override string TransformText()
    {
        Server server = new Server();
        Database database = new Database(server, DatabaseName);
        Table table = new Table(database, TableName);
        table.Refresh();
#>
create procedure <#= table.Name #>_Delete
<#+
        PushIndent("\t");
        foreach (Column column in table.Columns)
        {
            if (column.InPrimaryKey)
                WriteLine("@" + column.Name + " " + column.DataType.Name);
        }
        PopIndent();
#>
as
    delete from <#= table.Name #>
    where
<#+
        PushIndent("\t\t");
        foreach (Column column in table.Columns)
        {
            if (column.InPrimaryKey)
                WriteLine(column.Name + " = @" + column.Name);
        }
        PopIndent(); 

        return this.GenerationEnvironment.ToString();
    }
}
#>

Visual Basic

<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ import namespace="Microsoft.VisualBasic" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#+
Public Class DeleteProcedureTemplate
    Inherits Template 

    Public DatabaseName As String
    Public TableName As String 

    Public Overrides Function TransformText() As String
        Dim server As Server = New Server()
        Dim database As Database = New Database(server, DatabaseName)
        Dim table As Table = New Table(database, TableName)
        table.Refresh()
#>
create procedure <#= table.Name #>_Delete
<#+
        PushIndent(VbTab)
        For Each column As Column In table.Columns
            If column.InPrimaryKey Then
                WriteLine("@" & column.Name & " " & column.DataType.Name)
            End If
        Next
        PopIndent()
#>
as
    delete from <#= table.Name #>
    where
<#+
        PushIndent(VbTab & VbTab)
        For Each column As Column In table.Columns
            If column.InPrimaryKey then
                WriteLine(column.Name & " = @" & column.Name)
            End If
        Next
        PopIndent()

        Return Me.GenerationEnvironment.ToString()
    End Function

End Class
#>

Note that code generation parameters database name and table name are now encapsulated as public fields in the DeleteProcedureTemplate class. These fields are used inside of the TransformText method to retrieve table metadata information and generate the stored procedure with the help of text blocks, expression blocks and class feature blocks.

Reuse the code generation template

Having the code generation logic encapsulated in a template class, we can now reuse it in another code generation file without duplicating the code.

• Click Project->Add New Item in the main menu and select Script item from the Code Generation folder in the dialog.
• Name the new file Products_Delete.tt and change its contents to look similar to this.

C#

<#@ template language="C#v3.5" hostspecific="True" #>
<#@ output extension="sql" #>
<#@ include file="T4Toolbox.tt" #>
<#@ include file="DeleteProcedureTemplate.tt" #>
<#
    DeleteProcedureTemplate template = new DeleteProcedureTemplate();
    template.DatabaseName = "Northwind";
    template.TableName = "Products";
    template.Render();
#>

Visual Basic

<#@ template language="VBv3.5" hostspecific="True" #>
<#@ output extension="sql" #>
<#@ include file="T4Toolbox.tt" #>
<#@ include file="DeleteProcedureTemplate.tt" #>
<#
    Dim template As DeleteProcedureTemplate = New DeleteProcedureTemplate()
    template.DatabaseName = "Northwind"
    template.TableName = "Products"
    template.Render()
#>

This code generation script uses include directive to merge contents of DeleteProcedureTemplate.tt with the contents of Products_Delete.tt itself before it is compiled by the T4 engine. It then uses a statement block to create a new instance of the DeleteProcedureTemplate class; assigns database name and table name values and calls the Render method of the template object. After performing additional validation checks and exception handling, Render method ultimately calls the TransformText method we defined in DeleteProcedureTemplate.tt.

• Open Products_Delete.sql generated by this file.

create procedure Products_Delete
    @ProductID int
as
    delete from Products
    where
        ProductID = @ProductID

At this point we have a delete procedure for the Products table generated by the code generation script Products_Delete.tt with the help of the template defined in DeleteProcedureTemplate.tt. We can continue reusing the template by creating similar code generation scripts to produce delete procedures for other tables - Customers_Delete.tt, Orders_Delete.tt, etc. We can also create additional templates - InsertProcedureTemplate, UpdateProcedureTemplate, etc. to generate remaining types of CRUD stored procedures.

Conclusion

We have encapsulated logic for generating a DELETE stored procedure as a reusable template, which we can now use to generate multiple stored procedures without duplicating the code generation logic. However, table-specific code generation scripts like Products_Delete.tt and Customers_Delete.tt still contain a lot of duplication - 8 out of 10 lines required to render the template in each script are the same. Another problem with the current implementation is having a separate code generation script for each stored procedure. Combined with insert, update and select stored procedures, the total number of code generation scripts becomes difficult to manage even for a simple database like Northwind. We will address these problems in the next article, by creating a composite code generator that produces multiple types of stored procedures for all tables in a given database.

This article uses the nested template class pattern to make the code generation logic reusable. Although at this point it would have been sufficient to implement a simpler template method pattern to achieve similar results, using a template class allows us to achieve additional encapsulation and extensibility, which will be important later in this series when we talk about unit testing and customization of code generators.

Download

Source code, C#: Initial, Completed 
Source code, Visual Basic: Initial, Completed