Using Reflection in CodeSmith Templates to Generate your UI

We are currently evaluating whether to use The Text Template Transformation Toolkit (aka T4 - used by MS internally for MVC and Entity Frameworks and built into VS 2008 - see http://www.olegsych.com/2007/12/text-template-transformation-toolkit/) or CodeSmith for generation of our UI layer. This will save us time when getting started with a new application and remove a lot of the monkey work that needs to be done in the UI layer. This code generation will also be a selling point for the Oakton Application Framework.

Our planned process is to:

1) Create our Data Transfer Objects with the lists and properties we wish to display in the User Interface (ie not generate the UI directly from the database schema).
2) Quickly generate basic pages using Reflection on our Data Transfer Objects (Dtos)

This is a simplified example of using CodeSmith templates and the System.Reflection namespace to spit out html for your page based on the properties of your Dto classes (or any other classes that you wish to bind to your UI).

<%--
Name: GenerateUIFromDtoViaReflection_Draft0-1
Author: David Klein
Description: Using Reflection to Generate UI from Dto Assembly when has correct Mapping Attributes
Note: Make sure that referenced assemblies are in the GAC or in the CodeSmith /bin directory so they can be
resolved correctly.
--%>
<%@ CodeTemplate Language="C#" TargetLanguage="Text" Src="" Inherits="" Debug="True"
Description="Template description here."  %>
<%@ Property Name="AssemblyPath"
Type="System.String" Default="c:\temp\MyDtoAssembly.dll" Optional="false"
Category="Strings" Description="The path to your dto."
Editor="System.Windows.Forms.Design.FileNameEditor"  %>
<%@ Property Name="TypeName"
Type="System.String" Optional="false" Category="Strings"
Description="Fully qualified name for your Dto" %>
<%@ Assembly Name="System.Data" %>
<%@ Assembly Name="Oakton.Sample.Common.Dto" %>
<%@ Assembly Name="Oakton.Framework.Common" %>
<%@ Assembly Name="Oakton.Sample.Common" %>
<%@ Import Namespace="System.Reflection" %>
<%@ Import Namespace="Oakton.Sample.Common.Dto" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="Oakton.Framework.Common" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Windows.Forms" %>

<%
//Debugger.Break();
MessageBox.Show(TypeName);
Assembly assembly = System.Reflection.Assembly.LoadFile(AssemblyPath);
Type objectType = assembly.GetType(TypeName);

//System.Type objectType =  System.Type.GetType(TypeName);
//System.Windows.Forms.MessageBox.Show("Is null {0}", (objectType == null).ToString());

     PropertyInfo[] properties = objectType.GetProperties();

         foreach(PropertyInfo property in properties)
         {
              
    //Add Property of Dto to UI
                     string fieldName = property.Name;
    %>
     <div class="row">
                             <label>
                                 <asp:Literal ID="<% =fieldName %>Literal" runat="server" Text='<%%$ Resources:CommonResources, CustomerDetail_<% =fieldName %> %%>' />
                             </label>
                             <span class="input">
                                 <asp:TextBox runat="server" ID="<%=fieldName%>TextBox" Text='<%%# Bind("<% =fieldName %>")%%>'
                                     SkinID="short" />
                             </span>
                         </div>
    <%
 

   }
%>

If you are interested in some of the Patterns (e.g. Dtos and how they should be populated by Transfer Object Assemblers), mentioned above, see (Gasp! It's not from Microsoft!) the Java J2EE patterns site at http://java.sun.com/blueprints/corej2eepatterns/Patterns/TransferObjectAssembler.html.
The same principles apply regardless of your selected language.

Automatically Generating Audit Columns for your Database - no monkey work!

Every database should really have audit columns in it - even if your users say they are not required. A simple way to get you up and running (schema-wise) is to use the following Microsoft SQL Server script to generate a text file to add your standard audit columns to every table.

Open a query on your database in SQL 2005 Management Studio. Change your output mode to "text", and then run it by pressing F5. The output window will have your script to run for all tables in your database - table auditing heaven!

SELECT 'ALTER TABLE '  + TABLE_NAME + ' ADD Modified datetime NOT NULL DEFAULT GETDATE(),
 ModifiedBy nvarchar(255) NOT NULL DEFAULT USER_NAME(),
Created datetime NOT NULL DEFAULT GETDATE(),
CreatedBy nvarchar(255) NOT NULL DEFAULT USER_NAME() ' FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'sysdiagrams' 

Using Regular Expressions for Simple Code Generation in UltraEdit

I love getting tools to do repetitive 'monkey work' for me. To this end, there is an alternative to my post on code generation via sysobjects at http://ddkonline.blogspot.com/2008/02/rehash-daves-simple-sql-workbenchquery.html. Difference in this post that is code/SQL generation is done through… you guessed it… UltraEdit Regular Expressions!

My task – I wanted to do a multiple column join to determine if any updates had been made to a table (compared to a table which contained the original Excel import). Now instead of doing the hard work and going through all 70 columns and manually typing each table name in e.g. Table1.ColumnName = Table2.Column name for every column, I used the following shortcut:

1. Drag the columns folder from the treeview to the working area in SQL 2005 WorkBench so you have a list of comma-separated columns (e.g. RecordNumber, Information, RecordType, DocAuthor)
   
   
   



2. Copy this into my the text editor, UltraEdit (make sure you have Advanced -> Configuration -> Search -> Regular Expression Engine set to “Unix Style” as used in .NET rather than “Ultra Edit Style”)




3. Ctrl+R to open the replace dialog and put in “\W*(\w*),” to the Find box and “Table1.\1 = Table2.\1 AND\n” in the Replace Box.
   
   
   



4. Do the replace and you will now have a properly formatted join expression for use in your queries. i.e.
   
   Table1.RecordNumber = Table2.RecordNumber AND
   Table1.Information = Table2.Information AND
   Table1.RecordType = Table2.RecordType,

You can now put the joins back into your query:

    SELECT

        *

    FROM

        dbo.OpportunityRawImport_Stage1Load as Stage1

    INNER JOIN

        dbo.OpportunityRawImport_Stage2Load as Stage2

    ON

        Stage1.RecordNumber = Stage2.RecordNumber AND

        Stage1.Information = Stage2.Information AND

        Stage1.RecordType = Stage2.RecordType AND

Handy Tools for Building Regular Expressions:

A free regular expression editor with English interpretations of your regular expressions (The Regular Expression Workbench) can be found at:
http://blogs.msdn.com/ericgu/archive/2003/07/07/52362.aspx. For example, a regular expression for Australian Postcodes ("^[\d]{4,4}$". Is interpreted as:

^ (anchor to start of string)
Any character in "\d"
At least 4, but not more than 4 times
$ (anchor to end of string)

Rehash: Dave's simple SQL Workbench/Query Analyzer Code Generator

This is a perenially handy tool for generating database-oriented code using the most basic of tools: SQL 2005 Workbench. It makes use of system tables or the information_schema and is a very basic (but very useful) tool for code generation - especially when you have hundreds of tables.

Here is one I just made today to avoid having to create a CodeSmith template (which we don't have licences for) or doing the monkey work of manually typing in these attributes. Just set the output mode in isqlw/SQL Workbench to text and generate away! (minus the chimps..)

SELECT 
'[EntityPropertyMapping("' + column_name + '")]' + CHAR(10) +
'public ' + CASE data_type 
WHEN 'nvarchar' THEN 'string'
WHEN 'int'    THEN 'int'
WHEN 'datetime'    THEN 'DateTime'
END 
+ ' ' + column_name + ' { get; set; }' + CHAR(10) + CHAR(10)
FROM information_schema.columns 
WHERE table_name = 'Asset'

Output is:

[EntityPropertyMapping("AssetName")]
public string AssetName { get; set; }

[EntityPropertyMapping("Description")]
public string Description { get; set; }


[EntityPropertyMapping("Comments")]
public string Comments { get; set; }

Debugging CodeSmith Templates in Windows Vista

I had some problems with Windows Vista trying to debug the PLINQO (Professional Linq) CodeSmith templates. To get around the problems of crashing and restarting ( As described by Paul Welter,
http://community.codesmithtools.com/blogs/pwelter/archive/2007/06/09/tips-amp-tricks-debugging.aspx)

"There are some problems with using the Just-In-Time debugger in Windows Vista. First you need to make sure you have all the latest service packs installed. Next, the debugger in Vista will cause CodeSmith to hang when you finish debugging. You can work around this issue by updating the Just-In-Time debugger setting DbgJITDebugLaunchSetting. The setting is found in the registry at [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework]. Change the value of DbgJITDebugLaunchSetting to 2. This will cause the debugger dialog to be displayed immediately when your code hits a breakpoint. This will also allow control to return to CodeSmith when you continue the execution of the template from the debugger."

Note that the original value on my Vista machine was 10 Hex (16).

PLINQO - Professional LINQ Objects

Working on the Credit Returns system for Pfizer, I have started using the PLINQO CodeSmtih templates for generation of LINQ supporting classes. They have a few issues, but they generate separate files for all entities and give you some of the most common queries out of the box (e.g. get GetByForeignKey()) so you don't have to do the monkey work. They also give you absolute control over the output of your dbml files (e.g. it creates separate partial classes for each LINQ entity).

See:
http://community.codesmithtools.com/blogs/pwelter/archive/2007/08/08/plinqo.aspx