Json数据转化为DataTable的两种方法(vb.net)

---

前言

初接触Json类型数据，习惯用DataTable处理数据，因此想要将Json数据转化为datatable格式的数据。

---

一、利用Spit字符分隔的方式解释Json

1.步骤

1. 引用库：【system.web.extension】;
2. 定义分隔符格式： Dim JsonChar As Char =【json数据格式分隔符】；
3. 定义字符串数组： Dim JsonStr As String()
4. 建新的Table数据表： Dim JsonTable as DataTable=New DataTable();
5. 加入Table的Column表头，利用【spit】分隔字符获取，并写入DataTable；
6. 循环将解释的Json数据写入datatable；

2.例子

• 已获取字符串：

{“service”:“QREELQTY”,“msgcode”:0,“msg”:null,“strdata”:[{“mc”:“SM2104AB-1”,“mo”:1,“stage”:1,“slot”:12,“pn”:“201-018404-0222”,“did”:“LD000000000022994093”,“qty”:12,“fid”:“KT8FL 455125”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:4,“stage”:1,“slot”:10,“pn”:“301-030622-2492”,“did”:“LD000000000022939266”,“qty”:22,“fid”:“KT8FL 331810”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:3,“stage”:1,“slot”:10,“pn”:“301-030622-5102”,“did”:“LD000000000022949755”,“qty”:85,“fid”:“KT8FL 331801”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:7,“stage”:1,“slot”:11,“pn”:“440-900009-04R0”,“did”:“LD000000000022683192”,“qty”:85,“fid”:“KT8FL 332733”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:11,“pn”:“301-900058-10R0”,“did”:“LD000000000022948432”,“qty”:170,“fid”:“KT8FL 452899”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:9,“pn”:“301-030622-8202”,“did”:“LD000000000022947701”,“qty”:170,“fid”:“KT8FL 455160”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:12,“pn”:“131-005213-0000”,“did”:“LD000000000022861683”,“qty”:170,“fid”:“KT8FL 332724”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:6,“stage”:1,“slot”:10,“pn”:“301-030596-05R1”,“did”:“LDVFHDH2020082200059”,“qty”:170,“fid”:“KT8FL 331847”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:1,“stage”:1,“slot”:10,“pn”:“101-005181-06R2”,“did”:“LDVYJBA0092007132067”,“qty”:225,“fid”:“KT8FL 331915”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:12,“pn”:“131-005213-0000”,“did”:“LD000000000022861688”,“qty”:307,“fid”:“KT8FL 452850”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:8,“stage”:1,“slot”:12,“pn”:“466-900013-0000”,“did”:“LD000000000022970377”,“qty”:324,“fid”:“KT16F 040227”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:9,“pn”:“301-030622-8202”,“did”:“LD000000000022965778”,“qty”:798,“fid”:“KT8FL 455027”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:9,“stage”:1,“slot”:23,“pn”:“201-900093-0680”,“did”:“LDVCAPHW202007210002”,“qty”:798,“fid”:“KT12F 028230”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:6,“stage”:1,“slot”:10,“pn”:“301-030596-05R1”,“did”:“LDVFHDH2020082900053”,“qty”:1108,“fid”:“KT8FL 332795”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:12,“pn”:“301-900057-R025”,“did”:“LDVTA202007160000685”,“qty”:1208,“fid”:“KT8FL 452952”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:8,“stage”:1,“slot”:11,“pn”:“121-900030-0000”,“did”:“LD000000000022994199”,“qty”:1357,“fid”:“KT12F 028245”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:3,“stage”:1,“slot”:10,“pn”:“301-030622-5102”,“did”:“LD000000000022966007”,“qty”:1496,“fid”:“KT8FL 737007”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:4,“stage”:1,“slot”:10,“pn”:“301-030622-2492”,“did”:“LD000000000022965999”,“qty”:1998,“fid”:“KT8FL 331858”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:7,“stage”:1,“slot”:10,“pn”:“201-020052-0105”,“did”:“LD000000000022995502”,“qty”:2237,“fid”:“KT8FL 331912”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:1,“stage”:1,“slot”:11,“pn”:“201-020170-0104”,“did”:“LD000000000022994277”,“qty”:2605,“fid”:“KT8FL 455165”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:7,“stage”:1,“slot”:11,“pn”:“440-900009-04R0”,“did”:“LDVBT201011000017430”,“qty”:2608,“fid”:“KT8FL 455123”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:4,“stage”:1,“slot”:12,“pn”:“201-900022-0471”,“did”:“LD000000000022995506”,“qty”:2778,“fid”:“KT8FL 996740”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:6,“stage”:1,“slot”:11,“pn”:“201-018964-0105”,“did”:“LD000000000022995530”,“qty”:2908,“fid”:“KT8FL 360042”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:10,“pn”:“301-900059-8R25”,“did”:“LDVTA202008280000014”,“qty”:2950,“fid”:“KT8FL 331874”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:12,“pn”:“131-005213-0000”,“did”:“LD000000000022861692”,“qty”:2998,“fid”:“KT8FL 455194”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:1,“stage”:1,“slot”:12,“pn”:“201-018404-0222”,“did”:“LD000000000022995512”,“qty”:3178,“fid”:“KT8FL 455004”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:12,“pn”:“301-900057-R025”,“did”:“LDVTA202007160000684”,“qty”:3298,“fid”:“KT8FL 729575”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:2,“stage”:1,“slot”:11,“pn”:“301-900058-10R0”,“did”:“LDVYAG20101300000699”,“qty”:3450,“fid”:“KT8FL 455024”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:6,“stage”:1,“slot”:10,“pn”:“301-030596-05R1”,“did”:“LDVFHDH2020092700087”,“qty”:3498,“fid”:“KT8FL 331924”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:3,“stage”:1,“slot”:11,“pn”:“161-000705-0000”,“did”:“LDVXHD20101400000010”,“qty”:3850,“fid”:“KT8FL 455159”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:1,“stage”:1,“slot”:11,“pn”:“201-020170-0104”,“did”:“LD000000000022994353”,“qty”:3900,“fid”:“KT8FL 360065”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:10,“pn”:“301-030622-2202”,“did”:“LD000000000022948125”,“qty”:4160,“fid”:“KT8FL 332601”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:11,“pn”:“301-900053-6201”,“did”:“LDVYAG20101300000644”,“qty”:5900,“fid”:“KT8FL 331962”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:6,“stage”:1,“slot”:11,“pn”:“201-018964-0105”,“did”:“LD000000000022994732”,“qty”:6587,“fid”:“KT8FL 332690”,“status”:“2”},{“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:10,“pn”:“301-030622-2202”,“did”:“LDVXHD20101500000015”,“qty”:7998,“fid”:“KT8FL 996979”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:4,“stage”:1,“slot”:11,“pn”:“201-018959-0104”,“did”:“LD000000000022995508”,“qty”:9461,“fid”:“KT8FL 452811”,“status”:“1”},{“mc”:“SM2104AB-1”,“mo”:5,“stage”:1,“slot”:11,“pn”:“301-900053-6201”,“did”:“LDVYAG20092300000976”,“qty”:12224,“fid”:“KT8FL 994066”,“status”:“2”}]}

Imports Newtonsoft.Json.Linq
Imports Newtonsoft.Json
Imports System.Web.Script.Serialization

Public Function JasonToDataTable(Jason As String) As DataTable

        Try
        	'定义分隔符格式
            Dim rowf As Char = "{"
            Dim Str As Char = ","
            Dim StrC As Char = """"
			'定义字符串数组
            Dim rowStr As String()
            Dim ColStr As String()
			
            JasonToDataTable = New DataTable
			
            rowStr = Jason.Split(rowf) '分行
			
            ColStr = rowStr(2).Split(Str)
            '加入DataTable列名
            For i = 0 To ColStr.Length - 2
                Dim ColName As String()
                ColName = ColStr(i).Split(StrC)

                JasonToDataTable.Columns.Add(ColName(1))
            Next

            Dim EachColunm As String()
            Dim EachStr As String()
            Dim EachValue(ColStr.Length) As String
			'加入Json数据
            For j = 0 To rowStr.Length - 3
                JasonToDataTable.Rows.Add()
                EachColunm = rowStr(j + 2).Split(",")
                For k = 0 To EachColunm.Length - 1
                    If EachColunm(k).Length > 1 Then
                        EachStr = EachColunm(k).Split(":")
                        EachValue(k) = EachStr(1).Replace("}", "").Replace("]", "").Replace("""", "")
                        JasonToDataTable.Rows(j).Item(k) = EachValue(k)
                    End If
                Next
            Next

            Return JasonToDataTable
        Catch ex As Exception
            WarmTXT.Visible = True
            WarmTXT.Text = "Json数据转换出错！"
        End Try

    End Function

---

二、利用反序列化公式转化【JavaScriptSerializer】

1.步骤

1. 安装【Nugen】解决方案插件；
   
2. 读出Json数据；
3. 用JavaScriptSerializer排列Json数据；
4. 使用循环语句将Json数据列名、数据导入新建的DT；

2.例子

Public Function JsonToDataSet(Json As String) As DataSet
        Try
            Dim ds = New DataSet()
            Dim JSS = New JavaScriptSerializer()
            Dim obj As Object = JSS.DeserializeObject(Json)
            Dim datajson As Dictionary(Of String, Object)
            datajson = obj

            For Each item In datajson
                Dim dt = New DataTable("JsonDT")
                Dim rows As Object = item.Value
                If item.Key <> "strdata" Then Continue For
                For Each row In rows

                    Dim val As Dictionary(Of String, Object)
                    val = row
                    Dim dr As DataRow = dt.NewRow()
                    Dim sss As KeyValuePair(Of String, Object)
                    For Each sss In val
                        If dt.Columns.Contains(sss.Key) = True Then
                            dr(sss.Key) = sss.Value
                        Else
                            dt.Columns.Add(sss.Key)
                            dr(sss.Key) = sss.Value
                        End If
                    Next
                    dt.Rows.Add(dr)
                Next
                ds.Tables.Add(dt)
            Next
            Return ds
        Catch ex As Exception

        End Try
        Return Nothing
    End Function

---

总结

以上两种方法都可将Json数据转化为DataTable数据，总体上说没有差别。

方法一：比较适合Json数据包简单，而且永不会变化的数据类型格式；

方法二：普遍通用使用于所有Json数据转化为DataTable类型数据格式。