This was meant as a "Part II" to my prior post on generating Text, HTML & more XML with XSLT.  The point for today's post is that source code files are text files.  The example I am thinking of is generating a library of VB.NET wrapper classes for the stored procedures in a SQL Server database.

(I've also ticked the Continuous Integration category for this post.  It wouldn't be hard to think of a scenario where a build process would generate a library from a reference database on the check-in of a stored proc script, then deliver the latest rev of the library to the developers, anyway...)

Step one would be fetch the meta data about the stored procedures, for example: 

SELECT procs.Specific_Name, params.Parameter_Name, params.Data_Type, params.Parameter_Mode
FROM INFORMATION_SCHEMA.ROUTINES procs
LEFT JOIN INFORMATION_SCHEMA.PARAMETERS params
ON params.Specific_Name = procs.Specific_Name
WHERE procs.Routine_Type = 'PROCEDURE' AND
procs.Specific_Name NOT LIKE 'dt_%'
FOR XML AUTO

Once we add a document node to this, we will have a document that contains many element sets like this one:

  <procs Specific_Name="GetContactByID">
    <params Parameter_Name="@ContactID" Data_Type="nvarchar" Parameter_Mode="IN"/>
    <params Parameter_Name="@ContactGUID" Data_Type="int" Parameter_Mode="INOUT"/>
    <params Parameter_Name="@Found" Data_Type="bit" Parameter_Mode="INOUT"/>
  </procs>

I think that's all we need to get started.

Step two is to transform this data into VB.NET code.  For today's example it suits me to generate two files: 

1. One will contain classes that wrap ADO.NET calls to the database
2. The other will provide types that wrap properties for passing into and out of the first

The text of the first template is as follows

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
  <xsl:output method="text" indent="yes" />
  
  <xsl:template match="/">
Imports System
Imports System.Data.SqlClient
Imports System.Data

Public Class DatabaseAccess
    Inherits ConvienientBaseClass

    Public Sub New(ByVal cn As SqlConnection, ByVal trn As SqlTransaction)
        MyBase.New(cn, trn)
    End Sub
    
    <xsl:apply-templates select="/database/procs" /> 
    
End Class
  </xsl:template> 
  
  <xsl:template match="procs">
    '
    ' Wraps stored proc: <xsl:value-of select="@Specific_Name" />
    ' 
    Public Function Execute<xsl:value-of select="@Specific_Name" />(ByVal params As <xsl:value-of select="@Specific_Name" />Struct) _ 

                                             As <xsl:value-of select="@Specific_Name" />Struct

        Dim exec As New SqlCommand
        Dim param As SqlParameter

        With exec
            .CommandText = "<xsl:value-of select="@Specific_Name" />"
            .CommandType = CommandType.StoredProcedure
            .Connection = MyBase.DatabaseConnection
            .Transaction = MyBase.CurrentTransaction
        End With

  <xsl:apply-templates select="params" /> 

        Try
            If Not exec.Connection.State = ConnectionState.Open Then exec.Connection.Open()
            exec.ExecuteNonQuery()

        Catch ex As Exception
            Throw
        Finally
            If Not exec Is Nothing Then exec.Dispose()
        End Try

    End Function
  </xsl:template>
  
  
  <xsl:template match="params">
        param = exec.CreateParameter
        With param
            <xsl:if test="@Parameter_Mode='INOUT'">.Direction = ParameterDirection.Output</xsl:if>
            <xsl:if test="@Parameter_Mode='IN'">.Direction = ParameterDirection.Input</xsl:if> 
            .DbType = DbType.<xsl:value-of select="@Data_Type" />
            .Value = params.<xsl:value-of select="@Parameter_Name" />
            .ParameterName = "<xsl:value-of select="@Parameter_Name" />"
        End With
        exec.Parameters.Add(param)
  </xsl:template>
  
</xsl:stylesheet>

And the text of the second template is as follows:

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
  <xsl:output method="text" indent="yes" />
  
  <xsl:template match="/">
Imports System

    <xsl:apply-templates select="/database/procs" /> 
  </xsl:template>
  
  <xsl:template match="procs">
' 
' Parameters for method: Execute<xsl:value-of select="@Specific_Name" />
' 
Public Class <xsl:value-of select="@Specific_Name" />Struct
   <xsl:apply-templates select="params" />   
End Class 
  </xsl:template>
  
  <xsl:template match="params">
   Private _<xsl:value-of select="substring(@Parameter_Name,2,string-length(@Parameter_Name)-1)" /> As Date
 Public Property <xsl:value-of select="substring(@Parameter_Name,2,string-length(@Parameter_Name)-1)" />() _ 

                                                                     As  <xsl:value-of select="@Data_Type" />
    Get
      Return _<xsl:value-of select="substring(@Parameter_Name,2,string-length(@Parameter_Name)-1)" />
    End Get
    Set(ByVal Value As <xsl:value-of select="@Data_Type" />)
      _<xsl:value-of select="substring(@Parameter_Name,2,string-length(@Parameter_Name)-1)" /> = Value
    End Set
   End Property
  </xsl:template>
  
</xsl:stylesheet>

To my eye, these look more like VB.NET source files than XSLT templates.  That's because they started life as .vb files.  Then I renamed them .xslt and started inserting the XSLT tags in places where I needed substitution from the XML source. 

To emphasise the hybrid-ness (is that a word) of these files, I have highlighted the VB.NET parts blue and the XSLT parts green, rather than keeping the VB.NET syntax highlighting.

Some breif thoughts: 

• The value of this to my mind is in not having to hand code *every* one.  You hand code one, then generate the rest - in theory this reduces the opertunity for bugs which should be the focus of the exercise. 
• Maybe this may find a home in a long-running project to have this as part of the build process, or maybe as part of some tooling focused on small one-off
• This are commercial products that do code gen based on XSLT.  I haven't used them, but they may well be better than my home-brew sample
• Maybe useful for trainers, or producting samples for demos etc.  I'm thinking now about times where there may be a need to generate side-by-side VB.Net, C# & Java sample code for example.
• Also include comment blocks that are readable by NDoc!

[edit: added line breaks for formatting.]