In this article I’m going
to explain how to create google multi series Line chart from database using
Google visualization in ASP.NET.
Line and area charts are
used to show trends and performance over a period of time. For example, if you
want to see how company’s performance has been growing which includes different
aspects like purchase, sales and expenses, these charts can help you understand
the overall trend without going into the individual values themselves.
Here I’ll show you how to create multi
series Line chart from SQL Server database. First we have to create table.
Table design and sample data given below.
Table
design:
|
Column name
|
data type
|
|
id
|
int
|
|
year
|
varchar(10)
|
|
purchase
|
varchar(10)
|
|
sales
|
varchar(10)
|
|
expences
|
varchar(10)
|
Sample
data:
|
id
|
year
|
purchase
|
sales
|
expences
|
|
1
|
2000
|
100000
|
150000
|
50000
|
|
2
|
2001
|
60000
|
100000
|
30000
|
|
3
|
2002
|
140000
|
200000
|
100000
|
|
4
|
2003
|
120000
|
180000
|
80000
|
|
5
|
2004
|
170000
|
200000
|
90000
|
|
6
|
2005
|
130000
|
250000
|
200000
|
Here
we’ve to use following code to load google JSAPI library
<script type="text/javascript"
src="https://www.google.com/jsapi"></script>
Designer
source code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Multi series Line chart using Google
Visualization</title>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Literal ID="lt" runat="server"></asp:Literal>
<div id="chart_div"></div>
</div>
</form>
</body>
</html>
Note:
Here we have to use following
namespace for StringBuilder
class.
using System.Text;
C#
code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;
public partial class _Default :
System.Web.UI.Page
{
StringBuilder
str = new StringBuilder();
//Get connection
string from web.config
SqlConnection
conn = new SqlConnection("Data source=localhost; Initial catalog=Demo;
Integrated security=true");
protected void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
chart_bind();
}
}
private DataTable GetData()
{
DataTable
dt = new DataTable();
conn.Open();
string
cmd = "select * from Sales";
SqlDataAdapter
adp = new SqlDataAdapter(cmd,
conn);
adp.Fill(dt);
conn.Close();
return
dt;
}
private void chart_bind()
{
DataTable
dt = new DataTable();
try
{
dt = GetData();
str.Append(@"<script
type=*text/javascript*> google.load( *visualization*, *1*,
{packages:[*corechart*]});
google.setOnLoadCallback(drawChart);
function
drawChart() {
var data = new
google.visualization.DataTable();
data.addColumn('string', 'Year');
data.addColumn('number', 'Purchase');
data.addColumn('number', 'Sales');
data.addColumn('number', 'Expenses');
data.addRows(" + dt.Rows.Count + ");");
for
(int
i = 0; i <= dt.Rows.Count - 1; i++)
{
str.Append("data.setValue( " + i + "," + 0 + ","
+ "'" + dt.Rows[i]["year"].ToString() + "');");
str.Append("data.setValue(" + i + "," + 1 + ","
+ dt.Rows[i]["purchase"].ToString()
+ ") ;");
str.Append("data.setValue(" + i + "," + 2 + ","
+ dt.Rows[i]["sales"].ToString() +
") ;");
str.Append("data.setValue(" + i + "," + 3 + ","
+ dt.Rows[i]["expences"].ToString()
+ ");");
}
str.Append(" var chart = new
google.visualization.LineChart(document.getElementById('chart_div'));");
str.Append("
chart.draw(data, {width: 660, height: 300, title: 'Company Performance',");
str.Append("hAxis:
{title: 'Year', titleTextStyle: {color: 'green'}}");
str.Append("});
}");
str.Append("</script>");
lt.Text = str.ToString().Replace('*',
'"');
}
catch
{ }
}
}
For more details please
refer following link.
https://developers.google.com/chart/interactive/docs/gallery/
linechart