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

Last Reply 2 months ago By dharmendr

Posted 2 months 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 2 months 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