Convert json respone into datatable

Question

I have a json response from API and I need to store that information in a data table. Response is something like :

{
    "columns": [ 
      "firstName", 
      "lastName", 
      "email", 
      "password",
    ],
    "rows": [
      [
        "Alpha",
        "Tango",
        "AlphaTango@domain.com",
        "123"
      ],
      [
        "Charle",
        "Tango",
        "CharlieTango@domain.com",
        "456"
      ]
    ]
}

I need it to be converted into the format :

firstName lastName email                   password
Alpha     Tango    AlphaTango@domain.com   123
Charle    Tango    CharlieTango@domain.com 456

I did the reverse with the help of below snippet, but am not able to convert json back into data table acceptable format :

JsonConvert.SerializeObject(new {
   columns = dt1.Columns.Cast<DataColumn>().Select(x => x.ColumnName),
   rows = dt1.AsEnumerable().Select(r => r.ItemArray),
});

Any suggestion or pointers will be highly appreciated

You might get some answers from people just looking to gain reputation, but it looks like you've been around SO long enough to know you are supposed to make an attempt and ask specific questions about what is wrong with your code. — Crowcoder, Jan 19 '18 at 13:08

score 1 · Accepted Answer · answered Jan 19 '18 at 13:27

For this case you have to do some manual iterations over your json data. Can you try the code below?

static void Main(string[] args)
{
    string json = "{\"columns\":[\"firstName\",\"lastName\",\"email\",\"password\",],\"rows\":[[\"Alpha\",\"Tango\",\"AlphaTango@domain.com\",\"123\"],[\"Charle\",\"Tango\",\"CharlieTango@domain.com\",\"456\"]]}";
    dynamic jObject = JObject.Parse(json);
    //Get columns of your object
    List<string> columns = JsonConvert.DeserializeObject<List<string>>(jObject.columns.ToString());
    //Get rows of your object
    List<string[]> rows = JsonConvert.DeserializeObject<List<string[]>>(jObject.rows.ToString());

    using (DataTable dt = new DataTable())
    {
        //Create columns
        foreach (string column in columns)
            dt.Columns.Add(new DataColumn(column));

        //Add rows
        foreach (string[] row in rows)
        {
            int columnOrdinal = 0;
            DataRow newRow = dt.NewRow();
            foreach (string value in row)
            {
                newRow.SetField<string>(columnOrdinal, value);
                columnOrdinal++;
            }
            dt.Rows.Add(newRow);
        }

    }
}

Result for the code above:

Hope this helps

Convert json respone into datatable

1 Answers1