How to properly create a JSON file in Web API

Question

Following DataTable Data (The "|" is the separator to match respective Col3 to Col4/Col5):

Col1        Col2        Col3            Col4                                Col5
John        Doe         12              156-345                             792-098
Mike        Keller      12|15           145-394|909-203                     156-323|121-444,134-232
Hanes       Wara        34|12|18        180-655,202-175|123-654|118-000     121-343|654-222|109-220

I currently have a custom List which just displays the data like this in the API call:

[HttpGet]
public System.Web.Mvc.JsonResult RTData()
{
    //...call SQL to retrieve the data and populate the {custom List}
    //return...
    return new System.Web.Mvc.JsonResult { Data = {custom List} };
}

This is what I get (in Browser Console) when I call the API from JQuery:

Object
    ContentEncoding:null
    ContentType:null
    Data:Array(12)
        [0 … 11]
        length:12
        __proto__:Array(0)
    JsonRequestBehavior:1
    MaxJsonLength:null
    RecursionLimit:null
    __proto__:Object

Each result in the Data is like this:

Data:Array(12)
    [0 … 11]
    0:
        Col1: "John"
        Col2: "Doe"
        Col3: "12"
        Col4: "156-345"
        Col5: "792-098"
    1:
        Col1: "Mike"
        Col2: "Keller"
        Col3: "12|15"
        Col4: "145-394|909-203  "
        Col5: "156-323|121-444,134-232"

Desired output in JSON:

{
    "Col1": "John",
    "Col2": "Doe",
    "Col3Combined": [
        {
            "Col3": "12",
            "Col4Combined": [
                {
                    "Col4": "156-345"
                }
            ],
            "Col5Combined": [
                {
                    "Col5": "792-098"
                }
            ]
        }
    ]
},
{
    "Col1": "Mike",
    "Col2": "Keller",
    "Col3Combined": [
        {
            "Col3": "12",
            "Col4Combined": [
                {
                    "Col4": "145-394"
                }
            ],
            "Col5Combined": [
                {
                    "Col5": "156-323"
                }
            ]
        },
        {
            "Col3": "15",
            "Col4Combined": [
                {
                    "Col4": "909-203"
                }
            ],
            "Col5Combined": [
                {
                    "Col5": "121-444",
                    "Col5": "134-232"
                }
            ]
        }
    ]
}...//more data

How can I achieve the JSON format in the API call?

Class I came up with which should suffice?

public class RootObject
{
    public string col1 { get; set; }
    public string col2 { get; set; }
    public List<col3data> col3 { get; set; }
}

public class col3data
{
    public string col3d { get; set; }
    public List<col4data> col4d { get; set; }
    public List<col5data> col5d { get; set; }
}

public class col4data
{
    public string col4 { get; set; } //since col4 can also have comma separated values within each entry, as seen for Hanes, should this be a list too?
}

public class col5data
{
    public string col5 { get; set; } //since col5 can also have comma separated values within each entry, as seen for Mike, should this be a list too?
}

Something like this also:

public class RootObject
{
    public string col1 { get; set; }
    public string col2 { get; set; }
    public List<col3data> col3 { get; set; }
}

public class col3data
{
    public string col3d { get; set; }
    public List<col4data> col4d { get; set; }
    public List<col5data> col5d { get; set; }
}

public class col4data
{
    public List<col4subdata> col4sub { get; set; }
}

public class col4subdata
{
    public string col4_1 { get; set; }
    public string col4_2 { get; set; }
}

public class col5data
{
    public List<col5subdata> col5sub { get; set; }
}

public class col5subdata
{
    public string col5_1 { get; set; }
    public string col5_2 { get; set; }
}

I guess now I would have to iterate through each row and add to the RootObject class and create a JSON from it. Can I have some assistance with that?

Answer 1

You need to use JavascriptSerializer.

JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(lstCustom));

In the JQuery function when you call the API, if you use Console.log(response.data), you should see the custom list in JSON format.

You will have to create a class which has properties matching your columns in the resultset.

class Custom
{
    public string Col1 {get; set;}
    ....
    public List<int> Col3 {get; set;}
    ....
}

And then iterate over the columns in your result set to construct the custom list object (lstCustom). Use that with the js.Serialize() like above.

Answer 2

I believe the issue is that your list is not serialized and contenttype is null so the browser is unsure how to treat it. See the answer to this, hopefully this helps you too: Return a JSON string explicitly from Asp.net WEBAPI?