Oleg Sych

This article is a part of a series that introduces code generation using C# and Text Templates (also known as T4 Templates) in Visual Studio; 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 Standard Edition or higher, SQL Server 2005, T4 Toolbox and T4 Editor installed on your computer.

Introduction

In the first article of this series, we created the following code generation file 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 a particular database 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.

<#@ template language="C#" #>
<#@ 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();
#>    

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.

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 procedures 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, the template will generate a delete stored procedure for a single database table.

• Create a new C# Class Library 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.

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

public class DeleteProcedureTemplate : Template
{
    protected override void RenderCore()
    {

    }
}
#>

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. In the mean time, note that Visual Studio displays an error when trying to process this file.

• Select DeleteProcedureTemplate.tt in the solution explorer and change Custom Tool property from TextTemplatingFileGenerator to an empty value.

TextTemplatingFileGenerator is a custom tool that invokes the code generation Engine to run or transform the code generation file. Clearing the Custom Tool property of the code generation file prevents Visual Studio from trying to process the file and displaying the unnecessary error.

Encapsulate code generation logic in the template class

Template base class defines an abstract method called RenderCore. 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 RenderCore method in DeleteProcedureTemplate.tt and modify it to look like this:

<#@ 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;

    protected override void RenderCore()
    {
        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();
    }
}
#>

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 RenderCore 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 File 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.

<#@ template language="C#" 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();
#>

This code generation file uses include directive to pull in the contents of DeleteProcedureTemplate.tt. 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. Render method ultimately calls the RenderCore method with additional validation checks and exception handling.

• 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 file Products_Delete.tt with the help of the template defined in DeleteProcedureTemplate.tt. We can continue reusing the template by creating similar code generation files 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 reuse to generate multiple stored procedures without duplicating the code generation logic. However, table-specific code generation files like Products_Delete.tt and Customers_Delete.tt still contain a lot of duplication - 9 out of 10 lines required to render the template in each file are the same. Another problem with the current version is having a separate code generation file for each stored procedure. Combined with insert, update and select stored procedures, the total number of code generation files 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 a given database tables or multiple tables in a given database.

Download

Source code, completed.