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