private void btnMovDelete_Click(object sender, EventArgs e) { if (txtMovNum.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Movie Number.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovNum.Focus(); return; } else { Movie objMovieNum = MovieDB.GetMovie(Convert.ToInt16(txtMovNum.Text.Trim())); if (objMovieNum == null) { MessageBox.Show("Movie Number " + txtMovNum.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); txtMovNum.Clear(); txtMovNum.Focus(); return; } } Movie objMovie = new Movie(); objMovie.MovieNumber = txtMovNum.Text.Trim(); try { bool status = MovieDB.DeleteMovie(objMovie); if (status) { MessageBox.Show("Movie deleted from the database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); ClearMovieFields(); MovieListLoad(); } else { MessageBox.Show("Movie was not deleted from the database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void btnMovAdd_Click(object sender, EventArgs e) { if (txtMovNum.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Movie Number.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovNum.Focus(); return; } else { Movie objMovieNum = MovieDB.GetMovie(Convert.ToInt16(txtMovNum.Text.Trim())); if (objMovieNum != null) { MessageBox.Show("Movie Number " + txtMovNum.Text.Trim() + " already exists in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); txtMovNum.Clear(); txtMovNum.Focus(); return; } } if (txtMovTitle.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Movie Title.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovTitle.Focus(); return; } if (txtMovDesc.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Description.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovDesc.Focus(); return; } if (txtMovReleased.Text.Trim() == String.Empty) { MessageBox.Show("Please enter Year Released.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovReleased.Focus(); return; } if (txtMovGenre.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Genre ID #.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovGenre.Focus(); return; } else { Genre objGenre = GenreDB.GetGenre(Convert.ToInt16(txtMovGenre.Text.Trim())); if (objGenre == null) { MessageBox.Show("Genre ID # " + txtMovGenre.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); txtMovGenre.Clear(); txtMovGenre.Focus(); return; } } if (txtMovRating.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Movie Rating.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovRating.Focus(); return; } if (txtMovFormat.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Format.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovFormat.Focus(); return; } if (txtMovRetail.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Retail Cost.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovRetail.Focus(); return; } if (txtMovRental.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Rental Cost.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovRental.Focus(); return; } if (txtMovCopies.Text.Trim() == String.Empty) { MessageBox.Show("Please enter the # Copies On_Hand.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovCopies.Focus(); return; } if (txtMovImage.Text.Trim() == String.Empty) { MessageBox.Show("Please enter an Image.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovImage.Focus(); return; } if (txtMovTrailer.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Trailer.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovTrailer.Focus(); return; } if (txtMovVendor.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Vendor ID.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtMovVendor.Focus(); return; } else { Vendor objVendor = VendorDB.GetVendor(Convert.ToInt16(txtMovVendor.Text.Trim())); if (objVendor == null) { MessageBox.Show("Vendor ID # " + txtMovVendor.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); txtMovVendor.Clear(); txtMovVendor.Focus(); return; } } Movie objMovie = new Movie(); objMovie.MovieNumber = txtMovNum.Text.Trim(); objMovie.MovieTitle = txtMovTitle.Text.Trim(); objMovie.Description = txtMovDesc.Text.Trim(); objMovie.YearReleased = txtMovReleased.Text.Trim(); objMovie.GenreId = txtMovGenre.Text.Trim(); objMovie.Rating = txtMovRating.Text.Trim(); objMovie.Format = txtMovFormat.Text.Trim(); objMovie.RetailCost = txtMovRetail.Text.Trim(); objMovie.RentalCost = txtMovRental.Text.Trim(); objMovie.OnHand = txtMovCopies.Text.Trim(); objMovie.Image = txtMovImage.Text.Trim(); objMovie.Trailer = txtMovTrailer.Text.Trim(); objMovie.VendorId = txtMovVendor.Text.Trim(); try { bool status = MovieDB.AddMovie(objMovie); if (status) //You can use this syntax as well..if (status ==true) { MessageBox.Show("Movie added to the database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); ClearMovieFields(); MovieListLoad(); } else { MessageBox.Show("Movie was not added to the database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public static List<Movie> GetMemberMovie() { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; List<Movie> memberMovieList = new List<Movie>(); string movieSQL = "SELECT movie_number, movie_title, description, movie_year_made, genre_id," + " movie_rating, tape_rental_cost FROM Movie"; SqlCommand objMovCommand = null; SqlConnection objMovConn = null; SqlDataReader movReader = null; try { using (objMovConn = new SqlConnection(connectionString)) { //Opens the connection to the database objMovConn.Open(); //Command object created with the SQL statement using (objMovCommand = new SqlCommand(movieSQL, objMovConn)) { //SQL executes and returns a DataReader using ((movReader = objMovCommand.ExecuteReader(CommandBehavior.CloseConnection))) { while (movReader.Read()) { Movie objMovie = new Movie(); objMovie.MovieNumber = movReader["movie_number"].ToString(); objMovie.MovieTitle = movReader["movie_title"].ToString(); objMovie.Description = movReader["description"].ToString(); objMovie.YearReleased = movReader["movie_year_made"].ToString(); objMovie.GenreId = movReader["genre_id"].ToString(); objMovie.Rating = movReader["movie_rating"].ToString(); objMovie.RentalCost = movReader["tape_rental_cost"].ToString(); //Add Movie to collection memberMovieList.Add(objMovie); } } } return memberMovieList; } } catch (SqlException ex) { throw ex; } finally { if (objMovConn != null) { objMovConn.Close(); } } }
public static bool DeleteMovie(Movie objMovie) { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; int rowsAffected = 0; SqlConnection objMovConn = null; SqlCommand objMovCommand = null; string movieSQL; try { using (objMovConn = new SqlConnection(connectionString)) { //Open the connection to the datbase objMovConn.Open(); movieSQL = "DELETE Movie WHERE movie_number = @movie_number"; //Create a command object with the SQL statement using (objMovCommand = new SqlCommand(movieSQL, objMovConn)) { objMovCommand.Parameters.AddWithValue("@movie_number", objMovie.MovieNumber); //Execute the SQL and return the number of rows affected rowsAffected = objMovCommand.ExecuteNonQuery(); //Close the database connection objMovConn.Close(); if (rowsAffected > 0) { return true; } else { return false; } } } } catch (SqlException ex) { throw ex; } finally { if (objMovConn != null) { objMovConn.Close(); } } }
public static bool UpdateMovie(Movie objMovie) { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; int rowsAffected = 0; SqlCommand objMovCommand = null; SqlConnection objMovConn = null; string movieSQL; try { using (objMovConn = new SqlConnection(connectionString)) { //Open the connection to the datbase objMovConn.Open(); movieSQL = "UPDATE Movie " + Environment.NewLine + " SET movie_number = @movie_number, " + Environment.NewLine + " movie_title = @movie_title, " + Environment.NewLine + " description = @description, " + Environment.NewLine + " movie_year_made = @movie_year_made, " + Environment.NewLine + " genre_id = @genre_id, " + Environment.NewLine + " movie_rating = @movie_rating, " + Environment.NewLine + " media_type = @media_type, " + Environment.NewLine + " movie_retail_cost = @movie_retail_cost, " + Environment.NewLine + " tape_rental_cost = @tape_rental_cost, " + Environment.NewLine + " copies_on_hand = @copies_on_hand, " + Environment.NewLine + " image = @image, " + Environment.NewLine + " trailer = @trailer, " + Environment.NewLine + " vendor_id = @vendor_id " + Environment.NewLine + " WHERE movie_number = @movie_number "; //Create a command object with the SQL statement using (objMovCommand = new SqlCommand(movieSQL, objMovConn)) { //Use the command parameters method to set the paramater values of the SQL Insert statement objMovCommand.Parameters.AddWithValue("@movie_number", Convert.ToInt16(objMovie.MovieNumber)); objMovCommand.Parameters.AddWithValue("@movie_title", objMovie.MovieTitle); objMovCommand.Parameters.AddWithValue("@description", objMovie.Description); objMovCommand.Parameters.AddWithValue("@movie_year_made", Convert.ToInt16(objMovie.YearReleased)); objMovCommand.Parameters.AddWithValue("@genre_id", Convert.ToInt16(objMovie.GenreId)); objMovCommand.Parameters.AddWithValue("@movie_rating", objMovie.Rating); objMovCommand.Parameters.AddWithValue("@media_type", objMovie.Format); objMovCommand.Parameters.AddWithValue("@movie_retail_cost", Convert.ToDouble(objMovie.RetailCost)); objMovCommand.Parameters.AddWithValue("@tape_rental_cost", Convert.ToDouble(objMovie.RentalCost)); objMovCommand.Parameters.AddWithValue("@copies_on_hand", Convert.ToInt16(objMovie.OnHand)); objMovCommand.Parameters.AddWithValue("@image", objMovie.Image); objMovCommand.Parameters.AddWithValue("@trailer", objMovie.Trailer); objMovCommand.Parameters.AddWithValue("@vendor_id", objMovie.VendorId); //Execute the SQL and return the number of rows affected rowsAffected = objMovCommand.ExecuteNonQuery(); } if (rowsAffected > 0) { return true; } else { return false; } } } catch (SqlException ex) { throw ex; } finally { if (objMovConn != null) { objMovConn.Close(); } } }
public static Movie GetMovie(int movNumber) { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; string movieSQL = "SELECT movie_number, movie_title, description, movie_year_made, genre_id," + " movie_rating, media_type, movie_retail_cost, tape_rental_cost," + " copies_on_hand, image, trailer, vendor_id FROM Movie" + " WHERE movie_number = @movie_number"; SqlCommand objMovCommand = null; SqlConnection objMovConn = null; SqlDataReader movReader = null; Movie objMovie = null; try { using (objMovConn = new SqlConnection(connectionString)) { //Open the connection to the datbase objMovConn.Open(); //Create a command object with the SQL statement using (objMovCommand = new SqlCommand(movieSQL, objMovConn)) { //Set command parameter objMovCommand.Parameters.AddWithValue("@movie_number", movNumber); //Execute the SQL and return a DataReader using ((movReader = objMovCommand.ExecuteReader(CommandBehavior.CloseConnection))) { while (movReader.Read()) { objMovie = new Movie(); //Fill the customer object if found objMovie.MovieNumber = movReader["movie_number"].ToString(); objMovie.MovieTitle = movReader["movie_title"].ToString(); objMovie.Description = movReader["description"].ToString(); objMovie.YearReleased = movReader["movie_year_made"].ToString(); objMovie.GenreId = movReader["genre_id"].ToString(); objMovie.Rating = movReader["movie_rating"].ToString(); objMovie.Format = movReader["media_type"].ToString(); objMovie.RetailCost = movReader["movie_retail_cost"].ToString(); objMovie.RentalCost = movReader["tape_rental_cost"].ToString(); objMovie.OnHand = movReader["copies_on_hand"].ToString(); objMovie.Image = movReader["image"].ToString(); objMovie.Trailer = movReader["trailer"].ToString(); objMovie.VendorId = movReader["vendor_id"].ToString(); } } } return objMovie; } } catch (SqlException ex) { throw ex; } finally { if (objMovConn != null) { objMovConn.Close(); } } }
private void btnMRatedMovies_Click(object sender, EventArgs e) { txtGenreSearch.Clear(); string rsearch = ""; if (txtRatedSearch.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a movie rating to find.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtRatedSearch.Focus(); return; } else { rsearch = txtRatedSearch.Text.Trim(); } string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; List<Movie> ratedSearchList = new List<Movie>(); string movieSQL = "SELECT movie_number, movie_title, description, movie_year_made," + " genre_id, movie_rating, tape_rental_cost" + " FROM movie WHERE movie_rating = '" + rsearch + "'"; SqlCommand objMovCommand = null; SqlConnection objMovConn = null; SqlDataReader movReader = null; try { using (objMovConn = new SqlConnection(connectionString)) { //Opens the connection to the database objMovConn.Open(); //Command object created with the SQL statement using (objMovCommand = new SqlCommand(movieSQL, objMovConn)) { //SQL executes and returns a DataReader using ((movReader = objMovCommand.ExecuteReader(CommandBehavior.CloseConnection))) { while (movReader.Read()) { Movie objMovie = new Movie(); objMovie.MovieNumber = movReader["movie_number"].ToString(); objMovie.MovieTitle = movReader["movie_title"].ToString(); objMovie.Description = movReader["description"].ToString(); objMovie.YearReleased = movReader["movie_year_made"].ToString(); objMovie.GenreId = movReader["genre_id"].ToString(); objMovie.Rating = movReader["movie_rating"].ToString(); objMovie.RentalCost = movReader["tape_rental_cost"].ToString(); //Add Movie to collection ratedSearchList.Add(objMovie); } } } gridviewMemMovies.DataSource = ratedSearchList; //return memberMovieList; } } catch (SqlException ex) { throw ex; } finally { if (objMovConn != null) { objMovConn.Close(); } } }