Using T4 to include large SQL statements

The application I’m currently working on has quite a few SQL-queries of size. I was pondering for quite a while on how to best include these queries into my application for ease of use and editing.

I had tried to work with T4 a bit before, but only to the extent of making the class-generator for PetaPoco explicitly generate classes for tables instead of explicitly ignoring tables.

T4Demo - Solution ExplorerIn my solution I have a folder called SQL. Inside this folder I have several single SQL-files. For the purpose of this example the files are filled with queries from the MSDN library.

I want to have these queries easily available to me. The method I chose for this solution was a static class with static string fields. This choice can probably be debated, but I was young and ignorant. Only one of those have changed lately, so I still think this is the best method.

In the screenshot above you can also see a file called SQL.tt. This is the “meat and potatoes” of this blog post. This is the “Text Template” file we will be looking into.

<#@ template debug="false" hostspecific="true" language="C#" #>
<#@ assembly name="System.Core" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.IO" #>
<#@ output extension=".cs" #>
<#

	var files = System.IO.Directory.EnumerateFiles(Host.ResolvePath(""), "*.sql");

#>
namespace T4Demo {
	public static class SQL {
<# foreach (var file in files) { #>
		public static string <#= Path.GetFileNameWithoutExtension(file) #> {
			get {
				return @"<#= File.ReadAllText(file) #>";
			}
		}
<# } #>
	}
}

Since the T4 engine has no knowledge of the project it is hosted in you must specify all assemblies it needs to work inside the file. You must remember to import all namespaces you need. There is a ReSharper plugin for editing templates that help with syntax highlighting and such. Also remember to set the output extension to “.cs” since we are creating a C# class.

On line 10, we enumerate all files ending with .sql in our current directory. This gives us a list of all the SQL-files we want to embed.

Notice on line 13 that we are outside the block definition tags for the template. Text outside the block definition tags (<# #>) will be repeated as is. Think of the block definition tags as the ASP.NET tags (<% %>).

<# foreach (var file in files) { #>
		public static string <#= Path.GetFileNameWithoutExtension(file) #> {
			get {
				return @"<#= File.ReadAllText(file) #>";
			}
		}
<# } #>

This is where the magic happens. Here I iterate over the files in our list of files that we got on line 10. For each of these files we want a static string. Using this skeleton:

public static string NameOfFile() {
    get {
//        return contents of file as a string
    }
}

We grab the name of the file without the .sql extension using

Path.GetFileNameWithoutExtension(file)

We then return the contents of the file using the handy

File.ReadAllText(file)

This returns a string that is immediately inserted into the template using the

<#= #>

tags.

Note that we must use a verbatim string literal (the @ in front of the string) because our file probably contains multiple lines.

When you save this template Visual Studio is probably going to ask you if you want to run the template. If you answer yes to this you are going to end up with a file that looks something like this:

namespace T4Demo {
	public static class SQL {
		public static string Query1 {
			get {
				return @"USE AdventureWorks2012;
GO
SELECT *
FROM Production.Product
ORDER BY Name ASC;
-- Alternate way.
USE AdventureWorks2012;
GO
SELECT p.*
FROM Production.Product AS p
ORDER BY Name ASC;
GO";
			}
		}
		public static string Query2 {
			get {
				return @"USE AdventureWorks2012;
GO
SELECT p.Name AS ProductName,
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName DESC;
GO";
			}
		}
		public static string Query3 {
			get {
				return @"USE tempdb;
GO
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL
DROP TABLE #Bicycles;
GO
SELECT *
INTO #Bicycles
FROM AdventureWorks2012.Production.Product
WHERE ProductNumber LIKE 'BK%';
GO";
			}
		}
		public static string Query4 {
			get {
				return @"USE AdventureWorks2012;
GO
SELECT DISTINCT p.LastName, p.FirstName
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN
    (SELECT Bonus
     FROM Sales.SalesPerson AS sp
     WHERE e.BusinessEntityID = sp.BusinessEntityID);
GO";
			}
		}
		public static string Query5 {
			get {
				return @"USE AdventureWorks2012;
GO
SELECT DISTINCT pp.LastName, pp.FirstName
FROM Person.Person pp JOIN HumanResources.Employee e
ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN
(SELECT SalesPersonID
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN
(SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE ProductID IN
(SELECT ProductID
FROM Production.Product p
WHERE ProductNumber = 'BK-M68B-42')));
GO";
			}
		}
	}
}

It’s not pretty, but you are not going to look at this file anyway. Remember, it’s auto generated and you should not be changing anything inside it.

I use this solution on a project with ~25 large SQL-files, and it works without  any problems. One catch is that if you edit a SQL-file you need to open the TT-file and save it. This is necessary to run the transformation. I have tried a few plugins that are supposed to auto run any templates on build but I haven’t gotten any to work.

Leave a Reply

Your email address will not be published. Required fields are marked *