Features of the Code Generator

I just updated my code generator to optionally generate validation attributes. This simple change includes App.config file entries for all check boxes, and a new checkbox for "Validation" - aka validation attibute generation. While I was making this change, I realized that I really need to pass a CodeGenerationContext object to the DbTable, DbField and ModelGenerator classes. The requester can populate the context, and pass it to the code generator.

Anyway, enough about the code, let's talk about the templates. I made a simple template this weekend to generate a DataGridView column array, suitable for databinding. I'm sure my new template will need some tweaks to handle Foreign Keys better (it currently just displays them as TextBox). Let's look at a template.

##FILENAME:PR_${table.GetClassName()}_Insert.sql
##
## Generate a SQL stored procedure to insert a record into the 
## specified table and return the newly created primary key
##
## FUTURE: The generated procedure can support journalling, etc.
##

#set ($procname = "PR_${table.GetClassName()}_Insert")

#set ($prefix = '')
#set ($lbrace = '[')
#set ($rbrace = ']')
#set ($paramtypes = '')
#set ($nfields = '')
#set ($params = '')
#foreach($f in $fields)
#if (!$f.IsPrimaryKey())
#set ($nfields = "$nfields$prefix$lbrace${f.Column_Name}$rbrace")
#set ($paramtypes = "$paramtypes$prefix@p${f.Column_Name} ${f.GetSqlType()}")
#set ($params = "$params$prefix@p${f.Column_Name}")
#set ($prefix = ', ')
#end
#end

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[$procname]') 
 AND type in (N'P', N'PC'))
BEGIN
 DROP PROCEDURE [dbo].[$procname];
END;

## we are executing the stored procedure as a string, so double up on any quotes.
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[$procname]
 ($paramtypes)
AS
BEGIN
 SET NOCOUNT ON;
 INSERT INTO [dbo].$lbrace${table}$rbrace ($nfields)
  VALUES ($params) ; 
 SELECT @@IDENTITY AS ID ;
END;
'
GO

The lines with ## are comments, but line 1 is actually a second template, and will be used to generate the output filename. Below the comment section is a block of variable assignments that set a field list $nfields, parameter list with data types $paramtypes, and un-typed parameter list $params. Once we have these variables, it is quite easy to write our SQL stored procedure. You might notice that the stored procedure is preceded by a conditional DROP PROCEDURE statement, and the new CREATE PROCEDURE is executed as a string. Have you noticed that we have not mentioned "Active Record" yet? If a person knows NVelocity well enough, they can create a template to do some amazing things. The only requirement is that the template can derive it's functions, properties, method calls, etc. from the basic table name, primary key, foreign key, SQL type and .Net data type information.

If you are looking for inspiration, try writing an active record partial that contains a custom Find(string field1, string field2, etc.) with NULL values ignored in the internal Criterion list. For extra credit, break out the criterion array building into a Find_Query(...) method that can also be used to retrieve a count instead of finding the records.