Databinding for JSON.NET objects: how to implement?

Question

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 quite summary 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 on Winforms controls that use Databinding (mainly ComboBox and DataGridView). I don’t intend to edit data at this time, I just needed to display it in a user-friendly way.

I wrote a makeshift routine that converts IEnumerable(Of JToken) for 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 of 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:

Bill-------------------------Movement-Valor--------Favored-----
SO-AND-SO - ACCOUNT 2PAYMENTR$ 1.564,23SO-AND-SO
SICRANO DE TAL - CONTA 1PAYMENTR$ 2,323.79SICRANO DE TAL

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 being repopulated each time a new line is selected in the first. All based on Jtoken() conversion to Datatable based on the above code.

Nevertheless, I would like to write a custom class that wrapped the Jtoken objects, and perhaps a Collection class to list them so that the DataGridView understand and manipulate with ease, activating features such as rating (this is essential for me) and filtering (this would be an extra premium if I could).

My point is: which the minimum set of interfaces that such classes should implement to accomplish this?

Answer 1

As your problem is not very punctual, it seems to me that you have several files JSON, I shall then propose in its last edition JSON inserted in the question. The usual way to work with this would be to create the templates of classes and use the library JSON.NET to convert to a class object.

Models:

Base

Public MustInherit Class Base
    <Newtonsoft.Json.JsonProperty("id")>
    Public Property Id As Integer

    <Newtonsoft.Json.JsonProperty("nome")>
    Public Property Nome As String
End Class

---

Especie

Public Class Especie
    Inherits Base
End Class

---

ContaMovimento

Public Class ContaMovimento
    Inherits Base
End Class

---

Contas

Public Class Contas
    Inherits Base
End Class

---

Favorecido

Public Class Favorecido
    Inherits Base
    <Newtonsoft.Json.JsonProperty("contas")>
    Public Property Contas As List(Of Contas)
End Class

---

Conta

Public Class Conta
    <Newtonsoft.Json.JsonProperty("id")>
    Public Property Id As Integer

    <Newtonsoft.Json.JsonProperty("conta_movimento_id")>
    Public Property ContaMovimentoId As Integer

    <Newtonsoft.Json.JsonProperty("conta_movimento")>
    Public Property ContaMovimento As ContaMovimento

    <Newtonsoft.Json.JsonProperty("especie_id")>
    Public Property EspecieId As Integer

    <Newtonsoft.Json.JsonProperty("especie")>
    Public Property Especie As Especie

    <Newtonsoft.Json.JsonProperty("favorecido_id")>
    Public Property FavorecidoId As Integer

    <Newtonsoft.Json.JsonProperty("favorecido")>
    Public Property Favorecido As Favorecido

    <Newtonsoft.Json.JsonProperty("valor")>
    Public Property Valor As Decimal
End Class

_{Observing: all these classes have been decorated to know which item JSON he has to deserialize (Newtonsoft.Json.JsonProperty)}

---

How to use:

Dim json = System.IO.File.ReadAllText("arquivo.json")
Dim result = JsonConvert.DeserializeObject(Of List(Of Conta))(json)

and in the variable result has the result of a array of Conta with all the information from JSON organised and easy to handle.

---

To carry it inside a DataGridView is simple from the moment of loading of that class.

Form - Code

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim json = System.IO.File.ReadAllText("arquivo.json")
        Dim result = JsonConvert.DeserializeObject(Of List(Of Conta))(json)
        DataGridView1.DataSource =
            (From items In result
             Select New With {
                 .Conta = items.ContaMovimento.Nome,
                 .Movimento = items.Especie.Nome,
                 items.Valor,
                 .Favorecido = items.Favorecido.Nome
                 }).ToList()

    End Sub
End Class

Screen result:

That’s an example and you have what you need, because, JSON offered list of items, an object of an item and becomes easy based on that starting for others.

Some more links that can be used as tutorial:

• How to read one struct inside another with Json.Net?
• Deserialize Json Array in an Object Array
• How to ignore an error using Json.NET without damaging what has already been read
• How to convert a JSON response to a C object#?