Skip to content

Filtering sqlmetal output using XSLT

by Alex Peck on March 4th, 2010

I needed to generate LINQ to SQL O/RM classes for a subset of the objects in a large database. Unfortunately, sqlmetal doesn’t provide a mechanism to filter it’s output, so I made my own using XSLT, a batch file and a custom build target. This post explains how.

Overview

When you invoke a build, a custom “BeforeBuild” build target runs to do the code generation. This code generation is driven by a batch file, this is what it does:

  1. Run sqlmetal to output a dbml file for the entire database schema
  2. Using an XML configuration file and an XSLT transform, generate a test .cs file containing a reference to each expected table. When included in our project, this gives us a compile time test that we generated the expected classes (we make this internal).
  3. Run a second XSLT transform based on the configuration file and the output a dbml file from step 1. In this step we prune the xml according to the config file. The result is a dbml file containing only the tables we specify in our configuration.
  4. Run sqlmetal again using the result of step 3 as input. This time sqlmetal outputs our CSharp code for our Linq to Sql classes.

The XSLT transforms are run from the command line using MSXSL.exe, which is available here.

The generated CSharp files are part of my Visual Studio project. After the BeforeBuild target is run (which does the code generation), the generated code is compiled into an Assembly.

Custom build target

These are the pertinent parts of my project file. You can see that inside my project directory, I created a directory called Prebuild where the work happens. This is where the Configuration.xml, DbmlPruner.xslt, GenerateTestTypes.xslt and generate.bat files live.

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="3.5" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <!-- most of the project file omitted for brevity -->
  <Target Name="BeforeBuild" DependsOnTargets="GenerateDbClasses;"> </Target>
  <Target Name="GenerateDbClasses" Inputs="Prebuild\Configuration.xml;Prebuild\DbmlPruner.xslt;Prebuild\GenerateTestTypes.xslt" Outputs="MasterDb.Generated.cs;TestMasterDbTypes.cs">
    <Exec Command="$(ProjectDir)Prebuild\generate.bat $(ProjectDir) $(TargetName) master.dbml prunedmaster.dbml TestMasterDbTypes.cs MasterDb Configuration.xml master" />
  </Target>
</Project>

Configuration.xml

<?xml version="1.0" encoding="utf-8"?>
<!-- This configuration is used to specify which tables should generate linq to SQL classes, 
  and to generate a sanity check class which verifies all the expected types exist in the 
  generated code.
  -->
<Configuration Name="Master Tables" SourceXml="master.dbml" Namespace="Master.Data.Linq" TestClassName="TestMasterTypes">
  <Table SqlName="dbo.TestTable" DataContextPropertyName="TestTable" ClassName="TestTableRow"/>
</Configuration>

GenerateTestTypes.xslt

<?xml version="1.0" encoding="utf-8"?>
<!-- ===========================================================
  Generate a C# class with members corresponding to all the linq
  to SQL tables specified in the input file.
================================================================ -->
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output method="text" />
  <xsl:template match="/">
//-----------------------------------------------------------------------
// <![CDATA[<auto-generated>]]>
//     This code was generated by a tool.
//
//     Changes to this file may cause incorrect behavior and will be lost 
//     if the code is regenerated.
// <![CDATA[</auto-generated>]]>
//-----------------------------------------------------------------------
 
// Disable warning CS0169: The private field 'foo' is never used. This is 
// by design.
#pragma warning disable 0169
 
namespace <xsl:value-of select="/Configuration/@Namespace" />
{
    /// <![CDATA[<summary>]]>
    /// This class is provided as a compile time test for the linq to SQL
    /// classes specified in Prebuild/configuration.xml. It will fail to
    /// compile if one of the dependent classes is not generated (or not
    /// generated with the expected name). This is by design.
    /// <![CDATA[</summary>]]>
    internal class <xsl:value-of select="/Configuration/@TestClassName" />
    {      
<xsl:for-each select="/Configuration/Table"><xsl:text>&#9;</xsl:text><xsl:text>&#9;</xsl:text>private <xsl:value-of select="@ClassName" /> <xsl:value-of select="' '" /> <xsl:value-of select="concat(@ClassName, 'Member')" />;<xsl:text>&#xa;</xsl:text></xsl:for-each><xsl:text>&#9;</xsl:text>}
}
 
#pragma warning restore 0169
  </xsl:template>
</xsl:stylesheet>

DbmlPruner.xslt

<?xml version="1.0" encoding="utf-8"?>
<!-- ===========================================================
  Replicate a .dbml file based on the tables specified in a 
  configuration.
================================================================ -->
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
	 xmlns:sql="http://schemas.microsoft.com/linqtosql/dbml/2007"
     version="1.0" >
  	<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"
  	  omit-xml-declaration = "no"/>
 
	<xsl:template match="Configuration">
 
		<!-- load sql metal output as $database -->
		<xsl:variable name="database" select="document(@SourceXml)"/>
    <xsl:variable name="namespace" select="namespace-uri($database/sql:Database)"/>
 
		<xsl:comment> =====================================================================================
  <xsl:value-of select="@Name" /> generated from database <xsl:value-of select="$database/sql:Database/@Name" /> (<xsl:value-of select="@SourceXml" />)
========================================================================================== </xsl:comment>
 
		<!-- Output a tree which replicates @SourceXml but contains only the table nodes in the configuration -->
		<xsl:element name="Database" namespace="{$namespace}">
			<xsl:attribute name="Name">
        		<xsl:value-of select="$database/sql:Database/@Name" />
      		</xsl:attribute>
 
			<xsl:for-each select="/Configuration/Table">
 
				<xsl:variable name="sqlName" select="@SqlName"/>
 
				<!-- only output a table element when the source table exists -->
				<xsl:if test="$database/sql:Database/sql:Table[@Name=$sqlName]">
 
					<!-- Output the table substituting Member and Type for the ClassName in the configuration -->
					<xsl:element name="Table" namespace="{$namespace}">
						<xsl:attribute name="Name">
							<xsl:value-of select="@SqlName"/>
						</xsl:attribute>
						<xsl:attribute name="Member">
							<xsl:value-of select="@DataContextPropertyName"/>
						</xsl:attribute>
 
						<xsl:element name="Type" namespace="{$namespace}">
							<xsl:attribute name="Name">
								<xsl:value-of select="@ClassName"/>
							</xsl:attribute>
 
							<!-- Copy the children (Columns etc) -->
							<xsl:copy-of select="$database/sql:Database/sql:Table[@Name=$sqlName]/sql:Type/*"/>
						</xsl:element>
 
					</xsl:element>
 
				</xsl:if>
			</xsl:for-each>
		</xsl:element>
	</xsl:template>
 
</xsl:stylesheet>

generate.bat

This is my entire generate.bat file, you can glean the input arguments from the custom build target above.

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Generate LINQ to SQL classes based on tables defined in an xml config
::
 
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
 
setlocal 
 
set SQLMETAL=<put your path here!>\sdk\Win2008\Bin\sqlmetal.exe
set MSXSL=<put your path here!>\msxsl.exe
 
set PRJDIR=%1%
set PREDIR=%PRJDIR%Prebuild\
 
set NAMESPACE=%2
 
set METALOUT=%PREDIR%%3
set METALIN=%PREDIR%%4
 
set COMPILETESTCLASS=%PRJDIR%%5
set CONTEXTCLASS=%6
set CONTEXTCLASSFILE=%PRJDIR%%CONTEXTCLASS%.Generated.cs
 
set CONFIGPATH=%PREDIR%%7
set DATABASE=%8
 
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
 
echo ================ Generation of LINQ to SQL classes started ================
echo Using %CONFIGPATH%
 
echo 1. Building %METALOUT% for entire %DATABASE% database 
%SQLMETAL% /conn:"server=localhost; database=%DATABASE%;Integrated Security=SSPI" /dbml:%METALOUT%
if errorlevel 1 goto :Failed
 
echo 2. Generating %COMPILETESTCLASS% to test linq to sql types were generated correctly at compile time
%MSXSL% %CONFIGPATH% %PREDIR%GenerateTestTypes.xslt -o %COMPILETESTCLASS%
if errorlevel 1 goto :Failed
 
echo 3. Building %METALIN% from %CONFIGPATH%
%MSXSL% %CONFIGPATH% %PREDIR%DbmlPruner.xslt -o %METALIN%
if errorlevel 1 goto :Failed
 
echo 4. Generating %CONTEXTCLASSFILE% using %METALIN%
%SQLMETAL% %METALIN% /code:%CONTEXTCLASSFILE% /language:csharp /context:%CONTEXTCLASS% /namespace:%NAMESPACE% /serialization:Unidirectional
if errorlevel 1 goto :Failed
 
echo ======== Generation of LINQ to SQL classes completed successfully =========
endlocal
goto :EOF
 
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:Failed
 
echo Configuration %CONFIG% FAILED
endlocal
exit /B 1
No comments yet

Leave a Reply

Note: XHTML is allowed. Your email address will never be published.

Subscribe to this comment feed via RSS