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