How to return Complex Json Object from C# DataTable

Question

We currently use Newtonsoft.Json to convert a datatable to a json stream.

If my datatable includes the following:

Name    Sales
Joe     10
Mary    20

then the following code will return [{"Name": "Joe", "Sales":10},{"Name": "Mary", "Sales":20}] which is great:

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

But there are times that I need a more complex json stream. Something like this:

{ "map": "USA", "areas":[{"Name": "Joe", "Sales":10},{"Name": "Mary", "Sales":20}]}

The json now includes an extra tuple { "map": "USA", "areas":, and an additional {}.

What does my datatable need to include to get that type of json stream? And what does the C# need to look like?

Can you please add more explanation of what you do when you have only name and sales? — Valerii, Oct 12 '17 at 16:33
Why use a DataTable at all? Why not eliminate it and just use strongly typed objects? — mason, Oct 12 '17 at 16:53
This is being used for a chart, and the chart requires the `{ "map": "USA", "areas":` at the beginning of the `json` stream. — fdkgfosfskjdlsjdlkfsf, Oct 12 '17 at 17:35
@mason, I appreciate your comment, but the question isn't about how to replace the datatable with something else. — fdkgfosfskjdlsjdlkfsf, Oct 12 '17 at 17:36
@rbhat Yeah, it kinda is. A DataTable represents flat data. It doesn't handle hierarchical data very well. A class is much better suited to representing hierarchical data. Plus with classes you gain the benefit of strong typing and less memory usage. If you don't want to use strongly typed classes, you're going to end up manually generating JSON via the other API's offered by JSON.NET. Much more painful than using a strongly typed class to represent your data. — mason, Oct 12 '17 at 17:41
Understood. Would you have an example of how to make a call from a C# class to a tsql stored procedure that will eventually return a `json` stream in the format specified? — fdkgfosfskjdlsjdlkfsf, Oct 12 '17 at 17:49

Justin Herter · Answer 1 · 2017-10-12T19:16:42.953

You would need to define a new class and serialize the entire object like so:

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

Usage:

// Instantiate the object from the class / model
var returnData = new MyNewClass("USA", table);
// or
var returnData2 = new MyNewClass();
returnData2.map = "USA";
returnData2.areas = table;

// Finally serialize your object
var yourJson = JsonConvert.SerializeObject(returnData);
// or
var yourJosn2 = JsonConvert.SerializeObject(returnData2);

@rbhat does this not answer your question? – Justin Herter Oct 12 '17 at 19:13 — Justin Herter, Oct 12 '17 at 19:13

score 0 · Answer 2 · answered Oct 12 '17 at 19:33

If you data source is actually a database (and it looks like it is), there are numerous advantages of using POCOs over DataTable.

One of them is the possibility to use an ORM to easily fetch information in a convenient form. E.g. EntityFramework.

For your particular complex example, the flow could be the following:

1) Define classes to hold the data

  class Area 
   {
       public string Name { get; set; }
       public int Sales { get; set; }
   }

   class Map 
   {
       public string Map { get; set; }
       public List<Area> Areas { get; set; }
   }

2) Populate with data from database

3) Serialize Area with JsonConvert.SerializeObject.

How to return Complex Json Object from C# DataTable

2 Answers2