ASP.NET

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.


Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs