questions/45848550/newtonsoft-json-jsonserializationexception-error-getting-value-from-value-on

CSharp · Console

/Program.cs

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);
        }
    }