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']

have you looked at any of the links on the lower right hand side of this page under Related? here is a link from one of them stackoverflow.com/questions/17880171/… — MethodMan
– MethodMan, Commented Aug 5, 2013 at 21:16
Not quite what I want, I've seen this post already. Not looking for creating stored procedure. Looking to do this in C# only. I have no read/write privlages on SQL(not true but lets pretend) I just want to format the JSON in CSHARP the way I mentioned above. — lookin4answers
– lookin4answers, Commented Aug 6, 2013 at 13:34

Cameron Tinker · Accepted Answer · 2022-11-29 16:06:26Z

6

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

edited Nov 29, 2022 at 16:06

answered Aug 6, 2013 at 14:06

Cameron Tinker

9,83710 gold badges48 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lookin4answers Over a year ago

EXCELLENT! much appreciated. This is the answer I was looking for. CHEERS!

Mayo · Accepted Answer · 2013-08-06 13:51:28Z

1

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

answered Aug 6, 2013 at 13:51

Mayo

10.8k7 gold badges48 silver badges91 bronze badges

1 Comment

lookin4answers Over a year ago

This is GREAT, short simple and sweet. This is exactly what I was looking for. THANKS!

Collectives™ on Stack Overflow

Formatting JSON from SQL Table Results in C#

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related