Creating templates for use with TFDP is relatively easy, anyone with familiarity with T4 templates in general should find it no different than a "normal" T4 template. On this page there are a few examples of the features that are provided by TFDP in the T4 templates.


Before diving into the template building business, I want to point out that most of the types used (ISqlTable, ISqlProcedure and the like) are documented (although very limited) in the VSTSB API on MSDN It's very useful to determine what properties you can access through the Element property of the DatabaseTemplate base class (see below).

Generic database template:

This template serves as a kind of base template in TFDP and looks like this:

<#@ template language="C#" hostSpecific="true" debug="True" inherits="DatabaseTemplate<ISqlTable>" #>
<#@ output extension="sql" #>
<#@ assembly name="Microsoft.Data.Schema" #>
<#@ assembly name="Microsoft.Data.Schema.Sql" #>
<#@ assembly name="Microsoft.Data.Schema.Sql.SchemaModel" #>
<#@ assembly name="System.Collections.Generic" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="TFDP.Common" #>
<#@ import namespace="Microsoft.Data.Schema" #>
<#@ import namespace="Microsoft.Data.Schema.Sql" #>
<#@ import namespace="Microsoft.Data.Schema.Sql.SchemaModel" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="Microsoft.Data.Schema.SchemaModel" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="TFDP.Common" #>
<#@ import namespace="TFDP.Common.T4" #>


You'll notice that the template inherits from the generic class DatabaseTemplate, this base class provides the following properties and methods:


public abstract class DatabaseTemplate<T> {
    // Provides access to the source element
    public T Element { get; }
    // Provides access to the node in the database project
    public IDatabaseNode Node { get; }
    // Provides access to the Model (see VSTDB API)
    public ModelStore Model { get; }
    // true if CancelGeneration() has been called
    public bool IsCanceled { get; }
    // Cancels template execution
    public void CancelGeneration();

The type of the Element property is the generic type parameter specified in the inherits statement (in this case ISqlTable). When the template is processed the Element property is set to the source element that triggered the processing. For instance, if the table SomeTable is queued for generation then Element will contain the instance SomeTable.


In your template you can use the Element property as a shortcut to the model element you want to generate a new artifact for. So if you want to create a table that is based on SomeTable you can do something like this:

-- This table is generated from <#= Element.GetName() #>
-- and shall be called <#= Element.GetName() #>WithExtras
CREATE TABLE <#= Element.GetName() #>WithExtras (
column_1 int NULL

Creating a template for stored procedures (or any other model element type)

When you want to create a template that is triggered by a stored procedure you can simply copy the generic template and change the ISqlTable generic type parameter to ISqlProcedure. This will change the type of the Element property to ISqlProcedure so you can make use of the properties and methods of ISqlProcedure instead of ISqlTable. The same goes for any other type that derives from IModelElement in the VSTDB API.

Creating a regular T4 template

To create a template that works like a normal T4 template, that is process-on-save, you can do the following:

  • Add a new file to the Templates folder in your project
  • Set the property "Skip autogeneration" to false
  • Edit the template
  • Save the template

And that is all. The output file name is the name of the template + the output extension specified in the template.

To process the template at a later stage you can simply right-click the template and select "Regenerate objects". This will process the template and replace the output file.

(Hopefully useful) Tips

When creating T4 templates I've found the Visual T4 extension of Clarius Consulting very useful in keeping a structured overview of my templates.

Last edited Sep 6, 2011 at 8:40 AM by sandermvanvliet, version 5


No comments yet.