public JsonResult <List <Zones> > getSupportDataSet()
{
List <Zones> zonesList = new List <Zones>();
DataSet dataSet = webService.getSupportDataSet();
DataTable tableZone = dataSet.Tables[0];
DataTable tableArea = dataSet.Tables[1];
for (int i = 0; i < tableZone.Rows.Count; i++)
{
DataRow dr = tableZone.Rows[i];
Zones zones = new Zones();
zones.ID = Convert.ToInt32(dr[0].ToString());
zones.Zone = dr[1].ToString();
zones.Areas = new List <Models.Areas>();
DataRow[] drarry = tableArea.Select("ZoneID=" + dr[0]);
foreach (var item in drarry)
{
Models.Areas areas = new Models.Areas()
{
ID = Convert.ToInt32(item[0].ToString()),
ZoneID = Convert.ToInt32(item[1].ToString()),
Area = item[2].ToString(),
AreaCode = item[3].ToString()
};
zones.Areas.Add(areas);
}
zonesList.Add(zones);
}
;
return(Json <List <Zones> >(zonesList));
}
public void OnGet()
{
List<Models.Areas> areas = new List<Models.Areas>();
// clear exception:
EX = null;
try
{
string sql = string.Format(@"
SELECT Areas.Area, AreaName, Count(CID) as NumCrimes, ROUND(( CONVERT(float,(Count(CID))) / MAX(CID) * 100), 2) AS PercentCrime
FROM Areas
INNER JOIN Crimes ON Crimes.Area = Areas.Area
WHERE Areas.Area <> 0
GROUP BY Areas.Area, AreaName
ORDER BY AreaName ASC;
");
DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
Models.Areas m = new Models.Areas();
m.Area = Convert.ToInt32(row["Area"]);
m.AreaName = Convert.ToString(row["AreaName"]);
m.NumCrimes = Convert.ToInt32(row["NumCrimes"]);
m.PercentCrime = Convert.ToSingle(row["PercentCrime"]);
areas.Add(m);
}
}
catch(Exception ex)
{
EX = ex;
}
finally
{
AreasList = areas;
}
}
