Highcharts C# To JSON - Structuring Series Data

Question

I am a bit stuck on this, I am trying to create a highcharts stacked column and am going round in circles attempting to get the series correct, I have pulled my data from sql server into a datatable and it is in a pivot type format (it can be changed if there is a better way), the data is in the below format as it uses the sql server pivot function:

name 1 2 3 4 5 6 7 8 9 10
Bob  4 5 6 7 8 9 9 9 0 0
tim  4 5 6 7 4 3 2 5 6 3 

The numbers along the top are the days of the month, I am wanting stack the column chart on the name and the x axis is the day of the month with the actual value being the y axis.

I have tried several permiatations of this and the latest one is to create a custom object with the day of the month as an int array. basically I am stuck as to how this can be transferred into a highcharts stacked column

the resulting JSON I believe would need to be as below:

{
    name: Bob
    Valie [4,5,6,7,8,9,9,9,0,0]

    name: tim
    Value: [4,5,6,7,4,3,2,5,6,3]
}

I am ok serializing a list to a json object using JSON.net but I keep falling over when trying to produce that resulting JSON with an int array in it.

Does anybody have any best practice advice or maybe I am going about this all wrong and over complicating things I don't know. Below is my latest permiatation of an event , its just my latest one and its the result of a bit of hack and slashing in all honesty.

public class ChartData
    {
        public string Name { get; set; }
        public int Data { get; set; }
       // public int DayNumber { get; set; }

    }

protected void RenderChart()
    {

        List<int> _data = new List<int>();
        List<int> _data1 = new List<int>();
        List<string> _data2 = new List<string>();
        foreach (DataRow dr1 in resultsByDay.Rows)
        {
            _data.Add((int)dr1["Total"]);  //column name 
            _data1.Add((int)dr1["DayNumber"]);


            //_data.Add(new ChartData() { Data = ((int)dr1["Total"]) });
           // _data.Add(new Data() { DayNumber = ((int)dr1["DayNumber"]) });


        }
        //JavaScriptSerializer jss = new JavaScriptSerializer();
        //chartData = jss.Serialize(_data); //this make your list in jSON format like [88,99,10]
        //chartData1 = jss.Serialize(_data1);
        //chartData2 = jss.Serialize(_data2);

        JsonSerializer mySerializer = new JsonSerializer();

        chartData = JsonConvert.SerializeObject(_data);
        chartData1 = JsonConvert.SerializeObject(_data1);
        chartData2 = JsonConvert.SerializeObject(_data2);
    }

My thinking is that the int needs to be changed to an int[] but a little bit unsure how to go about structing the list so that JSON.net can convert it to a lovely JSON string for highcharts to use. I have managed to get a version of this working in highcharts just not in a stacked column using the below javascript, but that's not really much good to me

 <script type="text/javascript">  

                 $(function () {  
                 $('#chartContainer').highcharts({
                       chart: {  
                           type: 'column' },  
                       title: {  
                       text: '' },  
                      subtitle: {  
                      text: ''},  
                      xAxis: {  
                      title: {  
                       text: "Total Output Per Day"
                                    }, 
                            labels:{
                           rotation:-25,
                           y:50 },
                     categories: <%= chartData1%>  },  
                     yAxis: {  
                            linewidth : 10,
                            gridLineWidth: 0,
                            min: 0,  
                            title: {  
                            text: 'Total'  
                      }
                    },  
                    tooltip: {  
                        headerFormat: '<span style="font-size:10px">{point.key}</span><table>',  
            pointFormat: '<tr><td style="color:{series.color};padding:0">{series.name}: </td>' + '<td style="padding:0"><b>{point.y} </b></td></tr>',  
                         footerFormat: '</table>',  
                         shared: true,  
                         useHTML: true  
                    },  
                     plotOptions: {  
                           column: {  
                           pointPadding: 0.2,  
                           borderWidth: 0 ,
                           //stacking: 'normal'
                      }
                },series: [{
                    name: "Total",
                    data: <%= chartData%>,
                dataLabels: {
                             enabled: true,
                             rotation: -90,
                                color: '#FFFFFF',
                             align: 'center',
                             x:5,
                             y:10
                             }
                      }]    
                  });  
                });  

By the way I am working in webforms (will eventually convert to MVC :)

p.s. the data can be edited if easier into the following format:

Name DayNumber Total
Bob      1       5
Tim      1       10
bob      2       6
tim      2       8
bob      3       9
tim      3       5

Answer 1

Your question is a bit unclear. Your eventual plot call doesn't match what I assume you want on your plot - two series, one for Bob and one for Tim.

So let's start with with basics and get your database data into an array of Highchart series objects using JSON:

Assuming your first data structure coming back from the database:

name 1 2 3 4 5 6 7 8 9 10
Bob  4 5 6 7 8 9 9 9 0 0
tim  4 5 6 7 4 3 2 5 6 3 

This:

List<Dictionary<string, object>> allSeries = new List<Dictionary<string,object>>();
foreach (DataRow dr1 in table.Rows)
{
    Dictionary<string, object> aSeries = new Dictionary<string,object>();
    aSeries["name"] = dr1["name"];
    aSeries["data"] = new List<int>();
    int N = dr1.ItemArray.Length;
    for (int i = 1; i < N; i++)
    {
        ((List<int>)aSeries["data"]).Add((int)dr1[i]);
    }
    allSeries.Add(aSeries);
}
string jsonSeries = Newtonsoft.Json.JsonConvert.SerializeObject(allSeries);

in the jsonSeries string variable produces:

[{"name":"Bob","data":[4,5,6,7,8,9,9,9,0,0]},{"name":"Tim","data":[4,5,6,7,4,3,2,5,6,3]}]

This for Highcharts is an array of series objects.

You could then use this in a Highcharts call as:

$('#chartContainer').highcharts({
    chart: {  
        type: 'column' 
    },  
    series: <%= jsonSeries %>
});

Which creates: