c# - Newtonsoft.Json.JsonSerializationException(从“System.Data.SqlTypes.SqlDouble”上的 'Value' 获取值时出错)序列化 SqlGeography

Question

我尝试在数据库 SQL Server 2012 中使用 Newtonsoft.Json 版本“Newtonsoft.Json.10.0.3”将 DataTable 对象序列化为 Json。

该表有一个类型为“geography”的列，其中包含类型为 SqlGeography 的实例.

用于生成json的代码:

    public string SerializeToJson()
    {

     var connstring1 ="Data Source=server1;Initial Catalog=database1;user=xxx;password=yyy";
        var sql = "SELECT  * FROM table_1 "; //table_1 has a column of type geography
        using (var c1 = new SqlConnection(connstring1))
        {
            c1.Open();
            var da = new SqlDataAdapter()
            {
                SelectCommand = new SqlCommand(sql, c1)
            };

            DataSet ds1 = new DataSet("table");
            da.Fill(ds1, "table");
            var dt = ds1.Tables[0];

            //serialize to Json

            try
            {
                var options = new JsonSerializerSettings
                {
                    Formatting = Formatting.None
                };
                //this line fire exception for geography type
                var json = JsonConvert.SerializeObject(dt, options);
                return json;
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex);
            }                
        }
    }

我已经从 sql 2012 的功能包中安装了程序集“Microsoft.SqlServer.Types”

我使用带有 SqlGeography 列的数据表创建了一个完整的 C# 程序(独立于 sql server 安装)来显示问题 Try it

我得到错误:

Newtonsoft.Json.JsonSerializationException: Error getting value from 'Value' on 'System.Data.SqlTypes.SqlDouble'. --->

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. at System.Data.SqlTypes.SqlDouble.get_Value() at GetValue(Object ) at Newtonsoft.Json.Serialization.DynamicValueProvider.GetValue(Object target)

我到达了 https://github.com/JamesNK/Newtonsoft.Json/issues/993 , 但它无济于事。

解决问题的任何帮助。

编辑:

根据@dbc的评论，我提供了生成json的完整源码。

完整的错误信息是:

Newtonsoft.Json.JsonSerializationException: Error getting value from 'Value' on 'System.Data.SqlTypes.SqlDouble'. ---> >System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. at System.Data.SqlTypes.SqlDouble.get_Value() at GetValue(Object ) at Newtonsoft.Json.Serialization.DynamicValueProvider.GetValue(Object target)

--- End of inner exception stack trace --- at Newtonsoft.Json.Serialization.DynamicValueProvider.GetValue(Object target)

at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.CalculatePropertyValues(JsonWriter writer, Object value, JsonContainerContract contract, JsonProperty member, JsonProperty property, JsonContract& memberContract, Object& memberValue) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject (JsonWriter writer, Object value, JsonObjectContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue( JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member , JsonContainerContract containerContract, JsonProperty containerProperty) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject (JsonWriter writer, Object value, JsonObjectContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue( JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member , JsonContainerContract containerContract, JsonProperty containerProperty) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType) at Newtonsoft.Json.Serialization.JsonSerializerProxy.SerializeInternal(JsonWriter jsonWriter, Object value, Type rootType) at Newtonsoft.Json.Converters.DataTableConverter.WriteJson(JsonWriter writer, Object value, JsonSerializer serializer) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeConver table(JsonWriter writer, JsonConverter converter, Object value, JsonContract contract, JsonContainerContract collectionContract, JsonProperty containerProperty)

at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue( JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member , JsonContainerContract containerContract, JsonProperty containerProperty) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType) at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType) at Newtonsoft.Json.JsonConvert.SerializeObjectInternal(Object value, Type type, JsonSerializer jsonSerializer) at Newtonsoft.Json.JsonConvert.SerializeObject(Object value, JsonSerializerSettings settings) at JsonTester.SerializeToJson() in F:\JsonTester.cs:line 104

编辑2:

我按照@dbc 的描述启用跟踪，并获得以下日志:

2017-08-24T19:45:31.941 Info Started serializing System.Data.DataTable with converter Newtonsoft.Json.Converters.DataTableConverter. Path ''.

2017-08-24T19:45:31.972 Info Started serializing Microsoft.SqlServer.Types.SqlGeography. Path '[0].f1'.

2017-08-24T19:45:32.003 Info Started serializing System.Data.SqlTypes.SqlInt32.Path '[0].f1.STSrid'.

2017-08-24T19:45:32.003 Info Finished serializing System.Data.SqlTypes.SqlInt32. Path '[0].f1.STSrid'.

2017-08-24T19:45:32.003 Info Started serializing System.Data.SqlTypes.SqlDouble. Path '[0].f1.Lat'.

2017-08-24T19:45:32.003 Info Finished serializing System.Data.SqlTypes.SqlDouble. Path '[0].f1.Lat'.

2017-08-24T19:45:32.003 Info Started serializing System.Data.SqlTypes.SqlDouble. Path '[0].f1.Long'.

2017-08-24T19:45:32.003 Info Finished serializing System.Data.SqlTypes.SqlDouble. Path '[0].f1.Long'.

2017-08-24T19:45:32.003 Info Started serializing System.Data.SqlTypes.SqlDouble. Path '[0].f1.Z'.

2017-08-24T19:45:32.003 Error Error serializing System.Data.SqlTypes.SqlDouble.Error getting value from 'Value' on 'System.Data.SqlTypes.SqlDouble'.

2017-08-24T19:45:32.003 Error Error serializing System.Data.DataTable. Error getting value from 'Value' on 'System.Data.SqlTypes.SqlDouble'.

Answer 1

看起来像 System.Data.SqlTypes 中的 SqlDouble 之类的原始类型不能由 Json.NET 开箱即用地序列化，因为它们确实没有实现自己的 TypeConverter。来自docs :

Primitive Types

.Net: TypeConverter (convertible to String)
JSON: String

这将有必要实现 custom JsonConverter序列化这些类型。 Json.NET 有几个内置的 converters例如KeyValuePairConverter对于内置的 .Net 类型，所以这并不罕见。

SqlBoolean、SqlBinary、SqlDouble 等除INullable 外不共享公共(public)基类或接口(interface)的事实需要一些看起来重复的代码:

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

工作 .Net fiddle从你的 own fork 出来的.

如果您需要反序列化由此创建的 JSON，则会遇到另外两个问题。首先，SqlGeography的一些属性例如Lat和 Long是只读的。您将需要创建自定义 JsonConverter 以完全反序列化此类型。

其次，Json.NET 无法将包含行值的复杂对象的 JSON 反序列化为未类型化的 DataTable。因此，如果您需要反序列化包含复杂对象的 JSON(例如序列化的 SqlGeography)，您有以下选择:

1. 创建并反序列化为类型化的 DataTable。

2. 直接使用 DataTableConverter 来使用预先分配的列填充预先存在的 DataTable，如图所示 here .

3. 反序列化为 DTOs 的列表例如:

   public class TableRowDTO
   {
       [JsonConverter(typeof(SqlGeographyConverter))]
       public SqlGeography f1 { get; set; }
       public int id { get; set; }
   }
   

   根据需要，SqlGeographyConverter 是 SqlGeography 的自定义 JsonConverter。

   然后做:

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