In this article I will explain with an example, how to create Stacked Column Chart with database using Google Charts API in ASP.Net using C# and VB.Net.
The Stacked Column Chart will be populated with database data with the help of jQuery AJAX and WebMethod in ASP.Net using C# and VB.Net.
The Stacked Column Chart is different from Column Chart as compared to Column Chart which simply displays 2 sets of Values one on X axis and other Y axis, the Stacked Column Chart displays an additional set of information by dividing the each Column in different sections.
Understanding the structure of Google Visualization DataTable
Consider an example of the following Stacked Column Chart.
![Google Stacked Column Chart example with database in ASP.Net using C# and VB.Net]()
The Google Visualization DataTable of the above Stacked Column Chart will look as follows.
![Google Stacked Column Chart example with database in ASP.Net using C# and VB.Net]()
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.
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 Google Stacked Column Chart from database.
The SQL Query gets the Country-wise Order distribution for three years. The results are populated into a multi-dimensional object array using DataReader, which is finally sent to client as a Generic List of objects.
The populated DataTable will contain statistical data for multiple countries in following format. Basically it is the statistical data of Orders of each Country for each Year. Example, Total Orders of Brazil for Year 1996, 1997 and 1998.
![Google Stacked Column Chart example with database in ASP.Net using C# and VB.Net]()
Here the Country Names will be the Stacked data for the Chart and the Year values will form the Y axis and the Order Totals will form the X axis.
C#
[WebMethod]
public static List<object> GetChartData()
{
//Fetch the Statistical data from database.
string query = "SELECT ShipCountry, DATEPART(Year, OrderDate) [Year], COUNT(OrderId) [Total]";
query += " FROM Orders WHERE ShipCountry IN ('Germany', 'France', 'Brazil', 'Canada')";
query+= " GROUP BY ShipCountry, DATEPART(Year, OrderDate)";
DataTable dt = GetData(query);
//Get the DISTINCT Countries.
List<object> chartData = new List<object>();
List<string> countries = (from p in dt.AsEnumerable()
select p.Field<string>("ShipCountry")).Distinct().ToList();
//Insert Label for Country in First position.
countries.Insert(0, "Country");
//Add the Countries Array to the Chart Array.
chartData.Add(countries.ToArray());
//Get the DISTINCT Years.
List<int> years = (from p in dt.AsEnumerable()
select p.Field<int>("Year")).Distinct().ToList();
//Loop through the Years.
foreach (int year in years)
{
//Get the Total of Orders for each Country for the Year.
List<object> totals = (from p in dt.AsEnumerable()
where p.Field<int>("Year") == year
select p.Field<int>("Total")).Cast<object>().ToList();
//Insert the Year value as Label in First position.
totals.Insert(0, year.ToString());
//Add the Years Array to the Chart Array.
chartData.Add(totals.ToArray());
}
return chartData;
}
private static DataTable GetData(string query)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlDataAdapter sda = new SqlDataAdapter(query, con))
{
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
}
VB.Net
<WebMethod()> _
Public Shared Function GetChartData() As List(Of Object)
'Fetch the Statistical data from database.
Dim query As String = "SELECT ShipCountry, DATEPART(Year, OrderDate) [Year], COUNT(OrderId) [Total]"
query += " FROM Orders WHERE ShipCountry IN ('Germany', 'France', 'Brazil', 'Canada')"
query += " GROUP BY ShipCountry, DATEPART(Year, OrderDate)"
Dim dt As DataTable = GetData(query)
'Get the DISTINCT Countries.
Dim chartData As List(Of Object) = New List(Of Object)()
Dim countries As List(Of String) = (From p In dt.AsEnumerable() Select p.Field(Of String)("ShipCountry")).Distinct().ToList()
'Insert Label for Country in First position.
countries.Insert(0, "Country")
'Add the Countries Array to the Chart Array.
chartData.Add(countries.ToArray())
'Get the DISTINCT Years.
Dim years As List(Of Integer) = (From p In dt.AsEnumerable() Select p.Field(Of Integer)("Year")).Distinct().ToList()
'Loop through the Years.
For Each year As Integer In years
'Get the Total of Orders for each Country for the Year.
Dim totals As List(Of Object) = (From p In dt.AsEnumerable() Where p.Field(Of Integer)("Year") = year _
Select p.Field(Of Integer)("Total")).Cast(Of Object)().ToList()
'Insert the Year value as Label in First position.
totals.Insert(0, year.ToString())
'Add the Years Array to the Chart Array.
chartData.Add(totals.ToArray())
Next
Return chartData
End Function
Private Shared Function GetData(ByVal query As String) As DataTable
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Function
Populate Google Stacked Column Chart from database in ASP.Net
The very first thing is to load the Google Chart API packages and once all the packages are loaded then the drawChart method is invoked.
Inside the drawChart method a jQuery AJAX call to the WebMethod is initiated which gets the records from the database as an array of objects.
Following is the format of the multi-dimensional array received from server before it is converted to the Google Visualization DataTable.
![Google Stacked Column Chart example with database in ASP.Net using C# and VB.Net]()
The array of objects are converted to a Google Visualization DataTable and used for drawing the Column 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: 'Country wise Order Distribution',
width: 600,
height: 400,
legend: { position: 'top', maxLines: 3 },
bar: { groupWidth: '75%' },
isStacked: 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.ColumnChart($("#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>
Screenshot
![Google Stacked Column Chart example with database in ASP.Net using C# and VB.Net]()
Demo
Downloads
