[Fork] Question41748817 deserialize-json-into-datatable-in-vb-net-using-jsonconvert

VbNet · Console

/Program.vb

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"":""[email protected]""}]"

			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