public static List<Rental> GetRental() { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; List<Rental> rentalList = new List<Rental>(); string rentalSQL = "SELECT movie_number, member_number, media_checkout_date," + " media_return_date, media_exp_return_date, media_rental_cost FROM Rental"; SqlCommand objRCommand = null; SqlConnection objRConn = null; SqlDataReader rentReader = null; try { using (objRConn = new SqlConnection(connectionString)) { //Opens the connection to the database objRConn.Open(); //Command object created with the SQL statement using (objRCommand = new SqlCommand(rentalSQL, objRConn)) { //SQL executes and returns a DataReader using ((rentReader = objRCommand.ExecuteReader(CommandBehavior.CloseConnection))) { while (rentReader.Read()) { Rental objRent = new Rental(); objRent.MovieNumber = rentReader["movie_number"].ToString(); objRent.MemberNumber = rentReader["member_number"].ToString(); objRent.Checkedout = rentReader["media_checkout_date"].ToString(); objRent.Returned = rentReader["media_return_date"].ToString(); objRent.ExpectedReturn = rentReader["media_exp_return_date"].ToString(); objRent.RentalCost = rentReader["media_rental_cost"].ToString(); //Add Rental to collection rentalList.Add(objRent); } } } return rentalList; } } catch (SqlException ex) { throw ex; } finally { if (objRConn != null) { objRConn.Close(); } } }
private void btnRentAdd_Click(object sender, EventArgs e) { if (txtRentMovNum.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Movie Number.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtRentMovNum.Focus(); return; } else { Movie objMovie = MovieDB.GetMovie(Convert.ToInt16(txtRentMovNum.Text.Trim())); if (objMovie != null) { txtRentCost.Text = objMovie.RentalCost; } else { MessageBox.Show("Movie Number " + txtRentMovNum.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); txtRentMovNum.Clear(); txtRentMovNum.Focus(); return; } } if (txtRentMemNum.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Member Number.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtRentMemNum.Focus(); return; } else { Member objMember = MemberDB.GetMember(Convert.ToInt16(txtRentMemNum.Text.Trim())); if (objMember == null) { MessageBox.Show("Member Number " + txtRentMemNum.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); txtRentMemNum.Clear(); txtRentMemNum.Focus(); return; } } if (txtCheckout.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Date Checked Out.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtCheckout.Focus(); return; } if (txtExpReturn.Text.Trim() == String.Empty) { MessageBox.Show("Please enter an Exp. Return Date.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtExpReturn.Focus(); return; } if (txtReturn.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Date Returned.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtReturn.Focus(); return; } Rental objRental = new Rental(); objRental.MovieNumber = txtRentMovNum.Text.Trim(); objRental.MemberNumber = txtRentMemNum.Text.Trim(); objRental.Checkedout = txtCheckout.Text.Trim(); objRental.Returned = txtReturn.Text.Trim(); objRental.ExpectedReturn = txtExpReturn.Text.Trim(); objRental.RentalCost = txtRentCost.Text.Trim(); try { bool status = RentalDB.AddRental(objRental); if (status) //You can use this syntax as well..if (status ==true) { MessageBox.Show("Rental added to the database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); ClearRentalFields(); RentalListLoad(); } else { MessageBox.Show("Rental was not added to the database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void btnRentDelete_Click(object sender, EventArgs e) { if (txtRentMovNum.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Movie Number.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtRentMovNum.Focus(); return; } else { Movie objMovie = MovieDB.GetMovie(Convert.ToInt16(txtRentMovNum.Text.Trim())); if (objMovie == null) { MessageBox.Show("Movie Number " + txtRentMovNum.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); txtRentMovNum.Clear(); txtRentMovNum.Focus(); return; } } if (txtRentMemNum.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Member Number.", "", MessageBoxButtons.OK, MessageBoxIcon.Error); txtRentMemNum.Focus(); return; } else { Member objMember = MemberDB.GetMember(Convert.ToInt16(txtRentMemNum.Text.Trim())); if (objMember == null) { MessageBox.Show("Member Number " + txtRentMemNum.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); txtRentMemNum.Clear(); txtRentMemNum.Focus(); return; } } Rental objRental = new Rental(); objRental.MovieNumber = txtRentMovNum.Text.Trim(); objRental.MemberNumber = txtRentMemNum.Text.Trim(); try { bool status = RentalDB.DeleteRental(objRental); if (status) //You can use this syntax as well..if (status ==true) { MessageBox.Show("Rental deleted from the database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); ClearRentalFields(); RentalListLoad(); } else { MessageBox.Show("Rental was not deleted from the database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void btnDelete_Click(object sender, EventArgs e) { if (txtRentDelete.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Movie Number to delete.", "Rental Delete Error", MessageBoxButtons.OK, MessageBoxIcon.Error); txtRentalSave.Focus(); return; } Rental objRental = new Rental(); objRental.MovieNumber = txtRentDelete.Text.Trim(); objRental.MemberNumber = mNumber.ToString(); try { bool status = RentalDB.DeleteRental(objRental); if (status) //You can use this syntax as well..if (status ==true) { MessageBox.Show("Movie deleted from database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("Movie was not found on list to delete.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error); } txtRentDelete.Clear(); }
private void btnMSave_Click(object sender, EventArgs e) { GetDate(); string rCost = ""; if (txtRentalSave.Text.Trim() == String.Empty) { MessageBox.Show("Please enter a Movie Number to save.", "Rental Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error); txtRentalSave.Focus(); return; } string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; string rentalSQL = "SELECT tape_rental_cost FROM Movie WHERE movie_number = '" + txtRentalSave.Text.Trim() + "'"; SqlCommand objRentCommand = null; SqlConnection objRentConn = null; SqlDataReader rentReader = null; try { using (objRentConn = new SqlConnection(connectionString)) { //Opens the connection to the database objRentConn.Open(); //Command object created with the SQL statement using (objRentCommand = new SqlCommand(rentalSQL, objRentConn)) { //SQL executes and returns a DataReader using ((rentReader = objRentCommand.ExecuteReader(CommandBehavior.CloseConnection))) { while (rentReader.Read()) { rCost = rentReader["tape_rental_cost"].ToString(); } } } } if (objRentConn != null) { objRentConn.Close(); } Rental objRental = new Rental(); objRental.MovieNumber = txtRentalSave.Text.Trim(); objRental.MemberNumber = mNumber.ToString(); objRental.Checkedout = rentDate.ToString(); objRental.Returned = returnDate.ToString(); objRental.ExpectedReturn = returnDate.ToString(); objRental.RentalCost = rCost.ToString(); bool status = RentalDB.AddRental(objRental); if (status) //You can use this syntax as well..if (status ==true) { MessageBox.Show("Movie added to rental list.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("Movie was not added to rental list.", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (SqlException ex) { MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error); } txtRentalSave.Clear(); }
public static Rental GetRental(int memNumber, int movNumber) { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; string rentalSQL = "SELECT movie_number, member_number, media_checkout_date, media_return_date, " + " media_exp_return_date, media_rental_cost FROM Rental" + " WHERE member_number = @member_number AND movie_number = @movie_number"; SqlCommand objRCommand = null; SqlConnection objRConn = null; SqlDataReader rentReader = null; Rental objRental = null; try { using (objRConn = new SqlConnection(connectionString)) { //Open the connection to the datbase objRConn.Open(); //Create a command object with the SQL statement using (objRCommand = new SqlCommand(rentalSQL, objRConn)) { //Set command parameter objRCommand.Parameters.AddWithValue("@member_number", memNumber); objRCommand.Parameters.AddWithValue("@movie_number", movNumber); //Execute the SQL and return a DataReader using ((rentReader = objRCommand.ExecuteReader(CommandBehavior.CloseConnection))) { while (rentReader.Read()) { objRental = new Rental(); //Fill the customer object if found objRental.MovieNumber = rentReader["movie_number"].ToString(); objRental.MemberNumber = rentReader["member_number"].ToString(); objRental.Checkedout = rentReader["media_checkout_date"].ToString(); objRental.Returned = rentReader["media_return_date"].ToString(); objRental.ExpectedReturn = rentReader["media_exp_return_date"].ToString(); objRental.RentalCost = rentReader["media_rental_cost"].ToString(); } } } return objRental; } } catch (SqlException ex) { throw ex; } finally { if (objRConn != null) { objRConn.Close(); } } }
public static bool DeleteRental(Rental objRental) { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; int rowsAffected = 0; SqlConnection objConn = null; SqlCommand objCommand = null; string sqlString; try { using (objConn = new SqlConnection(connectionString)) { //Open the connection to the datbase objConn.Open(); sqlString = "DELETE Rental WHERE member_number = @member_number AND movie_number = @movie_number"; //Create a command object with the SQL statement using (objCommand = new SqlCommand(sqlString, objConn)) { //Use the command parameters method to set the paramater values of the SQL Insert statement objCommand.Parameters.AddWithValue("@member_number", objRental.MemberNumber); objCommand.Parameters.AddWithValue("@movie_number", objRental.MovieNumber); //Execute the SQL and return the number of rows affected rowsAffected = objCommand.ExecuteNonQuery(); //Close the database connection objConn.Close(); if (rowsAffected > 0) { return true; } else { return false; } } } } catch (SqlException ex) { throw ex; } finally { if (objConn != null) { objConn.Close(); } } }
public static bool UpdateRental(Rental objRental) { string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;"; int rowsAffected = 0; SqlCommand objRCommand = null; SqlConnection obRjConn = null; string rentalSQL; try { using (obRjConn = new SqlConnection(connectionString)) { //Open the connection to the datbase obRjConn.Open(); rentalSQL = "UPDATE rental " + Environment.NewLine + " SET movie_number = @movie_number, " + Environment.NewLine + " member_number = @member_number, " + Environment.NewLine + " media_checkout_date = @media_checkout_date, " + Environment.NewLine + " media_return_date = @media_return_date, " + Environment.NewLine + " media_exp_return_date = @media_exp_return_date, " + Environment.NewLine + " media_rental_cost = @media_rental_cost " + Environment.NewLine + " WHERE member_number = @member_number AND movie_number = @movie_number "; //Create a command object with the SQL statement using (objRCommand = new SqlCommand(rentalSQL, obRjConn)) { //Use the command parameters method to set the paramater values of the SQL Insert statement objRCommand.Parameters.AddWithValue("@movie_number", Convert.ToInt16(objRental.MovieNumber)); objRCommand.Parameters.AddWithValue("@member_number", Convert.ToInt16(objRental.MemberNumber)); objRCommand.Parameters.AddWithValue("@media_checkout_date", objRental.Checkedout); objRCommand.Parameters.AddWithValue("@media_return_date", objRental.Returned); objRCommand.Parameters.AddWithValue("@media_exp_return_date", objRental.ExpectedReturn); objRCommand.Parameters.AddWithValue("@media_rental_cost", Convert.ToDouble(objRental.RentalCost)); //Execute the SQL and return the number of rows affected rowsAffected = objRCommand.ExecuteNonQuery(); } if (rowsAffected > 0) { return true; } else { return false; } } } catch (SqlException ex) { throw ex; } finally { if (obRjConn != null) { obRjConn.Close(); } } }