Imports System

Imports System.Linq

Imports System.Collections.Generic

Imports System.Data

Imports System.Xml

Imports System.IO

Imports System.Numerics

Imports System.Globalization

​

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

        Private ReadOnly NumberTypes = New JTokenType() {JTokenType.[Integer], JTokenType.Float, JTokenType.[String], JTokenType.Comment, JTokenType.Raw, JTokenType.[Boolean]}

​

        Private Function ValidateToken(o As JToken, validTypes As JTokenType(), nullable As Boolean) As Boolean

            Return (Array.IndexOf(validTypes, o.Type) <> -1) OrElse (nullable AndAlso (o.Type = JTokenType.Null OrElse o.Type = JTokenType.Undefined))

        End Function

​

        <System.Runtime.CompilerServices.Extension> _

        Public Function CanConvertToNullableLong(token As JToken) As Boolean

            ' Reverse engineered from 

            ' public static explicit operator long?(JToken value)

            ' https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/Linq/JToken.cs#L1045

            If token Is Nothing OrElse token.Type = JTokenType.Null OrElse token.Type = JTokenType.Boolean Then

                Return True

            End If

            If Not ValidateToken(token, NumberTypes, True) Then

                Return False

            End If

            Dim jValue = TryCast(token, JValue)

            If jValue Is Nothing Then

                Return False

            End If

            If TypeOf jValue.Value Is BigInteger Then

                Dim i = CType(jValue.Value, BigInteger)

                Return i <= Long.MaxValue AndAlso i >= Long.MinValue

            End If

            Dim s = CType(jValue, String)

            Dim v As Long

            Return Long.TryParse(s, NumberStyles.Number, NumberFormatInfo.InvariantInfo, v)

        End Function

​

        Public Sub TryConvertColumnsToNullableLong(root As JToken)

            If TypeOf root Is JContainer Then

                ' If ALL columns values of a given name can be converted from string to long, then do so.

                ' Do not convert columns where some but not all are convertable.

                For Each group In DirectCast(root, JContainer) _

                    .Descendants() _

                    .OfType(Of JProperty)() _

                    .GroupBy(Function(p) p.Name) _

                    .Where(Function(g) g.All(Function(p) (p.Value.Type = JTokenType.String Or p.Value.Type = JTokenType.Null) AndAlso p.Value.CanConvertToNullableLong()))

                    For Each p In group

                        p.Value = CType(p.Value, System.Nullable(Of Long))

                    Next

                Next

            End If

        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""}]"

​

            Dim token = JToken.Parse(myRecords)

            JsonExtensions.TryConvertColumnsToNullableLong(token)

            Dim _dt = token.ToObject(Of DataTable)()

            Console.WriteLine("Deserialized columns: ")

            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