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