How-to: Creating your first CodeSmith Generator template

Introduction

CodeSmith Generator 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 Generator 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 Generator 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 Generator Explorer’s target language view. The TargetLanguage attribute is also used to syntax highlight the static content in a template in CodeSmith Generator Studio. And finally, the Description attribute allows us to provide a brief description of the template that will be shown in CodeSmith Generator Explorer.
In order to be able to generate code based on a database table, the template must somehow know about this table. CodeSmith Generator 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 Generator 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 Generator 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 Generator’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 Generator 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 Generator 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 PetShop.Product table.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO
----------------------------------------------------------------------------------------
-- Date Created: Tuesday, September 28, 2010
-- Created By:   Generated by CodeSmith Generator
----------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.UpdateProduct
	@ProductId VarChar(10), 
	@CategoryId VarChar(10), 
	@Name VarChar(80), 
	@Descn VarChar(255), 
	@Image VarChar(80) 
AS

UPDATE [Product] SET
	[CategoryId] = @CategoryId,
	[Name] = @Name,
	[Descn] = @Descn,
	[Image] = @Image
WHERE
	[ProductId] = @ProductId

GO

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 outputting 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 PetShop.Product looks like:

ProductId,
CategoryId,
Name,
Descn,
Image

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 <script> block. Again, this is just like ASP.NET.

<script runat="template">
public string GetSqlParameterStatement(ColumnSchema column)
{
	string param = "@" + column.Name + " " + column.NativeType;
	
	switch (column.DataType)
	{
		case DbType.Decimal:
		{
			param += "(" + column.Precision + ", " + column.Scale + ")";
			break;
		}
		case DbType.AnsiString:
		case DbType.AnsiStringFixedLength:
		case DbType.String:
		case DbType.StringFixedLength:
		{
			if (column.Size > 0)
			{
				param += "(" + column.Size + ")";
			}
			break;
		}
	}
	
	return param;
}
</script>

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 <script> blocks. Also, on that note, I should mention that you will need to escape your ASP.NET constructs if you plan to have them output in a template (ie. <%% expression %%> results in this output <% expression %>).

Here is a look at our final code for the UPDATE stored procedure:

----------------------------------------------------------------------------------------
-- Date Created: <%= DateTime.Now.ToLongDateString() %>
-- Created By:   Generated by CodeSmith Generator
----------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.Update<%= SourceTable.Name %>
	<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
	<%= GetSqlParameterStatement(SourceTable.Columns[i]) %><% if (i < SourceTable.Columns.Count - 1) { %>,<% } %> <% if (SourceTable.Columns[i].Description.Length > 0) { %>-- <%= SourceTable.Columns[i].Description %><% } %>
	<% } %>
AS

UPDATE [<%= SourceTable.Name %>] SET
	<% for (int i = 0; i < SourceTable.NonPrimaryKeyColumns.Count; i++) { %>
	[<%= SourceTable.NonPrimaryKeyColumns[i].Name %>] = @<%= SourceTable.NonPrimaryKeyColumns[i].Name %><% if (i < SourceTable.NonPrimaryKeyColumns.Count - 1) { %>,<% } %>
	<% } %>
WHERE
	<% for (int i = 0; i < SourceTable.PrimaryKey.MemberColumns.Count; i++) { %>
	<% if (i > 0) { %>AND <% } %>[<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>] = @<%= SourceTable.PrimaryKey.MemberColumns[i].Name %>
	<% } %>

Generate the Code

Now that you have built your template, you can use CodeSmith to execute it and see the results. You can execute the template by double-clicking the codesmith_sp.cst file that is included in the download at the top of this page. You will then see a screen that looks like this:

Click the ellipses next to the SourceTable property.

Now click the ellipses next to the Data Source drop down.

Click on the Add button.

Enter the Data Source information. Name can be anything but you should make it something that represents the database in your connection string since this the name that will be used to select the Data Source in the future.

The Provider Type can be SqlSchemaProvider, OracleSchemaProvider or choose from many other Schema Provider’s. SqlSchemaProvider should be used for any SQL Server or MSDE database.

The Connection String is the same as any other connection string you would specify in your application. We will click on the on the ellipse next to the Connection String text box. This will help us in creating a valid connection string.

Enter in your database connection information. This dialog will help you build your connection string. When you are done click on the Okay button.

Finally, choose the newly added database in the Data Source drop down and choose the Product table from the list. Click on the Select button when you are done.

Select Generate and see the results! After you generate the code, you can click the Copy Template Output button at the top and the output will be copied to your clipboard. Another great way to generate code once you have finished building your template is using the CodeSmith Generator Explorer window.

This is the window that is displayed if CodeSmith Generator Explorer is started with no parameters. It allows you to see all templates in a given directory. You can then drag a template to any application that supports dropping text (this includes Visual Studio). When you do so the template properties window will display, you will set all required properties and when you click Generate the output will be added to wherever you dragged the template.

Conclusion

CodeSmith Generator allows the use of templates to avoid repetitive coding tasks. The template source code for this example has been attached to this post. We used a previous version of CodeSmith Generator in our last project and were able to generate over 70% of our code. This included generating stored procedures, business objects, collection classes, ASPX pages and code-behind files.

Inside of CodeSmith Generator Explorer, the area that contains the template folders and templates is called the Template Explorer. The Template Explorer contains a DatabaseStoredProcedures folder that contains a complete and up-to-date solution for generating Stored Procedures.