I am working on a project that contains nearly identical clients (same basic featuresets, different UI) in very different environments (Winforms, Mobile, and Web). The biggest difference is that the web sites (one client-equiv site, one admin site, and a set of web services) contains some additional functionality. I’ve architected this to maximize the amount of code we can reuse in all of these environments. The data tiers all implement common interfaces or abstract classes. The business tiers all extend classes in a shared DLL. And the presentation tiers are all independent but all go through the same basic API to call into the business tier. If I get interest in it, I can go more into this.
Because there is so much common functionality, the databases are all very similar (and in some cases, large parts are 100% identical). The winforms and web systems all use SQL Server for their databases (Express on the clients but we don’t hit any of those limitations). However, with our Compact Framework project, it’s quite a bit different since we have to use SQL Server Compact Edition (or whatever they’re calling it this month). We have yet to find an easy automated way to take a desktop database and convert it into a compact edition database, so we wrote a little utility. It only took a couple of days, so it wasn’t that bad. We essentially call the sp_tables proc in SQL Server in our development database, iterate through the results, and generate CREATE TABLE scripts based on it. Easy enough. And for the data, we simply use Command Builders and DataAdapters (we use a SqlCeCommandBuilders and SqlCeDataAdapter for the Compact Edition interface but it works just fine for us) and the WriteXml and ReadXml methods that go with it.
During development of our utility, we used the IgnoreSchema option with WriteXml when trying to fix a bug and forgot to turn it back off. This just came back to bite us in the ass! One of our tables in the final database that we’re converting is called “MessageType” and is a simple lookup table (2 ints and a varchar(50) is all). Well, when we were trying to import the data for it, we were getting an exception on the DataAdapter.Update call: “There was an error parsing the query.” We spent 2-3 hours trying to figure it out and we finally figured out the problem and it came back to that IgnoreSchema option that we had set and erroneously left!
So we have our “MessageType” table and we also had an “AuditLog” table. A column in the AuditLog table was called “MessageType” and was completely unrelated to our MessageType table. Yeah, bad naming here – we need to fix that obviously but it shouldn’t be enough to confuse .NET, right? Wrong! Since our XML files were generated without schemas, .NET must make some assumptions about the XML coming in. In this case, I think it decides that any XML node that comes up as <MessageType> is an entry for our MessageType table, when it comes time to populate our MessageType table. But when it goes into the XML we pass it, it sees the <MessageType> nodes from our AuditLog table and because the tag matches, it tries to start parsing it as though it belongs to our MessageType table, which will obviously fail! So now you see the source of our problem.
Switching IgnoreSchema back to WriteSchema was a simple fix to the problem. Other things can be done that could have resolved this specific problem but this was the most wrong thing we had done and needed to fix. Now, time to go play with a freshly generated set of SQL Server Compact Edition databases on our PDAs! 🙂