예제 #1
0
        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;  
                    }
        }  
예제 #2
0
        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;
            }
        }