public List <Routes> FetchAll()
{
List <Routes> returnList = new List <Routes>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sqlQuery = "SELECT * FROM dbo.Routes";
SqlCommand command = new SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Routes route = new Routes();
TransportCenterDAO tcDAO = new TransportCenterDAO();
int centerAId = reader.GetInt32(reader.GetOrdinal("Center_A_Id"));
TransportCenter centerA = tcDAO.FetchOne(centerAId);
int centerBId = reader.GetInt32(reader.GetOrdinal("Center_B_Id"));
TransportCenter centerB = tcDAO.FetchOne(centerBId);
TransportTypeDAO ttDAO = new TransportTypeDAO();
int typeId = reader.GetOrdinal("Transport_Id");
TransportType transportType = ttDAO.FetchOne(typeId);
route.Id = reader.GetInt32(reader.GetOrdinal("Id"));
route.CenterA = centerA;
route.CenterB = centerB;
route.Type = transportType;
if (reader.IsDBNull(reader.GetOrdinal("Travel_Time_Hours")))
{
route.TravelTime = 1000;
}
else
{
route.TravelTime = reader.GetInt32(reader.GetOrdinal("Travel_Time_Hours"));
}
route.Price = (int)transportType.PricePerHour * route.TravelTime;
returnList.Add(route);
}
}
}
return(returnList);
}
public Routes FetchOne(int A_ID, int B_ID)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sqlQuery = "SELECT * from dbo.Routes WHERE(CENTER_A_ID = @A_ID AND CENTER_B_ID = @B_ID AND TRANSPORT_ID = 1) OR (CENTER_B_ID = @A_ID AND CENTER_A_ID = @B_ID AND TRANSPORT_ID = 1)";
SqlCommand command = new SqlCommand(sqlQuery, connection);
command.Parameters.Add("@A_Id", System.Data.SqlDbType.Int).Value = A_ID;
command.Parameters.Add("@B_Id", System.Data.SqlDbType.Int).Value = B_ID;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
Routes route = new Routes();
if (reader.HasRows)
{
while (reader.Read())
{
TransportCenterDAO tcDAO = new TransportCenterDAO();
int centerAId = reader.GetInt32(reader.GetOrdinal("Center_A_Id"));
TransportCenter centerA = tcDAO.FetchOne(centerAId);
int centerBId = reader.GetInt32(reader.GetOrdinal("Center_B_Id"));
TransportCenter centerB = tcDAO.FetchOne(centerBId);
TransportTypeDAO ttDAO = new TransportTypeDAO();
int typeId = reader.GetOrdinal("Transport_Id");
TransportType transportType = ttDAO.FetchOne(typeId);
route.Id = reader.GetInt32(reader.GetOrdinal("Id"));
route.CenterA = centerA;
route.CenterB = centerB;
route.Type = transportType;
if (reader.IsDBNull(reader.GetOrdinal("Travel_Time_Hours")))
{
route.TravelTime = 1000;
}
else
{
route.TravelTime = reader.GetInt32(reader.GetOrdinal("Travel_Time_Hours"));
}
route.Price = (int)transportType.PricePerHour * route.TravelTime;
}
}
return(route);
}
}
