Highcharts C # for JSON - structuring series data
I am a bit stuck with this, I am trying to create a high graph column and going in circles, trying to get the series right, I pulled my data from sql server to datatable and is at pivot (it can be changed if there is a better way), data is in in the following format, since it uses the sql server support 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 at the top are the days of the month, I want the stack to display a column chart by title, and the x-axis is the day of the month when the actual value is the y-axis.
I've tried several permiatations of this, and the last 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 carried over to high column level columns
the resulting JSON, I believe, should be as follows:
{
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'm okay serializing a list for a json object using JSON.net, but I keep falling down trying to create this resulting JSON with an int array in it.
Does anyone have any best practice tips, or maybe I'm going to get it all wrong and over complicate things that I don't know. Below is my last event permanence, its only my last and its result a little hack and hack 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 int needs to be changed to int [], but a little unsure how to do this in order to create a list so JSON.net can convert it to a fine JSON string for use as graphics cards. I was able to get the highcharts version of this working, not the stack, using the javascript below, but this is not very good for 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
}
}]
});
});
BTW I'm working in webforms (eventually converting to MVC :)
ps data can be edited if simpler in the following format:
Name DayNumber Total
Bob 1 5
Tim 1 10
bob 2 6
tim 2 8
bob 3 9
tim 3 5
source to share
Your question is a little unclear. Your eventual plot challenge doesn't match what I assume you want in your plot - two episodes, one for Bob and one for Tim.
So let's start with the basics and get your database data into an array of Highchart objects using JSON:
Assuming your first data structure is returned 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
It:
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 a string variable jsonSeries 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 is for Highcharts - an array of series objects.
Then you can use this in your call to Highcharts like:
$('#chartContainer').highcharts({
chart: {
type: 'column'
},
series: <%= jsonSeries %>
});
What creates:
source to share