using System;

using System.Collections;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Runtime.Serialization.Formatters;

using System.IO;

using System.Data;

using System.ComponentModel.DataAnnotations;

using System.Globalization;

using System.Xml;

​

​

namespace Question49898782

{

    using Newtonsoft.Json;

    using Newtonsoft.Json.Linq;

    using Newtonsoft.Json.Converters;

    using Newtonsoft.Json.Serialization;    

    //https://stackoverflow.com/questions/49898782/c-sharp-creating-a-datatable-from-not-fully-known-json#comment86830451_49898782

​

    public class ListOfBusinessKey

    {

        public Dictionary<string, object> business_key { get; set; }

    }

​

    public class KeyList

    {

        public int ssid { get; set; }

        public int dim { get; set; }

        public int type { get; set; }

        public List<ListOfBusinessKey> list_of_business_keys { get; set; }

​

        public DataTable ToDataTable()

        {

            var tbl = new DataTable();

            tbl.Columns.Add(new DataColumn("ssid", typeof(Int64)));

            tbl.Columns.Add(new DataColumn("dim", typeof(Int64)));

            tbl.Columns.Add(new DataColumn("type", typeof(Int64)));

​

            var columnQuery = EnumerableExtensions.Merge(

                list_of_business_keys

                .SelectMany(k => k.business_key)

                .Select(p => new KeyValuePair<string, Type>(p.Key, p.Value == null ? typeof(object) : p.Value.GetType())),

                p => p.Key, (p1, p2) => new KeyValuePair<string, Type>(p1.Key, MergeTypes(p1.Value, p2.Value)));

            foreach (var c in columnQuery)

                tbl.Columns.Add(c.Key, c.Value);

​

            foreach (var d in list_of_business_keys.Select(k => k.business_key))

            {

                var row = tbl.NewRow();

                row["ssid"] = ssid;

                row["dim"] = dim;

                row["type"] = type;

                foreach (var p in d.Where(p => p.Value != null))

                {

                    row[p.Key] = Convert.ChangeType(p.Value, tbl.Columns[p.Key].DataType, CultureInfo.InvariantCulture);

                }

                tbl.Rows.Add(row);

            }

            return tbl;

        }

​

        static Type MergeTypes(Type type1, Type type2)

        {

            // Enhance as needed

            if (type1 == type2)

                return type1;

            if (type2 == typeof(object))

                return type1;

            if (type1 == typeof(object))

                return type2;

            if (type1.IsAssignableFrom(type2))

                return type1;

            if (type2.IsAssignableFrom(type1))

                return type2;

            if (typeof(IConvertible).IsAssignableFrom(type1) && typeof(IConvertible).IsAssignableFrom(type2))

            {

                if (type1 == typeof(string))

                    return type1;

                if (type2 == typeof(string))

                    return type2;

                if ((type1 == typeof(long) || type1 == typeof(int)) && (type2 == typeof(decimal) || type2 == typeof(double)))

                    return type2;

                if ((type2 == typeof(long) || type2 == typeof(int)) && (type1 == typeof(decimal) || type1 == typeof(double)))

                    return type1;

            }

            throw new ArgumentException(string.Format("Cannot merge types {0} and {1}", type1, type2));

        }

    }

​

    public static class EnumerableExtensions

    {

        public static IEnumerable<TSource> Merge<TSource, TKey>(IEnumerable<TSource> source, Func<TSource, TKey> keySelector, Func<TSource, TSource, TSource> mergeSelector)

        {

            if (source == null || keySelector == null || mergeSelector == null)

                throw new ArgumentNullException();

            return MergeIterator(source, keySelector, mergeSelector);

        }

​

        static IEnumerable<TSource> MergeIterator<TSource, TKey>(IEnumerable<TSource> source, Func<TSource, TKey> keySelector, Func<TSource, TSource, TSource> mergeSelector)

        {

            var dictionary = new Dictionary<TKey, TSource>();

            foreach (TSource element in source)

            {

                var key = keySelector(element);

                TSource oldElement;

                if (!dictionary.TryGetValue(key, out oldElement))

                {

                    dictionary[key] = element;

                }

                else

                {

                    dictionary[key] = mergeSelector(element, oldElement);

                }

            }

            return dictionary.Values;

        }

    }

​

    class TestClass

    {

        public static void Test()

        {

            foreach (var json in GetJson())

                TestDictionary(json);

            //foreach (var json in GetJson())

                //TestJTokenRestructure(json);

            Console.WriteLine("\nDone.");

        }

​

        static void TestDictionary(string json)

        {

            Console.WriteLine("\nTesting conversion of {0} to {1}.", typeof(KeyList), typeof(DataTable));

            Console.WriteLine("Input JSON: ");

            Console.WriteLine(json);

​

            var keyList = JsonConvert.DeserializeObject<KeyList>(json);

​

            var tbl = keyList.ToDataTable();

​

            Console.WriteLine("Output {0}:", tbl.GetType());

            Console.WriteLine(JsonConvert.SerializeObject(tbl, Formatting.Indented));

​

            Assert.IsTrue(tbl.Rows.Count == JToken.Parse(json).SelectTokens("list_of_business_keys[*].business_key").Count());

        }

​

        static void TestJTokenRestructure(string json)

        {

            Console.WriteLine("\nTesting conversion of {0} to {1}", typeof(JObject), typeof(DataTable));

            Console.WriteLine("Input JSON: ");

            Console.WriteLine(json);

​

            var root = JObject.Parse(json);

​

            var tbl = root.ToStructuredDataTable();

​

            Console.WriteLine("Output {0}:", tbl.GetType());

            Console.WriteLine(JsonConvert.SerializeObject(tbl, Formatting.Indented));

​

            Assert.IsTrue(tbl.Rows.Count == root.SelectTokens("list_of_business_keys[*].business_key").Count());

        }

​

        static IEnumerable<string> GetJson()

        {

            var json = new[]

            {

                @"{ ""ssid"" : 1, 

  ""dim"" : 2,

  ""type"" : 3,

  ""list_of_business_keys"":

  [

      {""business_key"" : {""column_1"" : 100, ""column_2"" : 1000}},

      {""business_key"" : {""column_1"" : 200, ""column_2"" : 1000}},

      {""business_key"" : {""column_1"" : 300, ""column_2"" : 1000}},

      {""business_key"" : {""column_1"" : 400, ""column_2"" : 1000}},

      {""business_key"" : {""column_1"" : 500, ""column_2"" : 1000}}

  ]

}",

                @"{ ""ssid"" : 1, 

  ""dim"" : 2,

  ""type"" : 3,

  ""list_of_business_keys"":

  [

      {""business_key"" : {""xxx"" : ""abc"", ""yyy"" : 1000, ""zzz"" : 123}},

      {""business_key"" : {""xxx"" : ""cde"", ""yyy"" : 1000, ""zzz"" : 456}},

      {""business_key"" : {""xxx"" : ""efg"", ""yyy"" : 1000, ""zzz"" : 789}},

      {""business_key"" : {""xxx"" : ""hij"", ""yyy"" : 1000, ""zzz"" : 12 }},

      {""business_key"" : {""xxx"" : ""klm"", ""yyy"" : 1000, ""zzz"" : 345}}

  ]

}",

                @"{ ""ssid"" : 1, 

  ""dim"" : 2,

  ""type"" : 3,

  ""list_of_business_keys"":

  [

      {""business_key"" : {""xxx"" : null, ""yyy"" : 1000, ""zzz"" : 123}},

      {""business_key"" : {""xxx"" : ""row contains string-value cell that was initially null"", ""yyy"" : 1000, ""zzz"" : 456}},

      {""business_key"" : {""xxx"" : ""efg"", ""yyy"" : 1000, ""zzz"" : 789}},

      {""business_key"" : {""xxx"" : ""row contains floating point cell that was initially integral"", ""yyy"" : 1000, ""zzz"" : 12.123 }},

      {""business_key"" : {""added_property"" : ""added_value"", ""xxx"" : ""row contains string-valued cell that was initially integral"", ""yyy"" : ""1000"", ""zzz"" : 345 }}

  ]

}",

            };

            return json;

        }

    }

​

    public static class JsonExtensions

    {

        const string listOfKeys = "list_of_business_keys";

        const string key = "business_key";

​

        public static DataTable ToStructuredDataTable(this JObject obj)

        {

            if (obj == null)

                return null;

            var array = new JArray();

            var list = obj[listOfKeys] as JArray;

            var rootProperties = obj.Properties().Where(p => p.Name != listOfKeys).ToArray();

            if (list != null)

            {

                foreach (var item in list.Select(i => i[key]).OfType<JObject>())

                {

                    array.Add(new JObject(rootProperties.Concat(item.Properties())));

                }

            }

            return array.ToObject<DataTable>();

        }

    }

​

    public class Program

    {

        public static void Main()

        {

            Console.WriteLine("Environment version: " + Environment.Version);

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

            Console.WriteLine();

​

            try

            {

                TestClass.Test();

            }

            catch (Exception ex)

            {

                Console.WriteLine("Failed with unhandled exception: ");

                Console.WriteLine(ex);

                throw;

            }

        }

    }

​

    public class AssertionFailedException : System.Exception

    {

        public AssertionFailedException() : base() { }

​

        public AssertionFailedException(string s) : base(s) { }

    }

​

    public static class Assert

    {

        public static void IsTrue(bool value)

        {

            IsTrue(value, "failed");

        }

​

        public static void IsTrue(bool value, string message)

        {

            if (value == false)

                throw new AssertionFailedException(message ?? "failed");

        }

    }

}

​