Serialize nested JSON string using a datatable

Question

I have this JSON string:

{
  "DataflowID": 1234,
  "Data": [    
    {
      "Key": "TIMESTAMP",
      "Value": "2019/11/11 07:00:00"
    },
    {
      "Key": "test1",
      "Value": "5.819720684"
    },
    {
      "Key": "test2",
      "Value": "12.47921946"
    }
  ]
}

And I am using the below code to get the data from the database and make the JSON string:

Imports Newtonsoft.Json

        Dim dataSet As DataSet = New DataSet("dataSet")
        dataSet.[Namespace] = "NetFrameWork"
        Dim jsonTable As DataTable = New DataTable()
        Dim keyColumn As DataColumn = New DataColumn("Key")
        Dim valueColumn As DataColumn = New DataColumn("Value")
        jsonTable.Columns.Add(keyColumn)
        jsonTable.Columns.Add(valueColumn)
        dataSet.Tables.Add(jsonTable)

        jsonTable.TableName = "Data"

        For m = 1 To g
            Dim dataRow2 As DataRow = jsonTable.NewRow
            dataRow2("Key") = "Timestamp"
            dataRow2("Value") = testTimestamp(m)
            jsonTable.Rows.Add(dataRow2)

            For k = 1 To tagsCounter
                Dim dataRow1 As DataRow = jsonTable.NewRow
                dataRow1("Key") = testName(k)
                dataRow1("Value") = testValue(m, k)
                jsonTable.Rows.Add(dataRow1)
            Next k
        Next m

But I cannot make the property dataFlowID .

So, you are trying to make a json string that you wrote in the beginning? There are simpler ways then. — CruleD
– CruleD, Commented Nov 12, 2019 at 10:35

theduck · Accepted Answer · 2019-11-13 08:47:49Z

1

I would build a class to represent the JSON and fill it in. For example:

Public Class JsonHolder
    Public DataflowId As Integer
    Public Data As DataTable
End Class

It looks like you already have the Data part sorted, so you just need to put that into a new instance of this holder class along with the DataflowId:

Dim holder = New JsonHolder With {
    .DataflowId = 1234,
    .Data = jsonTable
}

Then use Newtonsoft to serialise to JSON:

Dim json = JsonConvert.SerializeObject(holder, Formatting.Indented)

edited Nov 13, 2019 at 8:47

answered Nov 12, 2019 at 10:35

theduck

2,61713 gold badges20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Zacharias Over a year ago

Thank you for your reply. I tried the code, but now the text though includes DataflowId the Data is not filled with data. This is the result: {"DataflowId":1234,"Data":[]}

theduck Over a year ago

Does your code produce jsonTable correctly? The problem you are describing suggests that your data table is empty.

Zacharias Over a year ago

When I use these lines of code I get the datatable exactly the way I want it. dataSet.AcceptChanges() Dim json As String = JsonConvert.SerializeObject(dataSet, Formatting.Indented)

Zacharias Over a year ago

I have tried it this already, but still I don't have the proper result.

theduck Over a year ago

Yep - you need to use jsonTable rather than the DataSet (have edited the answer back to the original) otherwise you get the Data header twice. I have the code above working in a test project and the output exactly matches your desired JSON.

|

Collectives™ on Stack Overflow

Serialize nested JSON string using a datatable

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related