Add reference line for Google Column Chart in ASP.Net using jQuery Ajax

Hi firasataries,

Refer below sample.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
    google.load("visualization", "1", { packages: ["corechart"] });
    google.setOnLoadCallback(drawChart);
    function drawChart() {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetChartData",
            data: '{}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (r) {
                var data = new google.visualization.DataTable();
                data.addColumn('string', 'ShipCity');
                data.addColumn('number', 'Orders');
                data.addColumn('number', 'KPI');
                for (var i = 0; i < r.d.length; i++) {
                    data.addRow([r.d[i][0], r.d[i][1], r.d[i][2]]);
                }

                var chart = new google.visualization.ComboChart(document.getElementById('chart'));
                chart.draw(data, {
                    seriesType: 'bars',
                    series: { 1: { type: 'line' } },
                    title: 'USA City Distribution',
                    width: 600,
                    height: 400,
                    bar: { groupWidth: "95%" },
                    legend: { position: "none" },
                    isStacked: true
                });
            },
            failure: function (r) {
                alert(r.responseText);
            },
            error: function (r) {
                alert(r.responseText);
            }
        });
    }
</script>
<div id="chart" style="width: 900px; height: 500px;">
</div>

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

[WebMethod]
public static List<object> GetChartData()
{
    string query = "SELECT ShipCity, COUNT(orderid) TotalOrders";
    query += " FROM Orders WHERE ShipCountry = 'USA' GROUP BY ShipCity";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    List<object> chartData = new List<object>();
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    chartData.Add(new object[] { sdr["ShipCity"], sdr["TotalOrders"], 8 });
                }
            }
            con.Close();
            return chartData;
        }
    }
}

VB.Net

<WebMethod>
Public Shared Function GetChartData() As List(Of Object)
    Dim query As String = "SELECT ShipCity, COUNT(orderid) TotalOrders"
    query += " FROM Orders WHERE ShipCountry = 'USA' GROUP BY ShipCity"
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim chartData As New List(Of Object)()
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand(query, con)
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    chartData.Add(New Object() {sdr("ShipCity"), sdr("TotalOrders"), 8})
                End While
            End Using
            con.Close()
            Return chartData
        End Using
    End Using
End Function

Screenshot

Hi firasataries,

Refer below sample.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
    google.load("visualization", "1", { packages: ["corechart"] });
    google.setOnLoadCallback(drawChart);
    function drawChart() {
        $.ajax({
            type: "POST",
            url: "VB.aspx/GetChartData",
            data: '{}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (r) {
                var data1 = google.visualization.arrayToDataTable(r.d[0]);
                var data2 = google.visualization.arrayToDataTable(r.d[1]);
                var data3 = google.visualization.arrayToDataTable(r.d[2]);
                var data4 = google.visualization.arrayToDataTable(r.d[3]);
                var chart1 = new google.visualization.ComboChart($("#chart")[0]);
                chart1.draw(data1, {
                    seriesType: 'bars', series: { 1: { type: 'line' } },
                    legend: { position: "none" },
                    bar: { groupWidth: "30%" },
                    isStacked: true,
                    title: 'Feasibility Study',
                    colors: ['rgb(104, 33, 33)'],
                    is3D: 'true', legend: 'none',
                    hAxis: {
                        title: 'Completion Year', format: '0'
                    },
                    vAxis: {
                        title: 'Average No. Of Days Taken to Complete Feasibility Study',
                        titleTextStyle: { color: 'rgb(104, 33, 33)' }, format: '0'
                    }
                });

                var chart2 = new google.visualization.ColumnChart($("#chart1")[0]);
                chart2.draw(data2, {
                    seriesType: 'bars', series: { 1: { type: 'line' } },
                    legend: { position: "none" },
                    bar: { groupWidth: "30%" },
                    isStacked: true,
                    title: 'Budget Estimate',
                    colors: ['rgb(245, 121, 30)'],
                    is3D: 'true',
                    legend: 'none',
                    hAxis: {
                        title: 'Completion Year', format: '0'
                    },
                    vAxis: {
                        title: 'Average No. Of Days Taken to Complete Budget Estimate', format: '0'
                    }
                });

                var chart3 = new google.visualization.ColumnChart($("#chart2")[0]);
                chart3.draw(data3, {
                    seriesType: 'bars', series: { 1: { type: 'line' } },
                    legend: { position: "none" },
                    bar: { groupWidth: "30%" },
                    isStacked: true,
                    title: 'Change Order (EHV)',
                    colors: ['rgb(133, 144, 175)'],
                    is3D: 'true',
                    legend: 'none',
                    hAxis: {
                        title: 'Completion Year', format: '0'
                    },
                    vAxis: {
                        title: 'Average No. Of Days Taken to Complete Change Order (EHV)', format: '0'
                    }
                });

                var chart4 = new google.visualization.ColumnChart($("#chart3")[0]);
                chart4.draw(data4, {
                    seriesType: 'bars', series: { 1: { type: 'line' } },
                    legend: { position: "none" },
                    bar: { groupWidth: "30%" },
                    isStacked: true,
                    title: 'Change Order',
                    colors: ['#1B4589'],
                    is3D: 'true',
                    legend: 'none',
                    hAxis: {
                        title: 'Completion Year', format: '0'
                    },
                    vAxis: {
                        title: 'Average No. Of Days Taken to Complete Change Order', format: '0'
                    }
                });
            },
            failure: function (r) {
                alert(r.responseText);
            },
            error: function (r) {
                alert(r.responseText);
            }
        });
    }
</script>
<div id="chart" style="width: 900px; height: 500px;"></div>
<div id="chart1" style="width: 900px; height: 500px;"></div>
<div id="chart2" style="width: 900px; height: 500px;"></div>
<div id="chart3" style="width: 900px; height: 500px;"></div>

Namespaces

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web.Services

Code

<WebMethod()>
Public Shared Function GetChartData() As List(Of List(Of Object))
    Dim chartDatas As List(Of List(Of Object)) = New List(Of List(Of Object))()
    Dim query As String = "SELECT cast(YEAR(OrderDate) as varchar(4)) AS Year ,AVG(OrderId) AS No_of_Days FROM [Orders] GROUP BY YEAR(OrderDate), ShipCountry HAVING ShipCountry='France' ORDER BY Year DESC"
    chartDatas.Add(GetData(query))

    query = "SELECT cast(YEAR(OrderDate) as varchar(4)) AS Year ,AVG(OrderId) AS No_of_Days FROM [Orders] GROUP BY YEAR(OrderDate), ShipCountry HAVING ShipCountry='Germany' ORDER BY Year DESC"
    chartDatas.Add(GetData(query))

    query = "SELECT cast(YEAR(OrderDate) as varchar(4)) AS Year ,AVG(OrderId) AS No_of_Days FROM [Orders] GROUP BY YEAR(OrderDate), ShipCountry HAVING ShipCountry='Brazil' ORDER BY Year DESC"
    chartDatas.Add(GetData(query))

    query = "SELECT cast(YEAR(OrderDate) as varchar(4)) AS Year ,AVG(OrderId) AS No_of_Days FROM [Orders] GROUP BY YEAR(OrderDate), ShipCountry HAVING ShipCountry='Belgium' ORDER BY Year DESC"
    chartDatas.Add(GetData(query))

    Return chartDatas
End Function

Private Shared Function GetData(ByVal query As String) As List(Of Object)
    Dim chartData As List(Of Object) = New List(Of Object)()
    chartData.Add(New Object() {"Year", "No of Days", "KPI"})
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand(query, con)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim myD As New DataTable
                sda.Fill(myD)
                If myD.Rows.Count > 0 Then
                    For i As Integer = 0 To myD.Rows.Count - 1
                        chartData.Add(New Object() {myD.Rows(i)(0), myD.Rows(i)(1), 5000})
                    Next
                End If
                Return chartData
            End Using
        End Using
    End Using
End Function

Screenshot