Dynamic chart from SQL Server in MVC

[Arqu07]

Hi,
I want to create a dynamic chart from sql server database, I will be inserting new values every 3s into database. I can create normal chart with cancasJS but I can trubles with dynamic. I was trying something like this below, but it doesn’t work for me.
Controller code:

namespace Testowanie.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }
private MojaBEntities _db = new MojaBEntities();
        public ActionResult About()
        {
            try
            {
                ViewBag.DataPoints = JsonConvert.SerializeObject(_db.wartosci.ToList(), _jsonSetting);

                return View();
            }
            catch (System.Data.Entity.Core.EntityException)
            {
                return View("Error");
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return View("Error");
            }
        }
        JsonSerializerSettings _jsonSetting = new JsonSerializerSettings() { NullValueHandling = NullValueHandling.Ignore };

View code:

<div id="chartContainer"></div>

<script type="text/javascript">
	var result = @Html.Raw(ViewBag.DataPoints);
	var dataPoints =[];
	for(var i = 0; i < result.length; i++){
		dataPoints.push({label:result[i].Czas, y:result[i].Wartosc4});
	}

	$(function () {
		var chart = new CanvasJS.Chart("chartContainer", {
			theme: "theme2",
			zoomEnabled: true,
			animationEnabled: true,
			title: {
				text: "Line Chart with Data-Points from DataBase"
			},
			data: [
			{
				type: "line",

				dataPoints: dataPoints,
			}
			]
		});
		chart.render();
		var x = i;
		var updateInterval = 4000;
		var updateChart = function(){
		    var result2 = @Html.Raw(ViewBag.DataPoints);
		    if (x < result2.length){
		        dataPoints.push({label:result[x].Czas, y:result[x].Wartosc4});
		        x++;
		    }
		    chart.render();

		};
		setInterval(function(){updateChart()}, updateInterval); 
	});
</script>

[Vishwas R]

@Arqu07,

Kindly download and check ASP.net samples from this link. Refer this section for tutorials on integrating CanvasJS charts to ASP.net MVC application.

—
Vishwas R

[Arqu07]

Thanks for your answer, your samples are great and very useful, but I am total beginner in that, it’s clear for me how to do this when we generate random data but when I want to use data from database i have troubles. When I load the page the chart is creating from my data but when I add new record the chart doesn’t updating.

[Vishwas R]

@Arqu07,

Unless we look into your code, we can’t predict what went wrong in it. Can you kindly create a sample project and share your code, so that we can look into it and help you out.

—
Vishwas R

[Arqu07]

My answer was deleted but I’ve received a reply on mail. I was trying do something similar like on this page but my chart still doesn’t update changes.
Here my model:

namespace ChartMVC.Models
{
    using System;
    using System.Collections.Generic;
    using System.Runtime.Serialization;
    
    [DataContract]
    public partial class Table
    {
        [DataMember]
        public int Id { get; set; }
        [DataMember]
        public Nullable<decimal> Value1 { get; set; }
        [DataMember]
        public Nullable<decimal> Value2 { get; set; }
        [DataMember]
        public Nullable<System.DateTime> Date { get; set; }
    }
}

Controller:

 private BazaDEntities _db = new BazaDEntities();
        public ActionResult About()
        {
            try
            {
                ViewBag.DataPoints = JsonConvert.SerializeObject(_db.Table.ToList(), _jsonSetting);

                return View();
            }
            catch (System.Data.Entity.Core.EntityException)
            {
                return View("Error");
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return View("Error");
            }
        }
        JsonSerializerSettings _jsonSetting = new JsonSerializerSettings() { NullValueHandling = NullValueHandling.Ignore };

View:

<div id="chartContainer"></div>

<script type="text/javascript">
	var result = @Html.Raw(ViewBag.DataPoints);
	var dataPoints =[];
	for(var i = 0; i < result.length; i++){
		dataPoints.push({label:result[i].Date, y:result[i].Value2});
	}

	$(function () {
		var chart = new CanvasJS.Chart("chartContainer", {
			theme: "theme2",
			zoomEnabled: true,
			animationEnabled: true,
			title: {
				text: "Line Chart with Data-Points from DataBase"
			},
		
			data: [
			{
				type: "line",

				dataPoints: dataPoints,
			}
			]
		});
		chart.render();
		var x = i;
		var updateInterval = 1000;
		var updateChart = function () {
		    for(var y=x; y < result.length; y++){
		        dataPoints.push({label:result[y].Date, y:result[y].Value2});
		    }
		    x=y;
		    chart.render();
		};
		setInterval(function () { updateChart() }, updateInterval);

	});
</script>

Any clues what I am doing wrong? Here is my full project

[Vishwas R]

@Arqu07,

We apologies for the deletion of your post. It has happened due to some technical issue and will be restored soon.

We will look into the the project you have shared and get back to you at the earliest.

—
Vishwas R

[Vishwas R]

@arqu07,

The issue is in updateChart function. It fails to enter for-loop as value of x (var x = i;) is same as the value of result.length. Read data from database, store it in another variable (lets say result1) and then push that to dataPoints as shown below.

var updateChart = function () {
    for(var y=0; y < result1.length; y++){
	dataPoints.push({label:result1[y].Date, y:result1[y].Value2});
    }
    x=y;
    chart.render();
};

Please check this updated project

—
Vishwas R

[Arqu07]

Thanks for your reply and efforts. But this is not exactly what I want to do. This chart is updating, but it draws the same chart over and over again, it’s useless and doesn’t react when I add new record in database. I want to draw only newly added points and I’m stuck with that :/

[Vishwas R]

@arqu07,

You can fetch the latest row in database, parse it and add it to chart-dataPoints. Please refer the following links for more information on retrieving latest row from database.

http://stackoverflow.com/questions/22323682/how-to-get-the-latest-row-in-a-table-using-entity-framework-considering-perform
http://stackoverflow.com/questions/18528736/how-to-retrieve-values-from-the-last-row-in-a-datatable
http://dba.stackexchange.com/questions/69358/retrieve-recently-inserted-5-rows

—
Vishwas R

[mmusi4@gmail.com]

Hi

I am a beginner, I managed to generate a chart using EntityFramework, but what i want to do is create a dashboard, do you have an example?

[Suyash Singh]

@mmusi4gmail-com,

Thank you for your interest in CanvasJS. We do have dashboard examples for download. Please have a look.

___
Suyash Singh
Team CanvasJS