Post
Ask Question

Google Charts API Using Database in ASP.Net

Introduction

This article introduces use of the Google Charts API with a database in ASP.NET. Google has a jQuery API for chart and graph visuals. I have explored a little more and used them with a SQL Server database data and simply plunged chart data with jQuery into an aspx page and it works.

At the following URL you will find technical documentation of the Google Charts API:

https://google-developers.appspot.com/chart/

1. Combo charts

The final result will be as shown below.



Step 1

Prepare the data in the SQL Server database as in the following:



Step 2

The following is the Stored Procedure to fetch data required for the chart:

  1. CREATE PROCEDURE dbo.GetData      
  2. AS  
  3. BEGIN  
  4.     SELECT *   
  5.     FROM tbl_data  
  6.   
  7. END 

Step 3

The following is the .aspx Page Script:

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4. <html xmlns="http://www.w3.org/1999/xhtml">  
  5. <head runat="server">  
  6.     <title>Charts Example</title>  
  7. </head>  
  8. <body>  
  9.     <form id="form1" runat="server">  
  10.     <div>  
  11.         <script type="text/javascript" src="https://www.google.com/jsapi"></script>  
  12.         <asp:GridView ID="gvData" runat="server">  
  13.         </asp:GridView>  
  14.         <br />  
  15.         <br />  
  16.         <asp:Literal ID="ltScripts" runat="server"></asp:Literal>  
  17.         <div id="chart_div" style="width: 660px; height: 400px;">  
  18.         </div>  
  19.     </div>  
  20.     </form>  
  21. </body>  
  22. </html> 

Step 4

The following is the ..aspx Page code behind:

  1. #region " [ Using ] "  
  2. using System;  
  3. using System.Web.UI;  
  4. using System.Data.SqlClient;  
  5. using System.Data;  
  6. using System.Configuration;  
  7. using System.Text;  
  8. #endregion  
  9.   
  10.   
  11. public partial class _Default : System.Web.UI.Page  
  12. {  
  13.     protected void Page_Load(object sender, EventArgs e)  
  14.     {  
  15.         if (!Page.IsPostBack)  
  16.         {  
  17.             // Bind Gridview  
  18.             BindGvData();  
  19.   
  20.             // Bind Charts  
  21.             BindChart();  
  22.         }  
  23.     }  
  24.   
  25.     private void BindGvData()  
  26.     {  
  27.         gvData.DataSource = GetChartData();  
  28.         gvData.DataBind();  
  29.     }  
  30.   
  31.     private void BindChart()  
  32.     {  
  33.         DataTable dsChartData = new DataTable();  
  34.         StringBuilder strScript = new StringBuilder();  
  35.   
  36.         try  
  37.         {  
  38.             dsChartData = GetChartData();  
  39.   
  40.             strScript.Append(@"<script type='text/javascript'>  
  41.                     google.load('visualization', '1', {packages: ['corechart']});</script>  
  42.   
  43.                     <script type='text/javascript'>  
  44.                     function drawVisualization() {         
  45.                     var data = google.visualization.arrayToDataTable([  
  46.                     ['Month', 'Bolivia', 'Ecuador', 'Madagascar', 'Average'],");  
  47.   
  48.             foreach (DataRow row in dsChartData.Rows)  
  49.             {  
  50.                 strScript.Append("['" + row["Month"] + "'," + row["Bolivia"] + "," +  
  51.                     row["Ecuador"] + "," + row["Madagascar"] + "," + row["Avarage"] + "],");  
  52.             }  
  53.             strScript.Remove(strScript.Length - 1, 1);  
  54.             strScript.Append("]);");  
  55.   
  56.             strScript.Append("var options = { title : 'Monthly Coffee Production by Country', vAxis: {title: 'Cups'},  hAxis: {title: 'Month'}, seriesType: 'bars', series: {3: {type: 'area'}} };");  
  57.             strScript.Append(" var chart = new google.visualization.ComboChart(document.getElementById('chart_div'));  chart.draw(data, options); } google.setOnLoadCallback(drawVisualization);");  
  58.             strScript.Append(" </script>");  
  59.   
  60.             ltScripts.Text = strScript.ToString();  
  61.         }  
  62.         catch  
  63.         {  
  64.         }  
  65.         finally  
  66.         {  
  67.             dsChartData.Dispose();  
  68.             strScript.Clear();  
  69.         }  
  70.     }  
  71.   
  72.     /// <summary>  
  73.     /// fetch data from mdf file saved in app_data  
  74.     /// </summary>  
  75.     /// <returns>DataTable</returns>  
  76.     private DataTable GetChartData()  
  77.     {  
  78.         DataSet dsData = new DataSet();  
  79.         try  
  80.         {  
  81.             SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);  
  82.             SqlDataAdapter sqlCmd = new SqlDataAdapter("GetData", sqlCon);  
  83.             sqlCmd.SelectCommand.CommandType = CommandType.StoredProcedure;  
  84.   
  85.             sqlCon.Open();  
  86.   
  87.             sqlCmd.Fill(dsData);  
  88.   
  89.             sqlCon.Close();  
  90.         }  
  91.         catch  
  92.         {  
  93.             throw;  
  94.         }  
  95.         return dsData.Tables[0];  
  96.     }  
  97. } 

Step 5

The following is the .Web.config file:

  1. <?xml version="1.0"?>  
  2. <configuration>  
  3.     <system.web>  
  4.         <compilation debug="true" targetFramework="4.0"/>  
  5.     </system.web>  
  6.     <connectionStrings>  
  7.         <add name="connectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>  
  8.     </connectionStrings>  
  9. </configuration> 

Step 6

Run it.



2. Pie Chart

The final result will be as shown below.



Step 1

Prepare the data as in the following:



Step 2

Prepare the Stored Procedure as in the following:

  1. CREATE PROCEDURE GetPieChartData  
  2. AS  
  3. begin     
  4.       
  5.     SELECT *   
  6.     FROM tbl_Data2  
  7. end 

Step 3

The following  is the .aspx script:

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="frmPieChart.aspx.cs" Inherits="frmPieChart" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4. <html xmlns="http://www.w3.org/1999/xhtml">  
  5. <head runat="server">  
  6.     <title></title>  
  7. </head>  
  8. <body>  
  9.     <form id="form1" runat="server">  
  10.     <div>  
  11.         <script type="text/javascript" src="https://www.google.com/jsapi"></script>  
  12.         <asp:GridView ID="gvData" runat="server">  
  13.         </asp:GridView>  
  14.         <br />  
  15.         <br />  
  16.         <asp:Literal ID="ltScripts" runat="server"></asp:Literal>  
  17.         <div id="piechart_3d" style="width: 900px; height: 500px;">  
  18.         </div>  
  19.     </div>  
  20.     </form>  
  21. </body>  
  22. </html> 

Step 4

The following  is the .aspx code behind:

  1. #region " [ Using ] "  
  2. using System;  
  3. using System.Web.UI;  
  4. using System.Data.SqlClient;  
  5. using System.Data;  
  6. using System.Configuration;  
  7. using System.Text;  
  8. #endregion  
  9.   
  10.   
  11. public partial class frmPieChart : System.Web.UI.Page  
  12. {  
  13.     protected void Page_Load(object sender, EventArgs e)  
  14.     {  
  15.         if (!Page.IsPostBack)  
  16.         {  
  17.             // Bind Gridview  
  18.             BindGvData();  
  19.   
  20.             // Bind Charts  
  21.             BindChart();  
  22.         }  
  23.     }  
  24.   
  25.     private void BindGvData()  
  26.     {  
  27.         gvData.DataSource = GetChartData();  
  28.         gvData.DataBind();  
  29.     }  
  30.   
  31.   
  32.     private void BindChart()  
  33.     {  
  34.         DataTable dsChartData = new DataTable();  
  35.         StringBuilder strScript = new StringBuilder();  
  36.   
  37.         try  
  38.         {  
  39.             dsChartData = GetChartData();  
  40.   
  41.             strScript.Append(@"<script type='text/javascript'>  
  42.                     google.load('visualization', '1', {packages: ['corechart']}); </script>  
  43.                       
  44.                     <script type='text/javascript'>  
  45.                      
  46.                     function drawChart() {         
  47.                     var data = google.visualization.arrayToDataTable([  
  48.                     ['Task', 'Hours of Day'],");  
  49.   
  50.             foreach (DataRow row in dsChartData.Rows)  
  51.             {  
  52.                 strScript.Append("['" + row["Task"] + "'," + row["Hours"] + "],");  
  53.             }  
  54.             strScript.Remove(strScript.Length - 1, 1);  
  55.             strScript.Append("]);");  
  56.   
  57.             strScript.Append(@" var options = {     
  58.                                     title: 'My Daily Schedule',            
  59.                                     is3D: true,          
  60.                                     };   ");  
  61.   
  62.             strScript.Append(@"var chart = new google.visualization.PieChart(document.getElementById('piechart_3d'));          
  63.                                 chart.draw(data, options);        
  64.                                 }    
  65.                             google.setOnLoadCallback(drawChart);  
  66.                             ");  
  67.             strScript.Append(" </script>");  
  68.   
  69.             ltScripts.Text = strScript.ToString();  
  70.         }  
  71.         catch  
  72.         {  
  73.         }  
  74.         finally  
  75.         {  
  76.             dsChartData.Dispose();  
  77.             strScript.Clear();  
  78.         }  
  79.     }  
  80.   
  81.     /// <summary>  
  82.     /// fetch data from mdf file saved in app_data  
  83.     /// </summary>  
  84.     /// <returns>DataTable</returns>  
  85.     private DataTable GetChartData()  
  86.     {  
  87.         DataSet dsData = new DataSet();  
  88.         try  
  89.         {  
  90.             SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);  
  91.             SqlDataAdapter sqlCmd = new SqlDataAdapter("GetPieChartData", sqlCon);  
  92.             sqlCmd.SelectCommand.CommandType = CommandType.StoredProcedure;  
  93.   
  94.             sqlCon.Open();  
  95.   
  96.             sqlCmd.Fill(dsData);  
  97.   
  98.             sqlCon.Close();  
  99.         }  
  100.         catch  
  101.         {  
  102.             throw;  
  103.         }  
  104.         return dsData.Tables[0];  
  105.     }  
  106. } 

Step 5

Run it.



Similar Articles