Complex Nested JSON Array Conversion to DataTable - C#

Question

I'm completely new to JSON and need to be able to get a my JSON string converted into a DataTable.

Here is my JSON. I have changed about data for security reasons

[
  {
    "uuid": "af9fcfc7-61af-4484-aaa8-7dhcced2f2f79",
    "call_start_time": 1551892096171,
    "call_duration": 1150,
    "created_on": "2019-03-06",
    "cost": 0,
    "call_type": "inbound",
    "from": {
      "uuid": "",
      "type": "number",
      "name": "",
      "nickname": "",
      "number": "+44 7*** ******"
    },
    "to": {
      "uuid": "",
      "type": "number",
      "name": "",
      "nickname": "",
      "number": "+44 **** ******0"
    },
    "answered": true,
    "answered_by": {
      "uuid": "48bj949-e72e-4239-a337-e181a1b45841",
      "type": "sipuser",
      "name": "SipUser",
      "nickname": "Myself",
      "number": "1001"
    },
    "has_recording": true,
    "call_route": "c30e45g0e-3da4-4a67-9a04-27e1d9d31129",
    "is_fax": false
  },
  {
    "uuid": "f62kmop2b-f929-4afc-8c05-a8c1bc43225d",
    "call_start_time": 1551890795202,
    "call_duration": 12,
    "created_on": "2019-03-06",
    "cost": 0.012,
    "call_type": "outbound",
    "from": {
      "uuid": "68a50328-f5b0-4c5e-837c-667ea50878f3",
      "type": "sipuser",
      "name": "Spare",
      "nickname": "Spare",
      "number": "1011"
    },
    "to": {
      "uuid": "",
      "type": "number",
      "name": "",
      "nickname": "",
      "number": "+44 *** *** ****"
    },
    "answered": true,
    "answered_by": {
      "uuid": "",
      "type": "number",
      "name": "",
      "nickname": "",
      "number": "+44 ***1*****0"
    },
    "has_recording": false,
    "call_route": "",
    "is_fax": false
  },
  {
    "uuid": "b1b495c4-ecf6-44c0-8020-28c9eddc7afe",
    "call_start_time": 1551890780607,
    "call_duration": 10,
    "created_on": "2019-03-06",
    "cost": 0.012,
    "call_type": "outbound",
    "from": {
      "uuid": "68a50328-f5b0-4c5e-837c-667ea50878f3",
      "type": "sipuser",
      "name": "Spare",
      "nickname": "Spare",
      "number": "1011"
    },
    "to": {
      "uuid": "",
      "type": "number",
      "name": "",
      "nickname": "",
      "number": "+44 *** *** ****"
    },
    "answered": true,
    "answered_by": {
      "uuid": "",
      "type": "number",
      "name": "",
      "nickname": "",
      "number": "+44 *** *** ****"
    },
    "has_recording": false,
    "call_route": "",
    "is_fax": false
  }
]

The way I want it presented needs to be similar to the way this website presents the datatable

https://konklone.io/json/

I've been all of the web now and am starting to run out of options. I did try looking into creating it with classes however that wasn't successful. I have also tried all of the following examples (plus others)

https://www.code-sample.com/2017/04/convert-json-to-datatable-asp-net-c.html

Import Complex JSON file to C# dataTable

Convert JSON to DataTable

https://www.codeproject.com/Questions/590838/convertplusJSONplusstringplustoplusdatatable

Even if this goes into a DataSet and then I sort out the tables from there. Any help at all will be much appreciated.

Edit

I will explain why this is a little bit different from the assumed duplicate question located here

Convert JSON to DataTable

The answer to this question doesn't seem to be taking into account that I have nested JSON's that I need to get access to. I have tried it and I still do not get any of the from/number fields and the to/number fields. I will admit that my question is a extention to this other duplicate question

Why were the solutions you tried not successful? What didn't work? — frido, Mar 06 '19 at 20:59
Some of the solutions I tried didn't include all of the columns, I pressume this might be down to the column headers being similer (e.g. to/number & from/number may have been conflicting so I was only getting the to/number column). Other stuff that I tried kept coming back with a deserilization exception message, which for the life of me, I couldn't crack (e.g. Newtonsoft.Json.JsonSerializationException: 'Additional text found in JSON string after finishing deserializing object. Path '[0].uuid', line 3, position 11.') — Flakie, Mar 06 '19 at 21:07
Possible duplicate of [Convert JSON to DataTable](https://stackoverflow.com/questions/11981282/convert-json-to-datatable) — Ňɏssa Pøngjǣrdenlarp, Mar 06 '19 at 21:25

Rahul Sharma · Accepted Answer · 2019-03-06T22:35:43.947

Ok so here is the code to parse your JSON structure into a C# List. Once you have this list, you can convert it to a DataTable using the methods that you have researched. I have created a sample Data Table based on your JSON structure.

Your model would be:

public class JsonInfo
{
    public string uuid { get; set; }
    public string call_start_time { get; set; }
    public string call_duration { get; set; }
    public string created_on { get; set; }
    public string cost { get; set; }
    public string call_type { get; set; }
    public string answered { get; set; }
    public string has_recording { get; set; }
    public string call_route { get; set; }
    public string is_fax { get; set; }
    public From from { get; set; }
    public To to { get; set; }
    public AnsweredBy answered_by { get; set; }
}

public class From
{
    public string uuid { get; set; }
    public string type { get; set; }
    public string name { get; set; }
    public string nickname { get; set; }
    public string number { get; set; }
}

public class To
{
    public string uuid { get; set; }
    public string type { get; set; }
    public string name { get; set; }
    public string nickname { get; set; }
    public string number { get; set; }
}

public class AnsweredBy
{
    public string uuid { get; set; }
    public string type { get; set; }
    public string name { get; set; }
    public string nickname { get; set; }
    public string number { get; set; }
}
//Create a model to show your information
public class  DisplayJsonInfo
    {
        public string uuid { get; set; }
        public string call_start_time { get; set; }
        public string call_duration { get; set; }
        public string created_on { get; set; }
        public string cost { get; set; }
        public string from_uuid { get; set; }
        public string from_type { get; set; }
        public string from_name { get; set; }
        public string from_nickname { get; set; }
    }

To serialize your JSON into created Model and then create your DataTable:

var json = "[{\"uuid\":\"af9fcfc7-61af-4484-aaa8-7dhcced2f2f79\",\"call_start_time\":1551892096171,\"call_duration\":1150,\"created_on\":\"2019-03-06\",\"cost\":0,\"call_type\":\"inbound\",\"from\":{\"uuid\":\"\",\"type\":\"number\",\"name\":\"\",\"nickname\":\"\",\"number\":\"+44 7*** ******\"},\"to\":{\"uuid\":\"\",\"type\":\"number\",\"name\":\"\",\"nickname\":\"\",\"number\":\"+44 **** ******0\"},\"answered\":true,\"answered_by\":{\"uuid\":\"48bj949-e72e-4239-a337-e181a1b45841\",\"type\":\"sipuser\",\"name\":\"SipUser\",\"nickname\":\"Myself\",\"number\":\"1001\"},\"has_recording\":true,\"call_route\":\"c30e45g0e-3da4-4a67-9a04-27e1d9d31129\",\"is_fax\":false},{\"uuid\":\"f62kmop2b-f929-4afc-8c05-a8c1bc43225d\",\"call_start_time\":1551890795202,\"call_duration\":12,\"created_on\":\"2019-03-06\",\"cost\":0.012,\"call_type\":\"outbound\",\"from\":{\"uuid\":\"68a50328-f5b0-4c5e-837c-667ea50878f3\",\"type\":\"sipuser\",\"name\":\"Spare\",\"nickname\":\"Spare\",\"number\":\"1011\"},\"to\":{\"uuid\":\"\",\"type\":\"number\",\"name\":\"\",\"nickname\":\"\",\"number\":\"+44 *** *** ****\"},\"answered\":true,\"answered_by\":{\"uuid\":\"\",\"type\":\"number\",\"name\":\"\",\"nickname\":\"\",\"number\":\"+44 ***1*****0\"},\"has_recording\":false,\"call_route\":\"\",\"is_fax\":false},{\"uuid\":\"b1b495c4-ecf6-44c0-8020-28c9eddc7afe\",\"call_start_time\":1551890780607,\"call_duration\":10,\"created_on\":\"2019-03-06\",\"cost\":0.012,\"call_type\":\"outbound\",\"from\":{\"uuid\":\"68a50328-f5b0-4c5e-837c-667ea50878f3\",\"type\":\"sipuser\",\"name\":\"Spare\",\"nickname\":\"Spare\",\"number\":\"1011\"},\"to\":{\"uuid\":\"\",\"type\":\"number\",\"name\":\"\",\"nickname\":\"\",\"number\":\"+44 *** *** ****\"},\"answered\":true,\"answered_by\":{\"uuid\":\"\",\"type\":\"number\",\"name\":\"\",\"nickname\":\"\",\"number\":\"+44 *** *** ****\"},\"has_recording\":false,\"call_route\":\"\",\"is_fax\":false}]";
var data = JsonConvert.DeserializeObject<List<JsonInfo>>(json);
//Call your helper method to convert
CreateDataTable cl = new CreateDataTable();
DataTable dt = new DataTable();
DisplayJsonInfo show = new DisplayJsonInfo();
List<DisplayJsonInfo> showInfo = new List<DisplayJsonInfo>();

        foreach(var value in data)
        {
            showInfo.Add(new DisplayJsonInfo
            {
                call_duration = value.call_duration,
                call_start_time = value.call_start_time,
                cost = value.cost,
                uuid = value.uuid,
                created_on = value.created_on,
                from_uuid = value.from.uuid,
                from_name = value.from.name,
                from_type = value.from.type,
                from_nickname=value.from.nickname
            });
        }
        dt = cl.ToDataTable(showInfo);

Once you have this use a helper extension like ToDataTable() as mentioned here: Convert JSON to DataTable

Edit:

So after parsing out the information and using this helper to convert your List to DataTable:

using System;
using System.Text;
using System.IO;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.ComponentModel;

public class CreateDataTable
{
public DataTable ToDataTable<T>(IList<T> data)
    {
        PropertyDescriptorCollection props =
        TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        for (int i = 0; i < props.Count; i++)
        {
            PropertyDescriptor prop = props[i];
            table.Columns.Add(prop.Name, prop.PropertyType);
        }
        object[] values = new object[props.Count];
        foreach (T item in data)
        {
            for (int i = 0; i < values.Length; i++)
            {
                values[i] = props[i].GetValue(item);
            }
            table.Rows.Add(values);
        }
        return table;
    }
}

Your DataTable would look something like this:

Edit Description: I have created a model only to show your required data into the DataTable. Since I have parsed the JSON data into my C# list, I can access the data shown in my code using foreach loop. Then I simply get the data what I required and then create my Data Table.

Cheers.

Thanks for the help, however there still seems to be a slight issue. I'm still not getting details about the 'from' and 'to' parts of the JSON. I am however getting columns called 'from' and 'to' which is further than what I was before. — Flakie, Mar 06 '19 at 21:56
I do not quite understand what you mean here? In your model, the To, From and AnsweredBy are objects that have data in them parsed during deserialization. You would need to handle their data and display in your DataTable. — Rahul Sharma, Mar 06 '19 at 21:58
That DataTable that you have in your edit is exactly what I got. What I need is that DataTable to contain infomation about the from/number and answered_by/number. I think this website will show best my intentions https://konklone.io/json/. You say I need to handle my Data and Display in my DataTable. I'm not too sure what you mean by this — Flakie, Mar 06 '19 at 22:14
Please check updated answer to match your requirements. Please let me know if it suffices to your need. Thanks — Rahul Sharma, Mar 06 '19 at 22:36
That's perfect! That is exactly what I need. Apologies for the abundance of C# knowledge, this was definitely a learning curve for myself — Flakie, Mar 06 '19 at 22:48

Complex Nested JSON Array Conversion to DataTable - C#

1 Answers1