Forging Transact SQL Code with CodeSmith

Coding Transact SQL (TSQL) can be tedious and repetitive. Often, a TSQL statement or stored procedure is a one-off variation of a program you may have written just yesterday. Nothing excites a developer more than eliminating unnecessarily tedious or repetitive work. Face it: We're lazy, but we're also pragmatic. Why write something from scratch when you can "borrow" and paste it into your program?

Plenty of tools ship with SQL Server and Visual Studio to eliminate typing TSQL code, but what about eliminating the thought process altogether and going with a fire-and-forget approach to coding TSQL? Plug in the one-off variation and voilà! The code is generated. To achieve this, you need to combine two ideas: the template and the code generator. With some examples, this article introduces a template-based code generator called CodeSmith and shows you how to add fire-and-forget programming to your TSQL development.

Let's start with the CodeSmith basics.

CodeSmith Basics

First, CodeSmith is a code generator capable of generating all types of text-based .NET code, such as C#, VB .NET, and TSQL. CodeSmith takes the template approach to code generation. Like Microsoft Office templates, CodeSmith templates allow you to enter static content and supplement it with dynamic content.

Coding in CodeSmith works and looks a lot like old ASP development. For example, take a look at the following snippet of CodeSmith code:

SELECT CONVERT(varchar(255),'<%=SourceTable.Name%>') AS TableName,
   CONVERT(varchar(255),'RecordCount') AS MetricName,
   CONVERT(decimal(18,4), COUNT(*)) AS MetricValue
   FROM <%=SourceTable.Name%>
<% for (int n=0; n < SourceTable.Columns.Count; n++ ) {%>
<% string sourceType = SourceTable.Columns[n].NativeType; %>
<% if ( ( sourceType == "decimal") || ( sourceType == "money") ||
   ( sourceType == "decimal")) {%>

As you can see, code appears between the <% %> symbols. Code appearing in the <%= %> must contain a simple text or numeric expression. Like old ASP, the <%= %> will emit the expression when the template executes. You also can structure your code to look like traditional C# or VB code and use a Response object to emit generated code.

CodeSmith has its own development environment and also can work as a plug-in inside of Visual Studio.

The top of every CodeSmith template contains a set of directives. Here is a list of common directives:

• In the Codetemplate directive, you specify, among other things, the language you'll use to develop the template (C#, VB, JScript) and the type of code you'll be generating (in this article, TSQL). The following is a sample Codetemplate directive:

  <%@ CodeTemplate Language="C#" Debug="True"
                   TargetLanguage="T-SQL"
                   Description="Generates a script based on the data
                                from a table." %>
  

• Property directives are the user interface for a CodeSmith template. Properties can collect simple information such as text or allow for navigating a list of tables in a database.
• Assembly directives allow you to incorporate your own assemblies or other .NET assemblies in the template. Import directives work a lot like the using statement in C#.

CodeSmith in Action

To demonstrate TSQL code generation with CodeSmith, the following sections look at three samples. Each one solves a different class of TSQL problem, and they all qualify as intermediate to advanced TSQL code. Here is a brief overview of each sample:

1. The AllStoredProcedures sample template ships with CodeSmith. Given a database name and some additional properties, the sample builds a set of Create, Read, Update, and Delete (CRUD) stored procedures. Creating CRUD statements is a classic TSQL problem.
2. FlashTotalsHelper generates aggregate SUM and record count SELECT statements on all numeric fields in a table. I use the template in data warehousing to do Data Profiling and to generate flash totals.
3. GenUpdateDependents generates UPDATE statements based on a FOREIGN KEY relationship. I use the sample to generate code that synchronizes the surrogate keys in a data warehousing operational data store (ODS).

CodeSmith really shines if you're trying to develop a pattern with a small variation like a table name and the fields in the table. The code samples will illustrate this point as you take a closer look at them.

AllStoredProcedures: Make CRUD

AllStoredProcedures is a good place to start getting acquainted with CodeSmith. CRUD statements are immediately recognizable to any ASP.NET or Forms developer interacting with a SQL Server database.

Aside from solving a classic TSQL coding problem, the sample template illustrates some CodeSmith fundamentals. Properties can be more sophisticated than simple text. These are partial property declarations:

<%@ Property Name="SourceDatabase"
             Type="SchemaExplorer.DatabaseSchema"
             Category="1. Context"
             Description="Database that the stored procedures should
                          be based on." %>
<%-- This property must be added manually because it is defined as
     a nested type in the StoredProcedures template --%>
<%@ Property Name="IsolationLevel"
             Type="StoredProceduresTemplate.TransactionIsolationLevelEnum
             Default="ReadCommitted" Category="2. Options"
             Description="Isolation level to use in the generated
                          procedures." %>
<%@ Property Name="ExcludedTables"
             Type="StringCollection"
             Default="" Optional="True" Category="2. Options"
             Description="If supplied, any matching tables will not
                          get stored procedures generated.
                          (* is treated as a wildcard)" %>

In the sample, only three properties are declared: SourceDatabase, IsolationLevel, and ExcludedTables. Yet, many more properties than you would expect appear in the template. When you select many of the properties, dialogs appear, allowing you to navigate items in the database.

Templates can be nested within one another. In the sample, the StoredProcedures template is utilized inside of AllStoredProcedures. The declaration below allows AllStoredProcedures to create and invoke the StoredProcedures template:

<%@ Register Template="StoredProcedures.cst
             Name="StoredProceduresTemplate"
             MergeProperties="True"
             ExcludeProperties="SourceTable,IsolationLevel" %>

CodeSmith includes a number of classes for reading database metadata. Reviewing AllStoredProcedures in a good way becomes more familiar with the classes.

Now that you've seen CodeSmith's incredible support for SQL Server, it's time to look at how you can leverage the support to augment the job of a data warehouse developer.

FlashTotalsHelper: Data Profiling and Flash Totals

Data profiling and flash totals are fundamental data warehousing techniques concerned with data quality and ensuring continued data quality. Frequently in data warehousing, you need to answer a question like: Is the data we copied what we were expecting to copy? Summing numeric columns and record counts and comparing the results to sums and counts on the source help answer this type of question.

This is part of the main logic in the FlashTotalsHelper template:

SELECT CONVERT(varchar(255),'<%=SourceTable.Name%>') AS TableName,
CONVERT(varchar(255),'RecordCount') AS MetricName, CONVERT(decimal(18,4),
COUNT(*)) AS MetricValue FROM <%=SourceTable.Name%>
<% for (int n=0; n < SourceTable.Columns.Count; n++ ) {%>
<% string sourceType = SourceTable.Columns[n].NativeType; %>
<% if ( ( sourceType == "decimal") || ( sourceType == "money") || (
sourceType == "decimal")) {%>
UNION
SELECT CONVERT(varchar(255),'<%=SourceTable.Name%>') AS TableName,
CONVERT(varchar(255),'<%=SourceTable.Columns[n].Name%>_Total') AS
MetricName, CONVERT(decimal(18,4),
SUM(ISNULL(<%=SourceTable.Columns[n].Name%>,0.0))) AS MetricValue FROM
<%=SourceTable.Name%>
<% } %>
<% } %>

As you can see, the code inspects the datatype of the columns and omits any non-numeric datatypes. This example uses the SUM statement, but you also could apply other aggregate functions such as MIN, MAX, and AVG.

Now, look at how CodeSmith can inspect table relationships and generate code based on those relationships.

GenUpdateDependents

GenUpdateDependents was built for a real-time data warehousing project in my company. In our project, we receive data from an application service provider (ASP) and store the data in a relational ODS for reporting. From our relational database, we build non-relational datamarts combining data from the ASP and data from our internal systems.

The problem we faced was determining how to synchronize surrogate keys in an ODS when the natural key value from the source system changes for a particular table. Our relational model included foreign key declarations. We leveraged the foreign relationships to build a template to generate the appropriate update statements. Each table has a nearly identical staging table that includes all but the surrogate key columns.

Here's a snippet of code generated by the template:

UPDATE LO_Cost
SET
ED_Object_ID = Src.ED_Object_ID
FROM LO_Cost, Staging_LO_Cost DestStage, Staging_LO SrcStage, LO Src
WHERE
--Connect Dest to Dest Stage
LO_Cost.ObjectId = DestStage.ObjectId
AND LO_Cost.SourceLoadingID = DestStage.SourceLoadingID
--Connect DestStage FK to PK Src Stage
AND DestStage.FKFld = SrcStage.ObjectId
AND SrcStage.ObjectId = @ObjectId

As you can see, the code references the staging table containing the primary key in the foreign key relationship. You can use classes built into CodeSmith to navigate the database metadata and find the foreign key relationships.

What Have You Learned?

By following along with this tutorial, you're now able to do the following:

1. Eliminate a lot of TSQL coding with CodeSmith templates.
2. Create and modify CodeSmith templates to solve common TSQL problems.
3. Leverage your knowledge of C#, VB, JScript, and the .NET environment to augment CodeSmith templates.

Download the Code

To download the accompanying source code for this article, click here.

About the Author

Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at jjuday@crowechizek.com.