public string BookSeat(DateTime date, string showTitle, int row, int number, string customerName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
            MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);

            // Get ID of the show that title is "showTitle".
            int showID = Convert.ToInt32(dataContext.Shows.Where(showEntity => showEntity.Title == showTitle).First().ShowID);

            // Get ID of the entertainment that refer to showID and will be performed on "date".
            int entertainmentID = Convert.ToInt32(dataContext.Entertainments.Where(entertainmentEntity =>
                entertainmentEntity.Date == date && entertainmentEntity.ShowID == showID).First().EntertainmentID);

            // Get required seat.
            var seat = dataContext.Seats.Where(seatEntity => seatEntity.EntertainmentID == entertainmentID &&
                seatEntity.Row == row && seatEntity.Number == number).First();

            if (seat.CustomerName == null)
            {
                seat.CustomerName = customerName;
                dataContext.SubmitChanges();
                return "success";
            }
            else
                return "fail";
        }
        /// <summary>
        /// Private method, ships number of open seats for particular entertainment. 
        /// </summary>
        /// <param name="entertainmentID">
        /// ID for investigating entertainment.
        /// </param>
        /// <returns>
        /// Number of open seats.
        /// </returns>
        private int OpenSeat(int entertainmentID)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
            MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);

            return dataContext.Seats.Where(seatEntity => seatEntity.EntertainmentID == entertainmentID &&
                seatEntity.CustomerName == null).Count();
        }
        public ArrayList GetShowDetails(string showTitle)
        {
            ArrayList reply = new ArrayList();

            string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
            MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);

            // Identify particular play based on "showTitle".
            var opera = dataContext.Shows.Where(showEntity => showEntity.Title == showTitle).First();

            // Populate reply array with appropiate data.
            reply.Add(opera.Conductor);
            reply.Add(opera.Cast);
            reply.Add(opera.Language);
            reply.Add(opera.MusicBy);
            reply.Add(opera.Orchestra);
            reply.Add(opera.Libretto);
            reply.Add(opera.Runtime.ToString());

            return reply;
        }
        public ArrayList GetSeats(DateTime date, string showTitle)
        {
            ArrayList reply = new ArrayList();

            string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
            MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);

            // Get ID of the show that title is "showTitle".
            int showID = Convert.ToInt32(dataContext.Shows.Where(showEntity => showEntity.Title == showTitle).First().ShowID);

            // Get ID of the entertainment that refer to showID and will be performed on "date".
            int entertainmentID = Convert.ToInt32(dataContext.Entertainments.Where(entertainmentEntity =>
                entertainmentEntity.Date == date && entertainmentEntity.ShowID == showID).First().EntertainmentID);

            // Get all seats that refer to entertainment.
            var allSeats = dataContext.Seats.Where(seatEntity => seatEntity.EntertainmentID == entertainmentID);

            // Extract data from each seat and populate with them final table.
            foreach (var item in allSeats)
            {
                reply.Add(item.Row.ToString() + "," + item.Number.ToString());
                if (item.CustomerName == null)
                    reply.Add("available");
                else
                    reply.Add("unavailable");
                reply.Add(item.Price.ToString());
            }

            return reply;
        }
        public ArrayList GetScheduleForDate(DateTime date)
        {
            ArrayList reply = new ArrayList();

            // Border dates.
            DateTime bottomDate = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0),
                upperDate = new DateTime(date.Year, date.Month, date.Day, 23, 59, 59);

            string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
            MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);

            // Collect all entertainments in "date".
            var collectionOfEntertainments = dataContext.Entertainments.Where(collectionEntity =>
                collectionEntity.Date >= bottomDate && collectionEntity.Date <= upperDate).OrderBy(x => x.Date);

            // Populate the final array.
            foreach (var item in collectionOfEntertainments)
            {
                // Get play that entertainment entity refered to.
                var opera = dataContext.Shows.Where(operaEntity => operaEntity.ShowID == item.ShowID).First();
                reply.Add(opera.Title);
                reply.Add(item.Date);
                reply.Add(opera.Conductor);
                reply.Add(opera.Cast);
                reply.Add(opera.Runtime);
                reply.Add(OpenSeat(item.EntertainmentID));
            }

            return reply;
        }
        public ArrayList GetScheduleForAMonth(DateTime date)
        {
            ArrayList reply = new ArrayList();

            string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
            MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);

            DateTime bottomDate = new DateTime(date.Year, date.Month, 1, 0, 0, 0),
                upperDate = bottomDate.AddMonths(1);

            var collectionOfEnt = dataContext.Entertainments.Where(ent => ent.Date >= bottomDate && ent.Date <= upperDate).OrderBy(x => x.Date);

            foreach (var item in collectionOfEnt)
            {
                string title = (dataContext.Shows.Where(showEntity => showEntity.ShowID == item.ShowID).Select(
                    searchedShow => searchedShow.Title).First());
                DateTime dateOfEnt = item.Date;

                int runtime = dataContext.Shows.Where(showEntity => showEntity.Title == title).Select(y => y.Runtime).First();
                int openSeats = OpenSeat(dataContext.Entertainments.Where(entity => entity.Date == dateOfEnt && entity.ShowID == item.ShowID).Select(z => z.EntertainmentID).First());
                reply.Add(title);
                reply.Add(dateOfEnt);
                reply.Add(runtime);
                reply.Add(openSeats);
            }

            return reply;
        }
        public ArrayList Get3EarliestEntertainment(DateTime sinceDate)
        {
            ArrayList reply = new ArrayList();

            string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
            MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);

            var collectionOfEntertainment = dataContext.Entertainments.Where(entertainment => entertainment.Date >= sinceDate);
            int counterOfEntertainments = 0;
            foreach (var item in collectionOfEntertainment)
            {
                string title = (dataContext.Shows.Where(showEntity => showEntity.ShowID == item.ShowID).Select(
                    searchedShow => searchedShow.Title).First());
                DateTime date = item.Date;

                int runtime = dataContext.Shows.Where(showEntity => showEntity.Title == title).Select(y => y.Runtime).First();
                int openSeats = OpenSeat(dataContext.Entertainments.Where(entity => entity.Date == date && entity.ShowID == item.ShowID).Select(z => z.EntertainmentID).First());
                reply.Add(title);
                reply.Add(date);
                reply.Add(runtime);
                reply.Add(openSeats);
                counterOfEntertainments++;

                if (counterOfEntertainments.Equals(3))
                    return reply;
            }

            return reply;
        }