Hi doram,
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
Namespaces
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
Controller
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
[HttpPost]
public JsonResult AjaxMethod()
{
string query = "SELECT ShipCountry,ShipCity, COUNT(orderid) TotalOrders FROM Orders WHERE ShipCountry = 'France' GROUP BY ShipCity,ShipCountry ";
query += "UNION ";
query += "SELECT ShipCountry,ShipCity, COUNT(orderid) TotalOrders FROM Orders WHERE ShipCountry = 'UK' GROUP BY ShipCity,ShipCountry ";
string constr = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
List<object> chartData = new List<object>();
chartData.Add(new object[] { "ShipCity", "TotalOrders", "ShipCountry" });
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
chartData.Add(new object[] { sdr["ShipCity"], sdr["TotalOrders"], sdr["ShipCountry"] });
}
}
con.Close();
}
}
return Json(chartData);
}
}
View
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<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: "/Home/AjaxMethod",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var chartDatas = new Array();
chartDatas.push(AddData(r[0]));
var key = "";
var status = false;
for (var i = 1; i < r.length; i++) {
if (key == "") {
key = r[i][2];
chartDatas.push(AddData(r[i]));
} else if (key == r[i][2]) {
chartDatas.push(AddData(r[i]));
if (i == r.length - 1) {
status = true;
}
} else {
status = true;
}
if (status) {
var options = { title: key + ' City Distribution' };
var data = google.visualization.arrayToDataTable(chartDatas);
var dvChart = $("<div style='width: 500px; height: 400px' />");
var chart = new google.visualization.PieChart($(dvChart)[0]);
chart.draw(data, options);
$('#dvCharts').append(dvChart);
key = r[i][2];
chartDatas = [chartDatas[0]];
chartDatas.push(AddData(r[i]));
status = false;
}
}
},
error: function (r) {
alert(r.responseText);
}
});
}
function AddData(data) {
var chartData = [];
for (var j = 0; j < data.length - 1; j++) {
chartData.push(data[j]);
}
return chartData;
}
</script>
<div id="dvCharts"></div>
</body>
</html>
Screenshot
