In this article I will explain how to use Google Chart APIs with database in ASP.Net using C#, VB.Net and jQuery.
In this example I will populate Pie chart, 3D Pie chart and Doughnut chart from database using jQuery AJAX and WebMethods in ASP.Net.
Database
For this article I am making use of the Microsoft’s Northwind Database. Download and install instructions are provided in the link below
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Web.Services
Imports System.Configuration
Imports System.Data.SqlClient
WebMethod
The following WebMethod will be used for populating the Pie chart, 3D Pie chart and Doughnut chart from database.
The SQL Query gets the City-wise Order distribution for the country United States. The results are populated into a two dimensional object array using DataReader, which is finally sent to client as a Generic List of objects.
C#
[WebMethod]
public static List<object> GetChartData()
{
string query = "SELECT ShipCity, COUNT(orderid) TotalOrders";
query += " FROM Orders WHERE ShipCountry = 'USA' GROUP BY ShipCity";
string constr = ConfigurationManager.ConnectionStrings["constr"].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 chartData;
}
}
}
VB.Net
<WebMethod()> _
Public Shared Function GetChartData() As List(Of Object)
Dim query As String = "SELECT ShipCity, COUNT(orderid) TotalOrders"
query += " FROM Orders WHERE ShipCountry = 'USA' GROUP BY ShipCity"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim chartData As New List(Of Object)()
chartData.Add(New Object() {"ShipCity", "TotalOrders"})
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand(query)
cmd.CommandType = CommandType.Text
cmd.Connection = con
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
chartData.Add(New Object() {sdr("ShipCity"), sdr("TotalOrders")})
End While
End Using
con.Close()
Return chartData
End Using
End Using
End Function
Populate Google Charts from database in ASP.Net using jQuery AJAX
The concept is simple i.e. to get the chart data from the database with the use of WebMethod (described earlier) and jQuery AJAX and then use the data for chart population.
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 WebMethod 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.
<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() {
var options = {
title: 'USA City Distribution'
};
$.ajax({
type: "POST",
url: "Default.aspx/GetChartData",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var data = google.visualization.arrayToDataTable(r.d);
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: 900px; height: 500px;">
</div>
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.
<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() {
var options = {
title: 'USA City Distribution',
is3D: true
};
$.ajax({
type: "POST",
url: "Default.aspx/GetChartData",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var data = google.visualization.arrayToDataTable(r.d);
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: 900px; height: 500px;">
</div>
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.
<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() {
var options = {
title: 'USA City Distribution',
pieHole: 0.5
};
$.ajax({
type: "POST",
url: "Default.aspx/GetChartData",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var data = google.visualization.arrayToDataTable(r.d);
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: 900px; height: 500px;">
</div>
Demo
Downloads
