/// /// <summary> /// Returns all the CTA Stations, ordered by name. /// </summary> /// <returns>Read-only list of CTAStation objects</returns> /// public IReadOnlyList <CTAStation> GetStations() { List <CTAStation> stations = new List <CTAStation>(); DataSet result = new DataSet(); try { // // TODO! // dataTier = new DataAccessTier.Data(_DBFile); string sql = "SELECT Name, StationID FROM Stations ORDER BY Name ASC;"; result = dataTier.ExecuteNonScalarQuery(sql); foreach (DataRow row in result.Tables["TABLE"].Rows) { string name = string.Format("{0}", Convert.ToString(row["Name"])); int id = Convert.ToInt32(row["StationID"]); var s = new BusinessTier.CTAStation(id, name); stations.Add(s); } } catch (Exception ex) { string msg = string.Format("Error in Business.GetStations: '{0}'", ex.Message); throw new ApplicationException(msg); } return(stations); }
/// /// <summary> /// Returns all the CTA Stations, ordered by name. /// </summary> /// <returns>Read-only list of CTAStation objects</returns> /// public IReadOnlyList <CTAStation> GetStations() { List <CTAStation> list = new List <CTAStation>(); try { String sql = string.Format(@" Select * From Stations Order by Name "); DataSet stations = dataTier.ExecuteNonScalarQuery(sql); foreach (DataRow row in stations.Tables["TABLE"].Rows) { var station = new BusinessTier.CTAStation(Convert.ToInt32(row["StationID"]), Convert.ToString(row["Name"])); list.Add(station); } } catch (Exception ex) { string msg = string.Format("Error in Business.GetStations: '{0}'", ex.Message); throw new ApplicationException(msg); } return(list); }
// Returns the top 10 stations by sunday/holiday riders public IReadOnlyList <CTAStation> GetTopSun() { List <CTAStation> list = new List <CTAStation>(); try { string sql = string.Format(@" Select Stations.StationID, Name From Stations Join (Select Top(10) StationID, Sum(DailyTotal) as Total From Riderships Where TypeOfDay = 'U' Group by StationID Order by Total DESC) as StationTotals ON Stations.StationID = StationTotals.StationID "); DataSet stations = dataTier.ExecuteNonScalarQuery(sql); foreach (DataRow row in stations.Tables["TABLE"].Rows) { var station = new BusinessTier.CTAStation(Convert.ToInt32(row["StationID"]), Convert.ToString(row["Name"])); list.Add(station); } } catch (Exception ex) { string msg = string.Format("Error in Business.GetTopStations: '{0}'", ex.Message); throw new ApplicationException(msg); } return(list); }
/// /// <summary> /// Returns the top N CTA Stations by ridership, /// ordered by name. /// </summary> /// <returns>Read-only list of CTAStation objects</returns> /// public IReadOnlyList <CTAStation> GetTopStations(int N) { if (N < 1) { throw new ArgumentException("GetTopStations: N must be positive"); } List <CTAStation> stations = new List <CTAStation>(); DataSet result; try { // // TODO! // DataAccessTier.Data dataTier = new DataAccessTier.Data(_DBFile); string sql = string.Format(@" SELECT TOP {0} Name, Sum(DailyTotal) As TotalRiders FROM Riderships INNER JOIN Stations ON Riderships.StationID = Stations.StationID GROUP BY Stations.StationID, Name ORDER BY TotalRiders DESC; ", N); result = dataTier.ExecuteNonScalarQuery(sql); foreach (DataRow row in result.Tables["TABLE"].Rows) { string name = string.Format("{0}", Convert.ToString(row["Name"])); var s = new BusinessTier.CTAStation(123, name); stations.Add(s); } } catch (Exception ex) { string msg = string.Format("Error in Business.GetTopStations: '{0}'", ex.Message); throw new ApplicationException(msg); } return(stations); }