vb.net get values from json like string in DataTable

Question

Here is my json like string:

{
    "ProductGroupId": "3",
    "ProductGroupName": "Frisdranken",
    "ProductId": "139",
    "ProductName": "Cola",
    "Quantity": 1,
    "QuantityUnit": "P",
    "SellingPrice": 2.7,
    "VatRateId": "A",
    "DiscountLines": []
}, {
    "ProductGroupId": "3",
    "ProductGroupName": "Frisdranken",
    "ProductId": "146",
    "ProductName": "Plat water",
    "Quantity": 1,
    "QuantityUnit": "P",
    "SellingPrice": 2.6,
    "VatRateId": "A",
    "DiscountLines": []
}

How do I get the "ProductName" and "Quantity" in a datatable?

Is that JSON enclosed in `[...]` and you just missed them during copy/paste? — Crowcoder, Mar 25 '18 at 13:20
Possible duplicate of https://stackoverflow.com/questions/41748817/deserialize-json-into-data-table-in-vb-net-using-jsonconvert — Stuart, Mar 25 '18 at 13:38

score 0 · Accepted Answer · answered Mar 25 '18 at 13:47

Assuming your json is an array and you just missed the enclosing [...], you can:

Dim json As String = "[{""ProductGroupId"":""3"",""ProductGroupName"":""Frisdranken"",""ProductId"":""139"",""ProductName"":""Cola"",""Quantity"":1,""QuantityUnit"":""P"",""SellingPrice"":2.7,""VatRateId"":""A"",""DiscountLines"":[]},{""ProductGroupId"":""3"",""ProductGroupName"":""Frisdranken"",""ProductId"":""146"",""ProductName"":""Plat water"",""Quantity"":1,""QuantityUnit"":""P"",""SellingPrice"":2.6,""VatRateId"":""A"",""DiscountLines"":[]}]"

'Use JSON.Net to obtain a JArray
Dim jobj As JArray = JsonConvert.DeserializeObject(json)

'Extract just the fields you want from the data with a Linq projection into
'anonymous type with ProductName and Quantity fields
Dim ProductsAndQuantities = jobj.Select(Function(j)
                                            Return New With {
                                                .ProductName = j("ProductName"), 
                                                .Quantity = j("Quantity")}
                                        End Function)

'build your DataTable
Dim dt As DataTable = New DataTable()
dt.Columns.Add("ProductName")
dt.Columns.Add("Quantity")

'Load the data table
For Each item In ProductsAndQuantities
    Dim dr As DataRow = dt.NewRow()
    dr("ProductName") = item.ProductName
    dr("Quantity") = item.Quantity
    dt.Rows.Add(dr)
Next

Thanks Crowcoder for this nice code, you helped me a lot. The enclosing [...] are in the code I just removed them for an other piece of code. (that doesn't work) — Koen Amant, Mar 25 '18 at 17:12

vb.net get values from json like string in DataTable

1 Answers1