The new MacBook, it’s not for you.

So apple “just” launched the new MacBook. And it’s stunning. 13.1mm thin, 0.9kg light. It’s awesome. But it’s not for you.

 

You know, You… The one that keeps complaining about all the stuff it’s missing. That it only has one USB 3.1 Type-C port (it also has a headphone jack by the way). That it’s CPU is way too slow and that it’s not upgradeable in any way. It’s not for you.

It’s for someone who wants a thin and nice travel companion with just enough space and just enough oomph. If you need to have terabytes of storage, this is not for you. If you need to connect 8 usb devices at the same time, this is not for you.

Why would you need all these usb connections? Mobile broadband? My phone can share it’s connection via wifi. Doesn’t every phone do this by now? Mouse? Surely you can get a BT mouse if you really need one, but do you really need one? Printer? Most printers the last few years have been wireless unless you venture in the “our price is so low, new ink costs more than the printer”-market.

“And it runs OS X”. Well, yes. It’s a mac. But you can get an equivalent machine for Windows use also.

ux305_edition_02

The Asus Zenbook UX305. Is this one for you? I don’t know, you may decide. It’s essentially the same machine hardware wise. It has 3 USB 3.0 ports, although they are not USB-C, so no DisplayPort. It has a mini-HDMI port, but who has mini-HDMI anyway. You’ll need a dongle for that.

My point is if you can’t see why in the world anyone would buy any of these, it’s not for you. Apple has a whole product range of laptops, from the 11″ MacBook Air, to the 15″ retina MacBook Pro. And if you don’t like Apple, then don’t buy a MacBook at all. Buy the Zenbook. Or if you want something bigger, buy something bigger. But stop complaining that something doesn’t fit you when it’s not made for you!

 

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.