Microsoft: Please allow Null values to be returned from strongly typed datasets!

Recently while working on an application created in Visual Studio 2005 (also applies to 2008) on the .NET 2.0 framework I noticed a strange error when running with live customer data. The error was, “Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints”: Read more

XML in SQL Server 2005

Those of you who are familiar with SQL Server 2005 will know that Microsoft have provided a brand new “xml” datatype for use with it.

The new xml datatype effectively allows a database administrator or developer to create SQL databases which contain heirarchical information within the context of a single field. This is an incredibly powerful data design paradigm. Using the standard data types for relational database management systems, you would have to create at least one normalised table per entity class, with each property of the entity class being designed for - ahead of time - by creating the table schema design for it. This works very well if you know well in advance exactly what data you are planning to store.

But what if you have to store information on a product which has a vast quantity of varying properties (such a car). You couldn’t add these as properties of a table, since you would quickly run out of space or have a lot of unused, redundant fields.

Traditionally, this would be exposed as a design flaw later on in the project lifecycle, necessitating the addition of lots of child tables with foreign keys, and therefore a large amount of code would need to be changed in order to accomodate this.

Enter, XML. Already used as the basis for most modern day information storage and transfer needs, it is now possible to store XML strings directly into columns within your SQL database. This is supported in any .mdf database engine, including SQL Server, SQL Everywhere, SQL Express and SQL Mobile.

This is wonderful news for object orientated programmers, who now have a flexible way of storing rich, hierarchical information, which can be extended at a moment’s notice without requiring any breaking changes to be made.

The XML in the database can also be queried. Previously to the XML datatype, I have seen XML data were stored as ntext (eww), varchar (yuck) or BLOB’s (binary objects). The new XML datatype can be queried directly. This is going to be as close as relational databases come to pivot-tables

Last week, in a recent database implementation for one of my clients, I noticed that SQL Server 2005 was throwing some very odd errors back to my SQL data class: “SqlException: unable to switch the encoding”. Long story short, SQL Server 2005 does not support UTF-8 to store data, and the “standard” XML well formed declaration contains encode=”UTF-8″.

Nearly everyone starting out using the xml datatype will hit this error. The error will be thrown the moment that you attempt to insert sample XML into the column:

System.Data.SqlClient.SqlException:
“XML parsing: line 1, character xxx, unable to switch the encoding”

SQL Server 2005 expects to receive UTF-16 encoded XML, and will not accept XML if it contains a UTF-8 declaration.

The solutions are to either:

1. Remove the declaration from your XML when inserting to your SQL database
2. Change the encoding to UTF-8.

If you want to do change the encoding to UTF-16, you could try using the following code to perform your conversion:

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
System.Xml.XmlDocument newDoc = new System.Xml.XmlDocument();
System.IO.StreamReader r = new System.IO.StreamReader(”C:\XML.xml”);
doc.LoadXml(r.ReadToEnd());
r.Close();

System.Xml.XmlDeclaration xmldecl;
System.Xml.XmlElement root = newDoc.DocumentElement;
xmldecl = newDoc.CreateXmlDeclaration(”1.0″, null, null);
xmldecl.Standalone = “yes”;xmldecl.Encoding = “UTF-16″;
newDoc.LoadXml(doc.DocumentElement.OuterXml.ToString());
newDoc.InsertBefore(xmldecl, root);

Or if you’re in a hurry (and have a hard-coded declaration string):

xmlString = xmlString.replace(”<?xml version=”"1.0″” encoding=”"UTF-8″“?>“,”");

I hope this helps someone. If you find the code above useful, please leave a comment.

The inefficient public sector (HMRC)

One of the biggest problems for vendors supplying the construction industry is the sheer complexity of the messaging system (this is a topic all of its own). In short, it involves connecting to, and messaging with the Government Gateway, waiting for responses, checking for errors, polling, and reading back the information, checking for errors again and continuing with the business rules. It’s complicated, let’s leave it at that.

Over the weekend, I was playing with exactly the same technology (XML and SOAP) - but this time instead of the Government Gateway server, it was the YouTube video server. I would imagine the YouTube video server to be servicing hundreds of thousands of queries per minute - far more than the Government Gateway (which crashed from demand in the opening days of new CIS). It took me two days to write an entire query engine (with base classes for videos and collections). It’s taken almost two years to get a product to market (at least six months of which was the messaging facility, including attending or receiving notes from developer meetings in Euston Tower, London) to do the same thing with a UK Government Project.

The documentation for the YouTube web service is a couple of pages long. Explanation of the classes, properties and the methods. It isn’t signed by anyone, and you don’t need to go through a long process to register with them, just give them your email will do.

The documentation for the Government Gateway service is huge. You will need to download all the Government Gateway messaging documentation, filter out the dull 1980’s EDI bits and then go and get the New CIS schemas and business rules. Printed out, on 120gsm paper (posh paper), the stack of reading (including the bits on learning the scheme) would literally kill someone if it lands on them. This documentation, going on for miles, signed off by almost everyone in the public sector, serves only to confuse and keep everyone but the most tenacious developer from understanding it.

It also doesn’t help that the way in which HM Revenue & Customs have their data designed isn’t normalised or intuitive.

To be fair: HM Revenue & Customs themselves are very helpful. They have a software developer support team who supremely helpful and are always available for geeky technical conversation. However, they cannot answer any questions on the Government Gateway or assist on the live service (did I mention there was no black box test service??). It’s not their fault, on the contrary, power to those with technical ability - put them in charge please!

So the question needs to be asked. Why does the public sector go the long way around to do something so simple? Why does it take millions of pounds of tax-payer money in the public sector to accomplish something a 15 year old script kiddie can do - before dinner - on their PC in a bedroom?

As a former consultant to the public sector, I can tell you:

• Lots of people

• Lots of arse-covering

Every man, woman, child and dog needs to sign off and see every specification. Everyone needs to debate every last bit of the implementation, and common standards and practices are not used, because there are so many parties involved. There is no engineer-manager at these places. With regards to HM Revenue & Customs, this is expecially true, as even when their servers blow up - they have to call in third party (very expensive) consultants to fix up their own systems.

This is why I love being in charge of my own Micro ISV.

I turn up at work, greet whoever is working with me that day (we subcontract), and put on some music and get to it. If a design decision needs to be made with a client, we call or email that client and put it to them directly. If it’s a technical decision - we get to it and get it done.

If we need to procure something. A product, a set of new controls, a new spikey office plant - we go and do it. No rubber stamps, no conferences.

I have worked myself on behalf of customers, in a shed on a lake in freezing tewkesbury for a week. I have delivered whilst on a plane. In a restaurant (god bless mobile devices). At 6am outside a train station. At 4am, chugging coffee at a neighbours house. At 3pm after landing at a small airfield in Dorset, as a passenger in a friend’s light aircraft.

As countless innovators in California have shown us; Small, agile and focussed beats big, corporate and slow - every single day.