I have planned to post about MYOB's developer support for some time and I was provoked by one of Clarke Scott's recent blogs to do it now. I have been working with a wholesale company to automate the export of data from their MYOB Premier company file to their website on a regular basis. They want their customers to be able to access current pricing and stock levels of all the products they distribute, and they want their customers to be able to view current back orders and recent invoices online.

I have done plenty of work like this before for non-MYOB accounting systems and even MYOB RetailManager (which conveniently uses a Microsoft Access database). Unfortunately, every other MYOB product uses a proprietary database format, which, at the time I started this job, required the customer to pay $259 Australian per company file for an ODBC driver. The ODBC driver was read only and was incompatible with ADO.NET in certain situations. For documentation on the MYOB table structure, some code samples and three months forum access, MYOB Australia expected the developer to cough up around $900. I discovered however that MYOB USA provides table structure documentation (the "Data Dictionary") for free download from their website. A few tables have slightly different names and some tax related items are different but it is mostly identical to the Australian MYOB system. I decided to call MYOB Australia about this situation and their response was basically, "that's the way it is, live with it".

Thankfully, in the last three months, MYOB Australia has improved their support for developers. They now provide the Data Dictionary, sample code, and tools for free download from the website. The ODBC driver has been updated and has hopefully been improved but I have not tested the ADO.NET problems yet. MYOB also have an excellent Developer Partner Program now. For just under $700 per annum you get an ODBC driver with write access, full access to MYOB's email and forum support, and your customers can use the ODBC driver with their company file free.

If you are working with MYOB, or plan to, the partner program is excellent.

While Googling for something quite different, I stumbled across an article on The Code Project by Scott McMaster. The article covers almost everything you would want to know about using the Win32 MessageBoxIndirect API from .NET. Specifically, he discusses the problems with using custom icons considering .NET resources and Win32 resources are completely different. One of Scott's solutions is to create a small unmanaged Visual C++ project purely to host the icons as Win32 resources.

However, Scott's MessageBoxIndirect wrapper exposes most of the gory details of the API and expects the caller to pass in a handle to the C++ resource DLL as an IntPtr. I decided to tidy this process for the wrapper I built and added an IDisposable implementation to my class. This class accepts the path to the DLL as a String and the resource ID as an Int32 and handles the calling of the LoadLibraryEx and FreeLibrary APIs appropriately.

In my ongoing war with the Framework, a memorable battle worth documenting is that of the ListView and the BackgroundImage. My current major project consists of several forms that all have a large ListView control in Details view as the prime user interface element. These forms are so similar that I was able to inherit them all from a custom base Form with most of the functionality. Unfortunately, some users don't really pay attention to what is in front of them, so I decided to put a nice big picture as the background of the ListView to make things obvious.

The ListView control has a public BackgroundImage property but it has attributes preventing it from displaying in the Properties window. I tried setting BackgroundImage property via code and discovered that it has no effect. After a great deal of digging with Lutz Roeder's trusty Reflector I discovered that the problem is a combination of the ListView's ControlStyles and the Control's WmEraseBkgnd. To solve this I created a new class to inherit from ListView and catch the WM_ERASEBKGND message in an overridden WndProc. In the WndProc, I change the UserPaint and AllPaintingInWmPaint styles appropriately, pass through to MyBase.WndProc to draw the background image, and finally revert the styles to their previous values. I also overrode the BackgroundImage property's attributes to show it in the Properties window again.

This time the background image shows in the ListView but as soon as an item is added to the list it obscures the image. Reflector showed that ListViewItems have their own BackColor which defaults to the parent ListView's BackColor if not specified. I changed my code to set each ListViewItem's BackColor to Color.Transparent but it didn't help. Further digging in Reflector shows than the framework's ColorTranslator.ToWin32 method discards transparency information. To solve this I catch WM_REFLECTNOTIFY (a special modification of WM_NOTIFY) in my subclass, call MyBase.WndProc to do most of the work, obtain a NMLVCUSTOMDRAW structure from the message's LParam, then use my own transparency-aware ToWin32 method to set the item's background colour correctly.

So now the background image shows and the items don't obscure it but as the selection moves from item to item, the unselected items still appear highlighted. My code never puts more than 50 items in the ListView so I decided to take the easy way out and call Me.Invalidate in my subclass' overridden OnSelectedIndexChanged method. I should find the rectangles for the unselected item(s) and only invalidate those but I will do that later when the effort is justified.

I hoped my custom ListView would be perfect now, and it was for a while, but as soon as it contained enough items to require scrolling I noticed the background image became garbled after each scroll. The ListView does not provide OnScroll methods so I needed to catch the WM_VSCROLL and WM_HSCROLL messages and once again call Me.Invalidate. The background image is always drawn correctly now but the list flickers when scrolling and I can't justify the effort to solve that minor glitch yet.

I decided to setup a PaintBackground event (normally missing from the ListView control) so I can draw dynamic information onto the ListView at runtime and everything is just dandy now. With this much work involved I though it was worth checking if these problems are fixed in .NET 2.0. I downloaded the beta and was pleased to find many new features in the ListView control, including support for the BackgroundImage property. Sadly, testing this feature showed that the people at Microsoft also had problems with the selection highlight not clearing. I lodged a bug report and apparently it has been fixed in time for the official release. I'm glad I don't need to fix the new one myself too; .NET 2.0 is much more complicated under the hood.

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>