Implementing chart in asp.net mvc project using amchart with sql Server as data Source

Introduction

Adding Visual effects like charts to your web application especially on dashboards is now becoming a selling point for most web application. There are several jQuery charts available out there but the one I have chosen over the years is Amchart because it is very robust and easy to integrate.

I will walk you through how you can implement amchart in asp.net MVC application using data from  SQL server.

Setup Database

Open your SQL Server Management Studio, create a database name SampleDb and add table name Price  to the SampleDb you just created.

You can also run the script SampleDb.sql attach to the complete project file.  This will create the necessary database and table with data.

Create Asp.net MVC Project

Open your visual studio and create a new asp.net MVC project. Select the MVC Default template and Name the project any valid name you want.

Add Ado.Net Entity

Create a folder name DataModel to the application root. Add new Ado.net Entity model item to the folder you just created. Select EF Designer From Database and click next. Setup new connection and make sure you select the SampleDb database you created earlier on. Make sure you select the price table and click finish.

Make sure you save and build the Solution.

Add SampleChart Controller

Create a new Controller called SampleChart and add the corresponding view for this Controller. In my case, I added AmChartSample as my view name.

The Controller Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace SampleChart.Controllers
{
    public class SampleChartController : Controller
    {
        // GET: SampleChart
        public ActionResult AmChartSample()
        {
            return View();
        }
    }
}

Add Amchart to View

Before we can use amchart to draw graph in our project, we have to download the necessary file from amchart official site. Click on free download and Make sure you select JavaScript Charts to download. Once your download is completed, unzip the file and copy the amcharts folder to your project.

The Unzipped File

In my own case, I added the amcharts folder to the Content folder in my solution.

In your view, add references to the following files:

• amcharts.js
• serial.js
• export.min.js
• export.css
• light.js

Add a div of Id chartdiv and set the height and width of the div to 500px and 100% respectively using inline style.

The View Code

@{
    ViewBag.Title = "AmChartSample";
}

<!--amchart Resoureces-->
<script src="~/Content/amcharts/amcharts.js"></script>
<script src="~/Content/amcharts/serial.js"></script>
<script src="~/Content/amcharts/plugins/export/export.min.js"></script>
<link href="~/Content/amcharts/plugins/export/export.css" rel="stylesheet" />
<script src="~/Content/amcharts/themes/light.js"></script>
<!--amchart Resoureces Ends Here-->

<div class="row">
    <div class="panel panel-default">
        <div style="background:#2c3643; color:#fff" class="panel-heading">
            <div class="row">
                <h4 class="panel-title">&nbsp;Sample Chart</h4>
            </div>
        </div>
        <div class="panel-body">
            <div class="row">
                <div class="col-md-12">
                    <div id="chartdiv" style="height:500px;width:100%"></div>
                </div>
            </div>
        </div>
    </div>
</div>

Let us Add Some JavaScript Code to Draw Serial graph

Create a Data Transfer Object(DTO) class name ChartDataDTO

 

public class ChartDataDTO
    {
        public int Id { get; set; }
        public string Date { get; set; }
        public decimal OfferPrice { get; set; }
        public decimal BidPrice { get; set; }
        public decimal EffectiveYield { get; set; }
    }
    

Add the following code to your controller to fetch data from the database

//create instance of datacontext
        SampleDbEntities db = new SampleDbEntities();
        [HttpGet]
        public JsonResult GetChartData()
        {
            var model = db.Prices
                .ToList()
                .Select(m => new ChartDataDTO
                 {
                    Id=m.ID,
                    OfferPrice=m.OfferPrice.HasValue?m.OfferPrice.Value:0,
                    BidPrice= m.BidPrice.HasValue ? m.BidPrice.Value : 0,
                    EffectiveYield=m.EffectiveYield.HasValue?m.EffectiveYield.Value:0,
                    Date=m.Date.Value.ToString("yyyy-MM-dd")
                }).OrderBy(j=>j.Date).ToList();

            return Json(model.ToArray(), JsonRequestBehavior.AllowGet);
        }
 

Add these JavaScript functions to call GetChartData method we just created in the controller.

@section Scripts{
    <!-- CUSTOM JS -->
  <script>
     function LoadVisualAjax() {
        return $.ajax({
            type: "Get",
            url: '@Url.Action("GetChartData", "SampleChart")',
        });
      }
    function LoadVisual() {
        $.when(LoadVisualAjax()).then((data) => {
        }).fail(() => {
           alert ("Fail to initialize Chart");
        });
      }
</script>
}

Let us add another JavaScript function called DrawChart to draw the serial graph and we will call  LoadVisual function in document ready event so that our chart will be drawn when the View page has finish loading.
The Complete JavaScript code

@section Scripts{
    <!-- CUSTOM JS -->
  <script>
     function LoadVisualAjax() {
        return $.ajax({
            type: "Get",
            url: '@Url.Action("GetChartData", "SampleChart")',
        });
      }
    function LoadVisual() {
        $.when(LoadVisualAjax()).then((data) => {
            DrawChart(data);
        }).fail(() => {
           alert ("Fail to initialize Chart");
        });
      }

   function DrawChart(dataVal) {
     
       var chart = AmCharts.makeChart("chartdiv", {
                "type": "serial",
                "theme": "light",
                "marginRight": 40,
                "marginLeft": 40,
                "autoMarginOffset": 20,
                "mouseWheelZoomEnabled": true,
                "dataDateFormat": "YYYY-MM-DD",
                "valueAxes": [{
                    "id": "v1",
                    "axisAlpha": 0,
                    "position": "left",
                    "ignoreAxisWidth": true,
                    "title": "Yield",
                    "position": "left",
                    "autoGridCount": false,
                    "labelFunction": function (value) {
                        return Math.round(value);
                    }
                }],

                "balloon": {
                    "borderThickness": 1,
                    "shadowAlpha": 0,
                },
                "graphs": [{
                    "id": "g1",
                    "bullet": "round",
                    "bulletBorderAlpha": 1,
                    "bulletColor": "#FFFFFF",
                    "bulletSize": 5,
                    "hideBulletsCount": 50,
                    "lineThickness": 2,
                    "title": "Effective Yield ",
                    "useLineColorForBulletBorder": true,
                    "valueField": "EffectiveYield",
                    "balloonText": "[[category]]: <b style='font-size: 130%'>[[value]]</b>",
                }],
                "chartScrollbar": {
                    "graph": "g1",
                    "oppositeAxis": false,
                    "offset": 30,
                    "scrollbarHeight": 80,
                    "backgroundAlpha": 0,
                    "selectedBackgroundAlpha": 0.1,
                    "selectedBackgroundColor": "#888888",
                    "graphFillAlpha": 0,
                    "graphLineAlpha": 0.5,
                    "selectedGraphFillAlpha": 0,
                    "selectedGraphLineAlpha": 1,
                    "autoGridCount": true,
                    "color": "#AAAAAA"
                },
                "chartCursor": {
                    "pan": true,
                    "valueLineEnabled": true,
                    "valueLineBalloonEnabled": true,
                    "cursorAlpha": 1,
                    "cursorColor": "#258cbb",
                    "limitToGraph": "g1",
                    "valueLineAlpha": 0.4,
                    "valueZoomable": true
                },
                "valueScrollbar": {
                    "oppositeAxis": false,
                    "offset": 50,
                    "scrollbarHeight": 10
                },
                "categoryField": "Date",
                "categoryAxis": {
                    "parseDates": true,
                    "equalSpacing": true,
                    "minPeriod": "DD",
                    "dashLength": 1,
                    "minorGridEnabled": true,
                },

                "legend": {
                    "useGraphSettings": true,
                    "position": "top"
                },
                "export": {
                    "enabled": false,
                },
                "dataProvider": dataVal

            });
       chart.addListener("rendered");
      }

      $(document).ready(function () {
          LoadVisual();
      });
 </script>
}

Note that the dataProvider is set to use the array object returned from the controller. Also, the Valuefield and Category field is set to "EffectiveYield"  and "Date" respectively.

The Complete Controller Code

using SampleChart.DataModel;
using SampleChart.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace SampleChart.Controllers
{
    public class SampleChartController : Controller
    {
        //create instance of datacontext
        SampleDbEntities db = new SampleDbEntities();

        // GET: SampleChart
        public ActionResult AmChartSample()
        {
            return View();
        }

        [HttpGet]
        public JsonResult GetChartData()
        {
            var model = db.Prices
                .ToList()
                .Select(m => new ChartDataDTO
                 {
                    Id=m.ID,
                    OfferPrice=m.OfferPrice.HasValue?m.OfferPrice.Value:0,
                    BidPrice= m.BidPrice.HasValue ? m.BidPrice.Value : 0,
                    EffectiveYield=m.EffectiveYield.HasValue?m.EffectiveYield.Value:0,
                    Date=m.Date.Value.ToString("yyyy-MM-dd")
                }).OrderBy(j=>j.Date).ToList();

            return Json(model.ToArray(), JsonRequestBehavior.AllowGet);
        }

    }
}

Note that an array object is returned from the controller and it must be ordered by Date in ascending order, if not the chart will not render well.
The Complete View Code

@{
    ViewBag.Title = "AmChartSample";
}

<!--amchart Resoureces-->
<script src="~/Content/amcharts/amcharts.js"></script>
<script src="~/Content/amcharts/serial.js"></script>
<script src="~/Content/amcharts/plugins/export/export.min.js"></script>
<link href="~/Content/amcharts/plugins/export/export.css" rel="stylesheet" />
<script src="~/Content/amcharts/themes/light.js"></script>
<!--amchart Resoureces Ends Here-->

<div class="row">
    <div class="panel panel-default">
        <div style="background:#2c3643; color:#fff" class="panel-heading">
            <div class="row">
                <h4 class="panel-title">&nbsp;Sample Chart</h4>
            </div>
        </div>
        <div class="panel-body">
            <div class="row">
                <div class="col-md-12">
                    <div id="chartdiv" style="height:500px;width:100%"></div>
                </div>
            </div>
        </div>
    </div>
</div>

@section Scripts{
    <!-- CUSTOM JS -->
  <script>
     function LoadVisualAjax() {
        return $.ajax({
            type: "Get",
            url: '@Url.Action("GetChartData", "SampleChart")',
        });
      }
    function LoadVisual() {
        $.when(LoadVisualAjax()).then((data) => {
            DrawChart(data);
        }).fail(() => {
           alert ("Fail to initialize Chart");
        });
      }

   function DrawChart(dataVal) {
     
       var chart = AmCharts.makeChart("chartdiv", {
                "type": "serial",
                "theme": "light",
                "marginRight": 40,
                "marginLeft": 40,
                "autoMarginOffset": 20,
                "mouseWheelZoomEnabled": true,
                "dataDateFormat": "YYYY-MM-DD",
                "valueAxes": [{
                    "id": "v1",
                    "axisAlpha": 0,
                    "position": "left",
                    "ignoreAxisWidth": true,
                    "title": "Yield",
                    "position": "left",
                    "autoGridCount": false,
                    "labelFunction": function (value) {
                        return Math.round(value);
                    }
                }],

                "balloon": {
                    "borderThickness": 1,
                    "shadowAlpha": 0,
                },
                "graphs": [{
                    "id": "g1",
                    "bullet": "round",
                    "bulletBorderAlpha": 1,
                    "bulletColor": "#FFFFFF",
                    "bulletSize": 5,
                    "hideBulletsCount": 50,
                    "lineThickness": 2,
                    "title": "Effective Yield ",
                    "useLineColorForBulletBorder": true,
                    "valueField": "EffectiveYield",
                    "balloonText": "[[category]]: <b style='font-size: 130%'>[[value]]</b>",
                }],
                "chartScrollbar": {
                    "graph": "g1",
                    "oppositeAxis": false,
                    "offset": 30,
                    "scrollbarHeight": 80,
                    "backgroundAlpha": 0,
                    "selectedBackgroundAlpha": 0.1,
                    "selectedBackgroundColor": "#888888",
                    "graphFillAlpha": 0,
                    "graphLineAlpha": 0.5,
                    "selectedGraphFillAlpha": 0,
                    "selectedGraphLineAlpha": 1,
                    "autoGridCount": true,
                    "color": "#AAAAAA"
                },
                "chartCursor": {
                    "pan": true,
                    "valueLineEnabled": true,
                    "valueLineBalloonEnabled": true,
                    "cursorAlpha": 1,
                    "cursorColor": "#258cbb",
                    "limitToGraph": "g1",
                    "valueLineAlpha": 0.4,
                    "valueZoomable": true
                },
                "valueScrollbar": {
                    "oppositeAxis": false,
                    "offset": 50,
                    "scrollbarHeight": 10
                },
                "categoryField": "Date",
                "categoryAxis": {
                    "parseDates": true,
                    "equalSpacing": true,
                    "minPeriod": "DD",
                    "dashLength": 1,
                    "minorGridEnabled": true,
                },

                "legend": {
                    "useGraphSettings": true,
                    "position": "top"
                },
                "export": {
                    "enabled": false,
                },
                "dataProvider": dataVal

            });
       chart.addListener("rendered");
      }

      $(document).ready(function () {
          LoadVisual();
      });
 </script>
 
}

You can now build and Run the project.

Thank You for Reading.

The Complete Source code Can be downloaded from Here