Formatting JSON from SQL Table Results in C#

Question

Im trying to format JSON in c# in the following manner. Lets say I have the following table

col1 col2 col3 col4
comA 1 2 3
comB 4 5 6
comC 7 8 9


I would like my JSON Output to be like so

[{ name: 'comA', data: [1,2,3]
}, { name: 'comB', data: [4,5,6] }, { name: 'comC', data: [7,8,9] }]

I have the following code

public class ChartLoc
{
    public string Category { get; set; }
    public string Data{ get; set; }
}

public void myFunc(){
    using (SqlConnection con = new SqlConnection(ConnectionString)
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("select * from table", con))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {

                List<String> _Category = new List<String>();
                List<String> _Data = new List<String>();

                while (reader.Read())
                {


                    _Data.Add(reader["col2"].ToString() + ',' + reader["col3"].ToString() + ',' + reader["col4"].ToString());

                    if (reader["store"] != DBNull.Value) _Category.Add(reader["col1"].ToString());
                }
                JavaScriptSerializer jss = new JavaScriptSerializer();
                cl.Category = jss.Serialize(_Category); 
                cl.Data = jss.Serialize(_Data);
            }
        }
    }         
}

although this output will give me

cl.Category = ['comA','comB','comC'] cl.Data = ['1,2,3','4,5,6','7,8,9']

Answer 1

If you want to have output like [{ name: 'comA', data: [1,2,3] }, { name: 'comB', data: [4,5,6] }, { name: 'comC', data: [7,8,9] }], you will need to serialize your object differently. Since you want a list of numbers in your JSON and not a string, you will need to represent the numbers as a List in your class definition. Something like this should work:

[Serializable]
public class ChartLoc
{
    public string Category { get; set; }
    public List<int> Data { get; set; }
}

public string myFunc()
{
    string jsonString = "";
    using (SqlConnection con = new SqlConnection(ConnectionString)
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("select * from table", con))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                List<ChartLoc> _ChartLoc = new List<ChartLoc>();

                while (reader.Read())
                {
                    ChartLoc chart = new ChartLoc();
                    chart.Data.Add(int.Parse(reader["col2"].ToString()));
                    chart.Data.Add(int.Parse(reader["col3"].ToString()));
                    chart.Data.Add(int.Parse(reader["col4"].ToString()));

                    if (reader["store"] != DBNull.Value) 
                        chart.Category = reader["col1"].ToString();
                    _ChartLoc.Add(chart);
                }
                JavaScriptSerializer jss = new JavaScriptSerializer();
                jsonString = jss.Serialize(_ChartLoc);
            }
        }
    }
    return jsonString;         
}

Answer 2

If you want a single JSON string containing a set of category/data pairings you will want to serialize a single object. I would suggest a list of ChartLoc and then serialize the list.

In addition, if your Data object is a string it will be serialized as a string (i.e. "1,2,3" instead of [1,2,3]). If you really want to fix that, you will want a collection of ints.

public class ChartLoc
{
    public string Category { get; set; }
    public List<int> Data { get; set; }
}
...
var chartLocs = new List<ChartLoc>();
chartLocs.Add(new ChartLoc { Category = "comA", Data = new List<int> { 1, 2, 3 } });
chartLocs.Add(new ChartLoc { Category = "comB", Data = new List<int> { 4, 5, 6 } });
chartLocs.Add(new ChartLoc { Category = "comC", Data = new List<int> { 7, 8, 9 } });

JavaScriptSerializer jss = new JavaScriptSerializer();
var json = jss.Serialize(chartLocs);

The resulting JSON is much more similar to what you are seeking...

[{"Category":"comA","Data":[1,2,3]},{"Category":"comB","Data":[4,5,6]},{"Category":"comC","Data":[7,8,9]}]