Newtonsoft.Json.JsonSerializationException（错误从'System.Data.SqlTypes.SqlDouble上的'Value'获取值）序列化SqlGeography

1 回答

• 12

  看起来像 System.Data.SqlTypes 的 SqlDouble 这样的原始类型无法通过Json.NET进行开箱即用，因为它们没有实现自己的 TypeConverter . 来自docs：

  原语类型.Net：TypeConverter（可转换为String）JSON：String

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

  SqlBoolean ， SqlBinary ， SqlDouble 等不共享公共基类或除INullable之外的接口这一事实需要一些重复的代码：

  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分叉 .

  如果需要反序列化由此创建的JSON，则还有两个问题 . 首先，SqlGeography的某些属性（例如Lat和Long）是get-only . 您需要创建自定义 JsonConverter 才能完全反序列化此类型 .

  其次，Json.NET无法将具有行值的复杂对象的JSON反序列化为无类型的 DataTable . 因此，如果需要反序列化包含复杂对象的JSON（例如序列化的 SqlGeography ），则可以使用以下选项：

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

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

  • 反序列化为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);
  

  回复于 2024-04-29T14:56:15+08:00