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
  7. END

Step 3

The following is the .aspx Page Script:

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
  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
  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();
  20. // Bind Charts
  21. BindChart();
  22. }
  23. }
  25. private void BindGvData()
  26. {
  27. gvData.DataSource = GetChartData();
  28. gvData.DataBind();
  29. }
  31. private void BindChart()
  32. {
  33. DataTable dsChartData = new DataTable();
  34. StringBuilder strScript = new StringBuilder();
  36. try
  37. {
  38. dsChartData = GetChartData();
  40. strScript.Append(@"<script type='text/javascript'>
  41. google.load('visualization', '1', {packages: ['corechart']});</script>
  43. <script type='text/javascript'>
  44. function drawVisualization() {
  45. var data = google.visualization.arrayToDataTable([
  46. ['Month', 'Bolivia', 'Ecuador', 'Madagascar', 'Average'],");
  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("]);");
  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>");
  60. ltScripts.Text = strScript.ToString();
  61. }
  62. catch
  63. {
  64. }
  65. finally
  66. {
  67. dsChartData.Dispose();
  68. strScript.Clear();
  69. }
  70. }
  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;
  85. sqlCon.Open();
  87. sqlCmd.Fill(dsData);
  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
  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" %>
  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
  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();
  20. // Bind Charts
  21. BindChart();
  22. }
  23. }
  25. private void BindGvData()
  26. {
  27. gvData.DataSource = GetChartData();
  28. gvData.DataBind();
  29. }
  32. private void BindChart()
  33. {
  34. DataTable dsChartData = new DataTable();
  35. StringBuilder strScript = new StringBuilder();
  37. try
  38. {
  39. dsChartData = GetChartData();
  41. strScript.Append(@"<script type='text/javascript'>
  42. google.load('visualization', '1', {packages: ['corechart']}); </script>
  44. <script type='text/javascript'>
  46. function drawChart() {
  47. var data = google.visualization.arrayToDataTable([
  48. ['Task', 'Hours of Day'],");
  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("]);");
  57. strScript.Append(@" var options = {
  58. title: 'My Daily Schedule',
  59. is3D: true,
  60. }; ");
  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>");
  69. ltScripts.Text = strScript.ToString();
  70. }
  71. catch
  72. {
  73. }
  74. finally
  75. {
  76. dsChartData.Dispose();
  77. strScript.Clear();
  78. }
  79. }
  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;
  94. sqlCon.Open();
  96. sqlCmd.Fill(dsData);
  98. sqlCon.Close();
  99. }
  100. catch
  101. {
  102. throw;
  103. }
  104. return dsData.Tables[0];
  105. }
  106. }

Step 5

Run it.