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 Core MVC.
This article will illustrate how to populate Pie and Doughnut Charts from database using the Google Charts API in ASP.Net Core MVC.
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Configuring the JSON Serializer setting
The first step is to configure the JSON Serializer settings in the Startup.cs file.
1. Open the Startup.cs class from the Solution Explorer window.
![ASP.Net Core Google Charts: Google (Pie / Doughnut) Chart example with database in ASP.Net Core MVC]()
2. Add the following namespace.
using Newtonsoft.Json.Serialization;
3. Then inside the ConfigureServices method, you will have to add the following code which will instruct the program to use Newtonsoft library for JSON serialization.
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc()
.AddJsonOptions(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());
}
Namespaces
You will need to import the following namespaces.
using System.Data;
using System.Data.SqlClient;
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 IActionResult Index()
{
return View();
}
[HttpPost]
public IActionResult AjaxMethod()
{
string query = "SELECT ShipCity, COUNT(orderid) TotalOrders";
query += " FROM Orders WHERE ShipCountry = 'USA' GROUP BY ShipCity";
string constr = @"Data Source=.\SQL2019;Initial Catalog=northwind;integrated security=true";
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="https://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>
![ASP.Net Core Google Charts: Google (Pie / Doughnut) Chart example with database in ASP.Net Core 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="https://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>
![ASP.Net Core Google Charts: Google (Pie / Doughnut) Chart example with database in ASP.Net Core 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="https://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>
![ASP.Net Core Google Charts: Google (Pie / Doughnut) Chart example with database in ASP.Net Core MVC]()
Downloads
