Introduction
This tip shows how you can use an SQL Server database to display dynamic charts in your application. basically I show you how to convert data into JSON and pass it to view from a controller to display charts. Most examples on the internet show tutorials using static data which can be difficult for beginners like myself when we need to do a real application. I hope that it can help someone out there who may face a similar situation.
Background
To illustrate the concept, I created a dummy application that displays defect/incidents in projects during different phases and environments. I used Entity Framework Database first concept and LINQ to entities/SQL.
Using the Code
Firstly I have four tables in SQL server: Defect, Project, Environment and Incident. You will see the schema in the accompanying source code.
I have a Dashboard controller that looks like this. Notice how you convert data to JSON in code highlighted in bold.
public class DashboardController : Controller
{
public ActionResult Index()
{
return View();
}
public ContentResult GetData()
{
using (var db = new QualityMatricsEntities1())
{
var result = (from tags in db.Defects
orderby tags.DefectDate ascending
select new { tags.Description, tags.NumberOfDefects }).ToList();
return Content(JsonConvert.SerializeObject(result), "application/json");
}
}
public ContentResult GetDefect()
{
using (var db = new QualityMatricsEntities1())
{
var defect = (from d in db.Defects
join e in db.Environments on d.DefectID equals e.EnvID
select new
{
d.Description,
d.NumberOfDefects,
e.EnvName
}).ToList();
return Content(JsonConvert.SerializeObject(defect));
}
}
}
Now, I am going to the view (Index.cshtml in this case). My code looks like this: I also notice the code highlighted in bold which shows you how to pass json in the $.getJson line and how to push data points to a Canvas chart.
@model IEnumerable<ChartDemo.Models.Project>
@{
ViewBag.Title = "Index";
}
<!---->
<script type="text/javascript" src="~/Scripts/jquery-2.1.1.min.js"></script>
<script src="~/Scripts/jquery-2.1.1.min.js"></script>
<script type="text/html" src="~/Scripts/canvasjs.min.js"></script>
<script src="~/Scripts/canvasjs.js"></script>
<script src="~/Scripts/excanvas.js"></script>
<div id="container" style="min-width: 310px; height: 400px; margin: 0 auto"></div>
<script type="text/javascript">
$(document).ready(function () {
var dataPoints = [];
$.getJSON("/Dashboard/GetData/", function (data) {
for (var i = 0; i <= data.length -1; i++) {
dataPoints.push({ label: data[i].Description, y: parseInt(data[i].NumberOfDefects) });
}
var chart = new CanvasJS.Chart("chartContainer", {
theme: "theme2", title: {
text: "Defects"
},
data: [
{
type: "column",
dataPoints: dataPoints
}
]
});
chart.render();
});
});
</script>
<body>
<div id="chartContainer" style="height: 300px; width: 50%; float:right"></div>
</body>
<script type="text/javascript">
$(document).ready(function () {
var dataPoints = [];
$.getJSON("/Dashboard/GetDefect/", function (data) {
for (var i = 0; i <= data.length - 1; i++) {
dataPoints.push({ label: data[i].EnvName, y: parseInt(data[i].NumberOfDefects) });
}
var chart = new CanvasJS.Chart("chartContainer1", {
theme: "theme2", title: {
text: "Number of Defects by Environment"
},
data: [
{
"area", "spline", "pie",etc.
type: "line",
dataPoints: dataPoints
}
]
});
chart.render();
});
});
</script>
<body>
<div id="chartContainer1" style="height: 300px; width: 50%;"></div>
</body>
<body>
<div id="chartContainer2" style="height: 300px; width: 50%; float:right"></div>
</body>
