Google Chart API


I wrote a article on using open flash charts awhile back. It was a descent solution to my charting needs then, but I needed a more scalable and documented solution moving forward. Say hello to the Google Chart Tools!

Languages and tools used:

For this article we are going to look at the bare minimum we need to get a chart going. On the front side of the .net page we need just a few lines of code.

<head> code

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<% chartInclude(); %>

<body> code

<div id="chart-success"></div>

That’s it! Well, at least for the front end of things. Let me explain what is going to happen. Google Charts use JavaScript to get data and draw the charts. The first line in the head code above imports the api. The second line calls a C# function which will query the database and write out the actual JavaScript to draw the chart. The reason we do this is so that we can pull dynamic information from the database! Without doing this we would just have a static chart that never changes!

So let’s get into the real meat of this charting method. Next we will look at the C# function we are referencing. Since I have everything in one giant block of code I will highlight what is going on first.

  1. Establish database connection and query
  2. Execute connection and fill data table
  3. Write out JavaScript for Google Chart and loop through data table

Code Behind (C#)

protected void chartInclude()
    {
        // Start of charts
        // Here is the google guide
        // http://code.google.com/apis/chart/interactive/docs/queries.html
        // ---------------------------------------------------------------
        // Go go gadget draw chart
        // ---------------------------
        // Initialize database, query, and vars
        SqlConnection mySqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["yourConnectionString"].ConnectionString);
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
        DataSet myDataSet = new DataSet();
        SqlCommand cmd = mySqlConnection.CreateCommand();
        cmd.Connection = mySqlConnection;
        // Use CommandType.StoredProcedure OR CommandType.Text
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "yourStoredProcedure";
        // If your using a stored procedure you can add in variables here
        cmd.Parameters.Add("@dateRange", SqlDbType.VarChar).Value = rangeA.Text;
        string dataTableName = "WhatEverYouWant";

        // Prepare Datatable to accept query
        mySqlDataAdapter.SelectCommand = cmd;
        mySqlDataAdapter.Fill(myDataSet, dataTableName);
        DataTable myDataTable = myDataSet.Tables[dataTableName];

        // Pull the trigger
        mySqlConnection.Open();
        cmd.ExecuteNonQuery();
        mySqlConnection.Close();

        // Push out the chart code using the datatable
        Response.Write("<script type="text/javascript">// <![CDATA[
");
        Response.Write("google.load(\"visualization\", \"1\", { packages: [\"corechart\"] });");
        Response.Write("google.setOnLoadCallback(drawChart);");
        Response.Write("function drawChart() {");
        Response.Write("    var data = new google.visualization.DataTable();");
        // Add in each column you want to map into the chart
        Response.Write("    data.addColumn('string', 'Campaign');");
        Response.Write("    data.addColumn('number', 'Some Statistic');");
        Response.Write("    data.addColumn('number', 'Another Statistic');");

        // Code to dynamically pull # of entries to span the chart accordingly
        int numRow = 0;
        foreach (DataRow row in myDataTable.Rows)
        {
            numRow++;
        }

        Response.Write("    data.addRows(" + numRow + ");");
        int i = 0;
        foreach (DataRow row in myDataTable.Rows)
        {
            Response.Write("    data.setValue(" + i + ", 0, '" + Convert.ToString(row[0]) + "');");
            Response.Write("    data.setValue(" + i + ", 1, " + Convert.ToString(row[9]) + ");");
            Response.Write("    data.setValue(" + i + ", 2, " + Convert.ToString(row[10]) + ");");
            i++;
        }

        Response.Write("");
        // The next line declares the chart type and references the div id you added in the front end
        Response.Write("    var chart = new google.visualization.ColumnChart(document.getElementById('chart-success'));");
        Response.Write("    chart.draw(data, { width: 500, height: 350, title: 'Your Title' });");
        Response.Write("}");
        Response.Write("</script>");
    }

Well that was a good bit of code, but with that we can render out a fully dynamic chart do display whatever we want! AWESOME!

If you have any questions do not hesitate to leave a comment and ask!

  • Swapnil Patil

    Nice Attempt..!!
    Try this one.
    1. Suppose, Take drop down list with two countries US and UK values.
    2. Based on drop down list value fill the chart with respective values
    from database.
    3. Use Update Panel for partial updates.
    4. Check the results in All the Browsers.
    Works in IE but not in Mozilla and other browsers.
    It’ll load the visualization on page load but when you change the drop
    down value fails to load or just disappears after a while. Remove the
    update panel It’ll work fine.

    try this example and lemme know.
    Thanks.

  • Pingback: [RESOLVED]google charts | Asp Forum()