使用JsonConvert将JSON反序列化到VB.NET中的数据表中 [英] Deserialize JSON into data table in VB.NET using JsonConvert
问题描述
我正在使用VB.NET中的以下语句将JSON反序列化为数据表.
Dim _dt As DataTable = JsonConvert.DeserializeObject(Of DataTable)(myRecords)
myRecords是一个JSON字符串.
它工作正常，但是myRecords具有一些数字属性，例如{"PhoneNo":"123456789"，"ID":"46541"}，并且在反序列化之后，这些属性被转换为数据类型为字符串的列.
如何将它们反序列化为数字? myRecords正在动态填充，因此我无法进行硬编码.
Json.NET为"PhoneNo" : "123456789"创建字符串类型的列的原因是"123456789"实际上是根据 JSON标准.数值文字看起来像这样，在值123456789周围没有双引号.您确定这些属性总是 是数字字符串吗?并非所有电话号码都是数字的，例如，因此对它们进行硬编码似乎是不明智的.
话虽如此，如果您确定这些属性将始终是数字字符串，并希望Json.NET为它们创建数字DataTable列，则需要提前告知它这些列的类型.一种选择是创建 JsonConvert.PopulateObject() 在预分配的DataTable上不起作用您需要直接调用 DataTableConverter.ReadJson() .这可以通过以下扩展方法来完成:
Public Module JsonExtensions
Public Sub PopulateDataTable(json As String, target As DataTable, Optional settings As JsonSerializerSettings = Nothing)
Using reader = New JsonTextReader(New StringReader(json))
Do
If reader.TokenType = JsonToken.StartArray Then
' Populate the table
Dim converter = New DataTableConverter()
converter.ReadJson(reader, target.GetType(), target, JsonSerializer.CreateDefault(settings))
End If
Loop While reader.Read()
End Using
End Sub
End Module
然后按如下所示使用它:
Dim _dt = New DataTable()
_dt.Columns.Add("PhoneNo", GetType(Long))
_dt.Columns.Add("ID", GetType(Long))
JsonExtensions.PopulateDataTable(myRecords, _dt)
示例小提琴.
您还写道，我不能硬编码.如果您确实事先不知道哪些带字符串值的列实际上应反序列化为数字类型，那么您可以做的是通过将JSON加载到 Jtoken 中进行处理所有属性值均按名称，并且对于每个组，请检查组中的 all 值是否为可转换为数字的字符串.如果所有都是可转换的，则可以进行转换.但是，如果只有某些是可转换的，则不应进行转换，因为这会破坏Json.NET的类型推断算法.可以使用以下扩展方法完成此操作:
Public Module JsonExtensions
Private ReadOnly NumberTypes = New JTokenType() {JTokenType.[Integer], JTokenType.Float, JTokenType.[String], JTokenType.Comment, JTokenType.Raw, JTokenType.[Boolean]}
Private Function ValidateToken(o As JToken, validTypes As JTokenType(), nullable As Boolean) As Boolean
Return (Array.IndexOf(validTypes, o.Type) <> -1) OrElse (nullable AndAlso (o.Type = JTokenType.Null OrElse o.Type = JTokenType.Undefined))
End Function
<System.Runtime.CompilerServices.Extension> _
Public Function CanConvertToNullableLong(token As JToken) As Boolean
' Reverse engineered from
' public static explicit operator long?(JToken value)
' https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/Linq/JToken.cs#L1045
If token Is Nothing OrElse token.Type = JTokenType.Null OrElse token.Type = JTokenType.Boolean Then
Return True
End If
If Not ValidateToken(token, NumberTypes, True) Then
Return False
End If
Dim jValue = TryCast(token, JValue)
If jValue Is Nothing Then
Return False
End If
If TypeOf jValue.Value Is BigInteger Then
Dim i = CType(jValue.Value, BigInteger)
Return i <= Long.MaxValue AndAlso i >= Long.MinValue
End If
Dim s = CType(jValue, String)
Dim v As Long
Return Long.TryParse(s, NumberStyles.Number, NumberFormatInfo.InvariantInfo, v)
End Function
Public Sub TryConvertColumnsToNullableLong(root As JToken)
If TypeOf root Is JContainer Then
' If ALL columns values of a given name can be converted from string to long, then do so.
' Do not convert columns where some but not all are convertable.
For Each group In DirectCast(root, JContainer) _
.Descendants() _
.OfType(Of JProperty)() _
.GroupBy(Function(p) p.Name) _
.Where(Function(g) g.All(Function(p) (p.Value.Type = JTokenType.String Or p.Value.Type = JTokenType.Null) AndAlso p.Value.CanConvertToNullableLong()))
For Each p In group
p.Value = CType(p.Value, System.Nullable(Of Long))
Next
Next
End If
End Sub
End Module
然后进行预处理和反序列化，如下所示:
Dim token = JToken.Parse(myRecords)
JsonExtensions.TryConvertColumnsToNullableLong(token)
Dim _dt = token.ToObject(Of DataTable)()
示例小提琴#2 .
但是我不确定我会推荐这个. JSON值是字符串这一事实表明，在生成JSON的数据库中，这些值可以是任意字符串.如果是这样，当将非数字值开始输入数据库时，侵入到long的转换中可能会导致问题.
I am deserializing JSON into a data table using the below statement in VB.NET.
Dim _dt As DataTable = JsonConvert.DeserializeObject(Of DataTable)(myRecords)
myRecords is a JSON string.
It is working fine, but myRecords has some number properties like {"PhoneNo":"123456789", "ID":"46541"} and after deserialization those properties are converted to a column with data type as string.
How can I deserialize them into a number? myRecords is getting dynamically populated, so I can't hard code.
The reason Json.NET creates a string-typed column for "PhoneNo" : "123456789" is that "123456789" is, in fact, a string literal according to the JSON standard. A numeric literal would look like this, without double-quotes around the value: 123456789. Are you certain that these properties will always be numeric strings? Not all phone numbers are numeric, e.g., so it seems unwise to hardcode them as such.
That being said, if you are sure that these properties will always be numeric strings and want Json.NET to create numeric DataTable columns for them, you need to tell it in advance the desired type for those columns. One option would be to create a typed DataTable from an appropriate schema. In that case, JsonConvert.DeserializeObject(Of TTypedDataTable)(myRecords) will create a DataTable subclass with the required column types.
Another option would be to create the DataTable manually with an appropriate set of columns, then populate the table from your JSON. Unfortunately JsonConvert.PopulateObject() will not work on a preallocated DataTable so you need to call DataTableConverter.ReadJson() directly. This could be done with the following extension method:
Public Module JsonExtensions
Public Sub PopulateDataTable(json As String, target As DataTable, Optional settings As JsonSerializerSettings = Nothing)
Using reader = New JsonTextReader(New StringReader(json))
Do
If reader.TokenType = JsonToken.StartArray Then
' Populate the table
Dim converter = New DataTableConverter()
converter.ReadJson(reader, target.GetType(), target, JsonSerializer.CreateDefault(settings))
End If
Loop While reader.Read()
End Using
End Sub
End Module
Then use it as follows:
Dim _dt = New DataTable()
_dt.Columns.Add("PhoneNo", GetType(Long))
_dt.Columns.Add("ID", GetType(Long))
JsonExtensions.PopulateDataTable(myRecords, _dt)
Example fiddle.
You also wrote, I can't hard code. If you really don't know in advance which columns with string values should actually be deserialized as numeric types, what you can do is to pre-process the JSON by loading it into a Jtoken, grouping all properties values by name, and for each group, checking that all the values in the group are strings that are convertible to numbers. If all are convertible, you can make the conversion. But if only some are convertible you should not make the conversion as this will break Json.NET's type inference algorithm. It can be done using the following extension methods:
Public Module JsonExtensions
Private ReadOnly NumberTypes = New JTokenType() {JTokenType.[Integer], JTokenType.Float, JTokenType.[String], JTokenType.Comment, JTokenType.Raw, JTokenType.[Boolean]}
Private Function ValidateToken(o As JToken, validTypes As JTokenType(), nullable As Boolean) As Boolean
Return (Array.IndexOf(validTypes, o.Type) <> -1) OrElse (nullable AndAlso (o.Type = JTokenType.Null OrElse o.Type = JTokenType.Undefined))
End Function
<System.Runtime.CompilerServices.Extension> _
Public Function CanConvertToNullableLong(token As JToken) As Boolean
' Reverse engineered from
' public static explicit operator long?(JToken value)
' https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/Linq/JToken.cs#L1045
If token Is Nothing OrElse token.Type = JTokenType.Null OrElse token.Type = JTokenType.Boolean Then
Return True
End If
If Not ValidateToken(token, NumberTypes, True) Then
Return False
End If
Dim jValue = TryCast(token, JValue)
If jValue Is Nothing Then
Return False
End If
If TypeOf jValue.Value Is BigInteger Then
Dim i = CType(jValue.Value, BigInteger)
Return i <= Long.MaxValue AndAlso i >= Long.MinValue
End If
Dim s = CType(jValue, String)
Dim v As Long
Return Long.TryParse(s, NumberStyles.Number, NumberFormatInfo.InvariantInfo, v)
End Function
Public Sub TryConvertColumnsToNullableLong(root As JToken)
If TypeOf root Is JContainer Then
' If ALL columns values of a given name can be converted from string to long, then do so.
' Do not convert columns where some but not all are convertable.
For Each group In DirectCast(root, JContainer) _
.Descendants() _
.OfType(Of JProperty)() _
.GroupBy(Function(p) p.Name) _
.Where(Function(g) g.All(Function(p) (p.Value.Type = JTokenType.String Or p.Value.Type = JTokenType.Null) AndAlso p.Value.CanConvertToNullableLong()))
For Each p In group
p.Value = CType(p.Value, System.Nullable(Of Long))
Next
Next
End If
End Sub
End Module
Then preprocess and deserialize as follows:
Dim token = JToken.Parse(myRecords)
JsonExtensions.TryConvertColumnsToNullableLong(token)
Dim _dt = token.ToObject(Of DataTable)()
Example fiddle #2.
I'm not sure I would recommend this however. The fact that the JSON values are strings suggest that, in the database from which the JSON was generated, these values could be arbitrary strings. If so, hacking in a conversion to long could cause problems later when non-numeric values start getting entered into the database.
这篇关于使用JsonConvert将JSON反序列化到VB.NET中的数据表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
