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>

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?