public void OnGet()
{
List<Models.CrimeCodes> crimes = new List<Models.CrimeCodes>();
// clear exception:
EX = null;
try
{
string sql = string.Format(@"
SELECT Codes.IUCR,Codes.PrimaryDesc,Codes.SecondaryDesc, COUNT(*) AS TotalNumOfCrimes
FROM Crimes
RIGHT JOIN Codes ON Crimes.IUCR = Codes.IUCR
GROUP BY Codes.IUCR,Codes.PrimaryDesc,Codes.SecondaryDesc
ORDER BY TotalNumOfCrimes DESC;
");
DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
Models.CrimeCodes c = new Models.CrimeCodes();
c.IUCR = Convert.ToString(row["IUCR"]);
c.PrimaryDesc = Convert.ToString(row["PrimaryDesc"]);
c.SecondaryDesc = Convert.ToString(row["SecondaryDesc"]);
c.TotalNumOfCrimes = Convert.ToInt32(row["TotalNumOfCrimes"]);
crimes.Add(c);
}
}
catch(Exception ex)
{
EX = ex;
}
finally
{
CrimeCodesList = crimes;
}
}
public void OnGet()
{
List<Models.CrimeCodes> crimecodes = new List<Models.CrimeCodes>();
// clear exception:
EX = null;
try
{
string sql = string.Format(@"
SELECT Codes.IUCR, PrimaryDesc, SecondaryDesc, COUNT(Crimes.IUCR) as NumCrimes
FROM Codes
FULL OUTER JOIN Crimes ON Crimes.IUCR = Codes.IUCR
GROUP BY Codes.IUCR, PrimaryDesc, SecondaryDesc
ORDER BY PrimaryDesc ASC, SecondaryDesc ASC;
");
DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
Models.CrimeCodes m = new Models.CrimeCodes();
m.IUCR = Convert.ToString(row["IUCR"]);
m.PrimaryDesc = Convert.ToString(row["PrimaryDesc"]);
m.SecondaryDesc = Convert.ToString(row["SecondaryDesc"]);
m.NumCrimes = Convert.ToInt32(row["NumCrimes"]);
crimecodes.Add(m);
}
}
catch(Exception ex)
{
EX = ex;
}
finally
{
CrimeCodesList = crimecodes;
}
}
