Generate Chart from Database using AmCharts in ASP.Net using C#

You need to remove the below line of code.

cksmhrjn says:

chartData.Add(new { DEPT_NAME = "DEPT_NAME", Employee = "Employee" }); // Column headers

Refer below example.

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://cdn.amcharts.com/lib/5/index.js"></script>
<script type="text/javascript" src="https://cdn.amcharts.com/lib/5/xy.js"></script>
<script type="text/javascript" src="https://cdn.amcharts.com/lib/5/themes/Animated.js"></script>
<!-- Chart code -->
    <script type="text/javascript">
    am5.ready(function () {
        var root = am5.Root.new("chartdiv");
        root.setThemes([am5themes_Animated.new(root)]);
        var chart = root.container.children.push(am5xy.XYChart.new(root, {
            panX: true,
            panY: true,
            wheelX: "panX",
            wheelY: "zoomX",
            pinchZoomX: true
        }));

        var cursor = chart.set("cursor", am5xy.XYCursor.new(root, {}));
        cursor.lineY.set("visible", false);

        var xRenderer = am5xy.AxisRendererX.new(root, { minGridDistance: 30 });
        xRenderer.labels.template.setAll({
            rotation: -90,
            centerY: am5.p50,
            centerX: am5.p100,
            paddingRight: 15
        });

        xRenderer.grid.template.setAll({
            location: 1
        })

        var xAxis = chart.xAxes.push(am5xy.CategoryAxis.new(root, {
            maxDeviation: 0.3,
            categoryField: "DEPT_NAME",
            renderer: xRenderer,
            tooltip: am5.Tooltip.new(root, {})
        }));

        var yAxis = chart.yAxes.push(am5xy.ValueAxis.new(root, {
            maxDeviation: 0.3,
            renderer: am5xy.AxisRendererY.new(root, {
                strokeOpacity: 0.1
            })
        }));

        var series = chart.series.push(am5xy.ColumnSeries.new(root, {
            name: "Employee",
            xAxis: xAxis,
            yAxis: yAxis,
            valueYField: "Employee",
            sequencedInterpolation: true,
            categoryXField: "DEPT_NAME",
            tooltip: am5.Tooltip.new(root, {
                labelText: "{valueY}"
            })
        }));

        series.columns.template.setAll({ cornerRadiusTL: 5, cornerRadiusTR: 5, strokeOpacity: 0 });
        series.columns.template.adapters.add("fill", function (fill, target) {
            return chart.get("colors").getIndex(series.columns.indexOf(target));
        });

        series.columns.template.adapters.add("stroke", function (stroke, target) {
            return chart.get("colors").getIndex(series.columns.indexOf(target));
        });

        // Set data dynamically using ASP.NET C#
        var chartData = <%= GetChartDataJson() %>;
        xAxis.data.setAll(chartData);
        series.data.setAll(chartData);

        series.appear(1000);
        chart.appear(1000, 100);
    });
</script>
<!-- HTML -->
<div id="chartdiv" style="width: 100%; height: 500px;"></div>

Namespaces

using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Script.Serialization;

Code

protected string GetChartDataJson()
{
    List<object> chartData = GetChartData();
    return new JavaScriptSerializer().Serialize(chartData);
}

public List<object> GetChartData()
{
    string query = "SELECT Country DEPT_NAME, COUNT(EmployeeId) AS Employee FROM Employees GROUP BY Country";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    List<object> chartData = new List<object>();

    if (string.IsNullOrEmpty(constr))
    {
        // Handle the case where the connection string is not found.
        return chartData;
    }

    using (SqlConnection con = new SqlConnection(constr))
    using (SqlCommand cmd = new SqlCommand(query, con))
    {
        try
        {
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                //chartData.Add(new { DEPT_NAME = "DEPT_NAME", Employee = "Employee" }); // Column headers
                while (sdr.Read())
                {
                    if (sdr["DEPT_NAME"] != DBNull.Value && sdr["Employee"] != DBNull.Value)
                    {
                        chartData.Add(new { DEPT_NAME = sdr["DEPT_NAME"], Employee = sdr["Employee"] });
                    }
                }
            }
        }
        catch (Exception ex)
        {
            // Handle the exception, log, or re-throw it.
            throw ex;
        }
    }

    return chartData;
}

Screenshot