Wednesday, March 11, 2009

How to Generate Database Setup and Teardown MSBuild script with T4?

Problem

I been getting lots of emails on my previous article on using T4 template to generate Wix scripts by recursively looping through the source directory. I been using T4 template a lot lately in our continuous integration and I am beginning to replace lots of my XSL with T4 templates because C# is much easier to deal with then XSL. And I think this is purely personal preference. Also with Linq to XMl T4 template just got much more poweful.


References used to research and learn

Many feedbacks I received from the readers.
how to use msbuild and wix to msi


Assumption

1) Familiar with C#, XML, Linq
2) Familiar with MSBuild


About the project

Download: GenerateDatabaseSetupAndTeardownWithT4T.zip

I will cover how to generate MSBuild scripts to setup and tear down database. This is necessary if you truly practice continuous integration's one click deployment and automation.

1) How to quickly start using T4?
2) What is alternate way to debug?
3) Best practices we found that we want to share that worked well for us.

Here is very useful way to utilize T4 template in Continuous Integration



We first centralize everything in VARS.XML that contains all the configuration (dev, qa, uat, stg ect...). In the past I used XSL to spit out apps.config or web.config for different environment but with T4 Template it is much easier by templatizing variables and with XML to Linq it got that much easier to deal with.

Requirements

1) VS 2008
2) .Net 3.5 SP1


Step by step instruction

Create Console App Project



Add text file with .tt extension GenerateDatabaseSetupAndTeardown.tt

Click Ok to VS 2008 Warning



Here is the trick with T4 Template. Strength of T4 Template is that it is literally C# code with few exceptions. To get a quick start with T4 template in my opinion it is best to start with Console and or NUnit to create C# code. In this example Console app is used to spit out strings using Console.WriteLine in T4 Template it uses WriteLine. Now there is way to replace using C# variables but I am not going to cover that here. I taking the approach of typical programmer mindset and translate that into T4.

REMEBER TO REPLACE varsXml pointing to where vars.xml is.

using System;
using System.Text;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
 
namespace GenerateDatabaseSetupAndTeardownWithT4T
{
  class Program
  {
    static void Main(string[] args)
    {
      XDocument doc;
      using (StreamReader s = File.OpenText(varsXml))
      {
        doc = XDocument.Load(s);
      }
 
      var dbs = from b in doc.Elements("Configurations")
                    .Elements("Global.Config")
                    .Elements("Databases")
                    .Elements("Database")
            where b.Attribute("ScriptThis").Value.ToLower().Equals("yes")
            select new
            {
              ScriptUser = b.Attribute("ScriptUser") == null ? null : b.Attribute("ScriptUser").Value,
              ScriptUserPassword = b.Attribute("ScriptUserPassword") == null ? null : b.Attribute("ScriptUserPassword").Value,
              Vars = b.Elements("Var"),
              Scripts = b.Elements("Scripts").Elements("Script")
            };
 
      List<string> sqlDataFileRoot = new List<string>();
      StringBuilder sb = new StringBuilder();
      foreach (var db in dbs)
      {
        string dataSource = string.Empty;
        string initialCatalog = string.Empty;
        string userID = db.ScriptUser;
        string password = db.ScriptUserPassword;
        string dropDB = string.Empty;
        string createDB = string.Empty;
        string currentSchema = string.Empty;
        string currentData = string.Empty;
 
        // extract from Var node
        foreach (XElement var in db.Vars)
        {
          if (var.Attribute("Name").Value.Equals("DataSource"))
          {
            dataSource = var.Attribute("Value").Value;
          }
          else if (var.Attribute("Name").Value.Equals("InitialCatalog"))
          {
            initialCatalog = var.Attribute("Value").Value;
          }
          else if (var.Attribute("Name").Value.Equals("SqlDataFileRoot"))
          {
            sqlDataFileRoot.Add(var.Attribute("Value").Value);
          }
        }
 
        // extract from Script node
        foreach (XElement script in db.Scripts)
        {
          if (script.Attribute("Name").Value.Equals("DropDB"))
          {
            dropDB = script.Attribute("Value").Value;
          }
          else if (script.Attribute("Name").Value.Equals("CreateDB"))
          {
            createDB = script.Attribute("Value").Value;
          }
          else if (script.Attribute("Name").Value.Equals("CurrentSchema"))
          {
            currentSchema = script.Attribute("Value").Value;
          }
          else if (script.Attribute("Name").Value.Equals("CurrentData"))
          {
            currentData = script.Attribute("Value").Value;
          }
        }
 
        // Generate MSBuild that tears down the database based no
        if (!string.IsNullOrEmpty(userID) && !string.IsNullOrEmpty(password))
        {
          sb.AppendFormat(@"<Sql.GetDatabaseVersion{0}", Environment.NewLine);
          sb.AppendFormat(@"      DatabaseName=""{0}""{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""user id={0};password={1};data source={2};initial catalog={3};""{4}", userID, password, dataSource, initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      CommandTimeout=""60"" {0}", Environment.NewLine);
          sb.AppendFormat(@"      ContinueOnError=""true"">{0}", Environment.NewLine);
          sb.AppendFormat(@"      {0}", Environment.NewLine);
          sb.AppendFormat(@"      <Output TaskParameter=""DatabaseVersion"" PropertyName=""{0}-DatabaseVersion""/>{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"    </Sql.GetDatabaseVersion>{0}", Environment.NewLine);
          sb.AppendFormat(@"    <Sql.DisconnectUsers{0}", Environment.NewLine);
          sb.AppendFormat(@"      DatabaseName=""{0}""{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""user id={0};password={1};data source={2};initial catalog={3};""{4}", userID, password, dataSource, initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      CommandTimeout=""60"" {0}", Environment.NewLine);
          sb.AppendFormat(@"      Condition= "" '$({0}-DatabaseVersion)' != '0.0' and '$({0}-DatabaseVersion)' != '' "" />{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"    <Sql.Execute {0}", Environment.NewLine);
          sb.AppendFormat(@"      Path=""{0}"" {1}", dropDB, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""user id={0};password={1};data source={2};initial catalog=master;"" />{3}", userID, password, dataSource, Environment.NewLine);
 
        }
        else
        {
          sb.AppendFormat(@"<Sql.GetDatabaseVersion{0}", Environment.NewLine);
          sb.AppendFormat(@"      DatabaseName=""{0}""{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""Integrated Security=true;data source={0};initial catalog={1};"" {2}", dataSource, initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      CommandTimeout=""60""{0}", Environment.NewLine);
          sb.AppendFormat(@"      ContinueOnError=""true"">{0}", Environment.NewLine);
          sb.AppendFormat(@"      {0}", Environment.NewLine);
          sb.AppendFormat(@"      <Output TaskParameter=""DatabaseVersion"" PropertyName=""{0}-DatabaseVersion""/>{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"    </Sql.GetDatabaseVersion>{0}", Environment.NewLine);
          sb.AppendFormat(@"    <Sql.DisconnectUsers{0}", Environment.NewLine);
          sb.AppendFormat(@"      DatabaseName=""{0}""{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""Integrated Security=true;data source={0};initial catalog={1};"" {2}", dataSource, initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      CommandTimeout=""60"" {0}", Environment.NewLine);
          sb.AppendFormat(@"      Condition= "" '$({0}-DatabaseVersion)' != '0.0' and '$({0}-DatabaseVersion)' != '' "" />{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"    <Sql.Execute {0}", Environment.NewLine);
          sb.AppendFormat(@"      Path=""{0}""{1}", dropDB, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""Integrated Security=true;data source={0};initial catalog=master;"" />{1}", dataSource, Environment.NewLine);
 
        }
      }
 
      foreach (string dir in sqlDataFileRoot)
      {
        sb.AppendFormat(@"<RemoveDir Directories=""{0}"" />{1}", dir, Environment.NewLine);
      }
 
      Console.WriteLine(sb.ToString());
 
    }
  }
}


Once tested bring it over to GenerateDatabaseSetupAndTeardown.tt

<#@ template language="C#v3.5" #>
<#@ assembly name="System.Core.dll" #>
<#@ assembly name="System.Xml.dll" #>
<#@ assembly name="System.Xml.Linq.dll" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="System.Xml.Linq" #>
<#@ import namespace="System" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Diagnostics" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Collections" #>
<#@ import namespace="System.Security.Cryptography" #>
<#@ output extension=".xml" #>
<#  
// string varsXml = @"[--VRSXML--]";
string varsXml = @"E:\repos\nas\src\blogs\GenerateDatabaseSetupAndTeardownWithT4T\GenerateDatabaseSetupAndTeardownWithT4T\vars.xml";
string dataSource;
string initialCatalog;
string userID;
string password;
 
GenerateScript(varsXml);
 
#>
 
<#+ private void GenerateScript(string varsXml) { #>
<?xml version="1.0" encoding="utf-8"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <Target Name="CleanDatabases">
<#+
      XDocument doc;
      using(StreamReader s = File.OpenText(varsXml))
      {
        doc = XDocument.Load(s);
      }
 
      var dbs = from b in doc.Elements("Configurations")
                    .Elements("Global.Config")
                    .Elements("Databases")
                    .Elements("Database")
              where b.Attribute("ScriptThis").Value.ToLower().Equals("yes")
              select new
              {
                ScriptUser = b.Attribute("ScriptUser") == null ? null : b.Attribute("ScriptUser").Value,
                ScriptUserPassword = b.Attribute("ScriptUserPassword") == null ? null : b.Attribute("ScriptUserPassword").Value,
                Vars = b.Elements("Var"),
                Scripts = b.Elements("Scripts").Elements("Script")
              };
 
      List<string> sqlDataFileRoot = new List<string>();
      StringBuilder sb = new StringBuilder();
      foreach (var db in dbs)
      {
        string dataSource = string.Empty;
        string initialCatalog = string.Empty;
        string userID = db.ScriptUser;
        string password = db.ScriptUserPassword;
        string dropDB = string.Empty;
        string createDB = string.Empty;
        string currentSchema = string.Empty;
        string currentData = string.Empty;
 
        // extract from Var node
        foreach (XElement var in db.Vars)
        {
          if (var.Attribute("Name").Value.Equals("DataSource"))
          {
            dataSource = var.Attribute("Value").Value;
          }
          else if (var.Attribute("Name").Value.Equals("InitialCatalog"))
          {
            initialCatalog = var.Attribute("Value").Value;
          }
          else if (var.Attribute("Name").Value.Equals("SqlDataFileRoot"))
          {
            sqlDataFileRoot.Add(var.Attribute("Value").Value);
          }
        }
 
        // extract from Script node
        foreach (XElement script in db.Scripts)
        {
          if (script.Attribute("Name").Value.Equals("DropDB"))
          {
            dropDB = script.Attribute("Value").Value;
          }
          else if (script.Attribute("Name").Value.Equals("CreateDB"))
          {
            createDB = script.Attribute("Value").Value;
          }
          else if (script.Attribute("Name").Value.Equals("CurrentSchema"))
          {
            currentSchema = script.Attribute("Value").Value;
          }
          else if (script.Attribute("Name").Value.Equals("CurrentData"))
          {
            currentData = script.Attribute("Value").Value;
          }
        }
 
        // Generate MSBuild that tears down the database based no
        if (!string.IsNullOrEmpty(userID) && !string.IsNullOrEmpty(password))
        {
          sb.AppendFormat(@"<Sql.GetDatabaseVersion{0}", Environment.NewLine);
          sb.AppendFormat(@"      DatabaseName=""{0}""{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""user id={0};password={1};data source={2};initial catalog={3};""{4}", userID, password, dataSource, initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      CommandTimeout=""60"" {0}", Environment.NewLine);
          sb.AppendFormat(@"      ContinueOnError=""true"">{0}", Environment.NewLine);
          sb.AppendFormat(@"      {0}", Environment.NewLine);
          sb.AppendFormat(@"      <Output TaskParameter=""DatabaseVersion"" PropertyName=""{0}-DatabaseVersion""/>{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"    </Sql.GetDatabaseVersion>{0}", Environment.NewLine);
          sb.AppendFormat(@"    <Sql.DisconnectUsers{0}", Environment.NewLine);
          sb.AppendFormat(@"      DatabaseName=""{0}""{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""user id={0};password={1};data source={2};initial catalog={3};""{4}", userID, password, dataSource, initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      CommandTimeout=""60"" {0}", Environment.NewLine);
          sb.AppendFormat(@"      Condition= "" '$({0}-DatabaseVersion)' != '0.0' and '$({0}-DatabaseVersion)' != '' "" />{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"    <Sql.Execute {0}", Environment.NewLine);
          sb.AppendFormat(@"      Path=""{0}"" {1}", dropDB, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""user id={0};password={1};data source={2};initial catalog=master;"" />{3}", userID, password, dataSource, Environment.NewLine);
 
        }
        else
        {
          sb.AppendFormat(@"<Sql.GetDatabaseVersion{0}", Environment.NewLine);
          sb.AppendFormat(@"      DatabaseName=""{0}""{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""Integrated Security=true;data source={0};initial catalog={1};"" {2}", dataSource, initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      CommandTimeout=""60""{0}", Environment.NewLine);
          sb.AppendFormat(@"      ContinueOnError=""true"">{0}", Environment.NewLine);
          sb.AppendFormat(@"      {0}", Environment.NewLine);
          sb.AppendFormat(@"      <Output TaskParameter=""DatabaseVersion"" PropertyName=""{0}-DatabaseVersion""/>{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"    </Sql.GetDatabaseVersion>{0}", Environment.NewLine);
          sb.AppendFormat(@"    <Sql.DisconnectUsers{0}", Environment.NewLine);
          sb.AppendFormat(@"      DatabaseName=""{0}""{1}",initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""Integrated Security=true;data source={0};initial catalog={1};"" {2}",dataSource, initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"      CommandTimeout=""60"" {0}", Environment.NewLine);
          sb.AppendFormat(@"      Condition= "" '$({0}-DatabaseVersion)' != '0.0' and '$({0}-DatabaseVersion)' != '' "" />{1}", initialCatalog, Environment.NewLine);
          sb.AppendFormat(@"    <Sql.Execute {0}", Environment.NewLine);
          sb.AppendFormat(@"      Path=""{0}""{1}", dropDB, Environment.NewLine);
          sb.AppendFormat(@"      ConnectionString=""Integrated Security=true;data source={0};initial catalog=master;"" />{1}", dataSource, Environment.NewLine);
 
        }
      }
 
      foreach(string dir in sqlDataFileRoot)
      {
        sb.AppendFormat(@"<RemoveDir Directories=""{0}"" />{1}", dir, Environment.NewLine);
      }
 
      WriteLine(sb.ToString());
#>
  </Target>
</Project>
<#+ } #>


When you Save .TT file you will see that it spits out MSBuild script

<?xml version="1.0" encoding="utf-8"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <Target Name="CleanDatabases">
<Sql.GetDatabaseVersion
      DatabaseName="MyProject1"
      ConnectionString="user id=sa;password=password007;data source=localhost;initial catalog=MyProject1;"
      CommandTimeout="60" 
      ContinueOnError="true">
 
      <Output TaskParameter="DatabaseVersion" PropertyName="MyProject1-DatabaseVersion"/>
    </Sql.GetDatabaseVersion>
    <Sql.DisconnectUsers
      DatabaseName="MyProject1"
      ConnectionString="user id=sa;password=password007;data source=localhost;initial catalog=MyProject1;"
      CommandTimeout="60" 
      Condition= " '$(MyProject1-DatabaseVersion)' != '0.0' and '$(MyProject1-DatabaseVersion)' != '' " />
    <Sql.Execute 
      Path="$(SqlRoot)DropMyDB1.sql" 
      ConnectionString="user id=sa;password=password007;data source=localhost;initial catalog=master;" />
<Sql.GetDatabaseVersion
      DatabaseName="MyProject2"
      ConnectionString="user id=sa;password=password321;data source=localhost;initial catalog=MyProject2;"
      CommandTimeout="60" 
      ContinueOnError="true">
 
      <Output TaskParameter="DatabaseVersion" PropertyName="MyProject2-DatabaseVersion"/>
    </Sql.GetDatabaseVersion>
    <Sql.DisconnectUsers
      DatabaseName="MyProject2"
      ConnectionString="user id=sa;password=password321;data source=localhost;initial catalog=MyProject2;"
      CommandTimeout="60" 
      Condition= " '$(MyProject2-DatabaseVersion)' != '0.0' and '$(MyProject2-DatabaseVersion)' != '' " />
    <Sql.Execute 
      Path="$(SqlRoot)DropMyDB2.sql" 
      ConnectionString="user id=sa;password=password321;data source=localhost;initial catalog=master;" />
<RemoveDir Directories="c:\sqldata\MyDB1" />
<RemoveDir Directories="c:\sqldata\MyDB2" />
 
  </Target>
</Project>
 


Conclusion

Before Linq I did not consider using T4 template for anything else but for Wix script generation but now with Linq to XML it is much easier to read and deal with XML then XSL in my opinion. And I get much more control with C#. Of course in typical SCM some people are not comfortable with C# so this approach I shown here definitely is not for everyone.