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; } }