private List <ScreeningRoom> GetScreeningRoomFromDB()
        {
            ScreeningRoom currentScreeningRoom;

            dbConnection.Open();

            // This is a string representing the SQL query to execute in the db.
            string sqlQuery = "SELECT * FROM screening_room;";

            NpgsqlCommand dbCommand = new NpgsqlCommand(sqlQuery, dbConnection);

            NpgsqlDataReader dataReader = dbCommand.ExecuteReader();

            while (dataReader.Read())
            {
                currentScreeningRoom = new ScreeningRoom();

                currentScreeningRoom.Code     = dataReader.GetString(0);
                currentScreeningRoom.Capacity = dataReader.GetInt32(1);
                if (dataReader.IsDBNull(2))
                {
                    currentScreeningRoom.Description = "";
                }
                else
                {
                    currentScreeningRoom.Description = dataReader.GetString(2);
                }
                screeningRoomsListbox.Items.Add(currentScreeningRoom.Code);
                foundScreeningRoom.Add(currentScreeningRoom);
            }
            dbConnection.Close();

            return(foundScreeningRoom);
        }
        private void deleteButton_Click(object sender, EventArgs e)
        {
            ScreeningRoom DeleteScreeningRoom = new ScreeningRoom();

            DeleteScreeningRoom.Code        = codeTextBox.Text;
            DeleteScreeningRoom.Capacity    = int.Parse(capacityTextBox.Text);
            DeleteScreeningRoom.Description = descriptionTextBox.Text;

            DeleteScreeningRoomInDB(DeleteScreeningRoom);
            screeningRoomsListbox.Items.Clear();
            foundScreeningRoom.Clear();
            GetScreeningRoomFromDB();
        }
        private List <ScreeningRoom> LoadScreeningRoom(string ScreeningRoomCode)
        {
            //The following Connection, Command and DataReader objects will be used to access the jt_genre_movie table
            NpgsqlConnection dbConnection6 = CreateDBConnection(DbServerHost, DbUsername, DbUuserPassword, DbName);
            NpgsqlCommand    dbCommand6;
            NpgsqlDataReader dataReader6;

            ScreeningRoom currentScreeningRoom;

            List <ScreeningRoom> ScreeningRoomList = new List <ScreeningRoom>();

            dbConnection6.Open();

            // This is a string representing the SQL query to execute in the db.
            string sqlQuery = "SELECT * FROM screening_room WHERE code = '" + ScreeningRoomCode + "';";

            dbCommand6 = new NpgsqlCommand(sqlQuery, dbConnection6);

            dataReader6 = dbCommand6.ExecuteReader();

            //While there are genre_codes in the dataReader2
            while (dataReader6.Read())
            {
                currentScreeningRoom = new ScreeningRoom();

                currentScreeningRoom.Code     = dataReader6.GetString(0);
                currentScreeningRoom.Capacity = dataReader6.GetInt32(1);
                if (dataReader6.IsDBNull(2))
                {
                    currentScreeningRoom.Description = "";
                }
                else
                {
                    currentScreeningRoom.Description = dataReader6.GetString(2);
                }

                ScreeningRoomList.Add(currentScreeningRoom);
            }
            dbConnection6.Close();

            return(ScreeningRoomList);
        }
        private int InsertScreeningRoomInDB(ScreeningRoom ScreeningRoom)
        {
            try
            {
                NpgsqlConnection dbConnection9 = CreateDBConnection(DbServerHost, DbUsername, DbUuserPassword, DbName);
                NpgsqlCommand    dbCommand9;

                //This variable will store the number of affecter rows by the INSERT query
                int queryResult;

                //Before sending commands to the database, the connection must be opened
                dbConnection9.Open();

                //This is a string representing the SQL query to execute in the db
                string sqlQuery1 = "INSERT INTO screening_room VALUES('" + ScreeningRoom.Code + "', '" + ScreeningRoom.Capacity + "', '" + ScreeningRoom.Description + "');";

                //This is the actual SQL containing the query to be executed
                dbCommand9 = new NpgsqlCommand(sqlQuery1, dbConnection9);

                queryResult = dbCommand9.ExecuteNonQuery();

                //After executing the query(ies) in the db, the connection must be closed
                dbConnection9.Close();

                return(queryResult);
            }
            catch
            {
                MessageBox.Show("please enter a different screening room");

                NpgsqlConnection dbConnection9 = CreateDBConnection(DbServerHost, DbUsername, DbUuserPassword, DbName);

                dbConnection9.Close();

                return(0);
            }
        }
        private int DeleteScreeningRoomInDB(ScreeningRoom ScreeningRoom)
        {
            try
            {
                NpgsqlConnection dbConnection15 = CreateDBConnection(DbServerHost, DbUsername, DbUuserPassword, DbName);
                NpgsqlCommand    dbCommand15;

                //This variable will store the number of affecter rows by the INSERT query
                int queryResult;

                //Before sending commands to the database, the connection must be opened
                dbConnection15.Open();

                //This is a string representing the SQL query to execute in the db
                string sqlQuery = "DELETE FROM screening_room WHERE code = '" + ScreeningRoom.Code + "';" + "DELETE FROM showtime WHERE s_room_code = '" + ScreeningRoom.Code + "';";
                //This is the actual SQL containing the query to be executed
                dbCommand15 = new NpgsqlCommand(sqlQuery, dbConnection15);

                queryResult = dbCommand15.ExecuteNonQuery();

                //After executing the query(ies) in the db, the connection must be closed
                dbConnection15.Close();

                return(queryResult);
            }
            catch
            {
                MessageBox.Show("please delete a different ScreeningRoom");

                NpgsqlConnection dbConnection15 = CreateDBConnection(DbServerHost, DbUsername, DbUuserPassword, DbName);

                dbConnection15.Close();

                return(0);
            }
        }
        private int ModifyScreeningRoomInDB(ScreeningRoom ScreeningRoom)
        {
            try
            {
                NpgsqlConnection dbConnection12 = CreateDBConnection(DbServerHost, DbUsername, DbUuserPassword, DbName);

                //This variable will store the number of affecter rows by the INSERT query
                int queryResult;

                //Before sending commands to the database, the connection must be opened
                dbConnection12.Open();

                //This is a string representing the SQL query to execute in the db
                string sqlQuery = "UPDATE screening_room SET capacity = '" + ScreeningRoom.Capacity + "', description = '" + ScreeningRoom.Description + "' WHERE code = '" + ScreeningRoom.Code + "';";

                //This is the actual SQL containing the query to be executed
                NpgsqlCommand dbCommand12 = new NpgsqlCommand(sqlQuery, dbConnection12);

                queryResult = dbCommand12.ExecuteNonQuery();

                //After executing the query(ies) in the db, the connection must be closed
                dbConnection12.Close();

                return(queryResult);
            }
            catch
            {
                MessageBox.Show("please Modify a different ScreeningRoom");

                NpgsqlConnection dbConnection12 = CreateDBConnection(DbServerHost, DbUsername, DbUuserPassword, DbName);

                dbConnection12.Close();

                return(0);
            }
        }