T4 Tutorial: Creating your first code generator
This is the first post in a 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.
In this series of articles, we will create a code generator that produces CRUD stored procedures for tables in a SQL Server database. In the examples, we will be using the Northwind sample database.
Creating a Code Generation Script
- Create a new Class Library (Visual Basic or C#) project in Visual Studio.
- Click Project->Add New Item in the main menu and select Code Generation->Script template in the dialog.
Note that Visual Studio templates in the Code Generation folder are provided by T4 Toolbox. If you don’t see them in the Add New Item dialog, download and install the latest version from CodePlex.
- Enter CrudStoredProcedures.tt as the item name and click the Add button.
- Double-click the new file in the Solution Explorer. You will see the following text in the text editor.
C#
<#@ template language=”C#v3.5” hostspecific=”True” debug=”True” #> <#@ output extension=”txt” #> <#@ include file=”T4Toolbox.tt” #> <# // <copyright file=”CrudProcedureGenerator.tt” company=”Your Company”> // Copyright © Your Company. All Rights Reserved. // </copyright> #>
Visual Basic
<#@ template language=”VBv3.5” hostspecific=”True” debug=”True” #> <#@ output extension=”txt” #> <#@ include file=”T4Toolbox.tt” #> <# ‘ <copyright file=”CrudProcedureGenerator.tt” company=”Your Company”> ‘ Copyright © Your Company. All Rights Reserved. ‘ </copyright> #>
This file is a Text Template, also known as a T4 Template. Text Templates are code generators that can be used to generate any text files, including C#, Visual Basic, SQL and XML. Text Templates use ASP.NET-like syntax and consist of text blocks, code blocks and directives. Text blocks are blocks of text in the template that are copied to the output file as is. Directives provide instructions to the text templating Engine on how to process the template. In the example above, template directive tells the T4 Engine which language the template uses in its code blocks. Code blocks contain C# or Visual Basic code that runs during template transformation and allow making generated output dynamic.
Note that color syntax highlighting for source code in T4 text templates is provided by the Tangible T4 Editor. If you only see plain, black-on-white text in Visual Studio editor, make sure to download and install the latest version from Tangible Engineering.
Generating Static Output
We will start implementing our code generator by hard-coding DELETE stored procedure for the Products table of Northwind database.
- Change CrudStoredProcedures.tt to look like so.
C# and Visual Basic
<#@ output extension=”SQL” #> create procedure Products_Delete @ProductID int as delete from Products where ProductID = @ProductID
When you save CrudStoredProcedures.tt, the T4 Engine transforms it to generate the output file. In the Solution Explorer, the output file appears nested under the the file. If you are using Visual Basic, you will need to click Show All Files button in the toolbar of Solution Explorer in order to see the output file.
You can also transform the template by right-clicking it in the Solution Explorer and selecting “Run Custom Tool” from the context menu. And if you have multiple text templates in your project, you can transform them all by clicking Transform All Templates button in the toolbar.
The template above contains a processing directive (output) and a text block. If you double-click the generated file, its contents will be identical to the contents of the text block and will look like so.
create procedure Products_Delete @ProductID int as delete from Products where ProductID = @ProductID
At this point, the generated output is static, which is not any better than coding this stored procedure by hand. Instead, we can generate it dynamically, using database schema information provided by SQL Server.
Adding .NET code to text template
- Change CrudStoredProcedures.tt to look like so
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 Products_Delete @ProductID int as delete from Products where ProductID = @ProductID
Visual Basic
<#@ template language=”VBv3.5” #> <#@ output extension=”SQL” #> <#@ assembly name=”Microsoft.SqlServer.ConnectionInfo” #> <#@ assembly name=”Microsoft.SqlServer.Smo” #> <#@ 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 Products_Delete @ProductID int as delete from Products where ProductID = @ProductID
This code uses template directive to specify that which language (C# or Visual Basic) this template uses in its code blocks. The template also contains a statement block which is defined using special markers - <# and #>. This block uses SQL Server Management Objects (SMO) to retrieve metadata information about Products table from the Northwind database running on the local SQL server instance. This API (SMO) is defined in a .NET assembly, Microsoft.SqlServer.Smo which is installed in the Global Assembly Cache (GAC) by SQL Server setup program. In order to use this API, the template uses an assembly directive to reference the assembly where it is defined and an import directive to specify the namespace where Server, Database and Table types are defined. The import directive is similar to the imports statement in Visual Basic and using statement in C#. It allows the template to use types from the specified namespace without having to use fully-qualified names.
Making Code Generation Dynamic
Having metadata information about the target table, we can now generate the DELETE stored procedure dynamically.
- Change CrudStoredProcedures.tt to look like so.
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() #>
This template uses expression blocks to dynamically generate name of the target table in the output file. Expression blocks are defined using special markers - <#= and #> and can contain any valid programming expression, which will be converted to string and written to the output file. This template also uses additional statement blocks to iterate through the list of Columns and calls WriteLine method to generate stored procedure parameter declarations and WHERE clause for the DELETE statement based on the primary key of the table. PushIndent and PopIndent methods are used to format parameter declarations and the where clause.
Conclusion
The text template shown in this article generates a single DELETE stored procedure based on table schema information retrieved from SQL Server using SMO. In the next article, we will talk about troubleshooting code generation errors.
I already installed T4 Editor from Clarius and T4 Toolbox, but I don’t have the all items under the CodeGeneration as you show in your first figure. The only items available are “Enumeration with a SQL View” and “LINQ to SQL Schema”. I am using VS2008 Team Suite Edition.
What I have to do to have all the items?
Thanks in advance