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:
- One will contain classes that wrap ADO.NET calls to the database
- 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.]