My application handles a lot of HTTP requests that return JSON data. I use the JSON.NET library to manipulate them. Here is a simulated and fairly brief example of the information (there are redundancies in the original data, which I can not post because they are sensitive information):
[{
"id": 1,
"conta_movimento_id": 726,
"conta_movimento": {
"id": 726,
"nome": "FULANO DE TAL - CONTA 2"
},
"especie_id": 1,
"especie": {
"id": 1,
"nome": "PAGAMENTO"
},
"favorecido_id": 34,
"favorecido": {
"id": 34,
"nome": "FULANO DE TAL",
"contas": [{
"id": 724,
"nome": "FULANO DE TAL - CONTA 1"
}, {
"id": 726,
"nome": "FULANO DE TAL - CONTA 2"
}]
},
"valor": 1564.23
}, {
"id": 2,
"conta_movimento_id": 725,
"conta_movimento": {
"id": 725,
"nome": "SICRANO DE TAL - CONTA 1"
},
"especie_id": 1,
"especie": {
"id": 1,
"nome": "PAGAMENTO"
},
"favorecido_id": 35,
"favorecido": {
"id": 35,
"nome": "SICRANO DE TAL",
"contas": [{
"id": 725,
"nome": "FULANO DE TAL - CONTA 1"
}]
},
"valor": 2323.79
}]
I need this data to be displayed in Winforms controls that use Databinding (mostly ComboBox and DataGridView ). I do not want to edit data at this time, I just needed to display them in a user-friendly way.
I wrote an impromptu routine that converts IEnumerable(Of JToken) to DataTable :
Module JsonAsDataTable
<Runtime.CompilerServices.Extension> Public Function ToDataTable(ByVal jtokens As IEnumerable(Of JToken), Optional ByVal trim_object_columns As Boolean = False, Optional ByVal castToCLRtypes As Boolean = False, Optional ByVal orderBy As String = Nothing) As DataTable
Dim dt As New DataTable, sdt As DataTable, dr, sdr As DataRow, dateproperty As Date
If jtokens IsNot Nothing Then
For Each jt In jtokens
dr = dt.NewRow
For Each jp In jt.Children(Of JProperty)
sdt = Nothing
If Not dt.Columns.Contains(jp.Name) Then dt.Columns.Add(jp.Name, IIf(castToCLRtypes, GetType(Object), GetType(JToken)))
If jp.Value.Type = JTokenType.Object AndAlso jp.Value.Children.Count = 1 AndAlso jp.Value.First.Type = JTokenType.Property AndAlso DirectCast(jp.Value.First, JProperty).Name = "date" Then
If Date.TryParse(DirectCast(jp.Value.First, JProperty).Value, dateproperty) Then
dr.SetField(jp.Name, New JValue(dateproperty))
Else
dr.SetField(jp.Name, JValue.CreateNull)
End If
Else
dr.SetField(jp.Name, jp.Value)
If jp.Value.Type = JTokenType.Object Then
sdt = AsDataTable({jp.Value}, trim_object_columns)
ElseIf jp.Value.Type = JTokenType.Array Then
sdt = AsDataTable(jp.Value, trim_object_columns)
End If
If sdt IsNot Nothing Then
sdr = sdt.Select.FirstOrDefault
For Each sdc As DataColumn In sdt.Columns
If Not dt.Columns.Contains(jp.Name & "." & sdc.ColumnName) Then dt.Columns.Add(jp.Name & "." & sdc.ColumnName, GetType(Object))
If sdr IsNot Nothing Then dr.SetField(jp.Name & "." & sdc.ColumnName, sdr(sdc.ColumnName))
Next
If trim_object_columns Then dt.Columns.Remove(jp.Name)
End If
End If
Next
dt.Rows.Add(dr)
Next
If castToCLRtypes Then
For Each drow In dt.Select
For Each dcol As DataColumn In dt.Columns
If TypeOf drow(dcol) Is JToken Then drow(dcol) = ToField(drow(dcol))
Next
Next
End If
If dt.Rows.Count > 0 AndAlso Not String.IsNullOrEmpty(orderBy) Then
dt = dt.Select("", orderBy).CopyToDataTable
End If
End If
Return dt
End Function
<Runtime.CompilerServices.Extension> Friend Function ToField(token As JToken) As Object
If token Is Nothing Then Return Nothing
Select Case token.Type
Case JTokenType.Boolean
Return token.Value(Of Boolean)
Case JTokenType.Bytes
Return token.Value(Of Byte())
Case JTokenType.Date
Return token.Value(Of Date)
Case JTokenType.Float
Return token.Value(Of Double)
Case JTokenType.Guid
Return token.Value(Of Guid)
Case JTokenType.Integer
Return token.Value(Of Integer)
Case JTokenType.Null
Return Nothing
Case JTokenType.Property
Return ExtractObjectFrom(CType(token, JProperty).Value)
Case JTokenType.String
Return token.Value(Of String)
Case JTokenType.TimeSpan
Return token.Value(Of TimeSpan)
Case JTokenType.Uri
Return token.Value(Of Uri)
Case Else
Return token.ToString
End Select
End Function
End Module
Now I am aware that my solution is neither clean nor reliable. It's just the first thing I thought to display the items in a DataGridView in a way that the user could sort the data by clicking on the column headers, which a simple JToken Array would not allow. My DataGridView should look like this:
Account ------------------------- Movement - Favored Value FULANO DE TAL SICRANO DE TAL - ACCOUNT 1
This is a very simple example. There are cases where each JSON item has properties that are also complex objects, so I use two DataGridView, the second one being repopulated each time a new row is selected in the first. All based on the conversion of JToken () into DataTable based on the above code.
However, I would like to write a custom class that wrapped JToken objects, and maybe a Collection class to list them so thatDataGridView would understand and manipulate with ease, activating features such as sorting (this is essential for me) and filtering (this would be an extra bonus if I could).
My question is: what is the minimum set of Interfaces that such classes should implement to accomplish this?
