As I work through T4 templates as I get ready for Boise Code Camp, I’ve noticed a few styles of template code. Just for kicks, I am going to name them and give an example of each using a code generation that I blogged about early last year. Each template uses an include file that I present after the four samples. Each template relies on its style to the point of absurdity; I didn’t go as far as to eliminate all text blocks.
Real life templates are mixtures of these styles applied at appropriate places.
I adapted the code generation that I did here
Embed
This can also be called the Classic ASP style. All generated code is added as text blocks or expression blocks (<#= #>). The logic is contained in code blocks mixed in with the text and expression blocks. For simple logic, this style is probably OK. It can be difficult to read and maintain as the logic gets more complex. This is the most concrete approach.
<#@ template language="C#v3.5" debug="true" #>
<#@ output extension="sql" #>
<#@ include file="TableDescriptionUtilCS.tt" #>
<#
string ConnectionString = @"Data Source=localhost\SQLEXPRESS;" +
"Initial Catalog=Northwind;Integrated Security=True";
string Table = "Employees";
TableDescriptionUtil tdu = new TableDescriptionUtil();
List<TableDescription> Columns = tdu.GetTableDescription(ConnectionString, Table);
int counter;
#>
CREATE PROCEDURE [dbo].[Insert<#=Table #>]
<#
counter = 1;
foreach (TableDescription item in Columns)
{
#> @<#= item.ColName #> <#= item.FullTypeName #><#
if (item.Status == 0x80)
#> OUT<#
if (item.ColIsNullable != 0)
#> = NULL<#
if (counter < Columns.Count)
#>,<#
counter++;
#><#= "\r\n"#><#
}
#>
AS
INSERT INTO [<#=Table #>] (<#
counter = 1;
foreach (TableDescription item in Columns)
{
if (item.Status != 0x80)
{
if (counter > 1)
#>, <#
if (counter % 5 == 0)
#><#= "\r\n " #><#
counter++;
#>[<#= item.ColName #>]<#
}
}
#>)
VALUES (<#
counter = 1;
foreach (TableDescription item in Columns)
{
if (item.Status != 0x80)
{
if (counter > 1)
#>, <#
if (counter % 5 == 0)
#><#= "\r\n " #><#
counter++;
#>@<#= item.ColName #><#
}
}
#>)
SET @<#
foreach (TableDescription item in Columns)
{
if (item.Status == 0x80)
{
#><#=item.ColName #><#
break;
}
}
#> = @@IDENTITY
Inline Write
This style uses code blocks that contain Write(), WriteLine(), PushIndent(), PopIndent(), and other statements.
This approach is more abstract that Embed, it is easier for me to read than Embed or String Function.
<#@ template language="C#v3.5" debug="true" #>
<#@ output extension="sql" #>
<#@ include file="TableDescriptionUtilCS.tt" #>
<#
string ConnectionString = @"Data Source=localhost\SQLEXPRESS;" +
Initial Catalog=Northwind;Integrated Security=True";
string Table = "Employees";
TableDescriptionUtil tdu = new TableDescriptionUtil();
List<TableDescription> Columns = tdu.GetTableDescription(ConnectionString, Table);
int counter;
#>
CREATE PROCEDURE [dbo].[Insert<# Write(Table); #>]
<#
PushIndent(" ");
counter = 1;
foreach (TableDescription item in Columns)
{
Write("@" + item.ColName + " " + item.FullTypeName);
if (item.Status == 0x80)
Write(" OUT");
if (item.ColIsNullable != 0)
Write(" = NULL");
if (counter < Columns.Count)
WriteLine(",");
counter++;
}
PopIndent();
#>
AS
INSERT INTO [<# Write(Table); #>] (<#
PushIndent(" ");
counter = 1;
foreach (TableDescription item in Columns)
{
if (item.Status != 0x80)
{
if (counter > 1)
Write(", ");
if (counter % 5 == 0)
WriteLine(String.Empty);
counter++;
Write(string.Format("[{0}]", item.ColName));
}
}
PopIndent();
#>)
VALUES (<#
PushIndent(" ");
counter = 1;
foreach (TableDescription item in Columns)
{
if (item.Status != 0x80)
{
if (counter > 1)
Write(", ");
if (counter % 5 == 0)
WriteLine(string.Empty);
counter++;
Write(String.Format("@{0}", item.ColName));
}
}
PopIndent();
#>)
SET @<#
foreach (TableDescription item in Columns)
{
if (item.Status == 0x80)
{
Write(item.ColName);
break;
}
}
#> = @@IDENTITY
String Function
Here I call a function in an expression block that returns a bit of generated code as a string. The functions are in feature blocks (or include file, linked assemblies, etc.).
Since I did some primitive string based code gen for a number of years, I find myself relying on this approach. This approach can lead to code just as ugly as the Embed approach. This approach is good for adapting old fashioned code gen to T4.
<#@ template language="C#v3.5" debug="true" #>
<#@ output extension="sql" #>
<#@ include file="TableDescriptionUtilCS.tt" #>
<#
string ConnectionString = @"Data Source=localhost\SQLEXPRESS;" +
"Initial Catalog=Northwind;Integrated Security=True";
string Table = "Employees";
TableDescriptionUtil tdu = new TableDescriptionUtil();
List<TableDescription> Columns = tdu.GetTableDescription(ConnectionString, Table);
int counter;
#>
CREATE PROCEDURE [dbo].[Insert<#= getTableName(Table) #>]
<#= getParameterList(Columns) #>
AS
INSERT INTO [<#= getTableName(Table) #>] (<#= getFieldNames(Columns) #>)
VALUES (<#= getValueNames(Columns) #>)
SET @<#=getIdentityFieldName(Columns) #> = @@IDENTITY
<#+
string getTableName(string table)
{
// Yes, this is hard core overkill
return table;
}
string getParameterList(List<TableDescription> columns)
{
string rVal = string.Empty;
int counter = 1;
foreach (TableDescription item in columns)
{
rVal += " @" + item.ColName + " " + item.FullTypeName;
if (item.Status == 0x80)
rVal += " OUT";
if (item.ColIsNullable != 0)
rVal += " = NULL";
if (counter < columns.Count)
rVal += ",\r\n";
counter++;
}
return rVal;
}
string getFieldNames(List<TableDescription> columns)
{
string rVal = string.Empty;
int counter = 1;
foreach (TableDescription item in columns)
{
if (item.Status != 0x80)
{
if (counter > 1)
rVal += ", ";
if (counter % 5 == 0)
rVal += "\r\n ";
counter++;
rVal += string.Format("[{0}]", item.ColName);
}
}
return rVal;
}
string getValueNames(List<TableDescription> columns)
{
string rVal = string.Empty;
int counter = 1;
foreach (TableDescription item in columns)
{
if (item.Status != 0x80)
{
if (counter > 1)
rVal += ", ";
if (counter % 5 == 0)
rVal += "\r\n ";
counter++;
rVal += string.Format("@{0}", item.ColName);
}
}
return rVal;
}
string getIdentityFieldName(List<TableDescription> columns)
{
string rVal = string.Empty;
foreach (TableDescription item in columns)
{
if (item.Status == 0x80)
{
rVal =item.ColName;
break;
}
}
return rVal;
}
#>
Write Function
Here I call a function to generate code a block of code with Write functions.
Unlike the String Function approach, the code generation is relying on a side effect of the function, so this approach isn’t functional.
<#@ template language="C#v3.5" debug="true" #>
<#@ output extension="sql" #>
<#@ include file="TableDescriptionUtilCS.tt" #>
<#
string ConnectionString = @"Data Source=localhost\SQLEXPRESS;" +
"Initial Catalog=Northwind;Integrated Security=True";
string Table = "Employees";
TableDescriptionUtil tdu = new TableDescriptionUtil();
List<TableDescription> Columns = tdu.GetTableDescription(ConnectionString, Table);
int counter;
#>
CREATE PROCEDURE [dbo].[Insert<# writeTableName(Table); #>]
<# writeParameterList(Columns); #>
AS
INSERT INTO [<# writeTableName(Table); #>] (<# writeFieldNames(Columns); #>)
VALUES (<# writeValueNames(Columns); #>)
SET @<# writeIdentityFieldName(Columns); #> = @@IDENTITY
<#+
void writeTableName(string table)
{
Write(table);
}
void writeParameterList(List<TableDescription> columns)
{
PushIndent(" ");
int counter = 1;
foreach (TableDescription item in columns)
{
Write("@" + item.ColName + " " + item.FullTypeName);
if (item.Status == 0x80)
Write(" OUT");
if (item.ColIsNullable != 0)
Write(" = NULL");
if (counter < columns.Count)
WriteLine(",");
counter++;
}
PopIndent();
}
void writeFieldNames(List<TableDescription> columns)
{
PushIndent(" ");
int counter = 1;
foreach (TableDescription item in columns)
{
if (item.Status != 0x80)
{
if (counter > 1)
Write(", ");
if (counter % 5 == 0)
WriteLine(String.Empty);
counter++;
Write(string.Format("[{0}]", item.ColName));
}
}
PopIndent();
}
void writeValueNames(List<TableDescription> columns)
{
PushIndent(" ");
int counter = 1;
foreach (TableDescription item in columns)
{
if (item.Status != 0x80)
{
if (counter > 1)
Write(", ");
if (counter % 5 == 0)
WriteLine(string.Empty);
counter++;
Write(String.Format("@{0}", item.ColName));
}
}
PopIndent();
}
void writeIdentityFieldName(List<TableDescription> columns)
{
foreach (TableDescription item in columns)
{
if (item.Status == 0x80)
{
Write(item.ColName);
break;
}
}
}
#>
Include File: TableDescriptionUtil.tt
I included this file in all the examples above.
<#@ assembly name="System.Data.dll" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#+
public class TableDescriptionUtil : TextTransformation
{
/// <remarks>
/// I don't actually call this, but it is necessary for T4 Templates to work
/// </remarks>
public override string TransformText()
{
return "";
}
/// <summary>
/// Gets a DataReader containing selected column information for all of the
/// columns in the indecated table from SQL Server System Tables
/// </summary>
/// <param name="tableName">Get information on THIS table</param>
/// <param name="conn">An existing open connection to be used </param>
/// <returns>A DataReader pointing to the results</returns>
private SqlDataReader GetTableDefReader(string tableName , SqlConnection conn )
{
SqlDataReader reader;// = new SqlDataReader();
string getDataForTable =
" SELECT c.name AS col_name, " +
" t.name AS type_name, " +
" CAST(c.length AS INT) AS length, " +
" c.isnullable, " +
" CAST(c.status AS INT) AS status " +
" FROM " +
" sysObjects o " +
" JOIN sysColumns c ON c.id = o.id " +
" JOIN systypes t on c.xtype = t.xtype AND t.status = 0 " +
" WHERE o.name = @tableName " +
" ORDER BY c.colid ";
using (var cmd = new SqlCommand(getDataForTable, conn))
{
cmd.Parameters.AddWithValue("@tableName", tableName);
reader = cmd.ExecuteReader();
}
return reader;
}
/// <summary>
/// Gets a List of column descriptions for a given table
/// </summary>
/// <param name="ConnectionString">Connection string to the database</param>
/// <param name="Table">The name of the table to describe</param>
/// <returns>A list of column descriptions</returns>
public List<TableDescription> GetTableDescription(string ConnectionString,
string Table)
{
SqlDataReader reader;
List<TableDescription> Columns = new List<TableDescription>();
using (var conn = new SqlConnection(ConnectionString))
{
conn.Open();
reader = GetTableDefReader(Table, conn);
while (reader.Read())
{
TableDescription Column = new TableDescription();
Column.ColName = reader.GetString(0);
Column.TypeName = reader.GetString(1);
Column.ColLength = reader.GetInt32(2);
Column.ColIsNullable = reader.GetInt32(3);
Column.Status = reader.GetInt32(4);
Column.FullTypeName = GetTypeName(Column.TypeName, Column.ColLength);
Columns.Add(Column);
}
conn.Close();
}
return Columns;
}
/// <summary>
/// Gets the TypeName as it should appear in a SP Parameter List
/// </summary>
/// <param name="TypeName"></param>
/// <param name="ColLength"></param>
/// <returns>The processed Type Name as a string</returns>
private string GetTypeName(string typeName , int ColLength )
{
switch (typeName.ToLower())
{
case "varchar":
case "nvarchar":
string lenString = ColLength.ToString();
if (ColLength == -1 )
lenString = "MAX";
return string.Format("{0}({1})", typeName, lenString);
default:
return typeName;
}
}
}
public class TableDescription
{
public string ColName {get; set;}
public string TypeName {get; set;}
public string FullTypeName {get; set;}
public int ColLength {get; set;}
public int Status {get; set;}
public int ColIsNullable {get; set;}
}
#>
