locked
Json String to DataGridView for ChildObjects RRS feed

  • Question

  • I have a JSON String that looks like below. I am trying to use the code snippet here, but it only allows me to choose the parent level. How can I pass the child data (depsoits or withdrawals) to a gridview?

    Dim personDetail = JsonConvert.DeserializeObject(Of RootObject)(jsonstring)
        DataGridView1.DataSource = personDetail.results
    
    
    {
      "results": [
        {
          "id": "51142254",
          "tp_id": "!XP4D49X0CD123628",
          "firstname": "Owner",
          "lastname": "Operator",
          "email": "",
          "phone": "",
          "enrolled": "1",
          "balance": 247.54,
          "fleet": "Test Express",
          "deposits": [
            {
              "id": "184022380",
              "date": "2016-02-17",
              "amount": "200.00",
              "transID": "135246",
              "memo": "Scheduled Deposit",
              "status": "Cleared"
            },
            {
              "id": "184022383",
              "date": "2016-02-25",
              "amount": "200.00",
              "transID": "246357",
              "memo": "Scheduled Deposit",
              "status": "Cleared"
            },
            {
              "id": "184022386",
              "date": "2016-03-02",
              "amount": "200.00",
              "transID": "975468",
              "memo": "Scheduled Deposit",
              "status": "Cleared"
            }
          ],
          "withdrawals": [
            {
              "id": "184026949",
              "date": "2016-03-09",
              "amount": "352.46",
              "transID": "395920",
              "memo": "Invoice\r\n\r\n100234",
              "status": "Cleared"
            }
          ]
        },
        {
          "id": "51142326",
          "tp_id": "!XP4D49X7CD123612",
          "firstname": "Owner",
          "lastname": "Operator",
          "email": "",
          "phone": "",
          "enrolled": "1",
          "balance": 0,
          "fleet": "Test\r\nExpress",
          "deposits": [],
          "withdrawals": []
        }
      ]
    }  

    Below is my class structure

    Public Class Deposit
        Public Property id() As String
            Get
                Return m_id
            End Get
            Set
                m_id = Value
            End Set
        End Property
        Private m_id As String
        Public Property [date]() As String
            Get
                Return m_date
            End Get
            Set
                m_date = Value
            End Set
        End Property
        Private m_date As String
        Public Property amount() As String
            Get
                Return m_amount
            End Get
            Set
                m_amount = Value
            End Set
        End Property
        Private m_amount As String
        Public Property transID() As String
            Get
                Return m_transID
            End Get
            Set
                m_transID = Value
            End Set
        End Property
        Private m_transID As String
        Public Property memo() As String
            Get
                Return m_memo
            End Get
            Set
                m_memo = Value
            End Set
        End Property
        Private m_memo As String
        Public Property status() As String
            Get
                Return m_status
            End Get
            Set
                m_status = Value
            End Set
        End Property
        Private m_status As String
    End Class
    
    Public Class Withdrawal
        Public Property id() As String
            Get
                Return m_id
            End Get
            Set
                m_id = Value
            End Set
        End Property
        Private m_id As String
        Public Property [date]() As String
            Get
                Return m_date
            End Get
            Set
                m_date = Value
            End Set
        End Property
        Private m_date As String
        Public Property amount() As String
            Get
                Return m_amount
            End Get
            Set
                m_amount = Value
            End Set
        End Property
        Private m_amount As String
        Public Property transID() As String
            Get
                Return m_transID
            End Get
            Set
                m_transID = Value
            End Set
        End Property
        Private m_transID As String
        Public Property memo() As String
            Get
                Return m_memo
            End Get
            Set
                m_memo = Value
            End Set
        End Property
        Private m_memo As String
        Public Property status() As String
            Get
                Return m_status
            End Get
            Set
                m_status = Value
            End Set
        End Property
        Private m_status As String
    End Class
    
    Public Class Result
        Public Property id() As String
            Get
                Return m_id
            End Get
            Set
                m_id = Value
            End Set
        End Property
        Private m_id As String
        Public Property tp_id() As String
            Get
                Return m_tp_id
            End Get
            Set
                m_tp_id = Value
            End Set
        End Property
        Private m_tp_id As String
        Public Property firstname() As String
            Get
                Return m_firstname
            End Get
            Set
                m_firstname = Value
            End Set
        End Property
        Private m_firstname As String
        Public Property lastname() As String
            Get
                Return m_lastname
            End Get
            Set
                m_lastname = Value
            End Set
        End Property
        Private m_lastname As String
        Public Property email() As String
            Get
                Return m_email
            End Get
            Set
                m_email = Value
            End Set
        End Property
        Private m_email As String
        Public Property phone() As String
            Get
                Return m_phone
            End Get
            Set
                m_phone = Value
            End Set
        End Property
        Private m_phone As String
        Public Property enrolled() As String
            Get
                Return m_enrolled
            End Get
            Set
                m_enrolled = Value
            End Set
        End Property
        Private m_enrolled As String
        Public Property balance() As Double
            Get
                Return m_balance
            End Get
            Set
                m_balance = Value
            End Set
        End Property
        Private m_balance As Double
        Public Property fleet() As String
            Get
                Return m_fleet
            End Get
            Set
                m_fleet = Value
            End Set
        End Property
        Private m_fleet As String
        Public Property deposits() As List(Of Deposit)
            Get
                Return m_deposits
            End Get
            Set
                m_deposits = Value
            End Set
        End Property
        Private m_deposits As List(Of Deposit)
        Public Property withdrawals() As List(Of Withdrawal)
            Get
                Return m_withdrawals
            End Get
            Set
                m_withdrawals = Value
            End Set
        End Property
        Private m_withdrawals As List(Of Withdrawal)
    End Class
    
    Public Class RootObject
        Public Property results() As List(Of Result)
            Get
                Return m_results
            End Get
            Set
                m_results = Value
            End Set
    
    
        End Property
        Private m_results As List(Of Result)
    
    End Class


    Tuesday, May 10, 2016 4:30 PM

Answers

  • Thank you Frank for all your efforts. I just tried the below code. Converting to XML was only one line of code. I then was able to use XML to parse out the deposits. 

     Dim node As XNode = JsonConvert.DeserializeXNode(jsonstring, "Results")
            Dim dataSet As DataSet = New DataSet
            Dim dataTable As DataTable = New DataTable("deposits")
            dataTable.Columns.Add("id", Type.GetType("System.String"))
            dataTable.Columns.Add("date", Type.GetType("System.String"))
            dataTable.Columns.Add("amount", Type.GetType("System.String"))
            dataTable.Columns.Add("transID", Type.GetType("System.String"))
            dataTable.Columns.Add("memo", Type.GetType("System.String"))
            dataTable.Columns.Add("status", Type.GetType("System.String"))
            dataSet.Tables.Add(dataTable)
    
            Dim xmlSR As System.IO.StringReader = New System.IO.StringReader(node.ToString)
    
            dataSet.ReadXml(xmlSR, XmlReadMode.IgnoreSchema)
    
            DataGridView1.DataSource = dataTable
    https://msdn.microsoft.com/en-us/library/fx29c3yd(v=vs.110).aspx

    That's fine, but you don't need to convert anything - and - all of those members are still strings.

    Try to sort by Date or Amount - it'll do a string sort.

    It's up to you of course, and that does accomplish the goal I guess. :)


    In the middle of difficulty ... lies opportunity. -- Albert Einstein

    • Marked as answer by wolfeste Tuesday, May 10, 2016 7:57 PM
    Tuesday, May 10, 2016 7:30 PM

All replies

  • Wolfeste,

    Have a look at a thread from yesterday:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/c7e16eba-692f-453e-8e08-c06eeafe7f33/format-json-output?forum=vbgeneral

    Using NewtonSoft, you can serialize to and from your class so getting a collection of the class should then be easy. From there, add in a method in your class that will create a DataTable and and another method that will give you back a BindingSource from that DataTable.

    That BindingSource will then be the .DataSource for the DGV.


    In the middle of difficulty ... lies opportunity. -- Albert Einstein


    • Edited by Frank L. Smith Tuesday, May 10, 2016 4:36 PM ...added link
    Tuesday, May 10, 2016 4:35 PM
  • Frank,

    Thank you for the link. It doesn't seem to be what I am looking for, or I didnt follow it correctly. I a little new to JSON so if anyone had some better examples I would be much appreciated. 

    Tuesday, May 10, 2016 5:26 PM
  • Frank,

    Thank you for the link. It doesn't seem to be what I am looking for, or I didnt follow it correctly. I a little new to JSON so if anyone had some better examples I would be much appreciated. 


    I'm guessing that the information is what came from collections of your classes, or just what is it from?

    In the middle of difficulty ... lies opportunity. -- Albert Einstein

    Tuesday, May 10, 2016 5:40 PM
  • the collection of classes came from copy/paste the JSON string to http://json2csharp.com/. I then converted the C# to vb.net
    Tuesday, May 10, 2016 6:20 PM
  • the collection of classes came from copy/paste the JSON string to http://json2csharp.com/. I then converted the C# to vb.net

    That's interesting - but that leaves me confused really.

    Is the data that's in the JSON from something that you created?

    Maybe I'm asking the wrong question here:

    What's your ultimate goal? I think if we look at it from the creation of the data to the persistence (JSON or otherwise) and back again, then it'll make more sense.


    In the middle of difficulty ... lies opportunity. -- Albert Einstein

    Tuesday, May 10, 2016 6:26 PM
  • The JSON data is from an API that a vendor will be providing us access. My end goal is to deserialize the JSON String I provided above and display it in a datagridview. The sample code above does what I am looking for, but it only does it for the parent nodes. I have not found a way to segement out the Child nodes into their own datagrid. For example. I want to take the Child Node "Deposits" and display all the deposits information in the datagrid. 
    Tuesday, May 10, 2016 6:31 PM
  • The JSON data is from an API that a vendor will be providing us access. My end goal is to deserialize the JSON String I provided above and display it in a datagridview. The sample code above does what I am looking for, but it only does it for the parent nodes. I have not found a way to segement out the Child nodes into their own datagrid. For example. I want to take the Child Node "Deposits" and display all the deposits information in the datagrid. 

    Reed would be a big help at this point. ;-)

    *****

    Do they offer any other output type, like XML? JSON is superior, but XML makes more readable sense to see just what they've provided, then figure out what to do with it from there.


    In the middle of difficulty ... lies opportunity. -- Albert Einstein

    Tuesday, May 10, 2016 6:36 PM
  • Yes they have an XML converter. Are you aware of way of doing this in XML then? 
    Tuesday, May 10, 2016 6:47 PM
  • Yes they have an XML converter. Are you aware of way of doing this in XML then? 

    I was looking for a way to "figure out" what the class itself looked like - notwithstanding your example.

    *****

    How about try something please.

    I put the JSON as a text file on my desktop and just called it "Json.txt".

    If you'll start a new project (so that it doesn't interfere with anything else), first put my class somewhere in the form's code (outside of the class Form1):

    Option Strict On Option Explicit On Option Infer Off Imports Newtonsoft.Json Imports System.IO.Path Public NotInheritable Class GenericJSON Public Shared Function _ GetJSON_Text(Of T As Class)(ByVal instanceOrCollection As T) As String Dim retVal As String = Nothing Try If instanceOrCollection Is Nothing Then Throw New _ ArgumentNullException("Instance Or Collection", _ "The instance or collection cannot be null." & vbCrLf) Else retVal = JsonConvert.SerializeObject(instanceOrCollection) End If Catch ex As Exception Throw End Try Return retVal End Function Public Shared Sub ExportToJSON(Of T As Class)(ByVal instanceOrCollection As T, _ ByVal filePath As String) Try If instanceOrCollection Is Nothing Then Throw New _ ArgumentNullException("Instance Or Collection", _ "The instance or collection cannot be null." & vbCrLf) ElseIf String.IsNullOrWhiteSpace(filePath) Then Throw New ArgumentException("The file path to export the JSON" & vbCrLf & _ "to cannot be null or empty.") ElseIf Not FolderCanBeWrittenTo(GetDirectoryName(filePath)) Then Throw New UnauthorizedAccessException("You do not have permission to write to this directory.") Else Dim sb As New System.Text.StringBuilder(GetJSON_Text(instanceOrCollection)) If sb.Length > 0 Then My.Computer.FileSystem.WriteAllText(filePath, sb.ToString, False) End If End If Catch ex As Exception Throw End Try End Sub Public Shared Function _ DeserializeJSON_Text(Of T As Class)(ByVal jsonText As String) As T Dim retVal As T = Nothing Try If String.IsNullOrWhiteSpace(jsonText) Then Throw New ArgumentException("The JSON text cannot be null or empty.") Else retVal = JsonConvert.DeserializeObject(Of T)(jsonText) End If Catch ex As Exception Throw End Try Return retVal End Function Public Shared Function _ ImportFromJSON(Of T As Class)(ByVal filePath As String) As T Dim retVal As T = Nothing Try If String.IsNullOrWhiteSpace(filePath) Then Throw New ArgumentException("The file path to import the JSON" & vbCrLf & _ "from cannot be null or empty.") ElseIf Not My.Computer.FileSystem.FileExists(filePath) Then Throw New IO.FileNotFoundException("The file path could not be located.") Else Dim sb As New System.Text.StringBuilder(My.Computer.FileSystem.ReadAllText(filePath)) If sb.Length > 0 Then retVal = DeserializeJSON_Text(Of T)(sb.ToString) End If End If Catch ex As Exception Throw End Try Return retVal End Function Private Shared Function FolderCanBeWrittenTo(ByVal fldr As String) As Boolean Dim retVal As Boolean = True Try Dim testPath As String = Combine(fldr, "Test.tmp") My.Computer.FileSystem.WriteAllText(testPath, "test", False) If My.Computer.FileSystem.FileExists(testPath) Then My.Computer.FileSystem.DeleteFile(testPath) End If Catch ex As Exception retVal = False End Try Return retVal End Function End Class


    Now in Form1's code, please put the following:

    Option Strict On Option Explicit On Option Infer Off Imports System.IO.Path Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load Dim desktop As String = _ My.Computer.FileSystem.SpecialDirectories.Desktop Dim jFilePath As String = _ Combine(desktop, "Json.txt") Dim test As RootObject = GenericJSON.ImportFromJSON(Of RootObject)(jFilePath) Stop End Sub End Class Public Class Deposit Public Property id() As String Get Return m_id End Get Set(value As String) m_id = Value End Set End Property Private m_id As String Public Property [date]() As String Get Return m_date End Get Set(value As String) m_date = Value End Set End Property Private m_date As String Public Property amount() As String Get Return m_amount End Get Set(value As String) m_amount = Value End Set End Property Private m_amount As String Public Property transID() As String Get Return m_transID End Get Set(value As String) m_transID = Value End Set End Property Private m_transID As String Public Property memo() As String Get Return m_memo End Get Set(value As String) m_memo = Value End Set End Property Private m_memo As String Public Property status() As String Get Return m_status End Get Set(value As String) m_status = Value End Set End Property Private m_status As String End Class Public Class Withdrawal Public Property id() As String Get Return m_id End Get Set(value As String) m_id = Value End Set End Property Private m_id As String Public Property [date]() As String Get Return m_date End Get Set(value As String) m_date = Value End Set End Property Private m_date As String Public Property amount() As String Get Return m_amount End Get Set(value As String) m_amount = Value End Set End Property Private m_amount As String Public Property transID() As String Get Return m_transID End Get Set(value As String) m_transID = Value End Set End Property Private m_transID As String Public Property memo() As String Get Return m_memo End Get Set(value As String) m_memo = Value End Set End Property Private m_memo As String Public Property status() As String Get Return m_status End Get Set(value As String) m_status = Value End Set End Property Private m_status As String End Class Public Class Result Public Property id() As String Get Return m_id End Get Set(value As String) m_id = Value End Set End Property Private m_id As String Public Property tp_id() As String Get Return m_tp_id End Get Set(value As String) m_tp_id = Value End Set End Property Private m_tp_id As String Public Property firstname() As String Get Return m_firstname End Get Set(value As String) m_firstname = Value End Set End Property Private m_firstname As String Public Property lastname() As String Get Return m_lastname End Get Set(value As String) m_lastname = Value End Set End Property Private m_lastname As String Public Property email() As String Get Return m_email End Get Set(value As String) m_email = Value End Set End Property Private m_email As String Public Property phone() As String Get Return m_phone End Get Set(value As String) m_phone = Value End Set End Property Private m_phone As String Public Property enrolled() As String Get Return m_enrolled End Get Set(value As String) m_enrolled = Value End Set End Property Private m_enrolled As String Public Property balance() As Double Get Return m_balance End Get Set(value As Double) m_balance = Value End Set End Property Private m_balance As Double Public Property fleet() As String Get Return m_fleet End Get Set(value As String) m_fleet = Value End Set End Property Private m_fleet As String Public Property deposits() As List(Of Deposit) Get Return m_deposits End Get Set(value As List(Of Deposit)) m_deposits = Value End Set End Property Private m_deposits As List(Of Deposit) Public Property withdrawals() As List(Of Withdrawal) Get Return m_withdrawals End Get Set(value As List(Of Withdrawal)) m_withdrawals = Value End Set End Property Private m_withdrawals As List(Of Withdrawal) End Class Public Class RootObject Public Property results() As List(Of Result) Get Return m_results End Get Set(value As List(Of Result)) m_results = Value End Set End Property Private m_results As List(Of Result) End Class


    Then run it and hover your mouse over "test". It definitely has *something* there, and it's a collection so things at least look right at first glance.

    If what I just said is confusing, let me know and I'll zip up the project folder and just upload it for you.


    In the middle of difficulty ... lies opportunity. -- Albert Einstein

    Tuesday, May 10, 2016 6:53 PM
  • Thank you Frank. I was able to get your sample code working. It works perfect for the Root/Parent Node, which is essentially what I have working now. If I were to pass "test" to the datagrid it will only display the Results Nodes. I am looking to bypass the "results" nodes and go straight to the "deposits". Then have the deposits displaying in a gridview. 
    Tuesday, May 10, 2016 7:04 PM
  • Yes they have an XML converter. Are you aware of way of doing this in XML then? 

    I just realized that you probably don't have NewtonSoft, so at least this one does have that in there:

    http://www.fls-online.com/VBNet_Forum/05-10-16/NewtonSoftExample.zip

    Download that somewhere then extract it. When you open it in Visual Studio, it will prompt you to convert it, so just follow the prompts and it should work.

    Converting it shouldn't change the framework version, but be sure that it's 4.0 because his assembly has different .dll files for different framework versions.

    You'll still need to put the text file on your desktop though.

    Let me know the results please?

    *****

    About the XML, there's still a possible use for it if this blows a gasket. ;-)


    In the middle of difficulty ... lies opportunity. -- Albert Einstein


    Tuesday, May 10, 2016 7:04 PM
  • Thank you Frank. I was able to get your sample code working. It works perfect for the Root/Parent Node, which is essentially what I have working now. If I were to pass "test" to the datagrid it will only display the Results Nodes. I am looking to bypass the "results" nodes and go straight to the "deposits". Then have the deposits displaying in a gridview. 

    Is deposits in there?

    I honestly didn't study it, I just looked at that it had something.


    In the middle of difficulty ... lies opportunity. -- Albert Einstein

    Tuesday, May 10, 2016 7:06 PM
  • Yes the deposits are in "test" but it's 2 levels deep in. When you pass "test" to a grid it will only display the 1st level. 
    Tuesday, May 10, 2016 7:11 PM
  • Yes the deposits are in "test" but it's 2 levels deep in. When you pass "test" to a grid it will only display the 1st level. 

    I see that now - I hadn't looked.

    This is what I'd do - It's a cheap fix, but it'll work: Create your OWN classes (either a collection class or a class that uses the factory method with shared methods) and leave that lousy one like it is (move it to a file though - it's ugly).

    Let it run the way that it does then create methods in your OWN class(es) to get the data, create a DataTable, a BindingSource, and all that.

    What do you think of that plan?


    In the middle of difficulty ... lies opportunity. -- Albert Einstein

    Tuesday, May 10, 2016 7:14 PM
  • Yes the deposits are in "test" but it's 2 levels deep in. When you pass "test" to a grid it will only display the 1st level. 

    Would you show me (or point me to a link) the XML?

    Here's why I ask: Look at the result (everything, including the nested ones). Everything is a string. That's not the way JSON was meant to be, so it might as well be XML.

    Depending on how the XML is set up - even though XML is more verbose by nature - it might easier to deal with that and then more directly interact with your own classes.

    Maybe. ;-)


    In the middle of difficulty ... lies opportunity. -- Albert Einstein

    Tuesday, May 10, 2016 7:19 PM
  • Thank you Frank for all your efforts. I just tried the below code. Converting to XML was only one line of code. I then was able to use XML to parse out the deposits. 

     Dim node As XNode = JsonConvert.DeserializeXNode(jsonstring, "Results")
            Dim dataSet As DataSet = New DataSet
            Dim dataTable As DataTable = New DataTable("deposits")
            dataTable.Columns.Add("id", Type.GetType("System.String"))
            dataTable.Columns.Add("date", Type.GetType("System.String"))
            dataTable.Columns.Add("amount", Type.GetType("System.String"))
            dataTable.Columns.Add("transID", Type.GetType("System.String"))
            dataTable.Columns.Add("memo", Type.GetType("System.String"))
            dataTable.Columns.Add("status", Type.GetType("System.String"))
            dataSet.Tables.Add(dataTable)
    
            Dim xmlSR As System.IO.StringReader = New System.IO.StringReader(node.ToString)
    
            dataSet.ReadXml(xmlSR, XmlReadMode.IgnoreSchema)
    
            DataGridView1.DataSource = dataTable
    https://msdn.microsoft.com/en-us/library/fx29c3yd(v=vs.110).aspx

    Tuesday, May 10, 2016 7:25 PM
  • Thank you Frank for all your efforts. I just tried the below code. Converting to XML was only one line of code. I then was able to use XML to parse out the deposits. 

     Dim node As XNode = JsonConvert.DeserializeXNode(jsonstring, "Results")
            Dim dataSet As DataSet = New DataSet
            Dim dataTable As DataTable = New DataTable("deposits")
            dataTable.Columns.Add("id", Type.GetType("System.String"))
            dataTable.Columns.Add("date", Type.GetType("System.String"))
            dataTable.Columns.Add("amount", Type.GetType("System.String"))
            dataTable.Columns.Add("transID", Type.GetType("System.String"))
            dataTable.Columns.Add("memo", Type.GetType("System.String"))
            dataTable.Columns.Add("status", Type.GetType("System.String"))
            dataSet.Tables.Add(dataTable)
    
            Dim xmlSR As System.IO.StringReader = New System.IO.StringReader(node.ToString)
    
            dataSet.ReadXml(xmlSR, XmlReadMode.IgnoreSchema)
    
            DataGridView1.DataSource = dataTable
    https://msdn.microsoft.com/en-us/library/fx29c3yd(v=vs.110).aspx

    That's fine, but you don't need to convert anything - and - all of those members are still strings.

    Try to sort by Date or Amount - it'll do a string sort.

    It's up to you of course, and that does accomplish the goal I guess. :)


    In the middle of difficulty ... lies opportunity. -- Albert Einstein

    • Marked as answer by wolfeste Tuesday, May 10, 2016 7:57 PM
    Tuesday, May 10, 2016 7:30 PM
  • I have a deadline to get this completed. I will get it working and finalize a better solution later on. Thanks again!
    Tuesday, May 10, 2016 8:00 PM
  • I have a deadline to get this completed. I will get it working and finalize a better solution later on. Thanks again!

    I understand then.

    *****

    If you want to make it work better - later - pick this up again and I'll do what I can if you want. :)


    In the middle of difficulty ... lies opportunity. -- Albert Einstein

    Tuesday, May 10, 2016 8:03 PM