Search code examples
jsonvb.netjson.net

difference between DE serializing a JSON string into a dataset and DE serializing to a data table, error thrown

In vb.net i have the deserialization to a dataset working without a problem, i can later save to SQL. Deserialization to dataset Not sure why i cant similarly deserialize to a datatable, should be easy enough Deserialization to datatable

I get the follow error, " Unexpected JSON token when reading Data Table. Expected Start Array."

EDITED** Below is the problem code sample that is DE serializing to a data table.

 Imports System
 Imports Newtonsoft.Json
 Imports System.Data

Public Module Module1
  Public Sub Main()
    

    Dim jsonString As String = "{""rows"":[{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Imbalance (S44)"",""value"":""7.7"",""unit"":""""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Temp (S12)"",""value"":""76.0"",""unit"":""°F""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Flowrate (D6)"",""value"":""0.466"",""unit"":""g/h""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Flowrate (D4)"",""value"":""1.107"",""unit"":""g/h""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Bleach (R13)"",""value"":""1.0"",""unit"":""""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Retrans (A34)"",""value"":""6.7"",""unit"":""%""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Total Cl (S23)"",""value"":""1.341"",""unit"":""ppm""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.ACIDPID (A31)"",""value"":""25.7"",""unit"":""%""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.pH to PI (A33)"",""value"":""56.9"",""unit"":""%""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Cond (S11)"",""value"":""1355.0"",""unit"":""µS/cm""},{""id"":""2141991"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:24:00.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:24:00.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.pH2 (S32)"",""value"":""7.91"",""unit"":""pH""},{""id"":""2141994"",""source"":""89675"",""type"":""sol_onguardMeasurement"",""time"":""2023-05-01 05:25:30.000"",""timeOffset"":0,""timeWithOffset"":""2023-05-01 05:25:30.000"",""creationTime"":""2023-05-01 06:00:53.438"",""creationTimeOffset"":0,""creationTimeWithOffset"":""2023-05-01 06:00:53.438"",""tagname"":""89675.sol_onguardMeasurement.Cond (S11)"",""value"":""1352.0"",""unit"":""µS/cm""}]}"

    Console.WriteLine(jsonString)
    
    Dim dt As DataTable
    dt  = JsonConvert.DeserializeObject(of DataTable)(jsonString)
    
    dt.TableName = "rows"
    
    Console.WriteLine("")
    Console.WriteLine("## " + dt.TableName + " ##")
    Console.WriteLine("-------------------")
    Console.WriteLine("")
    
    For Each r as DataRow in dt.Rows
    Console.WriteLine("id: " & r("id").ToString())
    Console.WriteLine("source: " & r("source").ToString())
    Console.WriteLine("type: " & r("type").ToString())
    Console.WriteLine("time: " & r("time").ToString())
    Console.WriteLine("timeOffset: " & r("timeOffset").ToString())
    Console.WriteLine("timeWithOffset: " & r("timeWithOffset").ToString())
    Console.WriteLine("creationTime: " & r("creationTime").ToString())
    Console.WriteLine("creationTimeOffset: " & r("creationTimeOffset").ToString())
    Console.WriteLine("creationTimeWithOffset: " & r("creationTimeWithOffset").ToString())
    Console.WriteLine("tagname: " & r("tagname").ToString())
    Console.WriteLine("value: " & r("value").ToString())
    Console.WriteLine("unit: " & r("unit").ToString())
    Console.WriteLine("")
    ' + " - " + r("id").ToString())
    next
    
  End Sub
End Module
Solution

  • you can only deserialize array to data table

    Dim jsonArr As JArray = JObject.Parse(json)("rows")

Dim dt As DataTable  = jsonArr.ToObject(of DataTable)()

// or 

Dim ds As DataSet = JsonConvert.DeserializeObject(of DataSet)(jsonString)
        
Dim dt As DataTable = ds.Tables("rows")