Auto Executing Generated SQL Scripts

When a CodeSmith Generator template generates a SQL script, such as one that creates stored procedures, you can run the script against the database right after it's generated. That way, generating the script also builds the objects in the database.

The ScriptUtility class in CodeSmith.BaseTemplates has an ExecuteScript method for this. It splits the script into batches at each GO and runs them against a SQL Server connection. The most convenient place to call it is an override of the template's OnPostRender method, which runs after the template has rendered its output.

Example

This example is adapted from the StoredProcedures.cst sample template (in the Database\StoredProcedures folder of the samples). An AutoExecuteScript property lets the user decide whether to run the script:

<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="1. Context"
	Description="Table that the stored procedures should be based on." %>
<%@ Property Name="AutoExecuteScript" Type="System.Boolean" Default="False" Category="2. Options"
	Description="Whether or not to immediately execute the script on the target database." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="CodeSmith.BaseTemplates" %>

The OnPostRender override, in the template's <script runat="template"> block, runs the generated script, result, against the database that contains the source table, and writes the outcome and any SQL Server messages to the trace output:

protected override void OnPostRender(string result)
{
    if (this.AutoExecuteScript)
    {
        // execute the output on the same database as the source table.
        CodeSmith.BaseTemplates.ScriptResult scriptResult =
            CodeSmith.BaseTemplates.ScriptUtility.ExecuteScript(this.SourceTable.Database.ConnectionString,
            result, new System.Data.SqlClient.SqlInfoMessageEventHandler(cn_InfoMessage));
        Trace.WriteLine(scriptResult.ToString());
    }

    base.OnPostRender(result);
}

private void cn_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
{
    Trace.WriteLine(e.Message);
}

In this example, SourceTable is a SchemaExplorer.TableSchema property. If your template asks for different metadata, change that part of the code to get the connection string of the database the script should run against.