Musing around T4 Templates – Part 2

 

In my previous article (Part 1) we went through the T4 basics and created a C# Template from the scratch. Let’s in this part take a closer look at the T4 Pragma Sections which came short in the first part. Start again with the very same solution from the previous part or you could create a fresh new Console Application as well. Furthermore I will assume you have installed at least one SQL-Server instance on your system. This is due to the reason we are going to create a Template which will loop through all SQL Server Databases. In order to create a list of databases we have to add references to these additional assemblies representing SQL Server Management Objects (SMO):

  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc.dll

These libraries are installed to and located at “%Program Files%\Microsoft SQL Server\100\SDK\Assemblies”. After you have managed to locate and add reference these assemblies, let’s create a new T4 Template adding a new Text template and changing the output extension to “.cs” (like we did similarly in the first part). Furthermore add to the new template the rather simple code looping over all databases (Code 1).

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".cs"  #>
// ——————————————
// <auto-generated>
//         This code was auto-generated <#= DateTime.Now #>
// <auto-generated>
// —————————————–

using System;
using Microsoft.SqlServer;
using Microsoft.SqlServer.Management.Smo;

namespace ConsoleAppT4 

    public class DbStuff 
    { 
        public void ListDatabases()
        {
            Server server = new Server(".");      
             foreach (Database db in server.Databases)         
                Console.WriteLine(db.Name);
        } 
    } 
}

Code 1

Compile the project and check please the output. In order to confirm the code not only compiles but works as well, open the Program.cs file. Create an instance of the DbStuff class and call the ListDatabases() method (Code 2). The output in the console should list all of your locally available databases (Picture 1).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ConsoleAppT4;

namespace ConsoleAppT4Repeat
{
    class Program
    {
        static void Main(string[] args)
        {
            DbStuff sb = new DbStuff();
            sb.ListDatabases();
            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();
        }
    }
}

Code 2

Picture 1

Next let’s change the Template in order to create in the output a class named DbStuff. This class however should have a few methods, one for each database figured on the current system. The method’s name should reflect the database instances’ names prefixed let say with “Get”. This changes the picture in the sense, that the database discovery must run at code-generation time and the C# output is dependent on the number and names of the databases. This scenario is similar discovering the Entity Framework’s edmx model generating classes and properties dynamically based upon that model.

We learned in the Part 1, that we could use and insert T4 Class (or helper) Sections which could run at code generation time. Such T4 Class Sections consists of one or more lines of code marked like: <#+ …Code goes here… #>.  After rearranging our Template in this regard we could get something like Code 3.

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".cs"  #>
// ——————————————
// <auto-generated>
//         This code was auto-generated <#= DateTime.Now #>
// <auto-generated>
// —————————————–

using System;
using Microsoft.SqlServer;
using Microsoft.SqlServer.Management.Smo;

namespace ConsoleAppT4 

    public class DbStuff 
    { 
<# ListDatabases(); #> 
    } 
}

<#+  public void ListDatabases()
{
    Server server = new Server(".");      
    foreach (Database db in server.Databases)
    {    #>

        public void Get<#= db.Name #>()
        {
        }
        
<#+ }} #>

Code 3

Please note, the two T4 Class Sections enclose the code which will be inserted into the generated output. This could be understood better while taking a look at the colored Template View (Picture 2). The lines 27-31 and 36 are making up the T4 Class (or helper) Section named ListDatabases, which is called at line 23 once from within a T4 Code Section.

  

 Picture 2

The only problem with this Template is, that it does not work and while trying to save it you are experiencing an error stating “Compiling transformation: The type or namespace name ‘Server’ could not be found (are you missing a using directive or an assembly reference?)    ….\App2\ConsoleAppT4\ConsoleAppT4Repeat\T4Text1.tt ”.

So what is matter? The problem seems to be caused by the fact, that at code-generation time the class Server is yet unknown and we are missing the appropriate assembly where Server is defined. As you remember we have added references to the SQL Server’s SMO Libraries and the code ran smoothly. Unfortunately at code-generation time the normal project references are not taken in account. In order to add references to a Template we have to use T4 Pragma Sections (Code 4) starting with the assembly keyword. The T4 Pragma Sections’ name attribute keeps the fully qualified four part name of the needed library-file. This is equivalent referencing the affected library in a normal Project.

<#@ assembly
name="Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
<#@ assembly
name="Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
<#@ assembly
name="Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>

Code 4

Saving and trying to generate the output will still result into some compilation errors. This time the assemblies are well referenced however the namespaces are yet unknown at code-generation time. We have to add more T4 Pragma Sections declaring the namespaces as well. This is something similar to the “using” declarations in a regular C# code. The namespaces are declared with another T4 Pragmas starting with the import keyword (Code 5).

<#@ import namespace="System" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#@ import namespace="Microsoft.SqlServer" #>

Code 5

Right now the Template should work fine and is depicted in Picture 3. The generated output is at Code 6.

Picture 3

// ——————————————
// <auto-generated>
//         This code was auto-generated 06/02/2010 19:05:22
// <auto-generated>
// —————————————–

using System;
using Microsoft.SqlServer;
using Microsoft.SqlServer.Management.Smo;

namespace ConsoleAppT4 

    public class DbStuff 
    { 
        public void GetACT()
        {
        }
        
        public void GetAdventureWorks()
        {
        }
        
        public void GetAdventureWorks2008()
        {
        }
        
        public void GetAdventureWorksDW()
        {
        }
        
        public void GetAdventureWorksDW2008()
        {
        }
        
        public void GetAdventureWorksLT()
        {
        }
        
        public void GetAdventureWorksLT2008()
        {
        }
        
        public void Getaspnetdb()
        {
        }
        
        public void GetASPState()
        {
        }
        
        public void Getmaster()
        {
        }
        
       

        
        public void GetReportServerTempDB()
        {
        }
        
        public void Gettempdb()
        {
        }
        
        public void GetTrackingStore()
        {
        }
        
        public void GetUsersDB()
        {
        }
          
    } 
}
Code 6
 

Let’s add some more logic into the Template filtering only those databases related to AdventureWorks. This could be done using one more conditional statement in the Template (Picture 4). Please note the changed lines 31 and 36.

Picture 4

Conclusion: this time we learned how to run code at T4 code-generation time whereas the classes needed to run are in assemblies explicitly referenced using T4 Pragma Sections. The Project containing the example from both Part 1 & Part 2 could be downloaded clicking on the icon below:

http://cid-8d365142bc4869ab.skydrive.live.com/embedicon.aspx/.Documents/ConsoleAppT4Repeat.zip

Advertisements
This entry was posted in Uncategorized - Common. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s