Saturday, 2 September 2017

Draw or implement Pie Chart, Column Chart and Doughnut Chart using Google Chart API in ASP.NET MVC

In this article, I am going to explain you how to draw or implement Pie Chart, Column Chart and Doughnut Chart using Google Chart API in ASP.NET MVC. We will first Entity Framework to load data from database and after that we will use ADO.NET code. Below is the step by step tutorial.

Creating Database Table

First we need to create a database table and insert some dummy records. Below is table script to create table. Table ProductSales has 3 columns Id, Month and SalesCount. Here we are plotting number of product sales against each month.
create table ProductSales
(
Id int primary key identity,
[Month] varchar(20),
SalesCount int
)
go
insert into ProductSales values('Jan',85)
insert into ProductSales values('Feb',90)
insert into ProductSales values('Mar',30)
insert into ProductSales values('Apr',46)
insert into ProductSales values('May',65)
insert into ProductSales values('Jun',75)
insert into ProductSales values('Jul',85)
insert into ProductSales values('Aug',60)
insert into ProductSales values('Sep',55)
insert into ProductSales values('Oct',40)
insert into ProductSales values('Nov',60)
insert into ProductSales values('Dec',90)

Adding Controller and Controller Action Method

The next step is to add controller and action method. Add a new controller named as HomeController and inside controller add a new action method ChartFunction as shown below. Return type of ChartFunction() should be JsonResult and data should be in List.
Using Entity Framework
using ChartDemo.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace ChartDemo.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        // Using Entity Framework
        public JsonResult ChartFunction()
        {
            ArticlesEntitiesDB db = new ArticlesEntitiesDB();
            var data = db.ProductSales.ToList();
            return Json(data, JsonRequestBehavior.AllowGet);
        }
    }
}
Here we are going to use ADO.NET code to get data from database. Output will be same in both the cases.
Using ADO.NET Code
using ChartDemo.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace ChartDemo.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }
        
        // Using ADO.NET Code
        public JsonResult ChartFunction()
        {
            string query = "select [Month],SalesCount from ProductSales";
            string constring = @"data source=#####;initial catalog=Articles;
            persist security info=True;user id=#####;password=#####;";
            List<ProductDetails> chartData = new List<ProductDetails>();
            using (SqlConnection con = new SqlConnection(constring))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            ProductDetails pd = new ProductDetails();
                            pd.Month = dr["Month"].ToString();
                            pd.SalesCount = Convert.ToInt32(dr["SalesCount"]);
                            chartData.Add(pd);
                        }
                    }
                    con.Close();
                }
            }
            return Json(chartData, JsonRequestBehavior.AllowGet);
        }
        class ProductDetails
        {
            public string Month { get; set; }
            public Nullable<int> SalesCount { get; set; }
        }
    }
}
Adding View
Next step is to add view, right click on Index action method and add a new view without any model. Below is the code to draw chart using Google API.
First add two JavaScript reference files. First for jQuery support and second for google api reference.In options, you can specify the css and other properties highlighted in yellow. You can specify the different type of chart highlighted in green.
@{
    ViewBag.Title = "Draw Chart Using Google Chart API";
}

<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>

<script type="text/javascript">
    google.load("visualization", "1", { packages: ["corechart"] });
    google.setOnLoadCallback(drawChart);
    function drawChart() {
        $.ajax({
            type: "POST",
            url: "/Home/ChartFunction",
            data: '{}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (data) {
                var dt = new google.visualization.DataTable();
                dt.addColumn('string', 'Month');
                dt.addColumn('number', 'Sales Count');
                for (var i = 0; i < data.length; i++) {
                    dt.addRow([data[i].Month, data[i].SalesCount]);
                }
                var options = {
                    title: 'Product Sales Month Wise'
                };
                var chart = new google.visualization.ColumnChart($("#chart")[0]);
                chart.draw(dt, options);
            },
            failure: function (r) {
                alert(r.d);
            },
            error: function (r) {
                alert(r.d);
            }
        });
    }
</script>

<fieldset>
    <legend>Google Chart API IN ASP.NET MVC</legend>
    <div id="chart" style="height: 400px;width:900px;"></div>
</fieldset>
Below the output for column chart.
Draw Column Chart using Google Chart API in ASP.NET MVC

To draw pie chart change from ColumnChart to PieChart as shown below.
var chart = new google.visualization.PieChart($("#chart")[0]);
Draw Pie Chart using Google Chart API in ASP.NET MVC

To draw Doughnut chart, make chart type to PieChart and add pieHole: 0.5 as shown below
    var options = {
        title: 'Product Sales Month Wise',
        pieHole: 0.5
    };
    var chart = new google.visualization.PieChart($("#chart")[0]);
    chart.draw(dt, options);
Draw Doughnut Chart using Google Chart API in ASP.NET MVC

Draw Chart on Button click

Until now, we were drawing chart when view is loaded and now to draw chart on button click modify the view as shown below.
1) Add a button control as shown in green.
2) Attach Click event to this button using jQuery as shown in yellow.
3) Add "callback": drawChart as shown in blue.
Now when you will click on button then chart will be loaded.
<script type="text/javascript">
    $(function () {
        $("#btn").click(function () {
            google.load("visualization", "1", { packages: ["corechart"], "callback": drawChart });
            google.setOnLoadCallback(drawChart);
            function drawChart() {
                $.ajax({
                    type: "POST",
                    url: "/Home/ChartFunction",
                    data: '{}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (data) {
                        var dt = new google.visualization.DataTable();
                        dt.addColumn('string', 'Month');
                        dt.addColumn('number', 'Sales Count');
                        for (var i = 0; i < data.length; i++) {
                            dt.addRow([data[i].Month, data[i].SalesCount]);
                        }
                        var options = {
                            title: 'Product Sales Month Wise',
                            pieHole: 0.5
                        };
                        var chart = new google.visualization.PieChart($("#chart")[0]);
                        chart.draw(dt, options);
                    },
                    failure: function (r) {
                        alert(r.d);
                    },
                    error: function (r) {
                        alert(r.d);
                    }
                });
            }
        });
    });
</script>

<input type="button" id="btn" value="Draw Chart"/>

<fieldset>
    <legend>Google Chart API IN ASP.NET MVC</legend>
    <div id="chart" style="height: 400px;width:900px;"></div>
</fieldset>
To draw Pie Chart in 3D make is3D :'true' in options.
    var options = {
        title: 'Product Sales Month Wise',
        pieHole: 0.5,
        is3D: 'true'
    };
Pie Chart in 3d look
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON