Data Conversion from SQL, C# with Linq to JSON for Highcharts Line chart

Question

I am looking to convert the below SQL results into a JSON list for a Highcharts line chart. I am using C#. I cant get my head around how I would create an appropriate class(es) and populate them with this data in the format needed.

ID  Name    Month   Year    Data
2   Name 1  10      2015    4
2   Name 1  11      2015    5
2   Name 1  12      2015    6
3   Name 2  10      2015    6
3   Name 2  11      2015    7
3   Name 2  12      2015    8
4   Name 3  10      2015    35
4   Name 3  11      2015    7
4   Name 3  12      2015    8

The end result needs to be in something like this format. The ID and year also need to come through but are used for different things. Can this be done?

categories: ['10', '11', '12'],....
series: [{
        name: 'Name 1',
        data: [4, 5, 6]
    }, {
        name: 'Name 2',
        data: [6, 7, 8]
    }, {
        name: 'Name 3',
        data: [35, 7, 8]
    }
}]

Pontus Ivarsson · Accepted Answer · 2016-01-29 10:06:05Z

2

class Program
{
    static void Main(string[] args)
    {
        var ro = new RootObject();
        ro.AddRow(1, "Name1", 2015, 10, 4);
        ro.AddRow(1, "Name1", 2015, 11, 5);
        ro.AddRow(1, "Name1", 2015, 12, 6);

        ro.AddRow(2, "Name2", 2015, 10, 6);
        ro.AddRow(2, "Name2", 2015, 11, 7);
        ro.AddRow(2, "Name2", 2015, 12, 8);

        ro.AddRow(3, "Name3", 2015, 10, 35);
        ro.AddRow(3, "Name3", 2015, 11, 7);
        ro.AddRow(3, "Name3", 2015, 12, 8);

        string output = JsonConvert.SerializeObject(ro);
        Console.WriteLine(output);

        //Output:
        //{
        //    "categories":[10,11,12],
        //    "series":[
        //        {"name":"Name1","data":[4,5,6]},
        //        {"name":"Name2","data":[6,7,8]},
        //        {"name":"Name3","data":[35,7,8]}]
        //}

        Console.ReadKey();
    }

    public class RootObject
    {
        public RootObject()
        {
            categories = new List<int>();
            series = new List<Series>();
        }

        public void AddRow(int id, string name, int year, int month, int data)
        {
            if (!categories.Contains(month))
            {
                categories.Add(month);
            }

            var serie = series.FirstOrDefault(x => x.name == name);
            if (serie == null)
            {
                serie = new Series(name);
                series.Add(serie);
            }
            serie.data.Add(data);
        }

        public List<int> categories { get; set; }
        public List<Series> series { get; set; }
    }

    public class Series
    {
        public Series(string _name)
        {
            name = _name;
            data = new List<int>();
        }

        public string name { get; set; }
        public List<int> data { get; set; }
    }
}

edited Jan 29, 2016 at 10:06

answered Jan 28, 2016 at 15:50

Pontus Ivarsson

1265 bronze badges

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

4 Comments

Daniel Over a year ago

How do I then add the data to the classes?

Pontus Ivarsson Over a year ago

I edited the answer, with a full working exempel. Paste the code in to a console application and use nuget to install Newtonsoft.Json. (Install-Package Newtonsoft.Json) or JSON serializer by choice.

Pontus Ivarsson Over a year ago

Does that answer your question?

Daniel Over a year ago

That has been really helpful, it's not something I've considered before so I should be able to implement this in other things.

Collectives™ on Stack Overflow

Data Conversion from SQL, C# with Linq to JSON for Highcharts Line chart

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related