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> countryList = (from p in dt.AsEnumerable()
select p.Field<string>("ShipCountry")).Distinct().ToList();
//Get the DISTINCT Countries.
List<object> chartData = new List<object>();
List<string> countries = (from p in dt.AsEnumerable()
select p.Field<string>("ShipCountry")).Distinct().ToList();
//Insert Label for Country in First position.
countries.Insert(0, "Country");
//Add the Countries Array to the Chart Array.
chartData.Add(countries.ToArray());
//Get the DISTINCT Years.
List<int> years = (from p in dt.AsEnumerable()
select p.Field<int>("Year")).Distinct().ToList();
//Loop through the Years.
foreach (int year in years)
{
List<object> totals = new List<object>();
foreach (string country in countryList)
{
//Get the Total of Orders for each Country for the Year.
var total = (from p in dt.AsEnumerable()
where p.Field<int>("Year") == year && p.Field<string>("ShipCountry") == country
select p.Field<int>("Total")).Cast<object>().FirstOrDefault();
totals.Add(total == null ? 0 : total);
}
//Insert the Year value as Label in First position.
totals.Insert(0, year.ToString());
//Add the Years Array to the Chart Array.
chartData.Add(totals.ToArray());
}
return Json(chartData);
}
public class Year
{
public int Value { 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://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
$(function () {
google.charts.load('current', { 'packages': ['corechart'] });
google.charts.setOnLoadCallback(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 (r) {
var options = {
title: 'Country wise Order Distribution',
legend: { position: 'top', maxLines: 3 },
bar: { groupWidth: '75%' },
isStacked: true
};
var data = google.visualization.arrayToDataTable(r);
var chart = new google.visualization.BarChart(document.getElementById('chart'));
chart.draw(data, options);
},
failure: function (r) {
alert(r);
},
error: function (r) {
alert(r);
}
});
}
});
</script>
</head>
<body>
<br />
<br />
<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: 500px; height: 300px;">
</div>
</td>
</tr>
</table>
</body>
</html>
Screenshot
