Converting API call Json URL string (Json Objects) into C# Datatable (Without using any constructive class with getters and setters or poco)

Question

I'm new to Json API call string parsing in C#, I have a requirement where I need to get Json string from a API call and convert that into C# data-table then show the results in a web page using Asp.Net GridView.

I tried several ways. I faced different type of issues in each approach only succeed with my Approach 4(mentioned below). But I don't want to create a separate class (With getter and setters) and converting Json string into object of that class. As I have more than 150 fields for each record in my Json file and also object names are dynamic they are generated randomly. I don't want one to one mapping (Class field to Json field).

Here is my Json file format,

{
"R_aabdcDgjZwp0ch":{ Record 1 information key value pair } // Here R_aabdcDgjZwp0ch are randomly generated value
"R_lkYnksdY6qXaPb":{ Record 2 information key value pair } //R_lkYnksdY6qXaPb random
………
"R_7GhjsnB29xWBjp":{ Record n information key value pair }//R_7GhjsnB29xWBjp not fixed value
}

Sample Records from Json string:

{
"R_3dSKpqkb0JuH0TW":{"ResponseSet":"Default Response Set","Name":"John, Smith","ExternalDataReference":"811221273","EmailAddress":"smithaa@gmail.com","IPAddress":"123.232.12.21","Status":"","StartDate":"2015-07-06 11:10:26","EndDate":"2015-07-06 11:10:55","Finished":"1","RecipientEmail":"smithaa@gmail.com","RecipientLastName":"John","RecipientFirstName":"Smith","MI":"Mia","EntryTerm":"","Classification":"","Type":"","MajorCode":"","Major":"","DeptCode":"","Dept":"","College":"","Age":"","Ethnicity":"","Gender":"","CB1":"","PIDM":"71121027","Military":"","OrientationDate":"4\/7\/2016","H1":1,"H2":1,"H3":2,"H4":2,"H5":"","Q1":"","Q2":"","Q3_1":"","Q3_2":"","Q3_3":"","Q3_4":"","Q3_5":"","Q3_6":"","Q3_7":"","Q3_7_TEXT":"","Q4_1":"","Q4_2":"","Q4_3":"","Q4_4":"","Q4_5":"","Q4_6":"","Q4_7":"","Q4_8":"","Q4_9":"","Q4_10":"","Q4_11":"","Q4_12":"","Q4_12_TEXT":"","Q5":"","Q5_TEXT":"","Q6_1":"","Q6_2":"","Q6_3":"","Q7":"","Q8":"","Q9":"","Q10_1":"","Q10_2":"","Q10_3":"","Q10_4":"","Q11_1":"","Q11_2":"","Q11_3":"","Q11_4":"","Q12_1":"","Q12_2":"","Q12_3":"","Q12_4":"","Q13":"","Q13_TEXT":"","Q14":"","Q14_TEXT":"","Q15_1":"","Q15_2":"","Q15_3":"","Q15_4":"","Q15_5":"","Q15_6":"","Q15_7":"","Q15_8":"","Q16_1":"","Q16_2":"","Q16_3":"","Q16_4":"","Q16_5":"","Q16_6":"","Q16_7":"","Q16_8":"","Q17_1":"","Q17_2":"","Q17_3":"","Q17_4":"","Q17_5":"","Q17_6":"","H6":"","Q18_1":"","Q18_2":"","Q18_3":"","Q19_1":"","Q19_2":"","Q19_3":"","Q19_4":"","Q20":"","Q21_1":"","Q21_2":"","Q22_1":"","Q22_2":"","Q23_1":"","Q23_2":"","Q23_3":"","Q23_4":"","Q24":"","Q24_TEXT":"","Q25":"","Q26":"","Q27":"","Q28":"","H7":"","Q29_1":"","Q29_2":"","Q29_3":"","Q29_4":"","Q29_5":"","Q30":"","Q30_TEXT":"","Q31":"","Q31_TEXT":"","Q32":"","Q33_1":"","Q33_2":"","Q33_3":"","Q33_4":"","Q33_5":"","Q33_6":"","Q33_6_TEXT":"","Q34":"","Q34_TEXT":"","Q35":"","Q35_TEXT":"","Q36_1":"","Q36_2":"","Q36_3":"","Q36_4":"","Q36_5":"","Q36_6":"","Q36_7":"","Q36_7_TEXT":"","Q37":"","H8":1,"H9":1},

"R_1kYrTV300hwdvPP":{"ResponseSet":"Default Response Set","Name":"priya, Sam","ExternalDataReference":"8901212","EmailAddress":"sam12@gmail.com","IPAddress":"123.232.12.21","Status":"","StartDate":"2015-07-06 11:14:18","EndDate":"2015-07-06 11:14:59","Finished":"1","RecipientEmail":"sam@gmail.com","RecipientLastName":"sam","RecipientFirstName":"priya","MI":"","EntryTerm":"","Classification":"","Type":"","MajorCode":"","Major":"","DeptCode":"","Dept":"","College":"","Age":"","Ethnicity":"","Gender":"","CB1":"","PIDM":"71121028","Military":"","OrientationDate":"6\/27\/2016","H1":1,"H2":1,"H3":2,"H4":2,"H5":"","Q1":"","Q2":"","Q3_1":"","Q3_2":"","Q3_3":"","Q3_4":"","Q3_5":"","Q3_6":"","Q3_7":"","Q3_7_TEXT":"","Q4_1":"","Q4_2":"","Q4_3":"","Q4_4":"","Q4_5":"","Q4_6":"","Q4_7":"","Q4_8":"","Q4_9":"","Q4_10":"","Q4_11":"","Q4_12":"","Q4_12_TEXT":"","Q5":"","Q5_TEXT":"","Q6_1":"","Q6_2":"","Q6_3":"","Q7":"","Q8":"","Q9":"","Q10_1":"","Q10_2":"","Q10_3":"","Q10_4":"","Q11_1":"","Q11_2":"","Q11_3":"","Q11_4":"","Q12_1":"","Q12_2":"","Q12_3":"","Q12_4":"","Q13":"","Q13_TEXT":"","Q14":"","Q14_TEXT":"","Q15_1":"","Q15_2":"","Q15_3":"","Q15_4":"","Q15_5":"","Q15_6":"","Q15_7":"","Q15_8":"","Q16_1":"","Q16_2":"","Q16_3":"","Q16_4":"","Q16_5":"","Q16_6":"","Q16_7":"","Q16_8":"","Q17_1":"","Q17_2":"","Q17_3":"","Q17_4":"","Q17_5":"","Q17_6":"","H6":"","Q18_1":"","Q18_2":"","Q18_3":"","Q19_1":"","Q19_2":"","Q19_3":"","Q19_4":"","Q20":"","Q21_1":"","Q21_2":"","Q22_1":"","Q22_2":"","Q23_1":"","Q23_2":"","Q23_3":"","Q23_4":"","Q24":"","Q24_TEXT":"","Q25":"","Q26":"","Q27":"","Q28":"","H7":"","Q29_1":"","Q29_2":"","Q29_3":"","Q29_4":"","Q29_5":"","Q30":"","Q30_TEXT":"","Q31":"","Q31_TEXT":"","Q32":"","Q33_1":"","Q33_2":"","Q33_3":"","Q33_4":"","Q33_5":"","Q33_6":"","Q33_6_TEXT":"","Q34":"","Q34_TEXT":"","Q35":"","Q35_TEXT":"","Q36_1":"","Q36_2":"","Q36_3":"","Q36_4":"","Q36_5":"","Q36_6":"","Q36_7":"","Q36_7_TEXT":"","Q37":"","H8":1,"H9":1}
}

I have tried several ways to parse/consume Json url string to Datatable. I’m receiving below errors.

Approach 1: Using Json.Net and directly converting from Json string to Data Table using Newtonsoft.Json. JsonConvert.DeserializeObject

Sample Code:

string url ="test.com/json...etc"; //Here actual url to call api
var json_data = string.Empty;
// attempt to download JSON data as a string
json_data = w.DownloadString(url); // we are passing API url here
DataTable items = JsonConvert.DeserializeObject<DataTable>(json_data); // Exception coming here

Exception: Newtonsoft.Json.JsonSerializationException: Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path '', line 1, position 1.

Approach 2: I have used http://json2csharp.com/# (Which Converts Json input file/Json url into constructive class which will have getters and setters with Root Object to access data from sub classes). But I end up with an exception. However I don't want this approach.

Exception: Newtonsoft.Json.JsonSerializationException: Cannot deserialize the current JSON object (e.g. {"name":"value"}) into type 'System.Collections.Generic.List`1[matrix+RANFpZfdGjZwp0ch]' because the type requires a JSON array (e.g. [1,2,3]) to deserialize correctly. To fix this error either change the JSON to a JSON array (e.g. [1,2,3]) or change the deserialized type so that it is a normal .NET type (e.g. not a primitive type like integer, not a collection type like an array or List) that can be deserialized from a JSON object. JsonObjectAttribute can also be added to the type to force it to deserialize from a JSON object. Path 'R_ANFpZfdGjZwp0ch', line 1, position 21.

Approach 3: I have tried using below. But ended with an exception.

DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));

Exception: Newtonsoft.Json.JsonSerializationException: Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path '', line 1, position 1. Tried below to solve this Approach 3 issue. But didn't work.

1. Newtonsoft.Json JsonConvert To Datatable
2. http://www.codeproject.com/Questions/817608/Newtonsoft-Json-JsonConvert-To-Datatable
3. Converting JSON string to DataTable
4. Parsing with Json.NET: "Unexpected token: StartObject"

Approach 4: With this approach I succeed using constructive class with getters and setters.

string url ="test.com/json...etc"; //Here actual url to call api
using (var w = new WebClient())
            {
                var json = string.Empty;
                // attempt to download JSON data as a string
                try
                {
                    json = w.DownloadString(url);
                }
                catch (Exception) { }


                 User obj = new User(json);

                Response.Write(obj.name);
            }


public class User
{
        /********* Used from https://stackoverflow.com/questions/2246694/how-to-convert-json-object-to-custom-c-sharp-object **********/
        public User(string json)
        {
            JObject jObject = JObject.Parse(json);
            JToken jUser = jObject["R_XYZanOp0ch"]; //R_XYZanOp0ch this value is randomly generated. I gave it constant to check for one record. In actual Json file there are so many randomly generated object names exist.
            name = (string)jUser["Name"];
            email = (string)jUser["Email"];
            ExternalDataReference = (string)jUser["NumberReference"];
        }

    public string name { get; set; }
    public string ExternalDataReference { get; set; } // student ID
    public string email { get; set; }



}

I got some result using this Approach 4. But problem is that we have more than 150 fields and we don't want to make getters and setters for each field and object names are dynamic not fixed. Unfortunately we need all fields data.

Below are my references I used for above approaches.

1. Convert Json String to C# Object List
2. Convert JSON to DataTable
3. How to convert json into datatable?

Thanks for reading. Sorry for my long text. Don't consider this post as duplicate, As I tried all ways but still I didn't get desired outcome, hence posting here. Can anyone help me or guide me as per my Json string structure, do I need to change any of code? Any samples or reading notes would be helpful.

Thank you.

Update : I tried both, I got System.FormatException: Input string was not in a correct format exception at array.ToObject(); please let me know If I miss any step in between.

var obj = JObject.Parse(json);
var array = new JArray(obj.Values());
//Response.Write("<br/>array[0]" + array[0].ToString()); // I could able to view the record 1 data 
var dt = array.ToObject<DataTable>(); // Having issue here.

Answer 1

Json.NET has a built-in converter for DataTable. It formats the table as an array, like so:

[
    {
        "Column1Name" : value11,
        "Column2Name" : value21
    },
    {
        "Column1Name" : value12,
        "Column2Name" : value22
    }, 
    // And so on
}

What you have is a dictionary with random keys, not an array, so you need to transform your JSON to an array before deserialization. This can be done with LINQ to JSON. If you do not need the random key names, you can do:

var obj = JObject.Parse(json);
var array = new JArray(obj.Values());
var dt = array.ToObject<DataTable>();

If you need the random key names, you could add them as a column to the DataTable like so:

var obj = JObject.Parse(json);
string keyColumnName = "__key";
var query = from p in obj.Properties()
            select new JObject(p.Value.OfType<JProperty>().Concat(new [] { new JProperty(keyColumnName, p.Name) }));
var array = new JArray(query);
var dt = array.ToObject<DataTable>();

Sample fiddle.

Answer 2

Thank you dbc. I appreciate your input it helped me to figure out middle layer (Converting Json to Array) in between Json to DataTable.

I modified your code little bit and used data-table conversion method call from https://stackoverflow.com/a/24339121/4425471

At movement we are ignoring the Random Key. Finally I got my desired outcome. I can able to convert Json to DataTable and then to the GridView.

Here is my final code. Thank you SO.

 string json = "test.com/json..."; // actual API call url
 DataTable dt = toDataTable(json);
 GridView1.DataSource = dt;
 GridView1.DataBind();



public static DataTable toDataTable(string json)
        {
            var result = new DataTable();

            var obj = JObject.Parse(json);

            var jArray = new JArray(obj.Values());
            //Initialize the columns
            foreach (var row in jArray)
            {
                foreach (var jToken in row)
                {
                    var jproperty = jToken as JProperty;

                    if (jproperty == null) continue;
                    if (result.Columns[jproperty.Name] == null)
                        result.Columns.Add(jproperty.Name,typeof(string));
                }
            }
            foreach (var row in jArray)
            {
                var datarow = result.NewRow();
                foreach (var jToken in row)
                {
                    var jProperty = jToken as JProperty;
                    if (jProperty == null) continue;
                    datarow[jProperty.Name] = jProperty.Value.ToString();
                }
                result.Rows.Add(datarow);
            }

            return result;
        }