How to convert DataTable to JSON in a special format?

Question

My DataTable looks like the following:

id      Descr           value
CA-AB   Descr1          3
CA-AC   Descr2          4
CA-AD   Descr3          8

With the following code, I was able to generate the following json:

string callback = Newtonsoft.Json.JsonConvert.SerializeObject(table);
byte[] resultBytes = Encoding.UTF8.GetBytes(callback);
return new System.IO.MemoryStream(resultBytes);

This is the result:

[
    {"id": "CA-AB", "Descr": "Descr1", "value": "3"}, 
    {"id": "CA-AC", "Descr": "Descr2", "value": "4"},
    {"id": "CA-AD", "Descr": "Descr3", "value": "8"}
]

But now I need to change the format so it looks like this:

{
  "CA-AB": {
    "Descr": "Descr1",
    "value": 3
  },
  "US-AK": {
    "Descr": "Descr2",
    "value": 4
  },
  "US-AZ": {
    "Descr": "Descr3",
    "value": 8
  }
}

I tried something like this:

var returnData2 = new Json();
returnData2.map = "UpperTuple";
returnData2.areas = result.Tables[0];
string callback = JsonConvert.SerializeObject(returnData2);
byte[] resultBytes = Encoding.UTF8.GetBytes(callback);
return new System.IO.MemoryStream(resultBytes);

public class Json
{
    // Case sensitive vvv to match your Json
    public string map { get; set; }
    public double zoomLevel { get; set; }
    public DataTable areas { get; set; }
    // you can have several constructor methods defined, I show the usage for each below.
    public Json() { }
    public Json(string countryMap, DataTable table, double zoom)
    {
        map = countryMap;
        areas = table;
    }
}

This creates a separate "tuple" before the actual rows, but it's obviously static, so that doesn't help much.

Any help is appreciated.

Hmm, just found an apparent duplicate: **[Convert DataTable to JSON with key per row](https://stackoverflow.com/q/11138035/3744182)**. — dbc, Feb 12 '18 at 08:32

Hasan Fathi · Answer 1 · 2018-02-08T21:02:43.957

1

Using JSON.NET (Newtonsoft.Json.Linq)

var obj = new JObject(
    table.Rows.Cast<DataRow>()
         .Select(r => new JProperty(r["id"].ToString(),
                 new JObject(
                     new JProperty("Descr", r["Descr"].ToString()),
                     new JProperty("value", r["value"].ToString())
                 )
             ))
);

// Convert the JObject to a JSON string
var json = obj.ToString();

edited Feb 08 '18 at 21:02

answered Feb 08 '18 at 20:38

Hasan Fathi

5,610
4
42
60

@rbhat i think this is same you want in a simple way, hope this is helpful for you – Hasan Fathi Feb 10 '18 at 23:03

score 0 · Answer 2 · answered Feb 08 '18 at 20:34

As explained in this link: click here using Json.Net you could do something like the code below (this code is only to show you how I achieved your json request and it do not follow best practices for a production code) :

class Program
{
    static void Main(string[] args)
    {
        DataSet dataset = new DataSet();

        DataTable tableCAAB = new DataTable("CA-AB");
        DataTable tableUSAK = new DataTable("US-AK");
        DataTable tableUSAZ = new DataTable("US-AZ");

        CreateColumns(tableCAAB);
        CreateColumns(tableUSAK);
        CreateColumns(tableUSAZ);

        DataRow newRow = CreateRow(tableCAAB.NewRow(), "Descr1", 3);
        tableCAAB.Rows.Add(newRow);

        newRow = CreateRow(tableUSAK.NewRow(), "Descr2", 4);
        tableUSAK.Rows.Add(newRow);

        newRow = CreateRow(tableUSAZ.NewRow(), "Descr3", 8);
        tableUSAZ.Rows.Add(newRow);

        dataset.Tables.Add(tableCAAB);
        dataset.Tables.Add(tableUSAK);
        dataset.Tables.Add(tableUSAZ);

        dataset.AcceptChanges();

        string json = JsonConvert.SerializeObject(dataset, Formatting.Indented);

        Console.WriteLine(json);

    }

    public static DataRow CreateRow(DataRow newRow, string value1, int value2)
    {
        newRow["Descr"] = value1;
        newRow["value"] = value2;

        return newRow;
    }

    public static void CreateColumns(DataTable table)
    {
        table.Columns.Add("Descr");
        table.Columns.Add("value");
    }

}

The expected result will be:

{
  "CA-AB": [
    {
      "Descr": "Descr1",
      "value": "3"
    }
  ],
  "US-AK": [
    {
      "Descr": "Descr2",
      "value": "4"
    }
  ],
  "US-AZ": [
    {
      "Descr": "Descr3",
      "value": "8"
    }
  ]
}

Hope that helps! Good luck!

score 0 · Answer 3 · answered Feb 12 '18 at 02:28

You can transform datatable to suitable format with help of ExpandoObject

See example below:

var expObject = (IDictionary<string, object>)new ExpandoObject();
foreach (var kv in dt.Rows.Cast<dynamic>()
    .Select(
        r =>
        {
            var kv = new KeyValuePair<string, object>(
                r["id"],
                new {Descr = r["Descr"], value = r["value"]});
            return kv;
        }))
{
    expObject.Add(kv.Key, kv.Value);
} 

var jsonString = JsonConvert.SerializeObject(expObject, Formatting.Indented);
Console.WriteLine(jsonString);

Giving your test data in DataTable, this will print

{
  "CA-AB": {
    "Descr": "Descr1",
    "value": 3
  },
  "CA-AC": {
    "Descr": "Descr2",
    "value": 4
  },
  "CA-AD": {
    "Descr": "Descr3",
    "value": 8
  }
}

How to convert DataTable to JSON in a special format?

3 Answers3