CODEWITHSUNDEEP
c#jsondatatable
Priyanka Rathee
Priyanka Rathee

Reputation: 1075

JSON object to Datatable in C#

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

Upvotes: 1

Views: 29180

Answers (5)

Mike
Mike

Reputation: 51

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

Upvotes: 2

Valeh Mikayilzadeh
Valeh Mikayilzadeh

Reputation: 919

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

Upvotes: 1

bottaio
bottaio

Reputation: 5093

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

Upvotes: 2

Anton Maiorov
Anton Maiorov

Reputation: 183

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

Upvotes: 2

STORM
STORM

Reputation: 4331

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

Upvotes: 1

Related Questions