XML serialization, doubles, decimals and SQL Server

We just had a problem with data that is sent to SQL Server as a XML string.

One of the values sent to the SQL Server is defined in the C# class as a double and interpreted as a 'DECIMAL(28, 9)' on the SQL Server.

This was working so far. Just wen someone entered a quite small number (0.000013) it stopped working.

The reson: 1.3E-05 is sent to the SQL Server and it can’t interprete this.

It COULD be parsed on the SQL Server, although would involve some floting point madness that could mess up the value.

The easyest would be to change the interface, but as we use the same class in the front end of a webservice, this would lead to an incopatability in the schema.

Now, the solution we’re going to use is this:

We specify a custom XML serialization for our class and handle the double as a decimal. I know that the value ranges are diffenrent, but in our example this doesn’t matter.

See the code below:

     
    //---------------------------------------------------------
    public class FooBar : IXmlSerializable
    {
        //---- Members -----------------------------------------------------
        public decimal ADecimal { get; set; }
        public double ADouble { get; set; }

        //---------------------------------------------------------
        public virtual void WriteXml(System.Xml.XmlWriter writer)
        {
            writer.WriteElementString("ADecimal", Convert.ToString(ADecimal));
            writer.WriteElementString("ADouble", Convert.ToString(Convert.ToDecimal(ADouble)));
        }

        //---------------------------------------------------------
        public virtual void ReadXml(System.Xml.XmlReader reader)
        {
            reader.ReadStartElement();
            ADecimal = decimal.Parse(reader.ReadElementString("ADecimal"));
            ADouble = double.Parse(reader.ReadElementString("ADouble"));
            reader.ReadEndElement();
        }

        //---------------------------------------------------------
        public XmlSchema GetSchema()
        {
            throw new NotImplementedException();
        }
    }