I really like CodeSmith, a great tool for generating templates.  It comes with a variety of default templates, all managed in a Visual Studio-like environments, complete with intellisense that’s good, but not quite as powerful as Visual Studio.  I do a lot of scripting, so I wanted to make stored procedure generation easier.  One scenario not covered is reference tables, where the reference tables happen to have two specific columns all the time:

EffectiveDate  datetime not null,
EndDate datetime null

The effective and end dates determine which entries are in process at the given time.  I don’t want to be having to write this code all the time.  Instead, I added this to the default template (StoredProcedures.cst) that comes with CodeSmith.  These are the modifications I made.  First, I need to be able to use the IEnumerable<T> interface, and thus we need the mscorlib assembly.  So I added these two declarations at the top.  More on why in a little bit.

<%@ Assembly Name=”mscorlib” %>
<%@ Import Namespace=”System.Collections.Generic” %>

I added a new property for this method that can be used to turn it off or on.  This is a boolean, with a default to true:

<%@ Property Name=”IncludeSelectByEffectiveEndDate” Type=”System.Boolean” Default=”True” Category=”3. Procedure Types”
 Description=”If true a SELECT procedure will be generated to select by effective/end dates, if the table has these fields.” %>

If you are unfamiliar with this, CodeSmith uses the @Property attribute to allow a configurable setting that can be changed in the property window (shown below).  This property window makes it easy to add all sorts of configuration capabilities to your scripts.  Note the description appears within the description window.

 So now we have a property to turn our feature on and off, we need some supporting methods, such as a method to generate the proc name.  The existing template has a variety of method that do this, and working in straight copy-and-adapt mode, I added a method that follows suit as shown below.  Note: if you aren’t aware, CodeSmith allows code segments within its <script runat=’server”> tags, following an inline code approach that ASP.NET uses.  This makes for a great place to stick in helper methods that return text, or write a response directly to the output.

public string GetSelectByEffectiveEndDateProcedureName()
{
 return String.Format(“{0}[{1}{2}ReadByEffectiveEndDate]”, GetTableOwner(), ProcedurePrefix, GetEntityName(true));
}

Our first task in using this is to drop the proc, if it exists, via the GenerateDropStatement method.  This work is already done, and needs a simple call to first check if the conditions are met (the flag is set to true and we indeed have an EffectiveDate/EndDate fields because not all tables will).  Note that SourceTable is a TableSchema object that represents that individual table.

if (IncludeSelectByEffectiveEndDate &&
  this.SourceTable.Columns.Contains(“EffectiveDate”) &&
  this.SourceTable.Columns.Contains(“EndDate”))
 {
  GenerateDropStatement(GetSelectByEffectiveEndDateProcedureName());
 }

 Next, the process for creating the proc is pretty simple too.  It starts off with a generic comment and header (as repeated from other examples.  I must do the same flag/effective/end date check, and if all is well, then we can begin by generating the header (already done via GenerateProcedureHeader).  Take a look at the body below.

<%——————————————————————————————
*
* SelectByEffectiveEndDate Procedure
*
——————————————————————————————%>
<%
if (IncludeSelectByEffectiveEndDate &&
 this.SourceTable.Columns.Contains(“EffectiveDate”) &&
 this.SourceTable.Columns.Contains(“EndDate”))
{
 GenerateProcedureHeader(GetSelectByEffectiveEndDateProcedureName());
%>

CREATE PROCEDURE <%= GetSelectByEffectiveEndDateProcedureName() %>
(
 @EffectiveDate  datetime,
 @EndDate   datetime
)
AS

SET NOCOUNT ON
<% GenerateSetTransactionIsolationLevelStatement(IsolationLevel); %>

SELECT
 <% GenerateColumns(SourceTable.Columns, 1); %>
FROM
 <%= GetTableOwner() %>[<%= SourceTable.Name %>]
WHERE
 ISNULL(EffectiveDate, ‘1/1/1900’) <= @EffectiveDate AND
 ISNULL(EndDate, ’12/31/9999′) >= @EndDate

<%
 GenerateProcedureFooter(GetSelectProcedureName());
 this.Progress.PerformStep();
}
%>

 In this case, I know the fields already, so I don’t need to generate them dynamically since I’m assuming they exist.  The GenerateColumns methods generates all columns for select, GetTableOwner returns dbo or the schema name for whatever the case may be, and the table name is accessible via SourceTable.Name property (SourceTable, as I said before is a TableSchema object).

For more information about using CodePlex and the database schema objects, check out this article: http://aspalliance.com/1580_Creating_Code_Smith_Templates.all