Auto Executing Generated SQL Scripts

If you're generating SQL scripts, it can be useful to execute those scripts right after you've generated them. That way, when you generate scripts that build new objects in a database, you can finish the process by actually building the objects.

The BaseTemplates.ScriptUtility object provides an ExecuteScript method that you can use for this purpose. If you want to execute the script immediately after it's been generated, it's convenient to override the template's OnPostRender method to do so. Here's an example of doing so, adapted from the StoredProcedures.cst template that ships with CodeSmith Generator:

protected override void OnPostRender(string result)
{
    // 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.Write(scriptResult.ToString());
    base.OnPostRender(result);
}

In this example, SourceTable is a property of type SchemaExplorer.TableSchema. Depending on what metadata you're prompting the user for, you'll need to adjust that part of the code to get a connection to the database where the generated script should be executed.