using System;

using System.IO;

using System.Data;

using Microsoft.SqlServer.Types;

using System.Data.SqlTypes;

using System.Xml;

using System.Xml.Serialization;

using System.Xml.Linq;

​

//All credit go to @dbc

//https://stackoverflow.com/questions/45895039/datatable-with-sqlgeography-column-cant-be-serialized-to-xml-correctly-with-los

//Test @dbc alternative 2 in his answer to the question 45895039

​

​

    public class Program

    {

        public static void Main()

        {

            try

            {

                var test = SqlXml.Null;

​

                Test();

                Console.WriteLine("");              

                Console.WriteLine("Result of serializing SqlXml.Null: ");

                Console.WriteLine(test.GetXml());

                Console.WriteLine("");              

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex);

                throw;

            }

        }

        public static void Test()

        {

            var dt2=GetGeoTable2();

            Console.WriteLine("Initial {0} using AsGml():", dt2);

            DumpDataTable(dt2);

            Console.WriteLine("");

            Console.WriteLine("------- Step 1: serialiization using datatable with SqlGeographyDTO------");

            var xml=SerializeDataTable(dt2);

            Console.WriteLine(xml);

​

            // Assert serialization is reproducible

            Assert.IsTrue(xml == SerializeDataTable(dt2));

            Console.WriteLine("\nStep2: DeSerialiization the same xml using datatable with SqlGeographyDTO");

            var dt3=DeSerializeDataTable(xml);

            Console.WriteLine("Writing data from the generated datatable:");

            DumpDataTable(dt3);

​

            // Assert serialization round-trip is correct

            Assert.IsTrue(xml == SerializeDataTable(dt3));

​

            Console.WriteLine("");

        }

        public static string SerializeDataTable(DataTable dt)

        {

             var writer = new StringWriter();    

             dt.WriteXml(writer,XmlWriteMode.WriteSchema );

             var xml=writer.ToString();     

            return xml;

        }

        public static DataTable  DeSerializeDataTable(string xml)

        {

            DataTable dt=new DataTable();

            StringReader srXMLtext  = new StringReader(xml);

            dt.ReadXml(srXMLtext);      

            return dt;

        }

        static DataTable GetGeoTable2()

        {

            DataTable dataTable = new DataTable("table");

            dataTable.Columns.Add("f1", typeof(SqlGeographyDTO));

            dataTable.Columns.Add("id", typeof(int));

            DataRow newRow = dataTable.NewRow();           

            newRow["f1"] = (SqlGeographyDTO)SqlGeography.Point(20, 10, 4326); 

            newRow["id"] = 1;

            dataTable.Rows.Add(newRow);

            newRow = dataTable.NewRow();

            // https://gis.stackexchange.com/questions/124457/creating-c-sqlgeography-point-with-z-value

            //newRow["f1"] = (SqlGeographyDTO)SqlGeography.STGeomFromText(new SqlChars("POINT ZM (1 1 5 60)"), 4326);

            //newRow["f1"] = (SqlGeographyDTO)SqlGeography.STGeomFromText(new SqlChars("TRIANGLE((0 0 0,0 1 0,1 1 0,0 0 0))"), 4326);

            newRow["f1"] = (SqlGeographyDTO)SqlGeography.STGeomFromText(new SqlChars("POINT(4 6)"), 4326);

            newRow["id"] = 2;

            dataTable.Rows.Add(newRow);

            return dataTable;

        }

        static void DumpDataTable(DataTable dataTable)

        {

            foreach (DataRow row in dataTable.Rows)

            {

                var ID = row["id"];

                var f1 = row["f1"];  

                Console.WriteLine("{0} |f1: {1}   ", ID, f1);                      

            }

        }

    }

    //@dbc Alternative 1 

    // https://stackoverflow.com/questions/45895039/datatable-with-sqlgeography-column-cant-be-serialized-to-xml-correctly-with-los

    public class SqlGeographyDTO

    {

        const int DefaultSRID = 4326; // TODO: check if this is the correct default.

        public int? STSrid { get; set; }

        public XElement Geography { get; set; }

        public static implicit operator SqlGeographyDTO(SqlGeography geography)

        {

            if (geography == null || geography.IsNull)

                return null;

            return new SqlGeographyDTO

            {

                STSrid = geography.STSrid.IsNull ? (int?)null : geography.STSrid.Value,

                Geography = geography.AsGml().ToXElement(),

            };

        }

        public static implicit operator SqlGeography(SqlGeographyDTO dto)

        {

            if (dto == null)

                return SqlGeography.Null;

            var sqlXml = dto.Geography.ToSqlXml();

            var geography = SqlGeography.GeomFromGml(sqlXml, dto.STSrid.GetValueOrDefault(DefaultSRID));

            return geography;

        }

        public override string ToString()

        {

            return Geography == null ? "" : Geography.ToString(SaveOptions.DisableFormatting);

        }

    }

    public static class XNodeExtensions

    {

        public static SqlXml ToSqlXml(this XNode node)

        {

            if (node == null)

                return SqlXml.Null;

            using (var reader = node.CreateReader())

            {

                return new SqlXml(reader);

            }

        }

    }

    public static class SqlXmlExtensions

    {

        public static XElement ToXElement(this SqlXml sql)

        {

            if (sql == null || sql.IsNull)

                return null;

            using (var reader = sql.CreateReader())

                return XElement.Load(reader);

        }

    }

​

    public class AssertionFailedException : System.Exception

    {

        public AssertionFailedException() : base() { }

​

        public AssertionFailedException(string s) : base(s) { }

    }

​

    public static class Assert

    {

        public static void IsTrue(bool value)

        {

            IsTrue(value, "failed");

        }

​

        public static void IsTrue(bool value, string message)

        {

            if (value == false)

                throw new AssertionFailedException(message ?? "failed");

        }

    }

​

    public static class XmlSerializationHelper

    {

        public static T LoadFromXml<T>(this string xmlString, XmlSerializer serial = null)

        {

            serial = serial ?? new XmlSerializer(typeof(T));

            T returnValue = default(T);

            using (StringReader reader = new StringReader(xmlString))

            {

                object result = serial.Deserialize(reader);

                if (result is T)

                {

                    returnValue = (T)result;

                }

            }

            return returnValue;

        }

​

        public static string GetXml<T>(this T obj, bool omitStandardNamespaces)

        {

            return obj.GetXml(null, omitStandardNamespaces);

        }

        public static string GetXml<T>(this T obj, XmlSerializer serializer = null, bool omitStandardNamespaces = false)

        {

            XmlSerializerNamespaces ns = null;

            if (omitStandardNamespaces)

            {

                ns = new XmlSerializerNamespaces();

                ns.Add("", ""); // Disable the xmlns:xsi and xmlns:xsd lines.

            }           

            using (var textWriter = new StringWriter())

            {

                var settings = new XmlWriterSettings() { Indent = true }; // For cosmetic purposes.

                using (var xmlWriter = XmlWriter.Create(textWriter, settings))

                    (serializer ?? new XmlSerializer(obj.GetType())).Serialize(xmlWriter, obj, ns);

                return textWriter.ToString();

            }

        }

    }

​