In this article I will explain with an example, how to use, implement and populate Charts from database using Google Charts API in ASP.Net MVC Razor.
This article will illustrate how to populate Pie and Doughnut Charts from database using the Google Charts API in ASP.Net MVC Razor.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Namespaces
You will need to import the following namespaces.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
Controller
The Controller consists of two Action methods.
Action method for handling GET operation
Inside this Action method, simply the View is returned.
Action method for handling AJAX POST operation
Inside this Action method, the statistical data for the Google Chart is fetched from the Orders table and it is populated into a two dimensional object array using DataReader, which is finally sent to View as a Generic List of objects in JSON format.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
[HttpPost]
public JsonResult AjaxMethod()
{
string query = "SELECT ShipCity, COUNT(orderid) TotalOrders";
query += " FROM Orders WHERE ShipCountry = 'USA' GROUP BY ShipCity";
string constr = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
List<object> chartData = new List<object>();
chartData.Add(new object[]
{
"ShipCity", "TotalOrders"
});
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
chartData.Add(new object[]
{
sdr["ShipCity"], sdr["TotalOrders"]
});
}
}
con.Close();
}
}
return Json(chartData);
}
}
View
Following are the different Views for displaying Pie Chart, 3D Pie Chart and Doughnut charts. The concept is simple, that inside View, there’s an HTML DIV which will render the chart and using jQuery AJAX call, the Controller’s Action method will be called to fetch the Chart data from database.
Finally the Chart data will be used to populate the Google Charts.
Pie Chart
The very first thing is to load the Google Chart API packages and when once all the packages are loaded then the drawChart method is invoked.
Inside this method a jQuery AJAX call to the Controller’s Action method is initiated which gets the records from the database as an array of objects.
The array of objects are converted to a Google Visualization DataTable and used for drawing the chart on to the specified HTML DIV element.
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width"/>
<title>Index</title>
</head>
<body>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/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/AjaxMethod",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var data = google.visualization.arrayToDataTable(r);
//Pie
var options = {
title: 'USA City Distribution'
};
var chart = new google.visualization.PieChart($("#chart")[0]);
chart.draw(data, options);
},
failure: function (r) {
alert(r.d);
},
error: function (r) {
alert(r.d);
}
});
}
</script>
<div id="chart" style="width: 500px; height: 400px;">
</div>
</body>
</html>
![Google Charts in ASP.Net MVC: Google (Pie / Doughnut) Chart example with database in ASP.Net MVC]()
3D Pie Chart
The 3D Pie chart is actually a Pie chart with a 3-dimensional look and feel. The population part is very similar to the Pie chart and we just need to set an additional property is3D to true.
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width"/>
<title>Index</title>
</head>
<body>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/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/AjaxMethod",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var data = google.visualization.arrayToDataTable(r);
//3D Pie
var options = {
title: 'USA City Distribution',
is3D: true
};
var chart = new google.visualization.PieChart($("#chart")[0]);
chart.draw(data, options);
},
failure: function (r) {
alert(r.d);
},
error: function (r) {
alert(r.d);
}
});
}
</script>
<div id="chart" style="width: 500px; height: 400px;">
</div>
</body>
</html>
![Google Charts in ASP.Net MVC: Google (Pie / Doughnut) Chart example with database in ASP.Net MVC]()
Doughnut Chart
The final chart is a Doughnut chart which again is similar to a Pie chart but has a hole at the center resembling a Doughnut. The population part is very similar to the Pie chart and we just need to set an additional property pieHole to the size of the hole we need.
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width"/>
<title>Index</title>
</head>
<body>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/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/AjaxMethod",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var data = google.visualization.arrayToDataTable(r);
//Doughnut
var options = {
title: 'USA City Distribution',
pieHole: 0.5
};
var chart = new google.visualization.PieChart($("#chart")[0]);
chart.draw(data, options);
},
failure: function (r) {
alert(r.d);
},
error: function (r) {
alert(r.d);
}
});
}
</script>
<div id="chart" style="width: 500px; height: 400px;">
</div>
</body>
</html>
![Google Charts in ASP.Net MVC: Google (Pie / Doughnut) Chart example with database in ASP.Net MVC]()
Downloads
