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 Standard Edition or higher, SQL Server 2005 or later, 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 script shown below to generate a DELETE procedure for the Products table in the Northwind database.

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

In its current form, this code generator has several drawbacks. Extending this script 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 database in separate SQL scripts. Let’s see how we can accomplish that using T4 Toolbox.

Creating a new code generator

• Continue using the C# or Visual Basic 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.

C#

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

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

    }
}
#>

Visual Basic

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

Public Class CrudProcedureGenerator
    Inherits Generator 

    Protected Overrides Sub RunCore()     

    End Sub 

End Class
#>

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 included 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 CrudProcedureGenerator.tt to look like this:

C#

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

Visual Basic

<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#@ include file="DeleteProcedureTemplate.tt" #>
<#+
Public Class CrudProcedureGenerator
  Inherits Generator 

  Public DatabaseName As String
  Public DeleteTemplate As DeleteProcedureTemplate = New DeleteProcedureTemplate() 

  Protected Overrides Sub RunCore()
    Dim server As Server = New Server()
    Dim database As Database = New Database(server, Me.DatabaseName)
    database.Refresh()
    For Each table As Table In database.Tables
      Me.DeleteTemplate.DatabaseName = Me.DatabaseName
      Me.DeleteTemplate.TableName = table.Name
      Me.DeleteTemplate.RenderToFile(table.Name & "_Delete.sql")
    Next
  End Sub
End Class
#>

Note that this class defines a public field, DatabaseName that allows 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 Script item from the Code Generation folder in the dialog.
• Name the new file NorthwindProcedures.tt and change its contents to look similar to this.

C#

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

Visual Basic

<#@ template language="VBv3.5" hostspecific="True" debug="True" #>
<#@ output extension="txt" #>
<#@ include file="T4Toolbox.tt" #>
<#@ include file="CrudProcedureGenerator.tt" #>
<#
    Dim generator As CrudProcedureGenerator = New CrudProcedureGenerator()
    generator.DatabaseName = "Northwind"
    generator.Run()
#>

This code generation script uses include directive to merge contents of CrudProcedureGenerator.tt with NorthwindProcedures.tt before it is compiled by T4 engine 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 NorthwindProcedures.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 NorthwindProcedures.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.
• To see generated files in Visual Basic, you will need to click the Show All Files button in the toolbar of Solution Explorer.

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 script, such as AdventureWorksProcedures.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.

Source code available for download below, includes templates for INSERT, UPDATE and DELETE procedures. Implementation of other enhancements is outside of the scope of this tutorial and is left for the readers to complete.

Conclusion

This article showed how to create a complex generator, which uses several 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, C#: Initial, Completed
• Source code, Visual Basic: Initial, Completed