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
– Anton Maiorov, Commented Aug 27, 2016 at 6:06
@AntonMaiorov Correct. But It may be more fields, so need foreach loop — Priyanka Rathee
– Priyanka Rathee, Commented Aug 27, 2016 at 6:12

bottaio · Accepted Answer · 2016-08-27 06:52:14Z

2

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; }
    }
}

edited Aug 27, 2016 at 6:52

answered Aug 27, 2016 at 6:32

bottaio

5,1233 gold badges21 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Priyanka Rathee Over a year ago

Getting en error as Program.Output is less accessible, any idea?

bottaio Over a year ago

@PrinceRathee Make Output class public :)

Priyanka Rathee Over a year ago

Thanks, it's amazing!

Anton Maiorov · Accepted Answer · 2016-08-27 06:50:37Z

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;
}

Mike · Accepted Answer · 2016-08-27 10:52:55Z

2

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

answered Aug 27, 2016 at 10:52

Mike

515 bronze badges

Comments

STORM · Accepted Answer · 2016-08-27 06:08:47Z

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, 2016 at 6:08

answered Aug 27, 2016 at 6:02

STORM

4,32614 gold badges65 silver badges107 bronze badges

3 Comments

Priyanka Rathee Over a year ago

Just two row added, but it should be 4 as per example above. (see expected output)

STORM Over a year ago

Have you debugeed to see what happens where? Maybe an error occures?

Priyanka Rathee Over a year ago

Checking now, i thought you tested and I'm missing anything.

Valeh Mikayilzadeh · Accepted Answer · 2016-08-27 07:09:03Z

1

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);
        }

edited Aug 27, 2016 at 7:09

answered Aug 27, 2016 at 6:59

Valeh Mikayilzadeh

9291 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

JSON object to Datatable in C#

5 Answers 5

3 Comments

Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related