CodeSmith Tools

Introduction

CodeSmith is a template based code generator for any ASCII language. It uses a syntax very similar to ASP.NET. This tutorial describes the process of building a template to generate SELECT/INSERT/UPDATE/DELETE stored procedures.

Getting Started

All templates must include a CodeTemplate directive like this one.

<%@ CodeTemplate Language="C#" TargetLanguage="T-SQL"
  Description="Generates SELECT/INSERT/UPDATE/DELETE stored procedures." %>

Here we tell CodeSmith that we plan to use C# as our template language, we plan to generate code for the T-SQL language and we also provide a description for what the template does. The Language attribute tells CodeSmith what language we will use to write our template code and can be C# (C#, CS, CSharp), VB.NET (VB, VBS, VisualBasic, VBScript) or JScript.NET (JS, JScript, JavaScript). The TargetLanguage attribute can be anything, but setting it to T-SQL will cause the template to be grouped together with other T-SQL templates in CodeSmith Explorer's target language view. The TargetLanguage attribute is also used to syntax highlight the static content in a template in CodeSmith Studio. And finally, the Description attribute allows us to provide a brief description of the template that will be shown in CodeSmith Explorer.

In order to be able to generate code based on a database table, the template must somehow know about this table. CodeSmith allows us to provide this contextual information to the template by specifying Property directives.

<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema"
  Category="Context"
  Description="Table that the stored procedures should be based on." %>

The Name attribute tells CodeSmith what we want the property to be named in our template. This is the name that we will use to access the property in our template. The Type attribute tells CodeSmith what type our new property should be. This can be any .NET type that has a designer (most built-in .NET types have designers). In this case we use a type from CodeSmith's included Schema Explorer library. This type allows us to pick a table from a database and will then tell us anything we want to know about that table. We will use this information to drive our template. The Category attribute simply allows us to put our new property into a group of properties with the same category. And lastly, the Description attribute allows for a brief description of what the property will be used for in the template.

CodeSmith allows the flexibility for anyone to create a new .NET type and, as long as they also create a designer for that type, it can be used in your templates. The included Schema Explorer library is an example of this flexibility. CodeSmith does not know anything about Schema Explorer, so we must instruct our template on how to find the SchemaExplorer.TableSchema type. We do this by adding an Assembly directive.

<%@ Assembly Name="SchemaExplorer" %>

This just tells our template that we plan to make use of code found in an external assembly. The Name attribute must correspond to an assembly (without the .dll extension) in either the same directory as the template or in the same directory as the CodeSmith executable.

Writing Our Template

Now that we have setup everything that we will need to base our template on, we can begin writing our template content. It is always best to start with an example of what you want the output to look like. Here is an example UPDATE stored procedure for the Northwind..Products table.

-----------------------------------------------------------------
-- Date Created: Thursday, January 02, 2003
-- Created By:   Eric J. Smith
-----------------------------------------------------------------

CREATE PROCEDURE dbo.UpdateProducts
  @ProductID int,
  @ProductName nvarchar(40),
  @SupplierID int,
  @CategoryID int,
  @QuantityPerUnit nvarchar(20),
  @UnitPrice money,
  @UnitsInStock smallint,
  @UnitsOnOrder smallint,
  @ReorderLevel smallint,
  @Discontinued bit
AS

UPDATE [Products] SET
  [ProductName] = @ProductName,
  [SupplierID] = @SupplierID,
  [CategoryID] = @CategoryID,
  [QuantityPerUnit] = @QuantityPerUnit,
  [UnitPrice] = @UnitPrice,
  [UnitsInStock] = @UnitsInStock,
  [UnitsOnOrder] = @UnitsOnOrder,
  [ReorderLevel] = @ReorderLevel,
  [Discontinued] = @Discontinued
WHERE
  [ProductID] = @ProductID

Now we must begin to make this into a dynamic template based on our SourceTable property. Let's start with a simple change to make the Date Created comment dynamic.

-- Date Created: <%= DateTime.Now.ToLongDateString() %>

Just like in ASP.NET we use the <%= %> construct. When the template is executed, this expression will be expanded to the current date in long date form.

Now we need to figure out how to build our list of parameters for the stored procedure. We will derive this list from the information in the SourceTable.Columns property. This property is a collection of ColumnSchema objects that represent each column in the selected table. Here is a simple example of outputing each column in the table.

<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
  <%= SourceTable.Columns[i].Name %>
  <% if (i < SourceTable.Columns.Count - 1) { %>,<% } %>
  <% } %>

In this example we use the <% %> construct to add logic to the template. What language you selected in the Language attribute from above will determine the language that you will need to use for your template logic. In our example, we have specified C# as our language so that is what we use here. The output from this example when the selected table is Northwind..Products looks like:

ProductID,
  ProductName,
  SupplierID,
  CategoryID,
  QuantityPerUnit,
  UnitPrice,
  UnitsInStock,
  UnitsOnOrder,
  ReorderLevel,
  Discontinued

This is a nice start, but we have a little more work to do before this looks like a real set of parameters. Since the logic for each parameter will be somewhat complex, we should go ahead and create a method to output each parameter. To do this we will need to use a

Instead of setting the runat attribute to server like we would do in ASP.NET, we set it to template. This causes the CodeSmith compiler to process this script block as code instead of passing it on as part of the template content. This distinction allows the creation of templates that output ASP.NET code with server