public Tram GetTrambyID(int id)
{
Tram tram = null;
string query = "SELECT T.ID, T.NUMMER, TT.ID, TT.OMSCHRIJVING, T.LENGTE, T.VERVUILD, T.DEFECT, T.CONDUCTEURGESCHIKT FROM TRAM T JOIN TRAMTYPE TT ON T.TRAMTYPE_ID = TT.ID WHERE T.ID = @ID";
using (SqlConnection connection = DatabaseManager.Connection)
{
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@ID", id);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
decimal tramid = reader.GetDecimal(0);
decimal nummer = reader.GetDecimal(1);
decimal typeid = reader.GetDecimal(2);
string typeomschrijving = reader.GetString(3);
decimal lengte = reader.GetDecimal(4);
bool vervuild = reader.GetBoolean(5);
bool defect = reader.GetBoolean(6);
bool conducteurgeschikt = reader.GetBoolean(7);
tram = new Tram(tramid, nummer, lengte, vervuild, defect, conducteurgeschikt, new Tramtype(typeid, typeomschrijving));
}
}
}
}
return(tram);
}
public List <Tram> GetAllTrams()
{
List <Tram> returnList = new List <Tram>();
string query = "SELECT T.ID, T.NUMMER, TT.ID, TT.OMSCHRIJVING, T.LENGTE, T.VERVUILD, T.DEFECT, T.CONDUCTEURGESCHIKT FROM TRAM T JOIN TRAMTYPE TT ON T.TRAMTYPE_ID = TT.ID WHERE BESCHIKBAAR = 0";
using (SqlConnection connection = DatabaseManager.Connection)
{
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
decimal tramid = reader.GetDecimal(0);
decimal nummer = reader.GetDecimal(1);
decimal typeid = reader.GetDecimal(2);
string typeomschrijving = reader.GetString(3);
decimal lengte = reader.GetDecimal(4);
bool vervuild = reader.GetBoolean(5);
bool defect = reader.GetBoolean(6);
bool conducteurgeschikt = reader.GetBoolean(7);
Tram tram = new Tram(tramid, nummer, lengte, vervuild, defect, conducteurgeschikt, new Tramtype(typeid, typeomschrijving));
returnList.Add(tram);
}
}
}
}
return(returnList);
}
public Sector(decimal id, decimal?nummer, decimal spoorid, Tram tram, bool beschikbaar, bool blokkade)
{
Id = id;
SpoorId = spoorid;
Tram = tram;
Nummer = nummer;
Beschikbaar = beschikbaar;
Blokkade = blokkade;
}
public List <Sector> GetAllSectors()
{
List <Sector> returnList = new List <Sector>();
string query =
"SELECT S.ID, S.SPOOR_ID, T.ID, T.NUMMER, TT.ID, TT.OMSCHRIJVING, T.LENGTE, T.VERVUILD, T.DEFECT, T.CONDUCTEURGESCHIKT, S.NUMMER, S.BESCHIKBAAR, S.BLOKKADE, COALESCE(S.LATITUDE, 0), COALESCE(S.LONGITUDE, 0) FROM SECTOR S left JOIN TRAM T ON S.TRAM_ID = T.ID left JOIN TRAMTYPE TT ON T.TRAMTYPE_ID = TT.ID";
using (SqlConnection connection = DatabaseManager.Connection)
{
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
decimal latitude = 0;
decimal longitude = 0;
decimal sectorid = reader.GetDecimal(0);
decimal spoorid = reader.GetDecimal(1);
Tram tram = null;
if (!reader.IsDBNull(2))
{
//Why use decimals? They're only INTS i guess
decimal tramid = reader.GetDecimal(2);
decimal tramnummer = reader.GetDecimal(3);
decimal tramtypeid = reader.GetDecimal(4);
string tramtypeomschrijving = reader.GetString(5);
decimal tramlengte = reader.GetDecimal(6);
bool vervuild = reader.GetBoolean(7);
bool defect = reader.GetBoolean(8);
bool conducteur = reader.GetBoolean(9);
tram = new Tram(tramid, tramnummer, tramlengte, vervuild, defect, conducteur,
new Tramtype(tramtypeid, tramtypeomschrijving));
}
decimal sectornummer = reader.GetDecimal(10);
bool sectorbeschikbaar = reader.GetBoolean(11);
bool sectorblokkade = reader.GetBoolean(12);
latitude = reader.GetDecimal(13);
longitude = reader.GetDecimal(14);
Sector sector = new Sector(sectorid, sectornummer, spoorid,
tram, sectorbeschikbaar, sectorblokkade, latitude, longitude);
returnList.Add(sector);
}
}
}
}
return(returnList);
}
public Sector(decimal?nummer, Spoor spoor, Tram tram)
{
Spoor = spoor;
Tram = tram;
Nummer = nummer;
}