看起来好像原始类型如SqlDouble
from System.Data.SqlTypes
无法通过 Json.NET 进行开箱即用的序列化,因为它们没有实现自己的TypeConverter
。来自docs https://www.newtonsoft.com/json/help/html/SerializationGuide.htm:
原始类型
.Net: TypeConverter
(可转换为字符串)
JSON:字符串
这将有必要实施一个custom JsonConverter https://www.newtonsoft.com/json/help/html/CustomJsonConverter.htm序列化这些类型。 Json.NET 有几个内置的转换器 https://www.newtonsoft.com/json/help/html/N_Newtonsoft_Json_Converters.htm例如KeyValuePairConverter https://www.newtonsoft.com/json/help/html/T_Newtonsoft_Json_Converters_KeyValuePairConverter.htm对于内置 .Net 类型,因此这并不罕见。
事实是SqlBoolean
, SqlBinary
, SqlDouble
等等不共享公共基类或接口,除了INullable https://msdn.microsoft.com/en-us/library/system.data.sqltypes.inullable(v=vs.110).aspx需要一些看起来重复的代码:
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);
}
}
Working .Net小提琴 https://dotnetfiddle.net/Qm5PgQ从你的分叉own https://dotnetfiddle.net/8MMQOW.
如果您需要反序列化由此创建的 JSON,则会遇到两个额外的问题。首先,一些属性SqlGeography https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlgeography.aspx例如Lat https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlgeography.lat.aspx and Long https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlgeography.long.aspx是仅获取的。您将需要创建一个自定义JsonConverter
完全反序列化此类型。
其次,Json.NET 不具备将行值的复杂对象的 JSON 反序列化为非类型化的能力DataTable
。因此,如果您需要反序列化包含复杂对象的 JSON(例如序列化的SqlGeography
),您有以下选择:
创建并反序列化为类型化DataTable
.
Use DataTableConverter
直接填充预先存在的DataTable
具有预先分配的列,如图所示here https://stackoverflow.com/a/41753376/3744182.
-
反序列化为列表DTOs https://en.wikipedia.org/wiki/Data_transfer_object例如以下内容:
public class TableRowDTO
{
[JsonConverter(typeof(SqlGeographyConverter))]
public SqlGeography f1 { get; set; }
public int id { get; set; }
}
Where SqlGeographyConverter
根据需要,是一个自定义JsonConverter
for SqlGeography
.
然后执行以下操作:
var settings = new JsonSerializerSettings().AddSqlConverters();
var list = JsonConvert.DeserializeObject<List<TableRowDTO>>(jsonString, settings);