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 previous article of this series, we created a reusable code generation template called DeleteProcedureTemplate that generates a DELETE stored procedure for a given database table using SMO. We have also created a simple code generation file shown below to generate a DELETE procedure for the Products table in the Northwind database.

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

In its current form, this code generator has several drawbacks. Extending this file to generate stored procedures for multiple tables will produce a single large SQL script, which becomes increasingly difficult to work with. On the other hand, creating similar files to generate additional stored procedures in separate files results in code duplication and increases the number of the code generation files themselves. Ideally, we want to generate all CRUD stored procedures for a given set of database tables in separate SQL scripts. Let’s see how we can accomplish that using T4 Toolbox.

Creating a new code generator

• Continue using the C# project you created previously or download the initial source code below.
• Click Project->Add New Item in the main menu and select Generator 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 CrudProcedureGenerator.tt as the item name and click the Add button.
• Double-click CrudProcedureGenerator.tt in the Solution Explorer. You will see the following text in the editor.

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

public class CrudProcedureGenerator : Generator
{
    protected override void RunCore()
    { 

    }
}
#>

This file contains a class feature block that defines a class called CrudProcedureGenerator. It inherits from a base class Generator, which is defined in T4 Toolbox. Similar to the template class definition we talked about in the previous article, this file 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.

• Select CrudProcedureGenerator.tt in the solution explorer and change Custom Tool property from TextTemplatingFileGenerator to an empty value. This will prevent Visual Studio from trying to use this file for code generation and reporting errors in the Error List window.

Encapsulate code generation logic in the generator class

Generator base class defines an abstract method called RunCore. Each concrete generator must override this method and implement the code generation logic inside of it. A concrete generator typically uses one or more templates to generate one or more output files.

• Modify CrudStoredProcedureGenerator.tt to look like this:

<#@ assembly name=“Microsoft.SqlServer.ConnectionInfo” #>
<#@ assembly name=“Microsoft.SqlServer.Smo” #>
<#@ import namespace=“Microsoft.SqlServer.Management.Smo” #>
<#@ include file=“DeleteProcedureTemplate.tt” #>
<#+
public class CrudProcedureGenerator : Generator
{
  public string DatabaseName;
  public DeleteProcedureTemplate DeleteTemplate = new DeleteProcedureTemplate(); 

  protected override void RunCore()
  {
    Server server = new Server();
    Database database = new Database(server, this.DatabaseName);
    database.Refresh();
    foreach (Table table in database.Tables)
    {
      this.DeleteTemplate.DatabaseName = this.DatabaseName;
      this.DeleteTemplate.TableName = table.Name;
      this.DeleteTemplate.RenderToFile(table.Name + “_Delete.sql”);
    }
  }
}
#>

Note that this class defines a public field, DatabaseName, to allow the caller to specify a database for which stored procedures need to be generated. It also defines a public field, DeleteTemplate, which is initialized with a DeleteProcedureTemplate instance. The RunCore method retrieve the list of all tables in a database with the specified DatabaseName and generates a DELETE stored procedure for each table with the help of the DeleteTemplate. Thanks to the RenderToFile method, which is defined in the Template base class, each stored procedure is generated in a separate SQL file.

Reuse the code generator

Having the logic necessary to generate DELETE stored procedures for multiple tables encapsulated in the CrudProcedureGenerator class, we can now start using this generator on one or more application development projects to generate stored procedures for different databases.

• 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 NorthwindProcedures.tt and change its contents to look similar to this.

<#@ template language=“C#” hostspecific=“True” debug=“True” #>
<#@ output extension=“txt” #>
<#@ include file=“T4Toolbox.tt” #>
<#@ include file=“CrudProcedureGenerator.tt” #>
<#
    CrudProcedureGenerator generator = new CrudProcedureGenerator();
    generator.DatabaseName = “Northwind”;
    generator.Run();
#>

This code generation file uses include directive to pull in the contents of CrudProcedureGenerator.tt and a statement block to create a new instance of the CrudProcedureGenerator class. It specifies value for the DatabaseName field of the generator and calls its Run method. Run method calls the RunCore method we implemented previously, with additional validation checks and error handling.

• Save NorthwindStoredProcedures.tt - Visual Studio will compile and run the code generator. Be patient, it can take a while for the code generator to retrieve metadata from a “cold” database.
• Click the “+” sign next to the NorthwindStoredProcedures.tt in Solution Explorer. You will see a number of SQL files nested under it, each with a single DELETE stored procedure for a particular database table.

At this point, we have a generator that produces stored procedures for all tables in the Northwind database. We can reuse this generator on another project by creating another code generation file, such as AdventureWorksStoredProcedures.tt that specifies a different database name to generate stored procedures for. It would be straightforward to implement additional templates for SELECT, INSERT and UPDATE procedures and extend our generator to incorporate them. We could also improve the generator by allowing the caller to define a subset of tables that need stored procedures using a wildcard or a regular expression. However, these enhancements are outside of the scope of this tutorial and are left for the readers to complete.

Conclusion

This article showed how to create a complex generator, which uses one or more templates to generate multiple output files of different types. This generator is easy to manage, as it generates all outputs for the given input. It can also be easily reused without introducing code duplication.

The next article will discuss reusing code generators on multiple projects and multiple teams.

Download

• Source code, initial.
• Source code, completed.