vb.net 将json转换为数据表vb

Convert json to datatable vb

提问by Madalin Mad

Good morning, today i was trying to convert my json to datatable.

早上好，今天我试图将我的 json 转换为数据表。

This is what i'm trying to do

这就是我想要做的

    Dim webclient_server7 As New System.Net.WebClient
    Dim json_result As String = webclient_server7.DownloadString("http://myhost/api/mycontroller/GetQuery")
    Dim json_jsonstring = Newtonsoft.Json.JsonConvert.SerializeObject(json_result)

    Try
        Dim table As DataTable = JsonConvert.DeserializeObject(Of DataTable)(json_jsonstring)
    Catch ex As Exception
        MsgBox("An exception occured: " & ex.Message)
    End Try

And i get an exception saying:

An exception occured: Unexpected JSON token when reading DataTable. Expected StartArray, got String. Path '', line 1, position 9919.

我收到一个异常说：

发生异常：读取数据表时出现意外的 JSON 令牌。预期的 StartArray，得到 String。路径 ''，第 1 行，位置 9919。

i validated my json on json lintand it says my json is valid.
Is there anyone who can help me fixing this?

here's a copy of my raw json

我在json lint上验证了我的 json ，它说我的 json 是有效的。
有没有人可以帮我解决这个问题？

这是我的原始 json 的副本

"{\r\n \"Table\": [\r\n {\r\n \"IdOwner\": \"Davide\",\r\n \"tag_id\": 1,\r\n \"tag_type\": \"3\",\r\n \"tag_group\": \"Group_2\",\r\n \"tag_name\": \"Alfa\",\r\n \"tag_sequence\": 123458,\r\n \"tag_description\": \"Description_2\",\r\n \"tag_short_descritpion\": \"Desc_2\",\r\n \"tag_um\": \"kg\",\r\n \"tag_active\": true,\r\n \"tag_collecting\": false,\r\n \"tag_data_edit\": true,\r\n \"tag_source_name\": \"Alfaservice\",\r\n \"tag_source_index\": \"Undefined_index\",\r\n \"tag_source_tagtype\": \"Source_tag_type\",\r\n \"tag_source_lenght\": 50,\r\n \"tag_collect_frequency\": 200,\r\n \"tag_collect_unit\": \"ms\",\r\n \"tag_low_limit\": 100.0,\r\n \"tag_high_limit\": 370.0,\r\n \"tag_control_limit_active\": true,\r\n \"tag_calc\": \"useless_field\",\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": null\r\n },\r\n {\r\n \"IdOwner\": \"Giuseppe\",\r\n \"tag_id\": 3,\r\n \"tag_type\": \"Type_1\",\r\n \"tag_group\": \"Group_2\",\r\n \"tag_name\": \"Bemad\",\r\n \"tag_sequence\": 123456,\r\n \"tag_description\": \"Description_5\",\r\n \"tag_short_descritpion\": \"Desc_5\",\r\n \"tag_um\": \"Kg\",\r\n \"tag_active\": true,\r\n \"tag_collecting\": false,\r\n \"tag_data_edit\": true,\r\n \"tag_source_name\": \"Alfaservice\",\r\n \"tag_source_index\": \"Undefined_index\",\r\n \"tag_source_tagtype\": \"Source_tag_type\",\r\n \"tag_source_lenght\": 50,\r\n \"tag_collect_frequency\": 200,\r\n \"tag_collect_unit\": \"ms\",\r\n \"tag_low_limit\": 250.0,\r\n \"tag_high_limit\": 660.0,\r\n \"tag_control_limit_active\": true,\r\n \"tag_calc\": \"useless_field\",\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": null\r\n },\r\n {\r\n \"IdOwner\": \"Madalin\",\r\n \"tag_id\": 2,\r\n \"tag_type\": \"Type_2\",\r\n \"tag_group\": \"Group_1\",\r\n \"tag_name\": \"Bemad\",\r\n \"tag_sequence\": 123456,\r\n \"tag_description\": \"Description_1\",\r\n \"tag_short_descritpion\": \"Desc_1\",\r\n \"tag_um\": \"Kg\",\r\n \"tag_active\": true,\r\n \"tag_collecting\": false,\r\n \"tag_data_edit\": true,\r\n \"tag_source_name\": \"Alfaservice\",\r\n \"tag_source_index\": \"Undefined_index\",\r\n \"tag_source_tagtype\": \"Source_tag_type\",\r\n \"tag_source_lenght\": 50,\r\n \"tag_collect_frequency\": 200,\r\n \"tag_collect_unit\": \"ms\",\r\n \"tag_low_limit\": 150.0,\r\n \"tag_high_limit\": 350.0,\r\n \"tag_control_limit_active\": true,\r\n \"tag_calc\": \"useless_field\",\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": null\r\n },\r\n {\r\n \"IdOwner\": \"test\",\r\n \"tag_id\": 6,\r\n \"tag_type\": null,\r\n \"tag_group\": null,\r\n \"tag_name\": \"REW_SAX_BOWED_ActualVelocity\",\r\n \"tag_sequence\": 0,\r\n \"tag_description\": \"1\",\r\n \"tag_short_descritpion\": \"1\",\r\n \"tag_um\": null,\r\n \"tag_active\": true,\r\n \"tag_collecting\": true,\r\n \"tag_data_edit\": false,\r\n \"tag_source_name\": \"REW_SAX_BOWED.ACTUALVELOCITY\",\r\n \"tag_source_index\": \"0\",\r\n \"tag_source_tagtype\": \"Float\",\r\n \"tag_source_lenght\": 0,\r\n \"tag_collect_frequency\": 100,\r\n \"tag_collect_unit\": \"1\",\r\n \"tag_low_limit\": 1.0,\r\n \"tag_high_limit\": 1.0,\r\n \"tag_control_limit_active\": false,\r\n \"tag_calc\": null,\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": null\r\n },\r\n {\r\n \"IdOwner\": \"test\",\r\n \"tag_id\": 7,\r\n \"tag_type\": null,\r\n \"tag_group\": null,\r\n \"tag_name\": \"REW_SAX_BOWED_ActualVelocity\",\r\n \"tag_sequence\": 0,\r\n \"tag_description\": \"1\",\r\n \"tag_short_descritpion\": \"1\",\r\n \"tag_um\": null,\r\n \"tag_active\": true,\r\n \"tag_collecting\": true,\r\n \"tag_data_edit\": false,\r\n \"tag_source_name\": \"REW_SAX_BOWED.ACTUALVELOCITY\",\r\n \"tag_source_index\": \"0\",\r\n \"tag_source_tagtype\": \"Float\",\r\n \"tag_source_lenght\": 0,\r\n \"tag_collect_frequency\": 100,\r\n \"tag_collect_unit\": \"1\",\r\n \"tag_low_limit\": 1.0,\r\n \"tag_high_limit\": 1.0,\r\n \"tag_control_limit_active\": false,\r\n \"tag_calc\": null,\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": \"Tag Name=REW_SAX_BOWED_ActualVelocity,Address=REW_SAX_BOWED.ACTUALVELOCITY,Data Type=Float,Respect Data Type=0,Client Access=RO,Scan Rate=100,Scaling=,Raw Low=,Raw High=,Scaled Low=,Scaled High=,Scaled Data Type=,Clamp Low=,Clamp High=,Eng Units=,Description=,Negate Value=,\"\r\n },\r\n {\r\n \"IdOwner\": \"test\",\r\n \"tag_id\": 8,\r\n \"tag_type\": null,\r\n \"tag_group\": null,\r\n \"tag_name\": \"REW_SAX_BOWED_ActualVelocity\",\r\n \"tag_sequence\": 0,\r\n \"tag_description\": \"1\",\r\n \"tag_short_descritpion\": \"1\",\r\n \"tag_um\": null,\r\n \"tag_active\": true,\r\n \"tag_collecting\": true,\r\n \"tag_data_edit\": false,\r\n \"tag_source_name\": \"REW_SAX_BOWED.ACTUALVELOCITY\",\r\n \"tag_source_index\": \"0\",\r\n \"tag_source_tagtype\": \"Float\",\r\n \"tag_source_lenght\": 0,\r\n \"tag_collect_frequency\": 100,\r\n \"tag_collect_unit\": \"1\",\r\n \"tag_low_limit\": 1.0,\r\n \"tag_high_limit\": 1.0,\r\n \"tag_control_limit_active\": false,\r\n \"tag_calc\": null,\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": \"Tag Name=REW_SAX_BOWED_ActualVelocity,Address=REW_SAX_BOWED.ACTUALVELOCITY,Data Type=Float,Respect Data Type=0,Client Access=RO,Scan Rate=100,Scaling=,Raw Low=,Raw High=,Scaled Low=,Scaled High=,Scaled Data Type=,Clamp Low=,Clamp High=,Eng Units=,Description=,Negate Value=,\"\r\n },\r\n {\r\n \"IdOwner\": \"test\",\r\n \"tag_id\": 9,\r\n \"tag_type\": null,\r\n \"tag_group\": null,\r\n \"tag_name\": \"REW_SAX_BOWED_ActualVelocity\",\r\n \"tag_sequence\": 0,\r\n \"tag_description\": \"1\",\r\n \"tag_short_descritpion\": \"1\",\r\n \"tag_um\": null,\r\n \"tag_active\": true,\r\n \"tag_collecting\": true,\r\n \"tag_data_edit\": false,\r\n \"tag_source_name\": \"REW_SAX_BOWED.ACTUALVELOCITY\",\r\n \"tag_source_index\": \"0\",\r\n \"tag_source_tagtype\": \"Float\",\r\n \"tag_source_lenght\": 0,\r\n \"tag_collect_frequency\": 100,\r\n \"tag_collect_unit\": \"1\",\r\n \"tag_low_limit\": 1.0,\r\n \"tag_high_limit\": 1.0,\r\n \"tag_control_limit_active\": false,\r\n \"tag_calc\": null,\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": \"Tag Name=REW_SAX_BOWED_ActualVelocity,Address=REW_SAX_BOWED.ACTUALVELOCITY,Data Type=Float,Respect Data Type=0,Client Access=RO,Scan Rate=100,Scaling=,Raw Low=,Raw High=,Scaled Low=,Scaled High=,Scaled Data Type=,Clamp Low=,Clamp High=,Eng Units=,Description=,Negate Value=,\"\r\n },\r\n {\r\n \"IdOwner\": \"test\",\r\n \"tag_id\": 10,\r\n \"tag_type\": null,\r\n \"tag_group\": null,\r\n \"tag_name\": \"REW_SAX_BOWED_ActualVelocity\",\r\n \"tag_sequence\": 0,\r\n \"tag_description\": \"1\",\r\n \"tag_short_descritpion\": \"1\",\r\n \"tag_um\": null,\r\n \"tag_active\": true,\r\n \"tag_collecting\": true,\r\n \"tag_data_edit\": false,\r\n \"tag_source_name\": \"REW_SAX_BOWED.ACTUALVELOCITY\",\r\n \"tag_source_index\": \"0\",\r\n \"tag_source_tagtype\": \"Float\",\r\n \"tag_source_lenght\": 0,\r\n \"tag_collect_frequency\": 100,\r\n \"tag_collect_unit\": \"1\",\r\n \"tag_low_limit\": 1.0,\r\n \"tag_high_limit\": 1.0,\r\n \"tag_control_limit_active\": false,\r\n \"tag_calc\": null,\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": \"Tag Name=REW_SAX_BOWED_ActualVelocity,Address=REW_SAX_BOWED.ACTUALVELOCITY,Data Type=Float,Respect Data Type=0,Client Access=RO,Scan Rate=100,Scaling=,Raw Low=,Raw High=,Scaled Low=,Scaled High=,Scaled Data Type=,Clamp Low=,Clamp High=,Eng Units=,Description=,Negate Value=,\"\r\n }\r\n ]\r\n}"An exception occured: Unexpected JSON token when reading DataTable. Expected StartArray, got String. Path '', line 1, position 9919.

Description=,Negate Value=,\"\r\n }\r\n ]\r\n}"发生异常：读取数据表时出现意外的 JSON 标记。预期的 StartArray，得到 String。路径 ''，第 1 行，位置 9919。

采纳答案by dbc

First you need to remove this line:

首先，您需要删除此行：

Dim json_jsonstring = Newtonsoft.Json.JsonConvert.SerializeObject(json_result)

The downloaded string is alreadyJSON, if you serialize it againyou will turn it into an escaped JSON string literal, whereupon deserializing it returns the string - and not a DataTableas you want. This explains the error Unexpected JSON token when reading DataTable. Expected StartArray, got String. Path '', line 1, position 9919: Json.NET parsed the entire json_jsonstringstring as a single, escaped string literal.

下载的字符串已经是JSON，如果你再次序列化它，你会将它变成一个转义的 JSON 字符串文字，因此反序列化它返回字符串 - 而不是DataTable你想要的。这解释了错误Unexpected JSON token when reading DataTable. Expected StartArray, got String. Path '', line 1, position 9919：Json.NET 将整个json_jsonstring字符串解析为单个转义字符串文字。

Second, your JSON contains an outer root object like so:

其次，您的 JSON 包含一个外部根对象，如下所示：

{
  "Table": [
    {
      "IdOwner": "Davide", 
      // Additional properties
    },
    // Additional rows.
  ]
}

Thus you need to introduce a root object with the necessary Tableproperty to deserialize the DataTableinto. You can do it with the following generic root:

因此，您需要引入一个具有必要Table属性的根对象以将其反序列化DataTable。您可以使用以下通用根执行此操作：

Class RootObject(Of T)
    Public Property Table As T
End Class

And then deserialize as follows:

然后反序列化如下：

Dim table = JsonConvert.DeserializeObject(Of RootObject(Of DataTable))(json_result).Table

Sample fiddle.

样品小提琴。

Or, if you don't care to create a root type, deserialize to a temporary Dictionary(Of string, DataTable):

或者，如果您不想创建根类型，请反序列化为一个临时的Dictionary(Of string, DataTable)：

Dim table = JsonConvert.DeserializeObject(Of Dictionary(Of string, DataTable))(json_result).Values.SingleOrDefault()

Sample fiddle #2:

样品小提琴 #2：

You asked, I was wondering if making a sort of "substring" deleting the "{ Table:" part and the last } could work?Certainly it's possible but I would not recommend this. You would be manually duplicating some of the logic of a JSON parser. Let Json.NET do the work for you, it will handle whitespace and newlines correctly.

您问，我想知道删除“{ Table：”部分和最后一个} 的某种“子字符串”是否可行？当然这是可能的，但我不推荐这样做。您将手动复制 JSON 解析器的某些逻辑。让 Json.NET 为您完成工作，它将正确处理空格和换行符。

回答by Madalin Mad

After almost a year of practice with json i decided to answer my question. If you're actually trying to deserialize a json into a datatable we can assume you're using vb.netor c#(in other words visual studio).

经过近一年的 json 练习，我决定回答我的问题。如果您实际上是在尝试将 json 反序列化为数据表，我们可以假设您使用的是vb.net或c#（换句话说，visual studio）。

To achive this task follow my list:

要完成此任务，请按照我的列表进行操作：

0)Download and install Newtonsoft.json from Nuget
1)Copy your raw json
2)create a new class in your solution and name it as you want. (example: MyDatatable.vb)
3)In the Mydatatable.vb page : Modify -> Paste Special -> Paste JSON as class (this is the key)

0)从 Nuget 下载并安装 Newtonsoft.json
1)复制您的原始 json
2)在您的解决方案中创建一个新类并根据需要命名。（例如：MyDatatable.vb）
3）在 Mydatatable.vb 页面中：修改 -> 选择性粘贴 -> 将 JSON 粘贴为类（这是关键）

At this point visual studio will start creating a bunch of classes that will help newtonsoft to deserialize your json into the main object. In my case the main object was a class named Tablelike you can see below :

此时，visual studio 将开始创建一堆类，这些类将帮助 newtonsoft 将您的 json 反序列化为主要对象。在我的例子中，主要对象是一个名为Table 的类，如下所示：

"{\r\n \"Table\": [\r\n {\r\n \"IdOwner\": \"Davide\",\r\n \"tag_id\": 1,\r\n \"tag_type\": \"3\",\r\n \"tag_group\": \"Group_2\",\r\n \"tag_name\": \"Alfa\",\r\n \"tag_sequence\": 123458,\r\n \"tag_description\": \"Description_2\",\r\n \"tag_short_descritpion\": \"Desc_2\",\r\n \"tag_um\": \"kg\",\r\n \"tag_active\": true,\r\n \"tag_colle

"{\r\n \"表\": [\r\n {\r\n \"IdOwner\": \"Davide\",\r\n \"tag_id\": 1,\r\n \"tag_type\": \"3\",\r\n \"tag_group\": \"Group_2\",\r\n \"tag_name\": \"Alfa\",\r\n \" tag_sequence\": 123458,\r\n \"tag_description\": \"Description_2\",\r\n \"tag_short_descritpion\": \"Desc_2\",\r\n \"tag_um\": \" kg\",\r\n \"tag_active\": 真,\r\n \"tag_colle

4)At this point you will have

4）此时你将有

• A class file named MyDatatable.vb containing many other classes like (*Table)
• Newtonsoft reference installed on your project

• 一个名为 MyDatatable.vb 的类文件包含许多其他类，如 ( *Table)
• 安装在您的项目中的 Newtonsoft 参考

this will allow you to deserialize your json with 2 instructions which are:

这将允许您使用 2 条指令反序列化您的 json：

Dim strMyJson as string = [your raw json here]
Dim tbFinalObject as Table = Newtonsoft.json.jsonconver.deserializeObject(of Table)(strMyJson)

So in your tbFinalObject Object you'll have your json deserialized. Note: if the second instruction gives you an error like 'expected some char at line x' this may be because your json needs to be deserialized into a string before to be deserialized into another object so you should have a set of instructions like below:

因此，在您的 tbFinalObject 对象中，您将反序列化 json。注意：如果第二条指令给你一个错误，比如'expected some char at line x'，这可能是因为你的json需要在反序列化成另一个对象之前反序列化成一个字符串，所以你应该有一组如下的指令：

 Dim strMyJson as string = [your raw json here]
 Dim strMyJson as string = Newtonsoft.json.jsonconvert.deserializeObject(of string)([your raw json here])
 Dim tbFinalObject as Table = Newtonsoft.json.jsonconvert.deserializeObject(of Table)(strMyJson)

That's how i solve most of my issues with json and vb.net deserializations.

这就是我使用 json 和 vb.net 反序列化解决大部分问题的方法。

Further Notes: When you paste a json as class visual studio will automatically create object and create some property as arrays. I use to replace arrays with lists because i found that some old versions of newtonsoft have some problems deserializing json into arrays.

进一步说明：当您将 json 粘贴为类时，visual studio 将自动创建对象并创建一些属性作为数组。我使用列表替换数组，因为我发现一些旧版本的 newtonsoft 在将 json 反序列化为数组时存在一些问题。