public Dictionary <SmallerCrimeCode, int> topAreaYear(int top, int min, int max, string name)
{
Dictionary <SmallerCrimeCode, int> topTypeNum = new Dictionary <SmallerCrimeCode, int>();
Business bus = new Business("C:\\Users\\Jacky\\Downloads\\CrimeDB.mdf");
string sql = string.Format(@"
SELECT TOP {0} T.PrimaryDesc, T.SecondaryDesc, Count(*) AS Total
FROM Crimes
INNER JOIN
(SELECT * FROM Codes) AS T
ON T.IUCR = Crimes.IUCR
WHERE Crimes.Year >= {1} AND
Crimes.Year <= {2} AND
Crimes.Area = (SELECT Area FROM AREAS WHERE AreaName = '{3}')
GROUP BY T.PrimaryDesc, T.SecondaryDesc
ORDER BY Total DESC;
",
top,
min,
max,
name);
DataSet result = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in result.Tables["TABLE"].Rows)
{
// topTypeNum.Add(Convert.ToString(row["T.PrimaryDesc"]), Convert.ToString(row["T.SecondaryDesc"]), Convert.ToInt32(row["Total"]));
SmallerCrimeCode c;
c = new SmallerCrimeCode(Convert.ToString(row["PrimaryDesc"]), Convert.ToString(row["SecondaryDesc"]));
topTypeNum.Add(c, Convert.ToInt32(row["Total"]));
}
return(topTypeNum);
}
public Dictionary <SmallerCrimeCode, int> topTypes(int num)
{
Dictionary <SmallerCrimeCode, int> topTypeNum = new Dictionary <SmallerCrimeCode, int>();
Business bus = new Business("C:\\Users\\Jacky\\Downloads\\CrimeDB.mdf");
string sql = string.Format(@"
SELECT TOP {0} T.PrimaryDesc, T.SecondaryDesc, Count(*) AS Total
FROM Crimes
INNER JOIN
(SELECT * FROM Codes) AS T
ON T.IUCR = Crimes.IUCR
GROUP BY T.PrimaryDesc, T.SecondaryDesc
ORDER BY Total DESC;
",
num);
DataSet result = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in result.Tables["TABLE"].Rows)
{
// topTypeNum.Add(Convert.ToString(row["T.PrimaryDesc"]), Convert.ToString(row["T.SecondaryDesc"]), Convert.ToInt32(row["Total"]));
SmallerCrimeCode c;
c = new SmallerCrimeCode(Convert.ToString(row["PrimaryDesc"]), Convert.ToString(row["SecondaryDesc"]));
topTypeNum.Add(c, Convert.ToInt32(row["Total"]));
}
return(topTypeNum);
}
