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); }