I am new to Json and I am stuck at creating a JSon data file for an Ext.Net Treepanel.
Here is the data which is I manually type them in and it works for me. And I want to generate a similar data format from a data table in VB.Net.
{
"partcode": "",
"threadid": "",
"description": "",
"specification": "",
"parentId": null,
"leaf": false,
"children": [
{
"partcode": "609",
"threadid": "",
"description": "This is code 609",
"specification": "Code 609 Spec",
"parentId": "root",
"leaf": false,
"children": [
{
"partcode": "609B",
"threadid": "",
"description": "This is code 609B",
"specification": "Code 609B Spec",
"parentId": "609",
"leaf": false,
"children": [
{
"partcode": "609BLevel2",
"threadid": "",
"description": "This is code 609B Level 2",
"specification": "Code 609BLevel2 Spec",
"parentId": "609B",
"leaf": false,
"children": [
{
"partcode": "609BLevel3",
"threadid": "",
"description": "This is code 609BLevel3",
"specification": "Code 609BLevel3 Spec",
"parentId": "609BLevel2",
"leaf": true
}
]
}
]
},
{
"partcode": "B609/3",
"threadid": "",
"description": "This is code B609/3",
"specification": "Code B609/3 Spec",
"parentId": "609",
"leaf": false,
"children": [
{
"children": [
{
"partcode": "B609/3 Level2",
"threadid": "",
"description": "This is code B609/3 Level 2",
"specification": "Code B609/3 Spec",
"parentId": "B609/3",
"leaf": false,
"children": [
{
"partcode": "B609/3 Level3",
"threadid": "",
"description": "This is code 609BLevel3",
"specification": "Code B609/3 Level3 Spec",
"parentId": "B609/3 Level2",
"leaf": true
}
]
}
]
}
]
}
]
}
]
}
In my data table,
NoIdx partcode parent description specification
0 609 0_ This is code 609 Code 609 Spec
1 609B 609 This is code 609B Code 609B Spec
2 609BLevel2 609B This is code 609BLevel2 Code 609BLevel2 Spec
3 609BLevel3 609BLevel2 This is code 609BLevel3 Code 609BLevel3 Spec
1 B609/3 609 blah blah blah
2 B609/3Level2 B609/3 blah blah blah
3 B609/3Level3 B609/3Level2 blah blah...
What I have tried:
Here below was what I attempted to do but it is not working. Can anyone help?
Dim dt As New DataTable
'dt = oSQLClass.GetSQLTable("SELECT * FROM get_BomTree ('609', '609', 1 )")
Try
Dim dc As New myDataConnection
Dim cn As New SqlClient.SqlConnection(dc.ConStringForSQLCommand)
cn.Open()
Dim sa As New SqlClient.SqlDataAdapter("SELECT * FROM get_BomTree ('609', '609', 1 )", cn)
cn.Close() 'connection close here , that is disconnected from data source
sa.Fill(dt)
Catch ex As Exception
End Try
If dt.Rows.Count = 0 Then
Exit Sub
End If
Using writer As Newtonsoft.Json.JsonWriter = New JsonTextWriter(sw)
writer.Formatting = Formatting.Indented
''This is a root mark up
''-----------------------
writer.WriteStartObject()
writer.WritePropertyName("partcode")
writer.WriteValue("")
writer.WritePropertyName("description")
writer.WriteValue("")
writer.WritePropertyName("specification")
writer.WriteValue("")
writer.WritePropertyName("parentId")
writer.WriteValue("root")
writer.WritePropertyName("leaf")
writer.WriteValue(False)
Dim r As DataRow
Dim rNext As DataRow = Nothing
''
For i As Int16 = 0 To dt.Rows.Count - 1
r = dt.Rows(i)
If i + 1 <= dt.Rows.Count Then
rNext = dt.Rows(i + 1)
Else
rNext = Nothing
End If
If r("NoIdx") = 0 Then
''This is a FATHER
''-----------------------
writer.WritePropertyName("children")
writer.WriteStartArray()
writer.WriteStartObject()
writer.WritePropertyName("partcode")
writer.WriteValue(Trim(r("partcode")))
writer.WritePropertyName("description")
writer.WriteValue(Trim(r("description")))
writer.WritePropertyName("specification")
writer.WriteValue(Trim(r("specification")))
writer.WritePropertyName("parentId")
writer.WriteValue(Trim(r("parent")))
writer.WritePropertyName("leaf")
writer.WriteValue(False) '' if have child then leaf=false
writer.WritePropertyName("children")
writer.WriteStartArray()
Else
writer.WriteStartObject()
writer.WritePropertyName("partcode")
writer.WriteValue(Trim(r("partcode")))
writer.WritePropertyName("description")
writer.WriteValue(Trim(r("description")))
writer.WritePropertyName("specification")
writer.WriteValue(Trim(r("specification")))
writer.WritePropertyName("parentId")
writer.WriteValue(Trim(r("parent")))
writer.WritePropertyName("leaf")
If rNext IsNot Nothing Then
''it has sub level
If CInt(rNext("NoIdx")) > r("NoIdx") Then
writer.WriteValue(False) '' continue add node in same level leaf=false
writer.WritePropertyName("children")
writer.WriteStartArray()
Else
If CInt(rNext("NoIdx")) = r("NoIdx") Then
writer.WriteValue(True) '' continue add node in same level leaf=false
writer.WriteEndObject()
Else
''no more sub level
writer.WriteValue(True) '' cut off and add new level leaf= true
writer.WriteEndConstructor()
End If
End If
End If
End If
Next
writer.WriteEnd()
writer.WriteEndObject()
End Using
Dim file As System.IO.StreamWriter
file = My.Computer.FileSystem.OpenTextFileWriter("C:\Users\User\Documents\File\TestData.Json", False)
file.WriteLine(sb.ToString)
file.Close()
Submit an article or tip