private void btnDelete_Click(object sender, EventArgs e)
{
DialogResult res = MessageBox.Show("Are you sure you want to Delete '" + txtTitle.Text + "' Movie", "Confirmation", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (res == DialogResult.OK)
{
try
{
//delete from database
SqlConnection conn = DatabaseManager.GetConnection();
string query = $"Delete from Movies where Id = {movieList[moviesBox.SelectedIndex].Id}";
SqlCommand cmd = new SqlCommand(query, conn);
if (cmd.ExecuteNonQuery() < 1)
{
throw new Exception("Movie not found, please refresh this page");
}
MessageBox.Show("Movie succesfully deleted!");
conn.Close();
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
if (res == DialogResult.Cancel)
{
txtTitle.Focus();
}
}
/// <summary>
/// Inserts this instance of a movie to the database if it is not already there
/// </summary>
public bool InsertToDatabase()
{
//Check if the id is present in the db
if (DatabaseManager.ExistsMovie(this))
{
return(false);
}
SqlConnection conn = DatabaseManager.GetConnection();
string query = $"Insert into Movies" +
$" values ('{Title}', {Year}, '{Director}', {IndexGenre}";
if (RottenTomatoesScore >= 0)
{
query += $", {RottenTomatoesScore}";
}
else
{
query += ", null";
}
if (TotalBoxOffice >= 0)
{
query += $", {TotalBoxOffice}";
}
else
{
query += ", null";
}
query += ");";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
/// <summary>
/// Looks at the database and stores the records in a list of movies
/// </summary>
/// <returns>A list with all the movies that are currently in our database</returns>
public static List <Movie> GetMovies()
{
List <Movie> res = new List <Movie>();
SqlConnection conn = DatabaseManager.GetConnection();
string query = "Select * from Movies order by title";
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Movie m;
int id = reader.GetInt32(0);
string t = reader.GetString(1);
uint year = (uint)reader.GetInt32(2);
string dir = reader.GetString(3);
string genre = genreMap[reader.GetInt32(4)];
try
{
int score = reader.GetInt32(5);
decimal total = reader.GetDecimal(6);
m = new Movie(id, t, year, dir, genre, score, total);
}
catch (System.Data.SqlTypes.SqlNullValueException)
{
m = new Movie(id, t, year, dir, genre);
}
res.Add(m);
}
conn.Close();
return(res);
}
public static bool ExistsMovie(Movie m)
{
SqlConnection conn = DatabaseManager.GetConnection();
string query = $"Select * from Movies where Id = {m.Id} or (Title = '{m.Title}' and Director = '{m.Director}')";
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
bool res = reader.HasRows;
conn.Close();
return(res);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
DialogResult res = MessageBox.Show("Are you sure you want to update '" + txtTitle.Text + "'s information?", "Confirmation", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (res == DialogResult.OK)
{
try
{
Movie currMovie = movieList[moviesBox.SelectedIndex];
SqlConnection conn = DatabaseManager.GetConnection();
StringBuilder sb = new StringBuilder("Update Movies set ");
bool changed = txtDirector.Text != currMovie.Director || txtTitle.Text != currMovie.Title ||
txtYear.Text != currMovie.Year.ToString() || txtScore.Text != currMovie.RottenTomatoesScore.ToString() ||
txtEarnings.Text != currMovie.TotalBoxOffice.ToString() || cboxGenre.SelectedIndex != currMovie.IndexGenre;
if (!changed)
{
throw new Exception("The movie's information remains the same, please change a field to update it in the database!");
}
if (txtDirector.Text != currMovie.Director)
{
sb.Append($"Director = '{txtDirector.Text}'");
}
if (txtTitle.Text != currMovie.Title)
{
sb.Append($"Title = '{txtTitle.Text}'");
}
if (txtYear.Text != currMovie.Year.ToString())
{
sb.Append($"Year = {txtYear.Text}");
}
if (txtScore.Text != currMovie.RottenTomatoesScore.ToString())
{
sb.Append($"RottenTomatoesScore = {txtScore.Text}");
}
if (txtEarnings.Text != currMovie.TotalBoxOffice.ToString())
{
sb.Append($"TotalEarned = {Convert.ToDecimal(txtEarnings.Text)}");
}
if (cboxGenre.SelectedIndex != currMovie.IndexGenre)
{
sb.Append($"Genre = {cboxGenre.SelectedIndex}");
}
sb.Append($" where Id = {currMovie.Id};");
SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
if (cmd.ExecuteNonQuery() < 1)
{
throw new Exception("Movie not found, please refresh this page");
}
MessageBox.Show("Movie succesfully updated!");
conn.Close();
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
if (res == DialogResult.Cancel)
{
txtTitle.Focus();
}
}
