In this article I will explain with an example, how to create Column Chart with database using Google Charts API in ASP.Net using C# and VB.Net.
The Column Chart will be populated with database data with the help of jQuery AJAX and WebMethod 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 Column 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 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.
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: 'USA City Distribution',
width: 600,
height: 400,
bar: { groupWidth: "95%" },
legend: { position: "none" },
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 Column Chart example with database in ASP.Net using C# and VB.Net]()
Demo
Downloads
