using System;

using System.IO;

using System.Data;

using Microsoft.SqlServer.Types;

using System.Data.SqlTypes;

using System.Xml;

​

//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

//************ changes******************

//created: Aug 28,2017

//Aug 29,2017 add methods: GetLat ,GetLong to SqlGeographyDTO2

//            STSrid not nullable

//**************************************************************************

public class Program

{

    public static void Main()

    {

        Console.WriteLine("@dbc Alternative2"); 

        var dt2=GetGeoTable2();

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

        var xml=SerializeDataTable(dt2);

        Console.WriteLine(xml);

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

        var dt3=DeSerializeDataTable(xml);

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

        DumpTable(dt3); 

    }

    public static void DumpTable(DataTable dt)

    {

        foreach (DataRow row in dt.Rows)

            {

                var ID = row["id"];

                var f1 = row["f1"];                 

                var geoDto = f1 as SqlGeographyDTO2;

                Console.WriteLine("id: {0} |f1: {1}  | Lat:{2} Long:{3}", ID, f1,geoDto.GetLat(),geoDto.GetLong());          

            }   

    }

    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 string GetXml(SqlGeography geo)

    {

        var g = (SqlGeographyDTO) geo;

        return g.Geography.Value;

    }

    static DataTable GetGeoTable2()

        {

            DataTable dataTable = new DataTable("table");

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

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

            SqlGeography g= SqlGeography.Point(20, 10, 4326); //lat long            

            DataRow newRow = dataTable.NewRow();           

            newRow["f1"] = (SqlGeographyDTO2)g; 

            newRow["id"] = 1;

            dataTable.Rows.Add(newRow);

            g=SqlGeography.Point(30, 15, 4326);

            newRow = dataTable.NewRow();

            newRow["f1"] = (SqlGeographyDTO2)g;;

            newRow["id"] = 2;

            dataTable.Rows.Add(newRow);

            DumpTable(dataTable);

    return dataTable;

}

​

//@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 SqlInt32 STSrid { get; set; }

​

    public SqlXml Geography { get; set; }

​

    public static implicit operator SqlGeographyDTO(SqlGeography geography)

    {

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

            return null;

        return new SqlGeographyDTO

        {

            STSrid = geography.STSrid,

            Geography = geography.AsGml(),

        };

    }

​

    public static implicit operator SqlGeography(SqlGeographyDTO dto)

    {

        if (dto == null)

            return SqlGeography.Null;

        var geography = SqlGeography.GeomFromGml(dto.Geography, dto.STSrid.IsNull ? DefaultSRID : dto.STSrid.Value);

        return geography;

    }

    public override string ToString()

    {

        return Geography.Value;

    }

}

​

​

}

​

//@dbc Alternative 2 

public class SqlGeographyDTO2

{

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

​

    public int? STSrid { get; set; }

​

    public string Geography { get; set; }

     public  double  GetLat()

        {

            var text = new SqlChars(Geography);

            var geo = SqlGeography.STPointFromText(text, (int)STSrid).Long.Value;

            return geo;

        }

      public double GetLong()

        {

            var text = new SqlChars(Geography);

            var geo = SqlGeography.STPointFromText(text, (int)STSrid).Lat.Value;

            return geo;

        }

​

    public static implicit operator SqlGeographyDTO2(SqlGeography geography)

    {

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

            return null;

        return new SqlGeographyDTO2

        {

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

            Geography = geography.ToString(),

        };

    }

​

    public static implicit operator SqlGeography(SqlGeographyDTO2 dto)

    {

        if (dto == null)

            return SqlGeography.Null;

        var geography = SqlGeography.STGeomFromText(new SqlChars(dto.Geography), dto.STSrid.GetValueOrDefault(DefaultSRID));        

        return geography;

    }

    public override string ToString()

    {

        return Geography;

    }

 }

​

​

​

​

​

​

​