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
For reading connection string in asp.net core refer below article.
Namespaces
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
Controller
public class HomeController : Controller
{
private IConfiguration Configuration;
public HomeController(IConfiguration _configuration)
{
Configuration = _configuration;
}
public IActionResult Index()
{
return View();
}
public IActionResult AjaxMethod([FromBody]Year selectedYear)
{
string query = "SELECT ShipCountry, DATEPART(Year, OrderDate) [Year], COUNT(OrderId) [Total]";
query += " FROM Orders WHERE ShipCountry IN ('Germany', 'France', 'Brazil', 'Canada')";
query += " AND (DATEPART(Year, OrderDate) = @Year OR @Year IS NULL)";
query += " GROUP BY ShipCountry, DATEPART(Year, OrderDate)";
SqlConnection con = new SqlConnection(this.Configuration.GetConnectionString("MyConn"));
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@Year", selectedYear != null ? selectedYear.Value : (object)DBNull.Value);
cmd.CommandText = query;
cmd.Connection = con;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
List<string> countries = (from p in dt.AsEnumerable()
select Convert.ToString(p["ShipCountry"])).Distinct().ToList();
List<int> years = (from p in dt.AsEnumerable()
select Convert.ToInt32(p["Year"])).Distinct().ToList();
ChartData chartData = new ChartData();
chartData.Category = years.ToArray();
List<Series> series = new List<Series>();
foreach (string country in countries)
{
Series serie = new Series();
List<int> data = new List<int>();
foreach (int year in years)
{
data.Add((from p in dt.AsEnumerable()
where Convert.ToInt32(p["Year"]) == year && p["ShipCountry"].ToString() == country.Trim()
select Convert.ToInt32(p["Total"])).FirstOrDefault());
}
serie.name = country;
serie.data = data.ToArray();
series.Add(serie);
}
chartData.Series = series;
return Json(chartData);
}
public class Year
{
public int Value { get; set; }
}
public class ChartData
{
public int[] Category { get; set; }
public List<Series> Series { get; set; }
}
public class Series
{
public string name { get; set; }
public int[] data { get; set; }
}
}
View
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<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://code.highcharts.com/highcharts.js"></script>
<script type="text/javascript" src="https://code.highcharts.com/modules/exporting.js"></script>
<script type="text/javascript">
$(function () {
drawChart();
$('#ddlYears').on('change', function () {
drawChart();
});
function drawChart() {
var obj = {};
obj.Value = $("#ddlYears").val();
$.ajax({
type: "POST",
url: "/Home/AjaxMethod",
data: JSON.stringify(obj),
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
var category = response.Category;
var series = response.Series;
Highcharts.chart('chart', {
chart: { type: 'column' },
title: { text: 'Country wise Order Distribution', style: { "color": "#A3A3A3", "fontSize": "12px" } },
subtitle: { text: '' },
xAxis: {
categories: category,
title: { text: null },
labels: {
style: { color: 'black', fontWeight: 'bold', fontSize: '10px' }
}
},
yAxis: {
min: 0,
title: {
text: 'Total',
align: 'high'
},
labels: {
style: { color: 'black', fontWeight: 'bold', fontSize: '10px' }
},
stackLabels: {
enabled: true,
style: { color: 'black', fontWeight: 'bold', fontSize: '10px' }
}
},
tooltip: {
formatter: function () {
return '<b>' + this.x + '</b><br/>' + this.series.name + ': ' + this.y +
'<br/>' + 'Total: ' + this.point.stackTotal;
}
},
plotOptions: {
column: {
stacking: 'normal',
dataLabels: {
enabled: true,
//color: (Highcharts.theme && Highcharts.theme.dataLabelsColor) || 'white',
style: { textShadow: '0 0 0px black' }
}
}
},
legend: {
enabled: true,
layout: 'vertical',
align: 'right',
verticalAlign: 'xbottom',
x: 10,
y: 40,
floating: false,
borderWidth: 1,
backgroundColor: Highcharts.defaultOptions.legend.backgroundColor || '#FFFFFF',
shadow: true,
itemStyle: { fontSize: '10px', font: 'Arial', color: '#0090CB' }
},
credits: { enabled: false },
series: series
});
},
failure: function (r) {
alert(r);
},
error: function (r) {
alert(r);
}
});
}
});
</script>
</head>
<body>
<table>
<tr>
<td align="center">
Year:
<select id="ddlYears">
<option value="">Select Year</option>
<option value="1996">1996</option>
<option value="1997">1997</option>
<option value="1998">1998</option>
</select>
</td>
</tr>
<tr>
<td><div id="chart" style="width: 300px; height: 400px;"></div></td>
</tr>
</table>
</body>
</html>
Screenshot
