List <FilmShowTime> IShowTimeDAO.GetAllShowtimes()
{
List <FilmShowTime> list = new List <FilmShowTime>();
try
{
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
string sql = @"SELECT s.showtime AS 'time', f.title AS 'title', f.id as 'filmId',
f.rating AS 'rating', s.price AS 'price', t.name AS 'screen'
FROM showtimes s JOIN theatres t ON s.theatreId = t.id JOIN films f ON t.id = f.screenId;";
//showtimes s
//JOIN films f on f.id = s.filmId
//JOIN theatres t on s.theatreId = t.id;";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
FilmShowTime showtime = this.RowToFilmShowTime(reader);
list.Add(showtime);
}
}
}
catch (SqlException ex)
{
throw ex;
}
return(list);
}
//Maps a single database line into a FilmShowTime object
private FilmShowTime RowToFilmShowTime(SqlDataReader reader)
{
FilmShowTime result = new FilmShowTime();
result.Time = (TimeSpan)(reader["time"]);
result.Title = Convert.ToString(reader["title"]);
result.Rating = Convert.ToString(reader["rating"]);
result.Screen = Convert.ToString(reader["screen"]);
result.Price = Convert.ToDecimal(reader["price"]);
result.FilmId = Convert.ToInt32(reader["filmId"]);
return(result);
}
