Render Data from Database

How to add dataPoints to Chart from Database.

With CanvasJS, you can easily create charts from database in your ASP.NET MVC Application.Before we start, create an empty project in Visual Studio as mentioned in this article.


Download ASP.Net MVC Chart Samples

Once the project is created, please follow below step-by-step instructions:

Step 1: Create Database

  1. Right-click the App_Data folder in the Solution Explorer window and select the menu option Add, New Item.
  2. Select the Data category and select the SQL Server Database template.
  3. Name your new database DataPointsDB.mdf and click the Add button.

After you create your database, you can connect to the database by double-clicking the DataPointsDB.mdf file located in the App_Data folder. Double-clicking the DataPointsDB.mdf file opens the Server Explorer window. Create a new database table and populate the database.

Step 2: Create Data Model

  1. Right-click the App_Data folder in the Solution Explorer window and select the menu option Add, New Item.
  2. Select the Data category and select the ADO.NET Entity Data Model template.
  3. Give your data model the name DataPointsDBModel.edmx and click the Add button.

After you click the Add button, the Entity Data Model Wizard appears. Follow these steps to complete the wizard:

  1. In the Choose Model Contents step, select the Generate from database option.
  2. In the Choose Your Data Connection step, use the DataPointsDB.mdf data connection and the name DataPointsDBEntities for the connection settings. Click the Next button.
  3. In the Choose Your Database Objects step, expand the Tables node, select the DataPoints table. Enter the namespace DataPoints.Models and click the Finish button.

Step 3: Modify Home Controller & Retrieve Data from database table

using System.Linq;
using System.Web.Mvc;
using DataPoints.Models;
namespace DataPoints.Controllers
{
	public class HomeController : Controller
	{
		private DataPointsDBEntities _db = new DataPointsDBEntities(); 
		public ActionResult Index()
		{
			return View(_db.DataPointsSet.ToList());
		}
	}
} 

Step 4: Modify View

Modify the View (Index.cshtml) as shown below.

<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].x, y:result[i].y});
	}

	$(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();
	});
</script>

Now build and run the project to see charts being rendered from database!

Finalising

To summarize, in order to render data from database you need to fetch data from database, parse it to CanvasJS supported format and then pass the dataPoints to the chart. For more info on ASP.Net MVC Models and Data-Access, refer this link.

Below is the compilation of final code. Sample provided above includes some of the auto-generated (by visual studio) files as well.


@{
     Layout = null;
}


<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>DataFromDataBase</title>
    <script src="http://canvasjs.com/assets/script/canvasjs.min.js"></script>
</head>
<body>
    <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].x, y:result[i].y});
        }

        window.onload = 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();
        });
    </script>
</body>
</html>			
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace ASPNET_MVC_Samples.Controllers
{
    public class HomeController : Controller
    {
       
public ActionResult DataFromDataBase()
        {
            try
            {
                ViewBag.DataPoints = JsonConvert.SerializeObject(_db.Points.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 };
    }
}			
//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace ASPNET_MVC_Samples.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class Point
    {
        public int x { get; set; }
        public Nullable<int> y { get; set; }
    }
}			


In order to provide better support,we have closed the comments and switched completely to our Forums.
If you have any questions, please feel free to ask in our forums.