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

			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