private void btnReturn_Click(object sender, EventArgs e) { Rental returnMovie = new Rental(); Movie find = new Movie(); int selected = 0; try { selected = dgvRental.CurrentCell.RowIndex; if (current[selected].MediaReturnDate == "") { returnMovie = current[selected]; int findNumber = Convert.ToInt32(returnMovie.MovieNumber); find = MovieCRUD.FindMovie(findNumber); int copies = Convert.ToInt32(find.OnHand) + 1; Rental.ReturnMovie(activeMember, returnMovie, copies); } else { MessageBox.Show("You have aready returned this movie"); } } catch(Exception ex) { MessageBox.Show(ex.Message); Rental.UpdateOnHand(find); current = Rental.GetInfo(activeMember); dgvRental.DataSource = current; } }
public static List<Rental> GetInfo(Member activeMember) { //connection string string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;Connect Timeout=30";; //command object SqlCommand objCommand; //sql string command string command = "select m.MovieTitle, r.MediaCheckOutDate, r.MediaExpReturnDate, r.MediaReturnDate, m.RentalCost, r.MovieNumber from RENTAL as r INNER JOIN MOVIE as m on r.MovieNumber=m.MovieNumber and r.MemberNumber=@MemberID"; //sql reader SqlDataReader rentalReader = null; //return member List<Rental> lsRental = new List<Rental>(); using (SqlConnection objConn = new SqlConnection(connectionString)) { //open sesame objConn.Open(); using (objCommand = new SqlCommand(command, objConn)) { objCommand.Parameters.AddWithValue("@MemberID", activeMember.MemberNumber); using (rentalReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)) { while (rentalReader.Read()) { Rental objRental = new Rental(); objRental.MovieTitle = rentalReader["MovieTitle"].ToString(); objRental.MediaCheckOut = rentalReader["MediaCheckOutDate"].ToString(); objRental.MediaExpReturnDate = rentalReader["MediaExpReturnDate"].ToString(); objRental.MediaReturnDate = rentalReader["MediaReturnDate"].ToString(); objRental.MediaRentalCost = rentalReader["RentalCost"].ToString(); objRental.MovieNumber = rentalReader["MovieNumber"].ToString(); lsRental.Add(objRental); } return lsRental; } throw new Exception("No rental found"); } } }
public static void ReturnMovie(Member activeMember, Rental theReturned, int onHand) { string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;Connect Timeout=30";; SqlCommand objCommand; string command = "UPDATE RENTAL SET MediaReturnDate=@MediaReturnDate where MemberNumber=@MemberNumber AND MovieNumber=@MovieNumber; UPDATE MOVIE SET CopiesOnHand=@onHand where MovieNumber=@MovieNumber;" ; int rowsAffected; using(SqlConnection objConn = new SqlConnection(connectionString)) { objConn.Open(); using(objCommand = new SqlCommand(command, objConn)) { DateTime current = DateTime.Now; string date = current.ToString("MM-dd-yyyy"); objCommand.Parameters.AddWithValue("MediaReturnDate", current); objCommand.Parameters.AddWithValue("@onHand", onHand); objCommand.Parameters.AddWithValue("@MemberNumber", activeMember.MemberNumber); objCommand.Parameters.AddWithValue("@MovieNumber", theReturned.MovieNumber); rowsAffected = objCommand.ExecuteNonQuery(); if (rowsAffected < 1) { throw new Exception("Error"); } else { throw new Exception("Success"); } } } }