The life of an application config from the registry to SQLite to SQL CE to XML

A new beta of ShutOff 2000 v3.0.0 is now available. The product is fast approaching code completion.

The Visual Basic 6 edition of the application has always used the Windows Registry to store its settings. However, Microsoft’s best practice for the Windows 7 and Windows Server 2008 R2 platform does not allow for this, so I’ve been researching new ways to store settings outside of the Registry.

Right from the start, I wanted to store everything in an XML file. I toyed around with encrypting the file, making it binary, and so on, but eventually I dismissed the notion and decided to use an embedded database.

That’s where things got interesting. Originally, I used SQLite, but after playing around with it for a while, I decided that there was a lot of overhead attached to this, as well as non-native interops, so I switched instead to use SQL Compact Edition (SQL CE), which operates in a similar fashion to SQLite.

Unfortunately, in all of my testing, SQLite and SQL CE was just not fast enough. The queries themselves to store and retrieve records were fast, but the initial connection was too slow. Since the new ShutOff 2000 has a command-line interface, I wanted millisecond response times from the stored settings.

I readily admit that the connection speed is probably my fault, but when it comes right down to it, I used the Windows Registry because I only need to save around ten plain-text values, and it felt as though I was over-engineering things.

After announcing on Twitter that SQL CE was too slow, I decided to fall back to my original XML idea. As mentioned above, this comes with specific rules about where you can save your files. At least with the embedded database, the file was distributed with the application, but in the case of XML configuration, I’d need to be able to freely create and modify the settings file at will.

Enter StackOverflow.com, with a question that pointed me in the right direction. I used the suggestions provided by the accepted answer, but was not completely satisfied with the result, which worked on a per-user level. Instead, I used my Bingle-Fu to find a project called “User options in XML” on CodeProject, by Richard Schneider.

I tweaked the class that he wrote so that it would utilise Environment.SpecialFolder.CommonApplicationData instead of Environment.SpecialFolder.ApplicationData as written, and added some very basic error handling for managing a corrupt XML file (which came about when I renamed the class and got an InvalidOperatorException reading the file).

The good news is that although this wasn’t exactly a drop-in replacement for SQL CE, the refactoring I previously did to separate registry settings from SQL CE settings reduced the implementation time dramatically. I was also able to refactor a lot more code and make the product more stable.

So now I’m feeling a lot better about how this is turning out. It’s been a long road for me: I’m more of a database person than a software developer, and I really wanted the embedded databases to work, but I think this is the right approach.