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