//Function to get info on each stop (handicap,direction of travel, lat/lon)
public CTAStop GetStopInfo(string name_stop, string station_id)
{
string sql = string.Format(@"SELECT * FROM Stops WHERE Name = '{0}' AND StationID = {1};", name_stop, station_id);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
DataRow row = ds.Tables["TABLE"].Rows[0];
//Handicap
/*
* string ada = "Yes";
* if (!Convert.ToBoolean(row["ADA"]))
* {
* ada = "No";
* }*/
//Direction
string direction = row["Direction"].ToString();
//Location
double lat = Convert.ToDouble(row["Latitude"]);
double lon = Convert.ToDouble(row["Longitude"]);
var ss = new CTAStop(Convert.ToInt32(row["StopID"]),
row["Name"].ToString(),
Convert.ToInt32(row["StationID"]),
direction,
Convert.ToBoolean(row["ADA"]),
lat,
lon
);
return(ss);
}
///
/// <summary>
/// Returns the CTA Stop info associated with a
/// given station and stop Name.
/// </summary>
/// <returns>CTAStop objec</returns>
///
public CTAStop getStopInfo(string stopName, int stationID)
{
CTAStop stop;
try
{
string sql = string.Format(@"
SELECT StopID, Direction, ADA, Latitude, Longitude
FROM Stops
WHERE Name = '{0}' AND
StationID = {1};
" , stopName, stationID);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
DataRow R = ds.Tables["TABLE"].Rows[0];
stop = new CTAStop(Convert.ToInt32(R["StopID"]), stopName,
stationID, R["Direction"].ToString(), Convert.ToBoolean(R["ADA"]), Convert.ToDouble(R["Latitude"]), Convert.ToDouble(R["Longitude"]));
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.getStopInfo: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stop);
}
///
/// <summary>
/// Returns the CTA Stops associated with a given station,
/// ordered by name.
/// </summary>
/// <returns>Read-only list of CTAStop objects</returns>
///
public IReadOnlyList <CTAStop> GetStops(int stationID)
{
List <CTAStop> stops = new List <CTAStop>();
try
{
string sql = String.Format(@"
select * from Stops
where Stops.StationID = {0}
order by Stops.Name
", stationID);
//for every row in the table returned, create CTAStop object and insert end of the list of CTA Stop
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
int stopID = Convert.ToInt32(row["StopID"]);
string stopName = row["Name"].ToString();
string direction = row["Direction"].ToString();
bool ada = Convert.ToBoolean(row["ADA"]);
double latitude = Convert.ToDouble(row["Latitude"]);
double longitude = Convert.ToDouble(row["Longitude"]);
CTAStop oneStop = new CTAStop(stopID, stopName, stationID, direction, ada, latitude, longitude);
stops.Add(oneStop);
}
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetStops: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stops);
}
public CTAStop GetStopDetails(string stopName)
{
int stopID = GetStopID(stopName);
CTAStop selectedStop = new CTAStop(stopID, GetDirection(stopID), GetHandicap(stopID),
GetLocationDisplay(stopID), GetLines(stopID));
return(selectedStop);
}
///
/// <summary>
/// Returns the CTA Stops associated with a given station,
/// ordered by name.
/// </summary>
/// <returns>Read-only list of CTAStop objects</returns>
///
public IReadOnlyList <CTAStop> GetStops(int stationID, string stationName)
{
List <CTAStop> stops = new List <CTAStop>();
stationName = stationName.Replace("'", "''");
try
{
//
// TODO!
//
string sql = string.Format(@"
SELECT Stops.Name, Stops.StationID, StopID, Direction, ADA, Latitude, Longitude
FROM Stops, Stations
WHERE Stops.StationID = Stations.StationID
AND Stations.Name = '{0}'
ORDER BY Stops.Name ASC;
", stationName);
DataSet result = dataTier.ExecuteNonScalarQuery(sql);
stationName = stationName.Replace("''", "'");
foreach (DataRow row in result.Tables["TABLE"].Rows)
{
CTAStop s = new CTAStop(stationID,
row["Name"].ToString(),
stationID,
row["Direction"].ToString(),
Convert.ToBoolean(row["ADA"]),
Convert.ToDouble(row["Latitude"]),
Convert.ToDouble(row["Longitude"])
);
stops.Add(s);
}
return(stops);
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetStops: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stops);
}
///
/// <summary>
/// Returns the CTA Stops associated with a given station,
/// ordered by name.
/// </summary>
/// <returns>Read-only list of CTAStop objects</returns>
///
public IReadOnlyList <CTAStop> GetStops(int stationID)
{
List <CTAStop> stops = new List <CTAStop>();
try
{
//
// TODO!
//
// String sql = string.Format(@"SELECT Stops.Name as StopName FROM Stops INNER JOIN Stations ON Stops.StationID = Stations.StationID WHERE Stations.Name = '{0}' ORDER BY Stops.Name ASC;", nameStation);
string sql = string.Format(@"SELECT Latitude, Longitude, ADA, Direction, StopID,Name,StationID FROM Stops WHERE StationID = {0} ORDER BY Name ASC;", stationID);
//adapter = new SqlDataAdapter(cmd);
DataSet ds = dataTier.ExecuteNonScalarQuery(sql);/*new DataSet();*/
//stops
foreach (DataRow row in ds.Tables["TABLE"].Rows)
{
//this.listBox2.Items.Add(row7["StopName"].ToString());
//int stopID, string stopName, int stationID, string direction, bool ada, double latitude, double longitude
var ss = new CTAStop(Convert.ToInt32(row["StopID"]),
row["Name"].ToString(),
Convert.ToInt32(row["StationID"]),
row["Direction"].ToString(),
Convert.ToBoolean(row["ADA"]),
Convert.ToDouble(row["Latitude"]),
Convert.ToDouble(row["Longitude"])
);
//Add stops
stops.Add(ss);
}
}
catch (Exception ex)
{
string msg = string.Format("Error in Business.GetStops: '{0}'", ex.Message);
throw new ApplicationException(msg);
}
return(stops);
}
