using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using Microsoft.SqlServer.Types;

using Newtonsoft.Json;

using Newtonsoft.Json.Serialization;

using System.Xml;

using System.Data.SqlTypes;

​

​

// https://stackoverflow.com/questions/45848550/newtonsoft-json-jsonserializationexception-error-getting-value-from-value-on

​

        public class TableRowDTO

        {

            public SqlGeography f1 { get; set; }

            public int id { get; set; }

        }

​

​

    public class Program

    {

        public static void Main()

        {

            Console.WriteLine("Hello SqlGeography");

            var dt= GetGeoTable();

            var jsonString = SerializeToJson(dt);

            var settings = new JsonSerializerSettings().AddSqlConverters();

            var list = JsonConvert.DeserializeObject<List<TableRowDTO>>(jsonString, settings);

            var json2 = JsonConvert.SerializeObject(list, Newtonsoft.Json.Formatting.Indented, settings);

            Console.WriteLine(json2);

            Console.WriteLine("Deserialized {0}", list);

            foreach (var row in list)

            {

                string ID = row.id.ToString();

                string f1 = row.f1.ToString();

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

            }

        }

    //create datatable with SqlGeography datatype

        static DataTable GetGeoTable()

        {

            DataTable dataTable = new DataTable("table");

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

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

​

            DataRow newRow = dataTable.NewRow();           

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

            newRow["id"] = 1;

            dataTable.Rows.Add(newRow);

​

            newRow = dataTable.NewRow();

            newRow["f1"] = SqlGeography.Point(30, 15, 4326);

            newRow["id"] = 2;

            dataTable.Rows.Add(newRow);

​

            foreach (DataRow row in dataTable.Rows)

            {

                string ID = row["id"].ToString();

                string f1 = row["f1"].ToString();

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

            }            

            return dataTable;

        }

    //Serialize datatable to Json

        static string SerializeToJson(DataTable dt)

        {   

​

            ITraceWriter traceWriter = new MemoryTraceWriter();           

            try

            {

                var options = new JsonSerializerSettings

                {   

                    Formatting = Newtonsoft.Json.Formatting.Indented,

                      TraceWriter = traceWriter,                   

                }.AddSqlConverters();

                var json = JsonConvert.SerializeObject(dt, options);

                Console.WriteLine(json);

                return json;

            }

            catch (Exception ex)

            {

                Console.WriteLine(traceWriter);

                Console.WriteLine(ex);

            }           

            return "";           

        }

​

    }

​

    public static class SqlPrimitiveConverters

    {

        public static JsonSerializerSettings AddSqlConverters(this JsonSerializerSettings settings)

        {

            foreach (var converter in converters)

                settings.Converters.Add(converter);

            return settings;

        }

​

        static readonly JsonConverter[] converters = new JsonConverter[]

        {

            new SqlBinaryConverter(),

            new SqlBooleanConverter(),

            new SqlByteConverter(),

            new SqlDateTimeConverter(),

            new SqlDecimalConverter(),

            new SqlDoubleConverter(),

            new SqlGuidConverter(),

            new SqlInt16Converter(),

            new SqlInt32Converter(),

            new SqlInt64Converter(),

            new SqlMoneyConverter(),

            new SqlSingleConverter(),

            new SqlStringConverter(),

            // TODO: converters for primitives from System.Data.SqlTypes that are classes not structs:

            // SqlBytes, SqlChars, SqlXml

            // Maybe SqlFileStream

        };

    }

​

    abstract class SqlPrimitiveConverterBase<T> : JsonConverter where T : struct, INullable, IComparable

    {

        protected abstract object GetValue(T sqlValue);

​

        public override bool CanConvert(Type objectType)

        {

            return typeof(T) == objectType;

        }

​

        public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)

        {

            T sqlValue = (T)value;

            if (sqlValue.IsNull)

                writer.WriteNull();

            else

            {

                serializer.Serialize(writer, GetValue(sqlValue));

            }

        }

    }

​

    class SqlBinaryConverter : SqlPrimitiveConverterBase<SqlBinary>

    {

        protected override object GetValue(SqlBinary sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlBinary.Null;

            return (SqlBinary)serializer.Deserialize<byte[]>(reader);

        }

    }

​

    class SqlBooleanConverter : SqlPrimitiveConverterBase<SqlBoolean>

    {

        protected override object GetValue(SqlBoolean sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlBoolean.Null;

            return (SqlBoolean)serializer.Deserialize<bool>(reader);

        }

    }

​

    class SqlByteConverter : SqlPrimitiveConverterBase<SqlByte>

    {

        protected override object GetValue(SqlByte sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlByte.Null;

            return (SqlByte)serializer.Deserialize<byte>(reader);

        }

    }

​

    class SqlDateTimeConverter : SqlPrimitiveConverterBase<SqlDateTime>

    {

        protected override object GetValue(SqlDateTime sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlDateTime.Null;

            return (SqlDateTime)serializer.Deserialize<DateTime>(reader);

        }

    }

​

    class SqlDecimalConverter : SqlPrimitiveConverterBase<SqlDecimal>

    {

        protected override object GetValue(SqlDecimal sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlDecimal.Null;

            return (SqlDecimal)serializer.Deserialize<decimal>(reader);

        }

    }

​

    class SqlDoubleConverter : SqlPrimitiveConverterBase<SqlDouble>

    {

        protected override object GetValue(SqlDouble sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlDouble.Null;

            return (SqlDouble)serializer.Deserialize<double>(reader);

        }

    }

​

    class SqlGuidConverter : SqlPrimitiveConverterBase<SqlGuid>

    {

        protected override object GetValue(SqlGuid sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlGuid.Null;

            return (SqlGuid)serializer.Deserialize<Guid>(reader);

        }

    }

​

    class SqlInt16Converter : SqlPrimitiveConverterBase<SqlInt16>

    {

        protected override object GetValue(SqlInt16 sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlInt16.Null;

            return (SqlInt16)serializer.Deserialize<short>(reader);

        }

    }

​

    class SqlInt32Converter : SqlPrimitiveConverterBase<SqlInt32>

    {

        protected override object GetValue(SqlInt32 sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlInt32.Null;

            return (SqlInt32)serializer.Deserialize<int>(reader);

        }

    }

​

    class SqlInt64Converter : SqlPrimitiveConverterBase<SqlInt64>

    {

        protected override object GetValue(SqlInt64 sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlInt64.Null;

            return (SqlInt64)serializer.Deserialize<long>(reader);

        }

    }

​

    class SqlMoneyConverter : SqlPrimitiveConverterBase<SqlMoney>

    {

        protected override object GetValue(SqlMoney sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlMoney.Null;

            return (SqlMoney)serializer.Deserialize<decimal>(reader);

        }

    }

​

    class SqlSingleConverter : SqlPrimitiveConverterBase<SqlSingle>

    {

        protected override object GetValue(SqlSingle sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlSingle.Null;

            return (SqlSingle)serializer.Deserialize<float>(reader);

        }

    }

​

    class SqlStringConverter : SqlPrimitiveConverterBase<SqlString>

    {

        protected override object GetValue(SqlString sqlValue) { return sqlValue.Value; }

​

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)

        {

            if (reader.TokenType == JsonToken.Null)

                return SqlString.Null;

            return (SqlString)serializer.Deserialize<string>(reader);

        }

    }

​