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