How to Link Google Chart to a database like SQL Server

Using Ajax and a Web Service … You can use Google charts tools to create dynamic and interactive charts to display data graphically on a web page. The data for the charts can be extracted dynamically using various data source such as an external JSON file or linking a database like SQL Server using Web API. Another simple method to link a Google chart to a database like SQL Server is by using Web Service.

I am talking about Asp.Net Web Service here. If you are new to Asp.Net web service, then I recommend checking some examples here. And, if you are new to Google Charts API, then please see this post first.

So, lets create a Line chart (or a curved chart) using Google Charts tool by linking the chart to a table in SQL Server.

Note: I am assuming that you know SQL Server and have access to SQL Server Management Studio.

In-addition, you should also have some knowledge of Asp.Net Web Service.

Create a table in SQL Server

CREATE TABLE dbo.Books_Annual_Sales(
    Month varchar(50) NULL,
    SalesFigure int NULL
) ON PRIMARY

Table dbo.Books_Annual_Sales has two columns, the Month and SalesFigure. The chart will show monthly sales figure for an entire year. This is enough for the Line chart example.

Now add some data into the table.

INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('JAN', 21)
INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('FEB', 56)
INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('MAR', 4)
INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('APR', 61)
INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('MAY', 45)
INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('JUN', 56)
INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('JUL', 12)
INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('AUG', 31)
INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('SEP', 37)
INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('OCT', 42)
INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('NOV', 11)
INSERT [dbo].[Books_Annual_Sales] ([Month], [SalesFigure]) VALUES ('DEC', 15)

The Web Service (C#)

Now, let’s create a Web Service and link it to the SQL Server database.

The web service is simple and has a single method named Annual_Sales(). It doesn’t take any parameter. This method is called from the web page (where you’ll have the chart) using Ajax.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;

[System.Web.Script.Services.ScriptService()]
public class WebService : System.Web.Services.WebService
{
    public class Sales_Figures
    {
        public string Month;
        public int SalesFigure;
    }

    [WebMethod]
    public List<Sales_Figures> Annual_Sales()
    {
        List<Sales_Figures> lstSales = new List<Sales_Figures>();

        string sConnString = "Data Source=DNA;Persist Security Info=False;" + 
            "Initial Catalog=DNA_Classified;User Id=sa;Password=;Connect Timeout=30;";

        SqlConnection myConn = new SqlConnection(sConnString);
        SqlCommand objComm = new SqlCommand("SELECT *FROM dbo.Books_Annual_Sales ", myConn);
        myConn.Open();

        SqlDataReader sdr = objComm.ExecuteReader();

        while (sdr.Read())
        {
            Sales_Figures objValues = new Sales_Figures();
            objValues.Month = sdr["Month"].ToString();
            objValues.SalesFigure = (int)sdr["SalesFigure"];

            lstSales.Add(objValues);
        }

        myConn.Close();
        sdr.Close();
        return lstSales;
    }
}

Web Service (Visual Basic)

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols

Imports System.Data.SqlClient

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
Public Class WebService
    Inherits System.Web.Services.WebService

    Public Class Sales_Figures
        Public Month As String
        Public SalesFigure As Integer
    End Class

    <WebMethod()> _
    Public Function Annual_Sales() As List(Of Sales_Figures)
        Dim lstSales As New List(Of Sales_Figures)()

        Dim sConnString As String = "Data Source=DNA;Persist Security Info=False;" & _
            "Initial Catalog=DNA_Classified;User Id=sa;Password=;Connect Timeout=30;"

        Dim myConn As New SqlConnection(sConnString)
        Dim objComm As New SqlCommand("SELECT *FROM dbo.Books_Annual_Sales ", myConn)
        myConn.Open()

        Dim sdr As SqlDataReader = objComm.ExecuteReader()

        While sdr.Read
            Dim objValues As New Sales_Figures()
            objValues.Month = sdr("Month").ToString()
            objValues.SalesFigure = sdr("SalesFigure")

            lstSales.Add(objValues)
        End While

        myConn.Close() : sdr.Close() : Return lstSales
    End Function

End Class

Now, if the web service is ready, let’s create our web page, from where we’ll call the web service method using Ajax to connect to the SQL Server table and draw the chart.

Note: Make sure that the Web Service is up and running.

The Markup and the Script

<!DOCTYPE html>

<html>
<head>
    <title>Link SQL Server database to Google Charts</title>

    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>

</head>
<body>

    <%--The DIV serves as a container for the chart.--%>
    <div id="salesReport" style="width:auto; height:300px;"></div>

</body>
<script>

    // Visualization API with the 'corechart' package.
    google.charts.load('visualization', { packages: ['corechart'] });
    google.setOnLoadCallback(drawChart);

    function drawChart() {

        // Set chart Options.
        var options = {
            title: 'Monthly Sales',
            curveType: 'function',
            legend: { position: 'bottom', textStyle: { color: '#888888', fontSize: 14} }
            // You can position the legend on 'top' or at the 'bottom'.
        };

        $.ajax({
            url: "WebService.asmx/Annual_Sales",
            dataType: "json",
            type: "POST",
            contentType: "application/json; charset=utf-8",
            success: function (data) {

                var arrValues = [['Month', 'Sales Figure']];        // Define an array.
                var iCnt = 0;

                $.each(data.d, function () {

                    // Populate array with the extracted data.
                    arrValues.push([data.d[iCnt].Month, data.d[iCnt].SalesFigure]);
                    iCnt += 1;

                });

                // Create DataTable and add the array to it.
                var figures = google.visualization.arrayToDataTable(arrValues)

                // Define the chart type (Line chart).
                var ele = document.getElementById('salesReport');   // The element (DIV) where the chart will be drawn.
                var chart = new google.visualization.LineChart(ele);

                chart.draw(figures, options);      // Draw the chart with data and options.

            },
            error: function (XMLHttpRequest, textStatus, errorThrown) {
                alert('Got an Error');
            }
        });

    }
</script>
</html>

Output

Link Google Chart with SQL Server Database

I’ll explain the script.

1) google.charts.load() - Using this method we’ll load the chart. The load() method takes two parameters, visualization and package. We are initializing the chart here.

visualization, provides the chart to us.

The package is an array, which has a list of visualization or chart properties. I have defined corechart in the above example.

2) var options = { }; - Here I have defined few chart options like header, the curve type and legend.

If you remove curve type, it will show lines.

To change the default colour of the curve or line in the chart, you can use the series option. For example,

var options = {
    title: 'Monthly Sales',
    curveType: 'function',
    series: {
        0: { color: 'red' }
    },
};

3) Making an Ajax call using $.ajax({});. That’s why I have added the jQuery CDN in the <header> section of the web page.

4) If the Ajax call is a success, I am assigning the chart with the necessary input, like data and options.

5) Finally, I have hooked the chart with a element (the <div> element) to show the chart.

var ele = document.getElementById('salesReport');

Note: You can try with different types of chart and link the charts to a database using a Web Service, as I have shown above.

You can learn more about Google Charts here.

Browser Support:
Chrome 39.0 - Yes | FireFox 34.0 - Yes | Internet Explorer 10 - Yes | Safari - Yes

← PreviousNext →