How To Implement Google Column Chart Dynamically Using Entity Framework And MVC 5

Introduction

In this article, I will demonstrate how to implement Google Column Chart dynamically using Entity Framework in MVC5. I will use jQuery AJAX to retrieve the data from the database and display it in Google Column Chart. The chart will animate columns with their respective data display in it.

Step 1

Open SQL Server 2014 and create a database table to insert and retrieve the data.

CREATE TABLE [dbo].[CompanyHiring](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Year] [int] NULL,
[Accounts] [int] NULL,
[HR] [int] NULL,
[IT] [int] NULL,
CONSTRAINT [PK_CompanyHiring] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Screenshot for a database table

Step 2

Open Visual Studio 2015, click on New Project and create an empty web application project.

Screenshot 1 for creating a new project

After clicking on New Project, one window will appear. Select Web from the left panel, choose ASP.NET Web Application to give a meaningful name to your project. Then, click on OK as shown in the below screenshot.

Screenshot 2 for creating a new project

After clicking on OK, one more window will appear. Choose Empty, check on MVC checkbox, and click on OK.

Screenshot for creating a new project-3

After clicking on OK, the project will be created with the name of MvcGoogleColumnChart_Demo.

Step 3

Add Entity Framework, right click on Models folder, select Add. Then, select New Item, then click on it.

Screenshot 1 for adding Entity Framework

After clicking on the new item, you will get a window from there. Select Data from the left panel and choose ADO.NET Entity Data Model, give it a name as DBModels (This name is not mandatory; you can give any name), and click on Add.

Screenshot 2 for adding Entity Framework

After you click on Add, a wizard will open. Choose EF Designer from the database and click Next.

Screenshot 3 for adding Entity Framework

On the next window, choose New Connection.

Screenshot 4 for adding Entity Framework

Another window will appear. Add your server name. If it is local, then enter dot (.). Choose your database and click on OK.

Screenshot 5 for adding Entity Framework

The connection will get added. If you wish to save it, connect as you want. You can change the name of your connection below. It will save the connection in web config, then click on Next.

Screenshot 6 for adding Entity Framework

After clicking on NEXT, another window will appear. Choose database table name as shown in the below screenshot. Then, click on Finish. Entity Framework will be added and respective class gets generated under Models folder.

Screenshot 7 for adding Entity Framework

Screenshot 8 for adding Entity Framework

The following class will be added.

namespace MvcGoogleColumnChart_Demo.Models
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
public partial class CompanyHiring
{
public int ID { get; set; }
[Required(ErrorMessage ="Please enter year hired")]
public Nullable<int> Year { get; set; }
[Required(ErrorMessage = "Enter number of employees hired in Account department")]
public Nullable<int> Accounts { get; set; }
[Required(ErrorMessage = "Enter number of employees hired in HR department")]
public Nullable<int> HR { get; set; }
[Required(ErrorMessage = "Enter number of employees hired in IT department")]
public Nullable<int> IT { get; set; }
}
}

Step 4

Right click on Controllers folder, select Add, then choose Controller.

After clicking on the Controller, a window will appear. Choose MVC5 Controller-Empty and click on Add.

After clicking on Add, another window will appear with DefaultController. Change the name to HomeController, then click on Add. HomeController will be added to Controllers folder. Remember, don’t change Controller. Instead of Default just change Home.

Add the following namespace in Controller.

using MvcGoogleColumnChart_Demo. Models;

Complete Controller code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcGoogleColumnChart_Demo.Models;
namespace MvcGoogleColumnChart_Demo.Controllers
{
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
public ActionResult Column()
{
return View();
}
[HttpPost]
public ActionResult Column(CompanyHiring company)
{
using (DBModel db = new DBModel())
{
db.CompanyHirings.Add(company);
db.SaveChanges();
}
return View();
}
public JsonResult GetHireData()
{
List<CompanyHiring> CompanyHireData = new List<CompanyHiring>();
using (DBModel db=new DBModel())
{
CompanyHireData = db.CompanyHirings.OrderBy(a => a.Year).ToList();
}
var chartData = new object[CompanyHireData.Count + 1];
chartData[0] = new object[]{
"Year",
"Account",
"HR",
"IT"
};
int j = 0;
foreach (var i in CompanyHireData)
{
j++;
chartData[j] = new object[] { i.Year, i.Accounts, i.HR, i.IT};
}
return new JsonResult { Data = chartData, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
}
}
}

Step 5

Right-click on Column action method in Controller. An "Add View" window will appear with default Column name unchecked (use a Layout page), click on Add. The View will be added in Views folder under Home folder with name Column.

Screenshot for adding View

Step 6

Click on Tools, select NuGet Package Manager, then choose Manage NuGet Packages for Solution click on it.

Screenshot for NuGet Package

After that, a window will appear. Choose Browse type as bootstrap and install the package in the project.

Similarly, type jQuery and install the latest version of jQuery package in the project and jQuery validation file from NuGet, then close NuGet Solution.

Keep the required bootstrap and jQuery file and delete the remaining file if not using. Or you can download and add in the project.

Step 7

Add required script and style in head section of the View.

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<script src="~/scripts/jquery-3.3.1.min.js"></script>
<script src="~/scripts/bootstrap.min.js"></script>
<script src="~/scripts/ColumnCart.js"></script>
<script src="~/scripts/jquery.validate.min.js"></script>
<script src="~/scripts/jquery.validate.unobtrusive.min.js"></script>

Step 8

Right-click on Scripts folder, select Add, and choose JavaScript file. Give it a name as ColumnCart.js. Write the script to get data from the database.

$(document).ready(function () {
//Load Data Here
var chartData = null;
$.ajax({
url: '/Home/GetHireData',
type: 'GET',
dataType: 'json',
data: '',
success: function (d) {
chartData = d;
},
error: function () {
alert('Error!');
}
}).done(function () {
drawChart(chartData);
});
});
function drawChart(d) {
var chartData = d;
var data = null;
data = google.visualization.arrayToDataTable(chartData);
var view = new google.visualization.DataView(data);
view.setColumns([0, {
type: 'number',
label: data.getColumnLabel(0),
calc: function () { return 0; }
}, {
type: 'number',
label: data.getColumnLabel(1),
calc: function () { return 0; }
}, {
type: 'number',
label: data.getColumnLabel(2),
calc: function () { return 0; }
}, {
type: 'number',
label: data.getColumnLabel(3),
calc: function () { return 0; }
}]);
var chart = new google.visualization.ColumnChart(document.getElementById('visualization'));
var options = {
title: 'Company Hiring Report',
legend: 'bottom',
hAxis: {
title: 'Year',
format: '#'
},
vAxis: {
minValue: 0,
maxValue: 1000,
title: 'Company Hire Employee'
},
chartArea: {
left: 100, top: 50, width: '70%', height: '50%'
},
animation: {
duration: 1000
}
};
var runFirstTime = google.visualization.events.addListener(chart, 'ready', function () {
google.visualization.events.removeListener(runFirstTime);
chart.draw(data, options);
});
chart.draw(view, options);
}
google.load('visualization', '1', { packages: ['corechart'] });

Step 9

Design the View with HTML, cshtml, and bootstrap 4 classes.

<body>
@using (Html.BeginForm("Column", "Home", FormMethod.Post))
{
<div class="container py-4">
<h2 class="text-uppercase text-center">Column Chart With Animation using MVC5</h2>
<div class="card">
<div class="card-header bg-danger">
<h6 class="card-title text-uppercase text-white">Company hiring Report</h6>
</div>
<div class="card-body">
<button type="button" style="margin-bottom:10px;" class="btn btn-primary rounded-0" data-toggle="modal" data-target="#CompanyReport"><i class="fa fa-plus-circle"></i> Add New Report</button>
<div class="modal fade" id="CompanyReport">
<div class="modal-dialog modal-lg">
<div class="modal-content">
<div class="modal-header">
<h6 class="modal-title">New Hiring Report</h6>
<button type="button" class="close" data-dismiss="modal">×</button>
</div>
<div class="modal-body">
<div class="row">
<div class="col-sm-6 col-md-6 col-xs-12">
<div class="form-group">
<label>Report Year</label>
@Html.EditorFor(model => model.Year, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Year, "", new { @class = "text-danger" })
</div>
</div>
<div class="col-sm-6 col-md-6 col-xs-12">
<div class="form-group">
<label>Account</label>
@Html.EditorFor(model => model.Accounts, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Accounts, "", new { @class = "text-danger" })
</div>
</div>
</div>
<div class="row">
<div class="col-sm-6 col-md-6 col-xs-12">
<div class="form-group">
<label>HR</label>
@Html.EditorFor(model => model.HR, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.HR, "", new { @class = "text-danger" })
</div>
</div>
<div class="col-sm-6 col-md-6 col-xs-12">
<div class="form-group">
<label>IT</label>
@Html.EditorFor(model => model.IT, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.IT, "", new { @class = "text-danger" })
</div>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-danger rounded-0" data-dismiss="modal">Close</button>
<button class="btn btn-primary rounded-0">Submit Report</button>
</div>
</div>
</div>
</div>
<div id="visualization" style="width:100%; height:400px">
</div>
</div>
</div>
</div>
}
</body>

Complete View code

@model MvcGoogleColumnChart_Demo.Models.CompanyHiring
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Column</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<script src="~/scripts/jquery-3.3.1.min.js"></script>
<script src="~/scripts/bootstrap.min.js"></script>
<script src="~/scripts/ColumnCart.js"></script>
<script src="~/scripts/jquery.validate.min.js"></script>
<script src="~/scripts/jquery.validate.unobtrusive.min.js"></script>
</head>
<body>
@using (Html.BeginForm("Column", "Home", FormMethod.Post))
{
<div class="container py-4">
<h2 class="text-uppercase text-center">Column Chart With Animation using MVC5</h2>
<div class="card">
<div class="card-header bg-danger">
<h6 class="card-title text-uppercase text-white">Company hiring Report</h6>
</div>
<div class="card-body">
<button type="button" style="margin-bottom:10px;" class="btn btn-primary rounded-0" data-toggle="modal" data-target="#CompanyReport"><i class="fa fa-plus-circle"></i> Add New Report</button>
<div class="modal fade" id="CompanyReport">
<div class="modal-dialog modal-lg">
<div class="modal-content">
<div class="modal-header">
<h6 class="modal-title">Company Hiring New Report</h6>
<button type="button" class="close" data-dismiss="modal">×</button>
</div>
<div class="modal-body">
<div class="row">
<div class="col-sm-6 col-md-6 col-xs-12">
<div class="form-group">
<label>Year</label>
@Html.EditorFor(model => model.Year, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Year, "", new { @class = "text-danger" })
</div>
</div>
<div class="col-sm-6 col-md-6 col-xs-12">
<div class="form-group">
<label>Account</label>
@Html.EditorFor(model => model.Accounts, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Accounts, "", new { @class = "text-danger" })
</div>
</div>
</div>
<div class="row">
<div class="col-sm-6 col-md-6 col-xs-12">
<div class="form-group">
<label>HR</label>
@Html.EditorFor(model => model.HR, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.HR, "", new { @class = "text-danger" })
</div>
</div>
<div class="col-sm-6 col-md-6 col-xs-12">
<div class="form-group">
<label>IT</label>
@Html.EditorFor(model => model.IT, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.IT, "", new { @class = "text-danger" })
</div>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-danger rounded-0" data-dismiss="modal">Close</button>
<button class="btn btn-primary rounded-0">Submit Report</button>
</div>
</div>
</div>
</div>
<div id="visualization" style="width:100%; height:400px">
</div>
</div>
</div>
</div>
}
</body>
</html>

Step 10

Run Project ctrl+F5.

Screenshot 1

Screenshot 2

Conclusion

In this article.I have explained how we can dynamically implement Google Column Chart using Entity Framework and MVC5. We can add year of hiring,number of employee hired in account,hr and IT department clicking on Add New Report button.I hope it will be useful in your upcoming projects.

4 Comments

Join the conversation! Your thoughts help the community grow.

Asad Khan8y ago
Sir thanks...but if suppose for any legend the value is zero for particular columns , then it will be having space in between. How to remove that space? Basically, how to hide a particular column if its legend value is zero
- Farhan Ahmed8y ago
  Hi asad. 0 is also a value so it will accupy a space. It can be remove but it is tedious.
Gautam Das8y ago
Good article, can you post similar articles for some other charting frameworks as well.
- Farhan Ahmed8y ago
  Thank you. I will do it soon.