public void OnGet()
{
List<Models.CrimeCode> Crimes = new List<Models.CrimeCode>();
// clear exception:
EX = null;
try
{
totalCrimes=0;
string sql = string.Format(@"
SELECT Crimes.IUCR, PrimaryDesc, SecondaryDesc, Count(*) AS numTimes
FROM Crimes
INNER JOIN Codes ON Crimes.IUCR = Codes.IUCR
GROUP BY Crimes.IUCR, PrimaryDesc, SecondaryDesc
ORDER BY PrimaryDesc, SecondaryDesc ASC;
");
DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
Models.CrimeCode m = new Models.CrimeCode();
m.IUCR = Convert.ToString(row["IUCR"]);
m.PrimaryDesc = Convert.ToString(row["PrimaryDesc"]);
m.SecondaryDesc = Convert.ToString(row["SecondaryDesc"]);
m.numCrimes = Convert.ToInt32(row["numTimes"]);
Crimes.Add(m);
totalCrimes++;
}
}
catch(Exception ex)
{
EX = ex;
}
finally
{
CrimeCodeList = Crimes;
}
}
public void OnGet()
{
List <Models.CrimeCode> crimes = new List <Models.CrimeCode>();
// clear exception:
EX = null;
try
{
string sql = string.Format(@"
SELECT Crimes.IUCR, PrimaryDesc, SecondaryDesc, COUNT(Crimes.IUCR) AS NumOccur FROM Crimes
LEFT JOIN Codes ON Crimes.IUCR = Codes.IUCR
GROUP BY Crimes.IUCR, PrimaryDesc, SecondaryDesc
ORDER BY PrimaryDesc, SecondaryDesc ASC;
");
DataSet ds = DataAccessTier.DB.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
Models.CrimeCode c = new Models.CrimeCode();
c.IUCR = Convert.ToString(row["IUCR"]);
c.PrimaryDesc = Convert.ToString(row["PrimaryDesc"]);
c.SecondaryDesc = Convert.ToString(row["SecondaryDesc"]);
c.NumOccur = Convert.ToInt32(row["NumOccur"]);
crimes.Add(c);
}
}
catch (Exception ex)
{
EX = ex;
}
finally
{
CrimeList = crimes;
}
}