Oleg Sych

This is the first post in a series that introduces code generation using C# and Text Templates (a.k.a. T4 Templates) in Visual Studio; 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, T4 Toolbox and T4 Editor installed on your computer.

In this series of articles, we will create a code generator 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 File

• Create a new C# Class Library project in Visual Studio.
• Click Project->Add New Item in the main menu and select Code Generation->File template in the dialog.

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

<#@ template language=“C#” hostspecific=“True” debug=“True” #>
<#@ output extension=“txt” #>
<#@ include file=“T4Toolbox.tt” #>
<#
// <copyright file=”CrudStoredProcedures.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 Engine that this template uses C# in code blocks. Code blocks contain C# or Visual Basic code that runs during template transformation and allow making generated output dynamic.

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.

<#@ output extension=“SQL” #>
create procedure Products_Delete
    @ProductID int
as
    delete from Products
    where ProductID = @ProductID

When you save CrudStoredProcedures.tt, the text templating Engine transforms it to generate the output file. In the Solution Explorer, the output file appears nested under the the file. You can also transform the file by right-clicking it in the Solution Explorer and selecting “Run Custom Tool” from the context menu. If you have multiple text templates in your project, you can transform them all by clicking “Transform All Templates” button in the toolbar.

In the template above, the text block is shown in gray. 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

<#@ template language=“C#” #>
<#@ 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

This code uses template directive to specify that this template uses C# in its code blocks.  This template 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 by SQL Server setup program. In order to use this API, this 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 without having to use fully-qualified type 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.

<#@ template language=“C#” #>
<#@ 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();
#>    

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.