Rob Farley posted a very interesting codegen post on scripting objects this morning, and as a reforming codegen junky I just couldn't let it go without comment

Firstly, I modified his query as so:

select quotename(si.name) as "@IndexName", quotename(ss.name) AS "@SchemaName", quotename(so.name) AS "@ObjectName",
    stuff((select ',' + quotename(sc.name)
            from sys.index_columns sic
            join sys.columns sc
                on sc.column_id = sic.column_id
            where so.object_id = sic.object_id
                and sic.index_id = si.index_id
                and sc.object_id = so.object_id
            order by sic.key_ordinal
            for xml path('')),1,1,'') as "@IndexColumns"
from sys.indexes si
join sys.objects so
    on so.object_id = si.object_id
join sys.schemas ss
    on ss.schema_id = so.schema_id
where so.type = 'U' and si.type = 1
for xml path('index'), root('indexes')

Things I'll point out:

• Adding the extra for xml clause, this time specifying a better row element name than 'row' and also adding a document node
  
• Once the outter for xml clause is in place, we can alias the columns using @ to have them come out as elements in the results

We can feed the results of that query straight into the following XSLT:

<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text"/>
    
    <xsl:template match='/'>
    <xsl:apply-templates select ='indexes/index'/>
    </xsl:template>

  <xsl:template match='index' >
create index <xsl:value-of select='@IndexName'/> 
  on <xsl:value-of select='@SchemaName'/>.<xsl:value-of select='@ObjectName'/> (<xsl:value-of select='@IndexColumns'/>)
  </xsl:template>
  
</xsl:stylesheet>

I'm not claiming this method is superior, just different, which I think is in keeping with the spirit of Rob's post   and I have learned more about the improvements of the for xml clause in SQL Server 2005 in the process.

I'll leave final judgment on the utility of this approach as an exercise to the reader; my instinctive reaction is to include it as a build step to help you snapshot schema changes between check-ins, or as Rob suggests to take objects from one database and create slightly different objects in another database – there are no wrong answers and if you think of something interesting please leave a comment

Grab the source files here: ScriptObjects.zip (.78 KB)

This is the last one in this current thread for a while :)

By the end of the last example we had code that would discover what stored procs were in a SQL Server database and generate a managed wrapper library that could be extended.

The next step it occured to me was to be then generate an assembly from this source and load it into the currently executing program.  The MSDN pages I have linked to have some good samples.

I'm adding this one to my toolbox of hammers looking for a nail.

(this post is an appendix to my prior post on Basic Code Generation with XSLT, because I forgot this bit the first time!)

I just wanted to expand on thsi line in the VB code part of the template:

Public Class DatabaseAccess
    Inherits ConvienientBaseClass

Including a (facetiously named) base class for the generated code was no accident.  It provides a good way to seperate the generated code from the human written code.  All the generated code should be the tedious, repetitive, error prone code.  Exception handling, transaction enrolement, logging etc should be implemented in the base class so it can be ripped out if need be and of equal importance; the generated code can be re-generated without clobbering any human written code.

But that isn't very .NET 2.0 now is it.  We can extend the concept of seperating the generated code from any human written parts by changing our template to generate partial classes:

Public Partial Class DatabaseAccess
    Inherits ConvienientBaseClass

 

 

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.]

I just thought it would be more sense to provide a link to the files mentioned in my last post.  Grab them if you are interested.

I've hacked on a little bit about XSLT and I think it's about time I added some more.  So this post will serve as the next instalment, and somewhat of an introduction to the uninitiated.

Lets say we were to consider the following data:

Position	Horse	Jockey
1	Delta Blues (JPN)	Y. Iwata
2	Pop Rock (JPN)	D. Oliver
3	Maybe Better (AUS)	C. Munce
4	Zipping (AUS)	G. Boss
5	Land 'n Stars (GB)	J. Egan
6	Mahtoum (AUS)	C. Brown
7	Yeats (IRE)	K. Fallon
8	Activation (NZ)	M. Rodd
9	Mandela (NZ)	C. Williams
10	Glistening (GB)	S. Seamer

As XML, it could concievably arrive like this:

<raceResults>
<finish result="1" jockey="Y. Iwata" originISO="JPN">Delta Blues</finish>
<finish result="2" jockey="D. Oliver" originISO="JPN">Pop Rock</finish>
<finish result="3" jockey="C. Munce" originISO="AUS">Maybe Better</finish>
<finish result="4" jockey="G. Boss" originISO="AUS">Zipping</finish>
<finish result="5" jockey="J. Egan" originISO="GB">Land 'n Stars</finish>
<finish result="6" jockey="C. Brown" originISO="AUS">Mahtoum</finish>
<finish result="7" jockey="K. Fallon" originISO="IRE">Yeats</finish>
<finish result="8" jockey="M. Rodd" originISO="NZ">Activation</finish>
<finish result="9" jockey="C. Williams" originISO="NZ">Mandela</finish>
<finish result="10" jockey="S. Seamer" originISO="GB">Glistening</finish>
</raceResults>

And that is fine, and may well even be useful.  The place where we should introduce XSLT is when we would like to do something with it like turning that XML into text, HTML, or more XML.

Text is an easy one to start with.  Consider the following stylesheet:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
<xsl:output method="text"/>

<xsl:template match="/">
   <xsl:apply-templates select="raceResults"/>
</xsl:template>

<xsl:template match="raceResults">
position,jockey,horse,
<xsl:apply-templates select="finish"/>
</xsl:template>

<xsl:template match="finish">
   <xsl:value-of select="@result"/>,<xsl:value-of select="@jockey"/>,<xsl:value-of select="."/>,
</xsl:template>

</xsl:stylesheet>

When applied to our XML, it will produce the following result:

position,jockey,horse,
1,Y. Iwata,Delta Blues,
2,D. Oliver,Pop Rock,
3,C. Munce,Maybe Better,
4,G. Boss,Zipping,
5,J. Egan,Land 'n Stars,
6,C. Brown,Mahtoum,
7,K. Fallon,Yeats,
8,M. Rodd,Activation,
9,C. Williams,Mandela,
10,S. Seamer,Glistening,

The trick is first to not look for flow control in the traditional fashion (For loops, etc).  Instead the XSLT processor will treat it in the following manner:

1. Start at the top of the source document - denoted with a /
2. Every time you find a raceResults do what is in the raceResults template.
3. (now inside the raceResults template) output the literal string: position,jockey,horse,
4. (still inside the raceResults template) every time you find a finish, do what is in the finish template
5. (now inside the finish template) Pick out the attribute called result, then pick out the attribute called jockey, then pick out what ever is contained in this element.  NB:  Attributes are denoted by the @

And that is it!

Now did you notice how literal text was handled?  All the bits of your output can live inside the template.  Now if we wanted an HTML representation we would use the following template:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
<xsl:output method="html"/>

<xsl:template match="/">
   <html><head><title>Race Results</title></head><body>
   <xsl:apply-templates select="raceResults"/>
   </body></html>
</xsl:template>

<xsl:template match="raceResults">
<xsl:apply-templates select="finish"/>
</xsl:template>

<xsl:template match="finish">
   <p>Position <b><xsl:value-of select="@result"/></b>was <b><xsl:value-of select="."/></b>ridden by <b><xsl:value-of select="@jockey"/></b></p>
</xsl:template>

</xsl:stylesheet>

...and following exactly the same steps as the text example, the XSLT processor will output the following HTML:

Position 1 was Delta Blues ridden by Y. Iwata

Position 2 was Pop Rock ridden by D. Oliver

Position 3 was Maybe Better ridden by C. Munce

Position 4 was Zipping ridden by G. Boss

Position 5 was Land 'n Stars ridden by J. Egan

Position 6 was Mahtoum ridden by C. Brown

Position 7 was Yeats ridden by K. Fallon

Position 8 was Activation ridden by M. Rodd

Position 9 was Mandela ridden by C. Williams

Position 10 was Glistening ridden by S. Seamer

As you can imagine, it can be easy to get a stylesheet for HTML output that contains a lot of HTML markup!  The important thing to note in this example is that the HTML inside the stylesheet must be valid XML as well as valid HTML.  Note the closing </p> which would normally be optional in HTML is manditory here.

Now we get to the fun example:  Turning XML into XML :D

Why would we want to turn XML into XML?  For as many reasons as you have XML in your life!  Imagine turning the above document into its own RSS feed for example.

We could use the following template:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
<xsl:output method="xml" indent="yes"/>

<xsl:template match="/">

<rss version="2.0">
   <channel>
      <title>Race News</title>
      <link>http://www.horses.com/</link>
      <description>Live race results as they cross the finish line.</description>
      <language>en-us</language>
      <pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
      <lastBuildDate>Tue, 13 Nov 2006 17:45:00 AEST</lastBuildDate>
      <docs>http://blogs.law.harvard.edu/tech/rss</docs>
      <generator>My RSS transformation</generator>
      <managingEditor>james@deepdark.net</managingEditor>
      <webMaster>james@deepdark.net</webMaster>
     
   <xsl:apply-templates select="raceResults"/>
  
   </channel>
</rss>

</xsl:template>

<xsl:template match="raceResults">
<xsl:apply-templates select="finish"/>
</xsl:template>

<xsl:template match="finish">
      <item>
         <title><xsl:value-of select="."/></title>
         <link>http://www.horses.com/lookup?<xsl:value-of select="."/></link>
         <description>Registered in: <xsl:value-of select="@originISO"/> .  Ridden by: <xsl:value-of select="@jockey"/></description>
         <pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
         <guid>http://www.horses.com/results/race6273-<xsl:value-of select="@result"/></guid>
      </item>
</xsl:template>
</xsl:stylesheet>

This example is the reason I took you so far down the page.  It builds on the key points of the prior two examples: 

1. The output can mingle in stylesheet, so long as it is valid XML.  I hope you have noticed that the stylesheet itself is valid XML too.
2. Again there are no flow control pieces.  In fact is is using exactly the same 5 step process I outlined for the first example (you guessed it, this was no mistake :)

The output is the following lovely RSS feed:

<?xml version="1.0"?>
<rss version="2.0">
<channel>
<title>Race News</title>
<link>http://www.horses.com/</link>
<description>Live race results as they cross the finish line.</description>
<language>en-us</language>
<pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
<lastBuildDate>Tue, 13 Nov 2006 17:45:00 AEST</lastBuildDate>
<docs>http://blogs.law.harvard.edu/tech/rss</docs>
<generator>My RSS transformation</generator>
<managingEditor>james@deepdark.net</managingEditor>
<webMaster>james@deepdark.net</webMaster>
<item>
<title>Delta Blues</title>
<link>http://www.horses.com/lookup?Delta Blues</link>
<description>Registered in: JPN .  Ridden by: Y. Iwata</description>
<pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
<guid>http://www.horses.com/results/race6273-1</guid>
</item>
<item>
<title>Pop Rock</title>
<link>http://www.horses.com/lookup?Pop Rock</link>
<description>Registered in: JPN .  Ridden by: D. Oliver</description>
<pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
<guid>http://www.horses.com/results/race6273-2</guid>
</item>
<item>
<title>Maybe Better</title>
<link>http://www.horses.com/lookup?Maybe Better</link>
<description>Registered in: AUS .  Ridden by: C. Munce</description>
<pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
<guid>http://www.horses.com/results/race6273-3</guid>
</item>
<item>
<title>Zipping</title>
<link>http://www.horses.com/lookup?Zipping</link>
<description>Registered in: AUS .  Ridden by: G. Boss</description>
<pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
<guid>http://www.horses.com/results/race6273-4</guid>
</item>
<item>
<title>Land 'n Stars</title>
<link>http://www.horses.com/lookup?Land 'n Stars</link>
<description>Registered in: GB .  Ridden by: J. Egan</description>
<pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
<guid>http://www.horses.com/results/race6273-5</guid>
</item>
<item>
<title>Mahtoum</title>
<link>http://www.horses.com/lookup?Mahtoum</link>
<description>Registered in: AUS .  Ridden by: C. Brown</description>
<pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
<guid>http://www.horses.com/results/race6273-6</guid>
</item>
<item>
<title>Yeats</title>
<link>http://www.horses.com/lookup?Yeats</link>
<description>Registered in: IRE .  Ridden by: K. Fallon</description>
<pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
<guid>http://www.horses.com/results/race6273-7</guid>
</item>
<item>
<title>Activation</title>
<link>http://www.horses.com/lookup?Activation</link>
<description>Registered in: NZ .  Ridden by: M. Rodd</description>
<pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
<guid>http://www.horses.com/results/race6273-8</guid>
</item>
<item>
<title>Mandela</title>
<link>http://www.horses.com/lookup?Mandela</link>
<description>Registered in: NZ .  Ridden by: C. Williams</description>
<pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
<guid>http://www.horses.com/results/race6273-9</guid>
</item>
<item>
<title>Glistening</title>
<link>http://www.horses.com/lookup?Glistening</link>
<description>Registered in: GB .  Ridden by: S. Seamer</description>
<pubDate>Tue, 13 Nov 2006 17:45:00 AEST</pubDate>
<guid>http://www.horses.com/results/race6273-10</guid>
</item>
</channel>
</rss>

I think this technique is going to be ever more important in the future.  Take in some SOAP, spit out some HTML.  Take in some XML from one API, spit it out as RSS.  A great tool for the toolbox.

I am so sorry about the title.  I am a child of a certain generation.

I deal a bit with XML in my day-to-day and "getting" XSLT was a pretty significant step forward in this for me.

Doug Tidwell's book XSLT was the key.

What prompted this revolation was seeing that XSLT was more than just a way of rendering XML to HTML in a browser, but that XML to XML translations can be exceedingly powerful.  Right now I am dealing with a data source that has been flattened from a normalized relational structure, and now it needs to be put back together into a third, different structure.  All this is do-able.

FWIW, my tool of choice currently for XSLT development is Architag's XRay... but I still prefer WMHelp's XML Pad for general day-to-day XML editing.