public IEnumerable<Showtime> GetPendingShowtimes(int movieId)
        {
            var executor = new CommandExecutor("dbo.BrowsePendingShowtimes", connectionString);
            executor.SetParam("@MovieId", movieId, SqlDbType.Int);
            var result = executor.ExecuteCommand();

            result.ThrowIfException();

            var dataSet = result as DataSet;

            foreach (DataRow row in dataSet.Tables[0].Rows)
            {
                yield return new Showtime
                {
                    Id = row["Id"].ToInt(),
                    Time = row["ShowtimeDate"].ToDate(),
                    Movie = new Movie
                    {
                        Id = row["MovieId"].ToInt(),
                        Title = row["MovieTitle"].ToString()
                    },
                    Auditorium = new Auditorium
                    {
                        Id = row["AuditoriumId"].ToInt(),
                        Name = row["AuditoriumName"].ToString(),
                        Rows = row["AuditoriumRows"].ToInt(),
                        Seats = row["AuditoriumSeats"].ToInt()
                    },
                    Price = row["Price"].ToInt(),
                    ThreeDee = row["ThreeDee"].ToBool()
                };
            }
        }
        public IEnumerable<Ticket> GetTickets()
        {
            var executor = new CommandExecutor("dbo.BrowseTickets", connectionString);
            var result = executor.ExecuteCommand();

            result.ThrowIfException();

            var dataSet = result as DataSet;

            foreach (DataRow row in dataSet.Tables[0].Rows)
            {
                yield return new Ticket
                {
                    Id = row["Id"].ToInt(),
                    Seat = new Seat
                    {
                        SeatNumber = row["SeatNumber"].ToInt(),
                        RowNumber = row["RowNumber"].ToInt(),
                    },
                    Showtime = new Showtime
                    {
                        Auditorium = new Auditorium
                        {
                            Name = row["Name"].ToString()
                        },
                        Movie = new Movie
                        {
                            Title = row["Title"].ToString()
                        },
                        Time = row["ShowtimeDate"].ToDate()
                    },
                    SellDate = row["SellDate"].ToDate()
                };
            }
        }
        public void Delete(Ticket ticket)
        {
            var executor = new CommandExecutor("dbo.DeleteTicket", connectionString);
            executor.SetParam("@Id", ticket.Id, SqlDbType.Int);

            executor.ExecuteCommand(true).ThrowIfException();
        }
        public void Delete(Auditorium auditorium)
        {
            var executor = new CommandExecutor("dbo.DeleteAuditorium", connectionString);
            executor.SetParam("@Id", auditorium.Id, SqlDbType.Int);

            executor.ExecuteCommand(true).ThrowIfException();
        }
        public IEnumerable<User> GetUsers()
        {
            var executor = new CommandExecutor("dbo.BrowseUsers", connectionString);
            var result = executor.ExecuteCommand();

            result.ThrowIfException();

            var dataSet = result as DataSet;

            foreach (DataRow row in dataSet.Tables[0].Rows)
            {
                var type = UserType.User;

                switch (row["Role"].ToInt())
                {
                    case 1:
                        type = UserType.User;
                        break;
                    case 2:
                        type = UserType.Admin;
                        break;
                    case 3:
                        type = UserType.Superadmin;
                        break;
                }

                yield return new User
                {
                    Login = row["User"].ToString(),
                    Type =  type
                };
            }
        }
        public void Delete(Showtime showtime)
        {
            var executor = new CommandExecutor("dbo.DeleteShowtime", connectionString);
            executor.SetParam("@Id", showtime.Id, SqlDbType.Int);

            executor.ExecuteCommand(true).ThrowIfException();
        }
        public void Delete(User user)
        {
            var executor = new CommandExecutor("dbo.DeleteUser", connectionString);

            executor.SetParam("@Username", user.Login, SqlDbType.NVarChar);

            var result = executor.ExecuteCommand();
            result.ThrowIfException();
        }
        public void SetLogo(BitmapImage logo)
        {
            var executor = new CommandExecutor("dbo.SetLogo", connectionString);

            var logoData = logo == null ? DBNull.Value : (object)logo.ToByteArray();
            executor.SetParam("@Logo", logoData, SqlDbType.Image);

            executor.ExecuteCommand(true).ThrowIfException();
        }
        public void Create(User user)
        {
            var executor = new CommandExecutor("dbo.CreateUser", connectionString);

            executor.SetParam("@Username", user.Login, SqlDbType.NVarChar);
            executor.SetParam("@Password", user.Password, SqlDbType.NVarChar);

            var userType = (int) user.Type + 1;
            executor.SetParam("@Usertype", userType, SqlDbType.Int);

            var result = executor.ExecuteCommand();
            result.ThrowIfException();
        }
        private void CheckConnectionWorkerOnDoWork(object sender, DoWorkEventArgs doWorkEventArgs)
        {
            var connectionString = ConnectionStringBuilder.Build(Server, Database, User, Password);

            var executor = new CommandExecutor("dbo.CurrentRole", connectionString);
            var result = executor.ExecuteCommand();

            view.Dispatcher.Invoke(() =>
            {
                view.IndicateConnectingFinished();

                var exception = result as Exception;
                if (exception != null)
                {
                    MessageBox.Show(exception.Message);
                    return;
                }

                try
                {
                    var userTypeId = (result as DataSet).Tables[0].Rows[0].ItemArray[0].ToInt();
                    Settings.Default.currentRole = (UserType) (userTypeId - 1);

                    if (Settings.Default.currentRole == UserType.User)
                    {
                        throw new Exception();
                    }
                }
                catch(Exception ex)
                {
                    MessageBox.Show(Resources.InvalidUserMessageText);
                    return;
                }

                Settings.Default.server = Server;
                Settings.Default.database = Database;
                Settings.Default.user = User;
                Settings.Default.password = Password;
                Settings.Default.Save();

                view.IndicateSuccess();
            });
        }
        public IEnumerable<Seat> GetOccupiedSeats(int showtimeId)
        {
            var executor = new CommandExecutor("dbo.GetOccupiedSeats", connectionString);
            executor.SetParam("@ShowtimeId", showtimeId, SqlDbType.Int);
            var result = executor.ExecuteCommand();

            result.ThrowIfException();

            var dataSet = result as DataSet;

            foreach (DataRow row in dataSet.Tables[0].Rows)
            {
                yield return new Seat
                {
                    SeatNumber = row["SeatNumber"].ToInt(),
                    RowNumber = row["RowNumber"].ToInt()
                };
            }
        }
        public IEnumerable<Auditorium> GetAuditoriums()
        {
            var executor = new CommandExecutor("dbo.BrowseAuditoriums", connectionString);
            var result = executor.ExecuteCommand();

            result.ThrowIfException();

            var dataSet = result as DataSet;

            foreach (DataRow row in dataSet.Tables[0].Rows)
            {
                yield return new Auditorium
                {
                    Id = row["Id"].ToInt(),
                    Name = row["Name"].ToString(),
                    Rows = row["RowsNumber"].ToInt(),
                    Seats = row["SeatsNumber"].ToInt()
                };
            }
        }
        public IEnumerable<LogEntry> GetLogEntries()
        {
            var executor = new CommandExecutor("dbo.BrowseLogs", connectionString);
            var result = executor.ExecuteCommand();

            result.ThrowIfException();

            var dataSet = result as DataSet;

            foreach (DataRow row in dataSet.Tables[0].Rows)
            {
                yield return new LogEntry
                {
                    Id = row["Id"].ToInt(),
                    Date = row["Date"].ToDate(),
                    User = row["User"].ToString(),
                    OperationType = (OperationType) (row["OperationType"].ToInt() + 1),
                    EntitiyId = row["EntityId"].ToNullableInt(),
                    TableName = row["EntityTable"].ToString()
                };
            }
        }
        public BitmapImage GetLogo()
        {
            var executor = new CommandExecutor("dbo.GetLogo", connectionString);
            var result = executor.ExecuteCommand();

            result.ThrowIfException();

            var dataSet = result as DataSet;

            var imageData = dataSet.Tables[0].Rows[0][0];
            if (imageData == DBNull.Value)
            {
                return null;
            }

            var posterBytes = (byte[]) imageData;
            var image = new BitmapImage();
            image.BeginInit();
            image.StreamSource = new MemoryStream(posterBytes);
            image.EndInit();

            return image;
        }
        public void RegisterTickets(int showtimeId, List<Seat> seats)
        {
            var executor = new CommandExecutor("dbo.RegisterTickets", connectionString);

            executor.SetParam("@ShowtimeId", showtimeId, SqlDbType.Int);

            var seatList = new DataTable();
            seatList.Columns.Add("SeatNumber");
            seatList.Columns.Add("RowNumber");

            seats.ForEach(seat => seatList.Rows.Add(seat.SeatNumber, seat.RowNumber));
            executor.SetParam("@Tickets", seatList, SqlDbType.Structured, "dbo.SeatList");

            executor.ExecuteCommand(true).ThrowIfException();
        }