Outline
- Introduction
- Creating Google Chart using data in the code
- Creating Google Chart using data from database
1. Introduction
A chart is a graphical representation of the data. Displaying data as a chart makes it easy for users to see comparisons, patterns, and trends in the data. For example, instead of having to analyze several columns of data in a table, users always like to see at a glance which category of products sales what percentage in the last year.
Google chart tools are powerful, simple to use and very useful for creating very interactive charts for browsers and mobile devices.
2. Creating Google Chart using data in the code
Here we will see how to create Google Charts using data from the code rather than from database. And in section 3 we see how we can access data from database and draw the Google Charts.
i. Create New Project
Go to File > New > Project > Select asp.net web forms application >
Call it GoogleChartTest and Click OK.
ii. Add a Web Page
Create new web page and design for show google line chart with database data call it GoogleChart.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="GoogleChart.aspx.cs" Inherits="GoogleChartTest.GoogleChart" %> <asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server"> <h1>Charts using data from database</h1> <div class="row"> <div id="chart_div_1" class="col-md-4"> <%--Here line chart will display--%> </div> <div id="chart_div_2" class="col-md-4"> <%--Here bar chart will display--%> </div> <div id="chart_div_3" class="col-md-4"> <%--Here pie chart will display--%> </div> </div> </asp:Content>
iii. Add JQuery code
Right click the Scripts folder and add new javascript file. Put the code in the file, give it proper name (revenue_chart.js) and save it.
// The script that draws the chart
// Load the Visualization API and the corechart package.
google.charts.load('current', { packages: ['corechart'] });
// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(drawChart);
// Callback that creates and populates a data table,
// instantiates the pie chart, passes in the data and
// draws it.
function drawChart() {
// Create the data table.
var data = new google.visualization.DataTable();
data.addColumn('string', 'Year');
data.addColumn('number', 'Mobile');
data.addColumn('number', 'Tablet');
data.addColumn('number', 'Laptop');
data.addRows([
["2012", 3000, 3600, 5600],
["2013", 2500, 4590, 6000],
["2014", 5500, 4800, 5600],
["2015", 5900, 4200, 5200],
["2016", 7000, 4000, 6010],
]);
// Set chart options
var options = {
'title': 'Revenue',
'is3D': true,
'pointSize': 3,
'width': 400,
'height': 300
};
// Instantiate and draw our chart, passing in some options.
var chart = new google.visualization.LineChart(document.getElementById('chart_div_1'));
chart.draw(data, options);
// Instantiate and draw our chart, passing in some options.
var chart3 = new google.visualization.ColumnChart(document.getElementById('chart_div_2'));
chart3.draw(data, options);
// Instantiate and draw our chart, passing in some options.
var chart2 = new google.visualization.PieChart(document.getElementById('chart_div_3'));
chart2.draw(data, options);
}
Then put the js file and the library in the header of the Site.Master
<script src="Scripts/jquery-1.10.2.js"></script> <%--Load the AJAX API--%> <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> <%--Load the AJAX API--%> <script src="Scripts/chart_from_db.js"></script>
iv. Run Application
Run the application and you will get the graphs on the web page.
3. Creating Google Chart using data from database
In this section we will see how to create Google Charts using data from database.
i. Create New Project
Go to File > New > Project > Select asp.net web forms application >
Call it GoogleChartTest and Click OK.
ii. Add a Database
Go to Solution Explorer > Right Click on App_Data folder > Add > SQL Server Database
Enter Database name (I call it MyDatabase) then click Add.
iii. Create a Table
Go to Solution Explorer > expand the App_Data and double click MyDatabase to Open it. Right Click on Table > Add New Table > Add Columns set the table name to CompRevenue and click the Update to update the database
In this example, I have used one tables as below
Insert the following data to the table
| SLID | Year | Mobile | Tablet | Laptop |
| 1 | 2012 | 3000 | 3600 | 5600 |
| 2 | 2013 | 2500 | 4590 | 6000 |
| 3 | 2014 | 5500 | 4800 | 5600 |
| 4 | 2015 | 5900 | 4200 | 5200 |
| 5 | 2016 | 7000 | 4000 | 6010 |
iv. Add Entity Data Model
Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data
Enter model name (RevenueModel) and click OK
A popup window will come (Entity Data Model Wizard) > Select EF Designer from database > Next
Chose your data connection > select your database > next > Select tables > enter Model Namespace (RevenueModel) > Finish.
v. Add a Web Page
Create new web page and design for show google line chart with database data call it GoogleChart.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="GoogleChart.aspx.cs" Inherits="GoogleChartTest.GoogleChart" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
<h1>Charts using data from database</h1>
<div class="row">
<div id="chart_div_1" class="col-md-4">
<%--Here line chart will display--%>
</div>
<div id="chart_div_2" class="col-md-4">
<%--Here bar chart will display--%>
</div>
<div id="chart_div_3" class="col-md-4">
<%--Here pie chart will display--%>
</div>
</div>
</asp:Content>
vi. Add JQuery code
Right click the Scripts folder and add new javascript file. Put the code in the file, give it proper name (revenue_chart.js) and save it. This Jquery code to call server side function and get data from our database.
// The script that draws the chart
var chartData; // globar variable for hold chart data
// Load the Visualization API and the corechart package.
google.charts.load('current', { packages: ['corechart'] });
// Here We will fill chartData from the database using ajax HTTP request
$(document).ready(function () {
$.ajax({
url: "GoogleChart.aspx/GetData",
data: "",
dataType: "json",
type: "POST",
contentType: "application/json; chartset=utf-8",
success: function (data) {
chartData = data.d;
},
error: function () {
alert("Error loading data! Please try again.");
}
}).done(function () {
// after complete loading data by calling drawChart
google.charts.setOnLoadCallback(drawChart);
});
});
// Callback that creates and populates a data table
function drawChart() {
// Create the dataset (DataTable)
var data = new google.visualization.DataTable();
// add the columns
data.addColumn('string', 'Year');
data.addColumn('number', 'Electronics');
data.addColumn('number', 'BookAndMedia');
data.addColumn('number', 'HomeAndKitchen');
// add rows
for (i = 0; i < chartData.length; i++) {
// avoid the first row which is the column name
if (i != 0)
{
console.log(chartData[i]);
data.addRows([chartData[i]]);
}
}
// set options for the chart
var options = {
'title': "Company Revenue",
'pointSize': 3,
'is3D': true,
'width': 400,
'height': 300
};
// Instantiate and draw Line chart, passing in some options.
var chart4 = new google.visualization.LineChart(document.getElementById('chart_div_4'));
chart4.draw(data, options);
// Instantiate and draw Column chart, passing in some options.
var chart6 = new google.visualization.ColumnChart(document.getElementById('chart_div_5'));
chart6.draw(data, options);
// Instantiate and draw Pie chart, passing in some options.
var chart5 = new google.visualization.PieChart(document.getElementById('chart_div_6'));
chart5.draw(data, options);
}
Then put the js file and the library in the header of the Site.Master
<script src="Scripts/jquery-1.10.2.js"></script> <%--Load the AJAX API--%> <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> <%--Load the AJAX API--%> <script src="Scripts/chart_from_db.js"></script>
vii. Write server side code (function) to get data from our database
In the GoogleChart.aspx.cs we are going to write a function that gets data from our database. First we add these namespaces to it.
using System.Web.Script.Services; using System.Web.Services;
So our GoogleChart.aspx.cs file will look like this with its function that will be called by the jquery code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Script.Services;
using System.Web.Services;
namespace GoogleChartTest
{
public partial class GoogleChart : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
GetData();
}
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static object[] GetData()
{
List<CompRevenue> data = new List<CompRevenue>();
//Here MyDatabaseEntities is our dbContext
using (CompRevenueEntity dc = new CompRevenueEntity())
{
// get the data from the database
data = dc.CompRevenues.ToList();
}
// Create the chartData with the data count + 1 for the header
var chartData = new object[data.Count + 1];
// Add the header to the first row
chartData[0] = new object[]{
"Year",
"Mobile",
"Tablet",
"Laptop"
};
// go through the data and add it to the chartData
int j = 0;
foreach (var i in data)
{
j++;
chartData[j] = new object[] { i.Year.ToString(), i.Mobile, i.Tablet, i.Laptop };
}
return chartData;
}
}
}
Then put the js file and the library in the header of the Site.Master
viii. Run Application
Run the application and you will get the graphs on the web page.
