JSON object to Datatable in C#

Question

I've the following c# Class

public class Output
        {
            public string name { get; set; }
            public string[] results { get; set; }
        }

Which results in below output after execution

[
  {
    "name": "Field1",
    "results": [
      "One",
      "Two",
      "Three"
    ]
  },
  {
    "name": "Field2",
    "results": [
      "One",
      "Two",
      "Three",
      "Four"
    ]
  }
]

Is there any utility or library which can convert my JSON into DataTable. I checked the JSON.net example but that require the JSON must be in below format to convert that into table

string json = @"[{"Field1" : "One", "Field2": "One"}
{"Field1" : "Two", "Field2": "Two"}
{"Field1" : "Three", "Field2": "Three"}
{"Field1" : "", "Field2": "Four"}];

var table = JsonConvert.DeserializeObject<DataTable>(json);

Also, Any clue if my JSON can be converted in the format needs for JsonConvert.DeserializeObject

It's not duplicate, the question marked in duplicate discusses the best way instead the complex JSON format example i provided here.

Expected Output

Field1,  Field2
One, One
Two, Two
Three, Three
null,Four

What structure should your DataTable have? From your example I conclude you want 2 columns: Field1 & Field2, correct? — Anton Maiorov, Aug 27 '16 at 06:06
@AntonMaiorov Correct. But It may be more fields, so need foreach loop — Priyanka Rathee, Aug 27 '16 at 06:12

bottaio · Accepted Answer · 2016-08-27T06:52:14.700

JsonConver.DeserializeObject does not provide you with an easy way to achieve your goal. It would be much easier to write a simple parser from a list of Outputs to a DataTable. I provide you with code:

public static class Helper
{
    public static DataTable ToDataTable(this List<Program.Output> list)
    {
        var dt = new DataTable();

        // insert enough amount of rows
        var numRows = list.Select(x => x.results.Length).Max();
        for (int i = 0; i < numRows; i++)
            dt.Rows.Add(dt.NewRow());

        // process the data
        foreach (var field in list)
        {
            dt.Columns.Add(field.name);
            for (int i = 0; i < numRows; i++)
                // replacing missing values with empty strings
                dt.Rows[i][field.name] = i < field.results.Length ? field.results[i] : string.Empty; 
        }

        return dt;
    }
}

And the example of usage:

public class Program
{
    static void Main(string[] args)
    {
        var s = "[{\"name\": \"Field1\", \"results\": [\"One\", \"Two\", \"Three\"]}, {\"name\": \"Field2\", \"results\": [\"One\", \"Two\", \"Three\", \"Four\"]}]";
        var o = JsonConvert.DeserializeObject<List<Output>>(s);
        // convert the list to a DataTable
        var dt = o.ToDataTable();
    }

    public class Output
    {
        public string name { get; set; }
        public string[] results { get; set; }
    }
}

Getting en error as Program.Output is less accessible, any idea? — Priyanka Rathee, Aug 27 '16 at 06:45

Anton Maiorov · Answer 2 · 2016-08-27T06:50:37.347

I suggest to convert your JSON back to array of Output objects. You can use JsonDataContractSerializer (requires annotations) or JavascriptSerializer (requires referencing System.Web.Extensions) for that purpose.

Having an array of Output objects you can do the following:

DataTable ConstructDataTable(Output[] outputArray)
{
  var table = new DataTable();
  var columnNames = outputArray.Select(i => i.name).Distinct().ToArray();
  var rows = outputArray.SelectMany(i => i.result).Distinct().ToArray();
  foreach(var cn in columnNames)
     table.Columns.Add(cn, typeof(string));
  foreach(var r in rows)
  {
    object[] values = new object[columnNames.Length];
    for (int i = 0; i < columnNames.Length; i++)
    {
      values[i] = outputArray.First(i => i.name == columnNames[i]).results.FirstOrDefault(i => i == r);
    }
    table.Rows.Add(values);
  }
  return table;
}

score 2 · Answer 3 · answered Aug 27 '16 at 10:52

2

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

answered Aug 27 '16 at 10:52

Mike

51
5

STORM · Answer 4 · 2016-08-27T06:08:47.857

1

What you could do is to convert the JSON data back to a List of Object like

JsonConvert.DeserializeObject<List<CustomerJson>>(json);

and then convert it to a DataTable with a converter method

public static DataTable ToDataTable<T>(this 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;        
}

edited Aug 27 '16 at 06:08

answered Aug 27 '16 at 06:02

STORM

4,005
11
49
98

Just two row added, but it should be 4 as per example above. (see expected output) – Priyanka Rathee Aug 27 '16 at 06:30
Have you debugeed to see what happens where? Maybe an error occures? – STORM Aug 27 '16 at 06:32
Checking now, i thought you tested and I'm missing anything. – Priyanka Rathee Aug 27 '16 at 06:33

Valeh Mikayilzadeh · Answer 5 · 2016-08-27T07:09:03.943

used Referances :

  using Newtonsoft.Json;
  using System.Collections.Generic;
  using System.Data;
  using System.IO;
  using FastMember;

Your Class:

  public class Output
  {
        public string name { get; set; }
        public string[] results { get; set; }
  }

And :

        var s = "[{\"name\": \"Field1\", \"results\": [\"One\", \"Two\", \"Three\"]}, {\"name\": \"Field2\", \"results\": [\"One\", \"Two\", \"Three\", \"Four\"]}]";
        List<Output> data = JsonConvert.DeserializeObject<List<Output>>(s);

        DataTable dt = new DataTable(); 
        using (var reader = ObjectReader.Create(data))
        {
            dt.Load(reader);
        }

JSON object to Datatable in C#

5 Answers5

Linked