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

本文介绍了Newtonsoft.Json.JsonSerializationException(从'System.Data.SqlTypes.SqlDouble的'Value'获取值时出错)序列化SqlGeography的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I tried to serialize a DataTable object to Json using Newtonsoft.Json version 'Newtonsoft.Json.10.0.3' in a database SQL server 2012.

该表的列类型为地理"，其中包含类型为 SqlGeography .

The table has a column with type 'geography', which contains instances of type SqlGeography.

用于生成json的代码:

The code used to generate 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'

I already installed the assembly 'Microsoft.SqlServer.Types' from feature Pack of sql 2012

我已使用带有SqlGeography列的数据表创建了完整的C#程序(独立于sql server安装)，以显示问题尝试

I have created a complete C# program (independent of sql server installation) using a datatable with SqlGeography column to show the problem Try it

我得到了错误:

System.Data.SqlTypes.SqlNullValueException:数据为Null.不能在Null值上调用此方法或属性.在System.Data.SqlTypes.SqlDouble.get_Value()在GetValue(Object)在Newtonsoft.Json.Serialization.DynamicValueProvider.GetValue(对象目标)

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 ，但这无济于事.

任何帮助解决该问题的方法.

Any help to resolve the problem.

基于@dbc注释，我提供了用于生成json的完整源代码.

Based on @dbc comments, I provided the complete source code used to generate json.

完整的错误消息是:

-内部异常堆栈跟踪的结尾-在Newtonsoft.Json.Serialization.DynamicValueProvider.GetValue(对象目标)

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

在Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject(JsonWriter编写器，对象值，JsonObjectContract合同，JsonProperty成员，JsonContainerContract collectionContract，JsonProperty containerProperty)在Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter作家，对象值，JsonContract valueContract，JsonProperty成员，JsonContainerContract containerContract，JsonProperty containerProperty)在Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject(JsonWriter编写器，对象值，JsonObjectContract合同，JsonProperty成员，JsonContainerContract collectionContract，JsonProperty containerProperty)在Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter作家，对象值，JsonContract valueContract，JsonProperty成员，JsonContainerContract containerContract，JsonProperty containerProperty)在Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter，Object value，Type objectType)中在Newtonsoft.Json.Serialization.JsonSerializerProxy.SerializeInternal(JsonWriter jsonWriter，对象值，类型rootType)在Newtonsoft.Json.Converters.DataTableConverter.WriteJson(JsonWriter作家，对象值，JsonSerializer序列化器)在Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeConver表(JsonWriter编写器，JsonConverter转换器，对象值，JsonContract合同，JsonContainerContract collectionContract，JsonProperty containerProperty)

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)

在Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter作家，对象值，JsonContract valueContract，JsonProperty成员，JsonContainerContract containerContract，JsonProperty containerProperty)在Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter，Object value，Type objectType)中在Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter，Object value，Type objectType)在Newtonsoft.Json.JsonConvert.SerializeObjectInternal(对象值，类型，JsonSerializer jsonSerializer)在Newtonsoft.Json.JsonConvert.SerializeObject(对象值，JsonSerializerSettings设置)在F:\ JsonTester.cs:第104行的JsonTester.SerializeToJson()中

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

Edit2

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

I enable tracing as described by @dbc, and get the following log:

2017-08-24T19:45:31.972信息开始序列化Microsoft.SqlServer.Types.SqlGeography.路径"[0] .f1".

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

2017-08-24T19:45:32.003信息开始序列化System.Data.SqlTypes.SqlInt32.Path'[0] .f1.STSrid'.

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

2017-08-24T19:45:32.003信息完成序列化System.Data.SqlTypes.SqlInt32.路径"[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信息开始序列化System.Data.SqlTypes.SqlDouble.路径"[0] .f1.Lat".

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

2017-08-24T19:45:32.003信息完成序列化System.Data.SqlTypes.SqlDouble.路径"[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信息开始序列化System.Data.SqlTypes.SqlDouble.路径"[0] .f1.Long".

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

2017-08-24T19:45:32.003信息完成序列化System.Data.SqlTypes.SqlDouble.路径"[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信息开始序列化System.Data.SqlTypes.SqlDouble.路径"[0] .f1.Z".

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

2017-08-24T19:45:32.003错误序列化System.Data.SqlTypes.SqlDouble.Error出错，无法从'System.Data.SqlTypes.SqlDouble'的'Value'获取值.

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错误序列化System.Data.DataTable时出错.从"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'.

推荐答案

似乎无法序列化来自诸如 System.Data.SqlTypes 的 SqlDouble 之类的原始类型Json.NET开箱即用，因为它们没有实现自己的 TypeConverter .从文档:

It looks as though the primitive types such as SqlDouble from System.Data.SqlTypes cannot be serialized out-of-the-box by Json.NET because they do not implement their own TypeConverter. From the docs:

.Net: TypeConverter (可转换为String)
JSON:字符串

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

这将有必要实现自定义 JsonConverter 序列化这些类型.Json.NET具有几个内置的转换器，例如不寻常.

This it will be necessary to implement a custom JsonConverter to serialize these types. Json.NET has several built-in converters such as KeyValuePairConverter for built-in .Net types so this is not unusual.

SqlBoolean ， SqlBinary ， SqlDouble 等事实除 INullable 需要一些重复的代码:

The fact that SqlBoolean, SqlBinary, SqlDouble and so on do not share a common base class or interface other than INullable requires some duplicated-looking code:

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小提琴从您的拥有.

如果需要反序列化由此创建的JSON，则还有两个其他问题.首先， SqlGeography ，例如 Lat 和 Long 是只读的.您将需要创建一个自定义 JsonConverter 来完全反序列化此类型.

If you need to deserialize the JSON thereby created, you have two additional problems. Firstly, Some of the properties of SqlGeography such as Lat and Long are get-only. You will need to create a custom JsonConverter to fully deserialize this type.

第二，Json.NET无法将具有复杂对象的JSON反序列化以将行值存储到未类型化的 DataTable .因此，如果您需要反序列化包含复杂对象的JSON(例如序列化的 SqlGeography )，则可以使用以下选项:

Secondly, Json.NET does not have the ability to deserialize JSON with complex objects for row values to an untyped DataTable. Thus, if you need to deserialize JSON containing a complex object (such as your serialized SqlGeography), you have the following options:

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

直接使用 DataTableConverter 用预先分配的列填充预先存在的 DataTable ，如.

Use DataTableConverter directly to populate a pre-existing DataTable with pre-allocated columns, as shown here.

反序列化为 DTO 的列表，例如以下:

Deserialize to a list of DTOs such as the following:

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

根据需要，其中 SqlGeographyConverter 是用于 SqlGeography 的自定义 JsonConverter .

Where SqlGeographyConverter is, as required, a custom JsonConverter for SqlGeography.

然后执行:

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

                        这篇关于Newtonsoft.Json.JsonSerializationException(从'System.Data.SqlTypes.SqlDouble的'Value'获取值时出错)序列化SqlGeography的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！