T4 Tutorial: Creating complex code generators


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.

Add New Item Dialog

  • 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.

Solution Explorer window

  • 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


Write a Comment

Take a moment to comment and tell us what you think. Some basic HTML is allowed for formatting.

Reader Comments

Formalizing the top level generation of multiple files into a Visual Studio template is a great idea - it will promote consistency in the generation process.

Developers will know to look for the RunCore command when multiple files are being generated.

Good stuff!

[…] the previous article of this series, we created CrudProcedureGenerator.tt – a reusable code generator that produces […]

Very useful, was able to produce crud stored procedures against a large database.

I did notice that I had to change the assembly statements for SQl Management to the following to get it to compile successfully

I noticed my problems are caused by VisualSVN. If you have a work around, please let me know. If I find one, I will post it.
Thanks!
Kurt

Good catch, Kurt. Do you think this may be a problem with the VisualSVN implementation of the source control (MSSCCI) plugin?

Also, there is an error in article:

Select DeleteProcedureTemplate.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.

Must be CrudProcedureGenerator.tt instead of DeleteProcedureTemplate.tt.

[…] also makes dudious use of the T4 templating support built into VS2008 to codegen files from the Model objects through to Views and Controllers.  A great kick start for any new project […]

This is a small thing. There is an extra- file created by this approach. The extra-file in this example is “NorthwindProcedures.txt”. Is there a way to supress the creation of this extra-file?

Unfortunately, no. This file is created by the Visual Studio itself, after T4 is done generating the file. Visual Studio will generate the output file even if main template output is empty.

Updated source code to be compatible with version 9.10 of the T4 Toolbox.

i’ve followed every step of the articles in order so far without trouble but this line:”Click Project->Add New Item in the main menu and select File item from the Code Generation folder in the dialog.”

I don’t have a ‘File item’ listed in the Code Generation Category?

Could you please update this page as with the previous one to refer to the ‘Script’ item instead of ‘File’ in the section “Reuse the code generator’.

Peter, thanks for pointing this out. I have updated the article.

This is in reply to ##8&9: I’ve found a bug that looks more like feature that allows to suppress an output file generation - specify an invalid extension in output directive like this:

< #@ output extension="\n" #>
or
< #@ output extension="\\" #>
or
< #@ output extension="?" #>

Good tip, Stanislav. Thanks! It’s too bad this workaround generates a warning.

[…] Creating complex code generators […]

About the behaviou described in #8 & 9 - the file is generated after “our” generator runs.. is there a way to force our generator to run after the default output is created?

I have got the generator setup working fine, but I also have an include of helper functions for the base template (here it is DeleteProcedureTemplate.tt) and I think I am running into nested type issues.

Basically, I had all the helper functions public and the compiler complained I couldn’t access them unless they were static - so I made them all static and then I couldn’t use things like this.WriteLine (obvious in hindsight I suppose :).

What is the best way of doing this? i.e. have another include of helper functions accessible to the actual template.

Thanks!
Dan

I am sure this example worked with VS2008 and SQL Server 2008. However, with VS2010SP1 and SQL 2008R2 I am getting an error that the script can’t connect to the server. After some analysis, looks like the problem is that CrudProcedureGenerator opens connection to the server, and then for every table in the database DeleteProcedureTemplate attempts to open another connection. Once I created DeleteSubProcedureTemplate where I pass Table object instead of string from the loop - it works fine.

Obviously, there is downside for real-life situation (caller has to open db connection) - but for T4 example it’s good enough… at least it works!

Hi Oleg. Thanks for the great tutorial. Is there a way to get the length of an nvarchar? All of my update/inserts with nvarchar are defualting to one character. I tried grabbing the DataType MaximumLength property to I could add that to the paramters, but with no luck.

Hey Oleg,

I answered my own question. It looks like MaximumLength will work, but it returns a -1 if it’s a nvarchar(max). It returns the length if it’s a different size. Thanks again for the article.

Hi to everyone! It`s great tutorial! Thanks to author!
I have one question. Can I start generating t4 templates from consol application?