Populate Multi-Series Bar Column Line Chart from database using AngularJS in ASP.Net

Hi rani,

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

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="//ajax.googleapis.com/ajax/libs/angularjs/1.3.9/angular.min.js"></script>
<script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/Chart.js/2.6.0/Chart.bundle.min.js"></script>
<script type="text/javascript">
    var app = angular.module('MyApp', [])
    app.controller('MyController', function ($scope, $http) {
        $scope.PopulateChart = function (chartType) {
            $http.post('CS.aspx/GetChartData', { headers: { 'Content-Type': 'application/json'} })
            .then(function (response) {
                var labels = response.data.d[0];
                var series1 = response.data.d[1];
                var series2 = response.data.d[2];
                var series3 = response.data.d[3];

                var ctx = document.getElementById("dvCanvas").getContext('2d');
                var myChart = new Chart(ctx, {
                    type: chartType,
                    data: {
                        datasets: [{
                            data: series1,
                            label: 'USA',
                            backgroundColor: "rgba(255, 5, 255, 0.5)"
                        }, {
                            data: series2,
                            label: 'Austria',
                            backgroundColor: "rgba(0, 0, 255, 0.5)"
                        }, {
                            data: series3,
                            label: 'Brazil',
                            backgroundColor: "rgba(0, 255, 255, 0.5)"
                        }],
                        labels: labels
                    },
                    options: { responsive: false }
                });
            });
        }
    });
</script>
<div ng-app="MyApp" ng-controller="MyController">
    <label for="ChartType">
        <input type="radio" name="ChartType" ng-click="PopulateChart('horizontalBar')" />Bar
        <input type="radio" name="ChartType" ng-click="PopulateChart('bar')" />Column
        <input type="radio" name="ChartType" ng-click="PopulateChart('line')" />Line
    </label>
    <hr />
    <canvas id="dvCanvas" height="200" width="500"></canvas>
</div>

Namespaces

C#

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

VB.Net

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

Code

C#

[WebMethod]
public static List<object> GetChartData()
{
    List<object> chartData = new List<object>();

    string query = "SELECT DISTINCT TOP 3 DATEPART(YEAR, OrderDate) Year FROM Orders";
    DataTable dtYears = GetData(query);
    List<int> labels = new List<int>();
    foreach (DataRow row in dtYears.Rows)
    {
        labels.Add(Convert.ToInt32(row["Year"]));
    }
    chartData.Add(labels);

    query = "SELECT DATEPART(YEAR, OrderDate) Year, COUNT(DATEPART(YEAR, OrderDate)) TotalOrders ";
    query += "FROM Orders WHERE ShipCountry = 'USA' GROUP BY DATEPART(YEAR, OrderDate)";
    DataTable dtCountry1 = GetData(query);
    List<int> series1 = new List<int>();
    foreach (DataRow row in dtCountry1.Rows)
    {
        series1.Add(Convert.ToInt32(row["TotalOrders"]));
    }
    chartData.Add(series1);

    query = "SELECT DATEPART(YEAR, OrderDate) Year, COUNT(DATEPART(YEAR, OrderDate)) TotalOrders ";
    query += "FROM Orders WHERE ShipCountry = 'Austria' GROUP BY DATEPART(YEAR, OrderDate)";
    DataTable dtCountry2 = GetData(query);
    List<int> series2 = new List<int>();
    foreach (DataRow row in dtCountry2.Rows)
    {
        series2.Add(Convert.ToInt32(row["TotalOrders"]));
    }
    chartData.Add(series2);

    query = "SELECT DATEPART(YEAR, OrderDate) Year, COUNT(DATEPART(YEAR, OrderDate)) TotalOrders ";
    query += "FROM Orders WHERE ShipCountry = 'Brazil' GROUP BY DATEPART(YEAR, OrderDate)";
    DataTable dtCountry3 = GetData(query);
    List<int> series3 = new List<int>();
    foreach (DataRow row in dtCountry3.Rows)
    {
        series3.Add(Convert.ToInt32(row["TotalOrders"]));
    }

    chartData.Add(series3);

    return chartData;
}

private static DataTable GetData(string query)
{
    DataTable dt = new DataTable();
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                sda.Fill(dt);
            }
        }
        return dt;
    }
}

VB.Net

<WebMethod()>
Public Shared Function GetChartData() As List(Of Object)
    Dim chartData As List(Of Object) = New List(Of Object)()

    Dim query As String = "SELECT DISTINCT TOP 3 DATEPART(YEAR, OrderDate) Year FROM Orders"
    Dim dtYears As DataTable = GetData(query)
    Dim labels As List(Of Integer) = New List(Of Integer)()

    For Each row As DataRow In dtYears.Rows
        labels.Add(Convert.ToInt32(row("Year")))
    Next

    chartData.Add(labels)
    query = "SELECT DATEPART(YEAR, OrderDate) Year, COUNT(DATEPART(YEAR, OrderDate)) TotalOrders "
    query += "FROM Orders WHERE ShipCountry = 'USA' GROUP BY DATEPART(YEAR, OrderDate)"
    Dim dtCountry1 As DataTable = GetData(query)
    Dim series1 As List(Of Integer) = New List(Of Integer)()
    For Each row As DataRow In dtCountry1.Rows
        series1.Add(Convert.ToInt32(row("TotalOrders")))
    Next

    chartData.Add(series1)
    query = "SELECT DATEPART(YEAR, OrderDate) Year, COUNT(DATEPART(YEAR, OrderDate)) TotalOrders "
    query += "FROM Orders WHERE ShipCountry = 'Austria' GROUP BY DATEPART(YEAR, OrderDate)"
    Dim dtCountry2 As DataTable = GetData(query)
    Dim series2 As List(Of Integer) = New List(Of Integer)()
    For Each row As DataRow In dtCountry2.Rows
        series2.Add(Convert.ToInt32(row("TotalOrders")))
    Next

    chartData.Add(series2)
    query = "SELECT DATEPART(YEAR, OrderDate) Year, COUNT(DATEPART(YEAR, OrderDate)) TotalOrders "
    query += "FROM Orders WHERE ShipCountry = 'Brazil' GROUP BY DATEPART(YEAR, OrderDate)"
    Dim dtCountry3 As DataTable = GetData(query)
    Dim series3 As List(Of Integer) = New List(Of Integer)()
    For Each row As DataRow In dtCountry3.Rows
        series3.Add(Convert.ToInt32(row("TotalOrders")))
    Next

    chartData.Add(series3)

    Return chartData
End Function

Private Shared Function GetData(query As String) As DataTable
    Dim dt As New DataTable()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand(query)
            Using sda As New SqlDataAdapter()
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                sda.SelectCommand = cmd
                sda.Fill(dt)
            End Using
        End Using
        Return dt
    End Using
End Function

Screenshot