The major project I am currently working on requires many tables to be created in several copies of an SQL Server database in multiple offices. All of these tables need very similar views and stored procedures. There usually are two views per table, one to show all the records, and one to show the records 'WHERE deleted = 0'. I never delete any information in this database, it just gets hidden. There also at least three stored procedures per table, two for synchronisation with other databases, and the other for inserting new records. Quite obviously this becomes tedious quickly. I couldn't find much on the web that suited my exact requirements and I was feeling too lazy to develop my own software. The answer is a very nice combo of XML, XSL, and a command line transformation tool kindly provided by Microsoft in the form of MSXSL.EXE.

Firstly I define any new tables I want to create in a fairly straight forward XML layout. For example:

<table name="locations">
 <field name="deleted" type="bit" nullable="false" id="deleted" />
 <field name="lastRevision" type="lastRevisionType" nullable="false" />
 <field name="number" type="int" nullable="false" id="pk">
  <constraint type="primaryKey" />
 </field>
 <field name="suburb" type="varchar" size="64" nullable="false" />
 <field name="state" type="varchar" size="4" nullable="false" />
 <field name="postCode" type="varchar" size="4" nullable="false" />
 <field name="freightZone" type="int" nullable="false">
  <constraint type="foreignKey" foreignTableName="freightZones" foreignFieldName="number" />
 </field>
</table>

Secondly I use some rather fine tuned XSL scripts and a neat batch file to call MSXSL with the appropriate parameters to output the CREATE TABLE SQL, and the SQL for all the views and stored procedures into a file ready to be pasted into Query Analyzer. As you can see, the XML layout is very easy to understand and it would be equally easy to export this layout for existing tables in almost any database or to use this layout to generate any SQL or perhaps even documentation, diagrams, .NET classes or .NET Windows Forms with all the needed controls.

The tricky part in using XSL to generate SQL is getting commas between all fields except the last and, even more so, getting the whitespace output neatly. Fortunately I was cunning enough to ask a markup-wizard colleague of mine to do most of the XSL work for me. It was greatly appreciated as I only had to add the finishing touches and didn't need to read the W3C's XSL specs again. Admittedly, I kept the transformations simple. If I need to define a table with a multi-field primary key then I will need to adjust the resulting SQL before I use it. This happens rarely and is still easier than doing it all manually. There are probably other situations that cause the XSL to fall over but I haven't encountered them yet.

For anyone who has used similar techniques before or decides to try it now, I would like to hear some of your experiences. Here is an example of one of the XSL scripts to get you started:

<?xml version="1.0" encoding="iso-8859-1" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
 <xsl:output method="text" encoding="iso-8859-1" indent="no" />
 <xsl:strip-space elements="*" />
 
 <xsl:template match="table">
CREATE VIEW dbo.vw_<xsl:value-of select="@name" /> AS
SELECT
<xsl:for-each select="field[not(@id='deleted')]">
 <xsl:text> </xsl:text>
 <xsl:value-of select="@name"/>
 <xsl:if test="position() &lt; last()"><xsl:text>,&#10;</xsl:text></xsl:if>
</xsl:for-each>
FROM dbo.tb_<xsl:value-of select="@name" />
WHERE
<xsl:for-each select="field[@id='deleted']">
 <xsl:text> </xsl:text>
 <xsl:if test="position() &gt; 1"><xsl:text>AND </xsl:text></xsl:if>
 <xsl:value-of select="@name"/>
 <xsl:text> = 0</xsl:text>
</xsl:for-each>
GO

GRANT SELECT ON dbo.vw_<xsl:value-of select="@name" /> TO sales, visitors
GO
 </xsl:template>
 
</xsl:stylesheet>

Often I feel like I spend too much of my time searching through the framework source and the Win32 API when developing .NET applications. Today was no exception. A colleague suggested that a pair of message boxes displayed the end user in some retail POS software appeared too similar and should have custom icons to help distinguish them.

Windows.Forms.MessageBox provides a wrapper for the Win32 MessageBoxEx function which only supports the four standard message icons. There is however the Win32 MessageBoxIndirect function which is not accessible via the standard .NET framework but does support custom icons. Unfortunately the MessageBoxIndirect function only supports icons embedded as a Win32 resource in the executable and .NET resources are embedded completely differently.

I can add a .rc/.res file to Managed C++ project to be embedded in the executable but the IDE doesn't support .rc/.res files in a C# or VB.NET project (which is the language used by the retail POS software). It is possible to use the command line tools to build the .res file from a .rc file and link it into a C# or VB.NET assembly but this would be awkward to do even with some Visual Studio IDE post-build addin. I could add a Managed C++ project to the solution to provide all the Win32 icon resources but either way this is becoming all too complex for a feature that was destined to be included in a common class library.

Considering .rc/.res files are language independent and are used by the IDE internally to embed the executable file's icon as a Win32 resource this should be possible. I would be very interested if anyone knows how to fudge a VB.NET project to link a .res file into the final output target as part of the standard build process.

On a similar note, I have ordered John Mueller's book ".NET Framework Solutions: In Search of the Lost Win32 API". Hopefully this will be very handy when I tackle the rest of the framework.

I often get the feeling that I am the only person who has setup Windows 2K/XP computers with limited-access users. Does everyone out there really configure every user as an Administrator? The majority of application software and games available today suggest this is true.

Microsoft published a document entitled "Designed for Windows XP Application Specification" about three years ago. I can only assume that nobody has read it or any of the other related publications or API documentation. On page 37 of this document is Section 3.0, "Data and Settings Management". Here it quite clearly explains where applications need to save user related data and how. The days of Windows 3.1 are over. We have a Program Files folder with security permissions denying write access to non-Administrators. This has been the situation since Windows 2000 (maybe Windows NT 4.0 also?). Yet too many programs attempt to store user configuration information in the same folder as the executable itself.

On my home PC, I have a user account for myself and a user account for my girlfriend, Libby. Libby's account is a limited user account and this breaks a lot of software. She can't use WinAmp without modified permissions on the application's install folder. Also, we each end up inheriting the configuration changes made by the other. Many games that Libby likes to play (including the new and fantastic Yourself Fitness!) have problems too. These games try to store game progress data in the game's install folder. As a result Libby has to start from the beginning everytime unless I change the default permissions.

As the administrator it is necessary for me to install any software that Libby requests. If I am happy it isn't something harmful that she has downloaded I go ahead. Unfortunately it seems that a lot of software won't install to multiple users either. It is not just that the setup program simply didn't put the shortcuts in the All Users start menu. If I create the appropriate shortcuts, the software still won't run under a non-privileged account.

I guess the problem is a combination of ignorance, failure to test restricted user scenarios, and simply considering the problem insignificant. WinAmp has been around for a long time. Version 5 was released not too long ago. However, discussions regarding multi-user support in WinAmp on the WinAmp developer forums have been dismissed as not important.

Everywhere we look today, the focus is on security but if even the most secure and stable software won't work for users other than administrators, what is the point?