Forging Transact SQL Code with CodeSmith Portland OR

provided by: 
Originally published at Internet.com

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),