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.

image

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.5hostspecific=”Truedebug=”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.5hostspecific=”Truedebug=”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

Generated output file in Solution Explorer 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.

Download


Write a Comment

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

Reader Comments

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

I’m sorry about that Bruno. You probably got version 8.8.17.1 installed. I uploaded a new release to CodePlex this morning, but didn’t make it default until now. Could you install version 8.8.31.1? This should make the other templates available in the Add New Item dialog.

Hi again, Oleg!

Is there still no way to assign values to template’s properties right before rendering the template?

Actually, I use templates for code generation in runtime, not in Visual Studio. Now I use CodeSmith engine, but for my open source project I’d like to have at least a parametrized template renderer that is free.

The best way is to convert CodeSmith templates into T4 templates on-the-fly (it seems to be possible) as there is no suitable T4 editor in sight. But all my unfortunate creatures with custom hosts did not leave a chance to keep CodeSmith templates unchanged.

Maybe you have any ideas about some workaround here?

Alex,

I blogged about passing parameters to a template from an external tool a couple of months ago. Check out http://www.olegsych.com/2008/04/t4-template-design-standalone-template/.

To answer your second question, I don’t think translating CodeSmith templates on the fly is viable. They are different enough to make such translation very complex. On the other hand, creating T4 templates from scratch is easy. I would guess that the effort required to recreate CodeSmith templates in T4 is less than the effort required to create an automated translation tool.

Hope this helps

Interesting! It looks a bit like codesmith templates actually…

Oleg,
I wrote an extension to the string object and I am trying to use it in a .tt file but I get an error. Does .tt recongize custom extensions?

John, if I understand you correctly, you have an extension method you want to use in template code. You will need to set language parameter of the template directive to “C#v3.5″ or “VBv3.5″, otherwise T4 engine will use an older version of the compiler that doesn’t support extension methods.

This seems like a great tool - I wish it was available for Visual Basic.Net.

Thanks Corey. You can also use T4 with Visual Basic, simply change language parameter of the template directive to “VB” and write Visual Basic code in the code blocks.

Oleg, could you, please, clear one thing to me? I’ve just tried to re-produce your steps. How can I get CodeGeneration selector when adding new item? I use VS2008, I have Visual Studio SDK installed, but there is no CodeGeneration template. What are other requirements?

Thank you.

Sorry about my previous question. No problem now, just a great surprise “How simple” :) Thanks for these tutorials.

Oleg,

Sorry I haven’t responded. I have been extremely busy and this is the first time I have had time to get back into T4.

Thank you the solution of my extensions. I will try that and let you know the outcome.

Also, I understand you will be speaking at the Tallahassee .Net User’s Group in December. I look forward to your session and hope I have enough knowledge of T4 to discuss this methodology with you.

Thanks,

John

You can pass in a server name and instance to the Server constructor.
In my case it was Server server = new Server(”.\\SQLEXPRESS”);

After downloading v8.12.27.1 of the T4Toolbox and running it, the installer reports that VS2008 must be installed first. However, I already have VS2008 VB Express Edition installed. What am I doing wrong?

T4Toolbox requires Visual Studio 2008 Standard or higher edition. EULA for Express Editions of Visual Studio explicitly prohibits extension by third-party add-ons.

The CodeGenerator item category is only available under Visual c# items.

Can it also be available under VB items?

T4 Toolbox currently supports only C# (as code generation language and target language). This is mainly because I don’t have the extra time that would be required to support Visual Basic. However, there are no technical reasons not to support it. If someone is interested in implementing support for VB in T4 Toolbox, please let me know. I would be happy to help with this.

Alex,

I am not sure how far along you are in your open source project. Why not just use MyGeneration? It is open source and has a CodeSmith converter.

RB

Will do, as soon as I start using NAnt instead of MSBuild…

Hi Oleg!

My Generated SQL file contents is

create procedure Products_Delete
as
delete from Products
where

What is wrong?

It looks like the Products table doesn’t have a primary key.

@hoyoch
You might not have the Northwind database installed.

Oleg,
I have VSTS 2008 and installed the new T4Toolbox.msi. After I add the File template I immediately get the error: ‘Failed to load Assembly ‘C:\Program Files\T4 Toolbox\Bin\T4Toolbox.dll’ for registered directive processor ‘T4Toolbox.DteProcessor”
What is wrong?

Przemek

Thanks for reporting it. There was a problem in build 9.3.21.1. Get build 9.3.21.2 here.

@hoyoch,

I was running into the same problem. My results came back the same way.

create procedure Products_Delete
as
delete from Products
where

No keys, no nothing.

I was running SQL Server 2005 EXPRESS and I recently upgraded to SQL Server Developer.

Solution: I uninstalled SQL Server 2005 Express.

Since this requires the Microsoft SQL Server SMO, SQL Server Express doesn’t have it. One downside to having SQL Server Express.

Now everything’s running just fine.

JD

Great catch, Jonathan. Thanks for sharing this tip. I wonder if installing SMO separately would help as well.

[…] the msdn site but to actually get productive with T4 and to see some sample code in action visit this post on Oleg Sych’s blog. Also go through the various articles on his blog, he’s done some pretty […]

[…] Oleg Sych’s blog and especially T4 Tutorial: Creating your First Code Generator […]

With Visual Studio Team System SP1,
I had to add an assembly directive for Microsoft.SqlServer.Management.SDK.Sfc

Else I would get a Compiling Transformation exception.

Other than that, great post. (and great posts in general, this is a great blog for T4 information)

Oleg,

First of, many thanks for the tool and your posts - it’s a great help. I’m using it for a year now in several projects and am pretty happy with it.

My latest project is using DSL Tools and it should run on vs2008sp1 machines (not 2010). Any recommendations on how to make T4Toolbox goodies to work with generated DirectiveProcessor?

I’m trying to use T4Toolbox ability to generate multiple files…

Thanks in advance

The July version of T4 Toolbox should be able to support the DSL scenario. Let’s continue this discussion in the T4 Toolbox forum on CodePlex. Could you start a new thread and post additional details about your scenario, including sample code? Thanks.

All –

This is a tip for T4 neophytes like me.

If you are having trouble connecting to the database in your TT template…

…for example, if you get this CTE…

Running transformation: Microsoft.SqlServer.Management.Smo.FailedOperationException: SetParent failed for Database ‘Northwind’

…then try changing from this…

Server server = new Server();

…to something like this…

Server server = new Server(@”YouServerName\SQLEXPRESS”);

…and that might help.

It worked for me.

(It is a big small thing.)

HTH.

Thank you.

– Mark

The double-quotes in the code on this web page are less than optimal. They are curly-double-quotes. They should be straight-double-quotes. Why? They should be straight-double-quotes because doing so would allow a reader to copy the code from the web page and paste it into VS.NET and VS.NET would not complain. I know the code is downloadable; but, having the inline code cut-and-paste-friendly would value added to the reader. This is a small thing; but, it is something you would probably want to know. Regardless, the ideas are great. Thank you. — Mark Kamoski

I installed the free version for Visual Studio 2008, then rebooted, but there’s no “Code Generation” templates. Oleg said to download the latest version, but that won’t help because that’s a beta for VS2010. So I can assume “free” really means it doesn’t work — a waste of time.

Ron, Which edition of Visual Studio 2008 do you have installed? Did you install T4 Toolbox?

I understand that this is a simple example and that you probably want to keep things simple, but from what understood from the template, it will not handle well tables with more than on column in the primary key since it does not put commas between the parameters of the procedure nor AND in the WHERE clause. I would recommend an update to the template or explicitly state somewhere in the article that it won’t work as expected if you have more than on column as the primary key. This will help who is trying to reuse the template with other tables. You have a lot of content here and I just started getting into it. Keep up with the good work!

Hi I had installed the T4 Editor and the T4 Toolbox, but I haven’t the “Code Generation” option.
Tnx in advance

Allright, hold on. I’m going to perform a Vulcan mind meld… Still working… Still working… Oh man, I’m too rusty!

Seriously, Salvatore, could you help me out here? What is the version and edition of Visual Studio you are using? Which version of the T4 Toolbox did you install? What type of project did you try to use?

Hi Oleg Sych,
I had downloaded T4 ver. 9.10.12.1.
My VS 2008 is a Team System edition ver. 9.0.30729.1 SP.

The tangibleprojectsystem and tangible t4 edit are installed.

Thank you :)

Hi Oleg

Thanks a lot for this. I was about to purchase CodeSmith when I came across this site by accident.

Why is this such an unknown feature? Before today, I had never heard of T4. Sometimes I wonder about Micrsoft’s marketing department.

We can only speculate. If you need to solve a particular problem, CodeSmith may still be a better option if it provides a ready-to-use set of templates you can use instead of building them yourself in T4.

Oleg,
In your tutorial, you say:
“Click Project->Add New Item in the main menu and select Code Generation->File template in the dialog.” However, I don’t see a “File” template. I do see “Azman Authorization Store”, “Ling to SQL Model”, “Script”, “Unit Test”, “Enumeration with a SQL View”, “Generator”, and “Template”.

Please advise.

By the way, your work looks really interesting.

Thanks

I’m using “VS2008 Pro Ver 9.0.30729.1 SP” and “T4 Toolbox 9.10″.

Thanks for the heads-up Geoff. I have renamed the “File” item template into “Script” a while back, but forgot to update this article.

I rolled my own version of this by just hooking into sql server’s build in procedures. I wish I ran into this post earlier hehe.

Hi, Oleg - I hope I haven’t missed this answer somewhere, but I’m having the same problem as some others in that I’ve installed your Toolbox (v9.12.25.1) but I do not see the Code Generation additions when creating a new project in VS.

I’m running VS 2008 Pro, v 9.0.30729.1 SP, on .Net 3.5 SP1.

Any idea what’s wrong ?

Thanks,
Bob S.

I am looking to learn T4 templates even though I know other code generators like CodeSmith, CodeBreeze, etc. I see the Server and Database objects being instantiated in the templates, but I do not see how I can see the T4 templates object model and view all the options I have access to. Can someone redirect/point me to where I need to look to view the T4 templates object model?

[…] Toolboxhttp://www.codeplex.com/t4toolboxOleg Sych tutorials (blog)http://www.olegsych.com/2008/09/t4-tutorial-creatating-your-first-code-generator/Scott Hanselman on […]

[…] T4 Tutorial, by Oleg Synch, and […]

[…] Creating your first code generator […]

Seems that there are some modifications required in VS2010 (or maybe it’s just me):

1. It didn’t work for me until I added the assemblies into GAC (SQL Server doesn’t add them during install; and just adding the DLLs to the project didn’t help - seems like transformation tool ignores project references).
2. you also need to add another assembly:

Hi Oleg,

Is T4 restricted to only .NET languagages. Can I use it for non .net framework languages also.. Say I want to generated code in Ruby, Php. Can I use Ph4 for generating code in Ruby or Php?

Venki,

With T4 you can use either C# or Visual Basic to generate any text files.

Oleg

[…] I started my code generation foray using Oleg’s article titled T4 Tutorial: Creating your first code generator and that got me most of the way to what I needed to do. I did have to do some more learning as the […]

Thanks for this post. I am just getting into T4 and this post helped me a lot… I blogged about it here :

http://www.mathlionsoftware.com/2010/10/finally-learning-t4-and-its-good/

In the second step, adding .NET code to text template, I got 10 “missing assembly” errors like the one below. I made sure all the sql server assemblies where in %windir%\assembly (I think that’s the GAC), but no good. I added the assemblies to the project, no good. I guess the only thing I can do is add absolute paths to the assemblies, but that is a HORRIBLE solution since I can’t share the solution with programmers who don’t have exactly the same absolute paths. Do you have any ideas how to fix this?

Error 1 Compiling transformation: The type ‘Microsoft.SqlServer.Management.Sdk.Sfc.ISfcValidate’ is defined in an assembly that is not referenced. You must add a reference to assembly ‘Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′.

This dependency was added in one of the SQL Server versions released after I wrote this article. Simply add
< #@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc" #> to your template file.

[…] Lösung bietet u.U.  ein 3rd Party Tool im Zusammenspiel mit einem WCF Data Service: http://www.olegsych.com/2008/09/t4-tutorial-creatating-your-first-code-generator/ […]

Hi Oleg:

First, thanks for putting up one of the few HELPFUL T4 tutorials. I really appreciate the time and effort.

I am working with VS2010SP1, and the latest version of t4toolbox (downloaded it today). I am running into a pair of issues I wanted to run past you.

First, my tables are not in the dbo schema, and they appear not to be “seen”. Anything in the dbo schema can be interacted with. How can I get at non-dbo tables without refactoring / recompiling source?

Second, this fails:
WriteLine(”@” + column.Name) + ” ” + column.DataType.Name);
whining about System.xml (NOT System.Xml.dll).
This runs:
WriteLine(”@” + column.Name );

Any ideas?

Thanks;
D

nice tool , reminds me of one i wrote years ago, but nicer.

I added the

<#
line to solve an error and got this one complain about needing ‘System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ implementing ‘System.Xml.Serialization.IXmlSerializable’.
You need to add

as well

VS2010 and SQL Server

seems the comments leave out the correct references..
a
assembly name=”System.Xml”

is neeeded as well as the
assembly name=”Microsoft.SqlServer.Management.Sdk.Sfc”

line to make it work on vs2010 and sql server