///
/// <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>();
try
{
//
// TODO!
//
//DataAccessTier.Data dataTier = new DataAccessTier.Data(DatabaseFilename);
string sql = @"
Select StationID, Name FROM Stations
ORDER BY Name;
";
DataSet result = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in result.Tables["TABLE"].Rows)
{
CTAStation s = new CTAStation(Convert.ToInt32(row["StationID"]), row["Name"].ToString());
stations.Add(s);
}
return(stations);
//stations = result;
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetStations: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
}
///
/// <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>();
try
{
string sql = String.Format(@"
select * from Stations
order by Name
");
//for every row in the table returned, create CTAStation object and insert end of the list of CTA Station
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
CTAStation oneStation = new CTAStation(Convert.ToInt32(row["StationID"]), row["Name"].ToString());
stations.Add(oneStation);
}
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetStations: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stations);
}
//returns read-only list of CTAStation objects given string partial station name
public IReadOnlyList <CTAStation> FindStations(string partialStationName)
{
List <CTAStation> stations = new List <CTAStation>();
try
{
partialStationName = partialStationName.Replace("'", "''");
string sql = String.Format(@"
select StationID, Name from Stations
where Name like '%{0}%'
", partialStationName);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
CTAStation oneStation = new CTAStation(Convert.ToInt32(row["StationID"]), row["Name"].ToString());
stations.Add(oneStation);
}
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetTopStations: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stations);
}
/********************** NICE CUMULATIVE GETX DETAILS FUNCTIONS \( ^O^ )/ *****************************************/
// not sure if this is really needed when I have all the other functions below
// but its a self-practice with using objects the BusinessTierObjects
// plus its nice to have to call only one function in Presentation to grab all the data
public CTAStation GetStationDetails(string stationName)
{
int stationID = GetStationID(stationName);
CTAStation selectedStation = new CTAStation(stationID, GetTotalRidership(stationID), GetAverageRidership(stationID),
GetPercentRidershipDisplay(stationID), GetRidershipByDay(stationID, 'W'), GetRidershipByDay(stationID, 'A'),
GetRidershipByDay(stationID, 'U'));
return(selectedStation);
}
///
/// <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>();
try
{
//
// TODO!
//
string sql = string.Format(@"
SELECT Top {0} Name, Stations.StationID, Sum(DailyTotal) As TotalRiders
FROM Riderships
INNER JOIN Stations ON Riderships.StationID = Stations.StationID
GROUP BY Stations.StationID, Name
ORDER BY TotalRiders DESC;
", N);
//MessageBox.Show(sql);
//SqlCommand cmd = new SqlCommand();
//cmd.Connection = db;
//SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);/*new DataSet();*/
//cmd.CommandText = sql;
//adapter.Fill(ds);
// display stations:
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
var ss = new CTAStation(Convert.ToInt32(row["StationID"]), row["Name"].ToString());
//add to stations
stations.Add(ss);
//this.lstStations.Items.Add(row["Name"].ToString());
}
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetTopStations: '{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> stations = new List <CTAStation>();
try
{
//
// TODO!
//
//db = new SqlConnection(BuildConnectionString());
//db.Open();
string sql = string.Format(@"SELECT Name, StationID FROM Stations ORDER BY Name ASC;");
//MessageBox.Show(sql);
//SqlCommand cmd = new SqlCommand();
//cmd.Connection = db;
//SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
//cmd.CommandText = sql;
//dapter.Fill(ds);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
//this.lstStations.Items.Add(row["Name"].ToString());
var ss = new CTAStation(Convert.ToInt32(row["StationID"]), row["Name"].ToString());
//add to stations
stations.Add(ss);
}
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetStations: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stations);
}
///
/// <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>();
try
{
//
// TODO!
//
string sql = string.Format(@"
SELECT Top {0} Name, Stations.StationID, Sum(DailyTotal) As TotalRiders
FROM Riderships
INNER JOIN Stations ON Riderships.StationID = Stations.StationID
GROUP BY Stations.StationID, Name
ORDER BY TotalRiders DESC;
", N);
DataSet result = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in result.Tables["TABLE"].Rows)
{
CTAStation s = new CTAStation(Convert.ToInt32(row["StationID"]), row["Name"].ToString());
stations.Add(s);
}
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetTopStations: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stations);
}
//private; returns read-only list of CTAStation objects of top N stations by riderships given a type of Day
private IReadOnlyList <CTAStation> GetTopStationsTypeOfDay(int N, string typeOfDay)
{
if (N < 1)
{
throw new ArgumentException("GetTopStations: N must be positive");
}
List <CTAStation> stations = new List <CTAStation>();
try
{
string sql = String.Format(@"
select Stations.StationID, Name from Stations
inner join (
select top {0} sum (Riderships.DailyTotal) as TotalRidership, Riderships.StationID from Riderships
where TypeOfDay = '{1}'
group by Riderships.StationID
order by sum (Riderships.DailyTotal) desc
) as T
on Stations.StationID = T.StationID
order by Name
", N, typeOfDay);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
CTAStation oneStation = new CTAStation(Convert.ToInt32(row["StationID"]), row["Name"].ToString());
stations.Add(oneStation);
}
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetTopStations: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stations);
}
