T4 template to create *multiple* html (for example) output files per table from database schema

Question

I'd like to use some T4 templates to generate html files derived from a sql server (in this case) database schema. For each table in the database, I would like to create 2 files:

1. tableName_List.aspx - would contain the appropriate html to display in an asp.net GridView, with a grid column defined for each db table column

2. tableName_Edit.aspx - would contain the appropriate html to display in an asp.net FormView, with a textbox (for simplicity's sake, for now) for each db table column

So, if I have 5 tables in the database, I would get 10 files output. I've been googling this and found related articles, but most of them don't seem to address this scenario. I've also seen references to using subsonic for this, but I'd rather not introduce yet another technology into the mix.

Answer 1

The code for the T4 template below will give you a relatively good start.

You will need to add references to the appropriate versions of the Microsoft.SqlSserver Smo DLLs to the project.

The following items need to be replaced in this code with the appropriate values for your environment:

SERVERNAMEGOESHERE
DATABASENAMEGOESHERE
PROJECTNAMESPACEGOESHERE

<#@ template language="C#v3.5" hostspecific="true" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="Microsoft.SqlServer.Management.Common" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#
    string connectionString = @"Server=SERVERNAMEGOESHERE;Trusted_Connection=True;";
    string databaseName = "DATABASENAMEGOESHERE";
    string projectNamespace = "PROJECTNAMESPACEGOESHERE";
    string relativeOutputFilePath = null;

    SqlConnection oneSqlConnection = new SqlConnection(connectionString);
    ServerConnection oneServerConnection = new ServerConnection(oneSqlConnection);
    Server oneServer = new Server(oneServerConnection);
    Database oneDatabase = oneServer.Databases[databaseName];
    foreach (Table oneTable in oneDatabase.Tables)
    {
        if (!oneTable.Name.Equals("sysdiagrams"))
        {
#>
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="<#= oneTable.Name #>_List.aspx.cs" Inherits="<#= projectNamespace #>.<#= oneTable.Name #>_List" %>
<asp:DataGrid ID="<#= oneTable.Name #>DataGrid" runat="server" AutoGenerateColumns="false">
    <Columns>
<#
            foreach (Column oneColumn in oneTable.Columns)
            {
#>
        <asp:BoundColumn DataField="<#= oneColumn.Name #>" HeaderText="<#= oneColumn.Name #>"></asp:BoundColumn>
<#
            }
#>
    </Columns>
</asp:DataGrid>
<#
            relativeOutputFilePath = @"\Output\" + oneTable.Name + "_List.aspx";
            TemplateHelper.WriteTemplateOutputToFile(relativeOutputFilePath, Host, GenerationEnvironment);
            GenerationEnvironment = new System.Text.StringBuilder();
#>
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="<#= oneTable.Name #>_Edit.aspx.cs" Inherits="<#= projectNamespace #>.<#= oneTable.Name #>_Edit" %>
<#
            foreach (Column oneColumn in oneTable.Columns)
            {
#>
    <asp:TextBox ID="<#= oneColumn.Name #>TextBox" runat="server" />
<#
            }
            relativeOutputFilePath = @"\Output\" + oneTable.Name + "_Edit.aspx";
            TemplateHelper.WriteTemplateOutputToFile(relativeOutputFilePath, Host, GenerationEnvironment);
            GenerationEnvironment = new System.Text.StringBuilder();
        }
    }
#>
<#+
public class TemplateHelper
{
    public static void WriteTemplateOutputToFile(
        string relativeOutputFilePath,
        Microsoft.VisualStudio.TextTemplating.ITextTemplatingEngineHost Host,
        System.Text.StringBuilder GenerationEnvironment)
    {
        string outputPath = System.IO.Path.GetDirectoryName(Host.TemplateFile);
        string outputFilePath = outputPath + relativeOutputFilePath;
        System.IO.File.WriteAllText(outputFilePath, GenerationEnvironment.ToString());
    }
}
#>

Answer 2

Here are the turorial articles that walk you through implementing a code generator that produces multiple .sql files for each table in a SQL database:

http://www.olegsych.com/2008/09/t4-tutorial-creating-reusable-code-generation-templates/

http://www.olegsych.com/2008/09/t4-tutorial-creating-complex-code-generators/

The complete tutorial can be found here: http://t4toolbox.codeplex.com

Answer 3

This article describes how to create multiple files from 1 template:

http://damieng.com/blog/2009/11/06/multiple-outputs-from-t4-made-easy-revisited

You can view a sample of this approach by downloading T4CSS and checking out the source.

As an alternative, you may find this approach more straightforward for simple cases:

http://www.olegsych.com/2008/03/how-to-generate-multiple-outputs-from-single-t4-template/