Imports System

Imports System.Linq

Imports System.Collections.Generic

Imports System.Data

Imports System.Xml

Imports System.IO

​

Imports Newtonsoft.Json

Imports Newtonsoft.Json.Linq

Imports Newtonsoft.Json.Converters

Imports Newtonsoft.Json.Serialization

​

    ' https://stackoverflow.com/questions/41748817/deserialize-json-into-datatable-in-vb-net-using-jsonconvert#41748817

​

    Public Module JsonExtensions

        Public Sub PopulateDataTable(json As String, target As DataTable, Optional settings As JsonSerializerSettings = Nothing)

            Using reader = New JsonTextReader(New StringReader(json))

                Do

                    If reader.TokenType = JsonToken.StartArray Then

                        ' Populate the table

                        Dim converter = New DataTableConverter()

                        converter.ReadJson(reader, target.GetType(), target, JsonSerializer.CreateDefault(settings))

                    End If

                Loop While reader.Read()

            End Using

        End Sub

    End Module

​

​

    Public Module TestClass 

​

        Friend Sub Test()

            Dim myRecords = "[{""PhoneNo"":""123456789"",""ID"":""46541"",""Name"":""John Doe"",""Email"":""hello@there""},{""PhoneNo"":""8675309"",""ID"":""1234"",""Name"":""Alice Bob"",""Email"":""goodbye@here""},{""PhoneNo"":null,""ID"":""0001"",""Name"":""No Phone"",""Email"":""no@email.either""}]"

​

            Console.WriteLine("Input JSON: ")

            Console.WriteLine(JToken.Parse(myRecords))

            Dim _dt = New DataTable()

            _dt.Columns.Add("PhoneNo", GetType(Long))

            _dt.Columns.Add("ID", GetType(Long))

            JsonExtensions.PopulateDataTable(myRecords, _dt)

            Console.WriteLine("Deserialized {0} columns: ", _dt.GetType().Name)

            Console.WriteLine(JsonConvert.SerializeObject(_dt.Columns.Cast(Of DataColumn)().Select(Function(c) String.Format("{0}: {1}", c.ColumnName, c.DataType.FullName)), Newtonsoft.Json.Formatting.Indented))

            Console.WriteLine("Deserialized and reserialized JSON: ")

            Console.WriteLine(JsonConvert.SerializeObject(_dt, Newtonsoft.Json.Formatting.Indented))

        End Sub 

    End Module

​

    Public Module Module1   

        Public Sub Main()

​

            Console.WriteLine("Json.NET version: " + GetType(JsonSerializer).Assembly.FullName)

            Console.WriteLine("")

            TestClass.Test()

        End Sub 

    End Module