Retrieve data table from JSON response in ASP.NET or C# code?

Question

Get the JSON response from the API request as shown below. I wanted to create a data table from this JSON response. How to retrieve data table from JSON response using C# .net application?

 {
    "status": "success",
    "data": [
        [
            {
                "value": "Department of Information Technology and Communication",
                "key": "POSTED_DEPARTMENT"
            },
            {
                "value": 5800002,
                "key": "APPOINTING_DEPT_ID"
            },
            {
                "value": 170,
                "key": "EMP_CADRE"
            },
            {
                "value": "Department of Information Technology and Communication",
                "key": "APPOINTING_DEPT"
            },
            {
                "value": "RJBI198009003722",
                "key": "UNIQUE_ID"
            },
            {
                "value": 5800002,
                "key": "POSTED_DEPARTMENT_ID"
            },
            {
                "value": 3004216,
                "key": "EMP_ID"
            },
            {
                "value": null,
                "key": "DATE_OF_BIRTH"
            },
            {
                "value": "06 Jan 2018",
                "key": "IPR_SUBMITTED_DATE"
            },
            {
                "value": "Programmer",
                "key": "DESIGNATION"
            },
            {
                "value": null,
                "key": "PROPERTY_YEAR"
            },
            {
                "value": "DINESHARORA",
                "key": "EMPLOYEE_NAME"
            },
            {
                "value": "Mr.MURLI MANOHAR ARORA",
                "key": "FATHER_NAME"
            },
]
],
 "msg": "Data Successfully Retrived"
}

I am using Json.net but data is not coming in proper format. Thanks for instant reply — Prashant Verma, Jan 31 '18 at 07:29
Possible duplicate of [Convert JSON to DataTable](https://stackoverflow.com/questions/11981282/convert-json-to-datatable) — Damien Flury, Jan 31 '18 at 07:48
The JSON is incorrect I think. The data element is array of array. It is very difficult to convert to classes or datatable. Make sure that the JSON response is correct or not. — Sanjeev S, Jan 31 '18 at 07:54

score 0 · Accepted Answer · answered Jan 31 '18 at 10:24

I guess this is one right way to convert your complex JSON response to DataTable. Try the below. Create classes for de-serialize the JSON data

 public class Example
{
    public string status { get; set; }
    public IList<IList<Datum>> data { get; set; }
    public string msg { get; set; }
}

public class Datum
{
    public object value { get; set; }
    public string key { get; set; }
}

I choose the sample JSON format and Try with the code on method

string filter1 =  "{\"status\": \"success\", \"data\": [[ {\"value\": \"RJBI198009003722\",  \"key\": \"UNIQUE_ID\"},  {\"value\": 5800002, \"key\": \"POSTED_DEPARTMENT_ID\"},],[{\"value\": \"Department of Information Technology and Communication\", \"key\": \"POSTED_DEPARTMENT\" }] ],\"msg\":\"Data Successfully Retrived\"}";

        var dat = JsonConvert.DeserializeObject<Example>(filter1);
        var result = new List<Datum>();
        foreach (var item in dat.data)
        {
            for (int i = 0; i < item.Count; i++)
            {
                result.Add(item[i]);
            }

        }
        DataTable table = new DataTable();
        table = ConvertToDataTable<Datum>(result);

The following is the method through which you can convert any list object to datatable.

public static DataTable ConvertToDataTable<T>(IList<T> data)
    {
        PropertyDescriptorCollection properties =
           TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;

    }

The datatable format as shown in below.

Thanks!

Thanks man it saved my job and If i want to show only specific rows then how could it be possible? — Prashant Verma, Jan 31 '18 at 11:21
Sanjeev I want to bind column name with value not default keys and values — Prashant Verma, Jan 31 '18 at 15:14

Retrieve data table from JSON response in ASP.NET or C# code?

1 Answers1