Populate Google API Bar chart from database and show bar colors based on value using jQuery in ASP.Net

Last Reply 27 days ago By dharmendr

Posted 27 days ago

How to connect google bar chart to sql database and show bar colors based on value range.

    <script type="text/javascript">
        google.charts.load('current', { callback: drawChart, packages: ['corechart'] });
        function drawChart() {
            var data = google.visualization.arrayToDataTable([
                ['Asset', 'Days in Stock'],
                ['4990/473', 606], ['4990/489', 504], ['4990/557', 159], ['4990/559', 147],
                ['4990/578', 87], ['4990/581', 63], ['4990/582', 53], ['4990/586', 41],
                ['4990/590', 25], ['4990/592', 20], ['4990/593', 5],
              ]);

            var view = new google.visualization.DataView(data);
            view.setColumns([0, 1, {
                calc: function (dt, row) {
                    if ((dt.getValue(row, 1) >= 0) && (dt.getValue(row, 1) <= 60)) {
                        return 'green';
                    } else if ((dt.getValue(row, 1) > 60) && (dt.getValue(row, 1) <= 100)) {
                        return 'yellow';
                    } else {
                        return 'red';
                    }
                },
                type: 'string',
                role: 'style'
            }, {
                calc: 'stringify',
                sourceColumn: 1,
                type: 'string',
                role: 'annotation'
            }]);

            var options = {
                title: '',
                titleTextStyle: {
                    fontSize: 16,
                    bold: true
                },
                backgroundColor: 'transparent',
                chartArea: {
                    left: 80,
                    top: 30,
                    bottom: 60,
                    right: 10
                },
                legend: {
                    textStyle: {
                        fontSize: 11
                    }
                },
                vAxis: {
                    title: 'Asset',
                    textStyle: {
                        fontName: 'Arial',
                        fontSize: 10
                    },
                    titleTextStyle: {
                        fontSize: 12,
                        italic: false,
                        bold: true
                    }
                },
                hAxis: {
                    title: 'Days in Stock',
                    gridlines: {
                        count: 22
                    },
                    textStyle: {
                        fontName: 'Arial',
                        fontSize: 11
                    },
                    titleTextStyle: {
                        fontSize: 12,
                        italic: false,
                        bold: true
                    }
                },
                pointSize: 3,
                pointShape: 'circle',
                annotations: {
                    alwaysOutside: true,
                    textStyle: {
                        fontName: 'Arial',
                        fontSize: 9,
                        color: '#000000',
                        opacity: 1
                    }
                }
            };

            var chartDiv = document.getElementById('chart_div');
            var chart = new google.visualization.BarChart(chartDiv);
            chart.draw(view, options);
        }
    </script>
    <div id="chart_div" />
Posted 27 days ago

Hi merix,

I have binded the data from DataTable. You need to fetch DataTable from database.

Check this example. Now please take its reference and correct your code.

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
        google.charts.load('current', { 'packages': ['corechart'] });
        google.charts.setOnLoadCallback(drawChart);
        function drawChart() {
            $.ajax({
                type: "POST",
                url: "Default.aspx/GetChartData",
                data: {},
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    var dataTable = [['Asset', 'Days in Stock']];
                    for (var i = 0; i < response.d.length; i++) {
                        var asset = response.d[i][0].toString();
                        var stock = response.d[i][1].toString();
                        dataTable.push([asset, parseInt(stock)]);
                    }
                    var data = google.visualization.arrayToDataTable(dataTable);
                    var view = new google.visualization.DataView(data);
                    view.setColumns([0, 1, {
                        calc: function (dt, row) {
                            if ((dt.getValue(row, 1) >= 0) && (dt.getValue(row, 1) <= 60)) {
                                return 'green';
                            } else if ((dt.getValue(row, 1) > 60) && (dt.getValue(row, 1) <= 100)) {
                                return 'yellow';
                            } else {
                                return 'red';
                            }
                        },
                        type: 'string',
                        role: 'style'
                    },
                    {
                        calc: 'stringify',
                        sourceColumn: 1,
                        type: 'string',
                        role: 'annotation'
                    }]);

                    var options = {
                        title: '',
                        titleTextStyle: { fontSize: 16, bold: true },
                        backgroundColor: 'transparent',
                        chartArea: {
                            left: 80, top: 30, bottom: 60, right: 10
                        },
                        legend: {
                            textStyle: { fontSize: 11 }
                        },
                        vAxis: {
                            title: 'Asset',
                            textStyle: { fontName: 'Arial', fontSize: 10 },
                            titleTextStyle: { fontSize: 12, italic: false, bold: true }
                        },
                        hAxis: {
                            title: 'Days in Stock',
                            gridlines: { count: 22 },
                            textStyle: { fontName: 'Arial', fontSize: 11 },
                            titleTextStyle: { fontSize: 12, italic: false, bold: true }
                        },
                        pointSize: 3,
                        pointShape: 'circle',
                        annotations: {
                            alwaysOutside: true,
                            textStyle: { fontName: 'Arial', fontSize: 9, color: '#000000', opacity: 1 }
                        }
                    };

                    var chartDiv = document.getElementById('chart_div');
                    var chart = new google.visualization.BarChart(chartDiv);
                    chart.draw(view, options);
                }, error: function (response) {
                    alert(response.responseText);
                }
            });
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div id="chart_div" />
    </form>
</body>
</html>

Namespaces

C#

using System.Data;
using System.Web.Services;

VB.Net

Imports System.Data
Imports System.Web.Services

Code

C#

[WebMethod]
public static List<object> GetChartData()
{
    // Get your DataTable from DataBase.
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { new DataColumn("Asset"), new DataColumn("Days in Stock", typeof(int)) });
    dt.Rows.Add("4990/473", 150);
    dt.Rows.Add("4990/489", 63);
    dt.Rows.Add("4990/557", 41);
    dt.Rows.Add("4990/559", 147);
    dt.Rows.Add("4990/578", 87);
    dt.Rows.Add("4990/581", 175);
    dt.Rows.Add("4990/582", 53);
    dt.Rows.Add("4990/586", 159);
    dt.Rows.Add("4990/590", 25);
    dt.Rows.Add("4990/592", 20);
    dt.Rows.Add("4990/593", 5);

    List<object> chartData = new List<object>();
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        chartData.Add(new object[] { dt.Rows[i]["Asset"], dt.Rows[i]["Days in Stock"] });
    }

    return chartData;
}

VB.Net

<WebMethod()>
Public Shared Function GetChartData() As List(Of Object)
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("Asset"), New DataColumn("Days in Stock", GetType(Integer))})
    dt.Rows.Add("4990/473", 150)
    dt.Rows.Add("4990/489", 63)
    dt.Rows.Add("4990/557", 41)
    dt.Rows.Add("4990/559", 147)
    dt.Rows.Add("4990/578", 87)
    dt.Rows.Add("4990/581", 175)
    dt.Rows.Add("4990/582", 53)
    dt.Rows.Add("4990/586", 159)
    dt.Rows.Add("4990/590", 25)
    dt.Rows.Add("4990/592", 20)
    dt.Rows.Add("4990/593", 5)
    Dim chartData As List(Of Object) = New List(Of Object)()
    For i As Integer = 0 To dt.Rows.Count - 1
        chartData.Add(New Object() {dt.Rows(i)("Asset"), dt.Rows(i)("Days in Stock")})
    Next

    Return chartData
End Function

Screenshot