Monday, August 06, 2007

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.

3 Comments:

Blogger Jim R said...

Thanks for the good work. I appreciate your efforts. It is saving me some significant time. I am continuing to use it and will forward any ideas/code that I create.
One question, why is "System.ComponentModel" in the using statement?

August 08, 2007 12:44 PM  
Blogger Jim R said...

I figured out that ComponentModel is require for the "NotifyPropertyChanged" method.

I have been having some trouble with generated code from "ModelTemplate.vm". For my situation I had to change line 87 in "ModelTemplate.vm" to:
${f.GetPrivateVariableName()} = (value.${f.GetPropertyName}ID > 0) ? value : null;.

Line 243 in "DbFieldInfo.cs" is the fourth line in the code block of the "GetInEqualityTest()" method. It had to be changed to"

return "(" + GetPrivateVariableName() + " == null) || (value == null) || (value." + GetPropertyName() + "ID != " + GetPrivateVariableName() + "." + GetPropertyName()+ "ID)";"
.

These line numbers are from a Subversion checkout that I did today.

Without these changes I was getting property names of "ID" which did not work for me.

I have not addressed the issue, but it seems that the line that is generated by the "GetInEquality()" method should be generated in the template code. Is there a specific reason that you did it in the generater code, or is that just how it evolved?

August 08, 2007 3:50 PM  
Blogger Roy Tate said...

I will look closer, but this is another case where I relied on my table and field naming convention. Foreign keys mapped as ... USER.ADDRESS_ID relates to ADDRESS.ID. This information is now exposed in the DbFieldInfo class. I can quickly change that template to use the INFORMATION_SCHEMA.

August 08, 2007 4:22 PM  

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home