private void btnSubmit_Click(object sender, EventArgs e)
{
Member addMember = new Member();
if (TestInput())
{
if (txtPassword.Text.Trim() == txtConfirm.Text.Trim())
{
addMember.Username = txtUsername.Text.Trim();
addMember.FirstName = txtFirstName.Text.Trim();
addMember.LastName = txtLastName.Text.Trim();
addMember.Address = txtAddress.Text.Trim();
addMember.City = txtCity.Text.Trim();
addMember.State = txtState.Text.Trim();
addMember.Zipcode = txtZipcode.Text.Trim();
addMember.Phone = txtPhone.Text.Trim();
addMember.Password = txtPassword.Text.Trim();
try
{
MemberCRUD.AddToMember(addMember);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
this.Close();
}
}
else
{
MessageBox.Show("Please Enter Matching Passwords");
}
}
}
public static void AddRental(Movie rent, Member renter)
{
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;Connect Timeout=30";
SqlCommand objCommand;
int rowsAffected;
string sqlCommand = "INSERT into RENTAL values (@MovieNumber, @MemberNumber, @MediaCheckOutDate, @MediaExpReturnDate, @MediaReturnDate, @RentalCost)";
using (SqlConnection objConn = new SqlConnection(connectionString))
{
objConn.Open();
using (objCommand = new SqlCommand(sqlCommand, objConn))
{
DateTime current = DateTime.Now;
string turnIn = current.AddDays(5.0).ToString("MM-dd-yyyy");
string checkout = current.ToString("MM-dd-yyyy");
objCommand.Parameters.AddWithValue("@MovieNumber", rent.MovieNumber);
objCommand.Parameters.AddWithValue("@MemberNumber", renter.MemberNumber);
objCommand.Parameters.AddWithValue("@MediaCheckOutDate", checkout);
objCommand.Parameters.AddWithValue("@MediaReturnDate", Convert.DBNull);
objCommand.Parameters.AddWithValue("@MediaExpReturnDate", turnIn);
objCommand.Parameters.AddWithValue("@RentalCost", rent.RentalCost);
rowsAffected = objCommand.ExecuteNonQuery();
if (rowsAffected < 1)
{
throw new Exception("Error");
}
else
{
throw new Exception("You will recieve your movie tomorrow!");
}
}
}
}
private void MoviePage_Load(object sender, EventArgs e)
{
activeMember = (Member)this.Tag;
currentMovie = (Movie)LoadMovie;
asfMovie.Movie = currentMovie.Trailer;
lblTitle.Text = currentMovie.MovieTitle;
lblDate.Text = currentMovie.YearMade;
rtbDescription.Text = currentMovie.Description;
lblRating.Text = currentMovie.MovieRating;
lblGenre.Text = currentMovie.GenreId;
lblOnHand.Text = currentMovie.OnHand;
}
public static List<Member>GetMemberList()
{
// Connect
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;Connect Timeout=30";
//Command object
SqlCommand objCommand;
string sqlCommand = "SELECT Username, MemberID, JoinDate, FirstName, LastName, Address, City, State, Zipcode, Phone, MemberStatus FROM MEMBER";
List<Member> currentMembers = new List<Member>();
//instantiate new connection
SqlDataReader memberReader = null;
try
{
using(SqlConnection objConn = new SqlConnection(connectionString))
{
//open connection
objConn.Open();
//pass in sqlcommand
using(objCommand = new SqlCommand(sqlCommand, objConn))
{
//execute sql
using(memberReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
while(memberReader.Read())
{
Member objMember = new Member();
objMember.MemberNumber = memberReader["MemberID"].ToString();
objMember.LastName = memberReader["LastName"].ToString();
objMember.FirstName = memberReader["FirstName"].ToString();
objMember.Address = memberReader["Address"].ToString();
objMember.City = memberReader["Address"].ToString();
objMember.State = memberReader["State"].ToString();
objMember.Zipcode = memberReader["Zipcode"].ToString();
objMember.Phone = memberReader["Phone"].ToString();
objMember.Joindate = (DateTime)memberReader["JoinDate"];
objMember.Username = memberReader["Username"].ToString();
//add to list
currentMembers.Add(objMember);
}
}
}
objConn.Close();
return currentMembers;
}
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
}
public static bool AddToMember(Member newMember)
{
//Connect too SQL DATABASE
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;Connect Timeout=30";;
//Create a command object
SqlCommand objCommand;
int rowsAffected;
//command to execute on database
string sqlCommand = "INSERT into MEMBER values (@Username, @JoinDate, @FirstName, @LastName, @Address, @City, @State, @Zipcode, @Phone, @MemberStatus, @Password)";
using (SqlConnection objConn = new SqlConnection(connectionString))
{
//open connection
objConn.Open();
//create command object
using (objCommand = new SqlCommand(sqlCommand, objConn))
{
DateTime currentTime = DateTime.Now;
objCommand.Parameters.AddWithValue("@Username", newMember.Username);
objCommand.Parameters.AddWithValue("@JoinDate", currentTime);
objCommand.Parameters.AddWithValue("@FirstName", newMember.FirstName);
objCommand.Parameters.AddWithValue("@LastName", newMember.LastName);
objCommand.Parameters.AddWithValue("@Address", newMember.Address);
objCommand.Parameters.AddWithValue("@City", newMember.City);
objCommand.Parameters.AddWithValue("@State", newMember.State);
objCommand.Parameters.AddWithValue("@Zipcode", newMember.Zipcode);
objCommand.Parameters.AddWithValue("@Phone", newMember.Phone);
objCommand.Parameters.AddWithValue("@MemberStatus", "a");
objCommand.Parameters.AddWithValue("@Password", newMember.Password);
//test how many rows affected
rowsAffected = objCommand.ExecuteNonQuery();
if(rowsAffected < 1)
{
throw new Exception("Incorrect");
}
else
{
throw new Exception("Successfully Added");
}
}
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
//create a new member login
Member login = new Member();
Member activeMember = new Member();
login.Username = txtUsername.Text.Trim();
login.Password = txtPassword.Text.Trim();
// boolean to see if the the getlogin function is true
int loginTest = 0;
try
{
if (IsInput(txtUsername) && IsInput(txtPassword))
{
loginTest = MemberCRUD.GetLogin(login);
}
if(loginTest > 0)
{
activeMember = MemberCRUD.FindMember(loginTest);
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (loginTest > 0)
{
this.Hide();
Form1 newForm = new Form1();
newForm.Tag = activeMember;
newForm.ShowDialog();
}
else
{
MessageBox.Show("Invalid Username or Password");
txtPassword.Focus();
}
}
}
private void MyAccount_Load(object sender, EventArgs e)
{
activeMember = (Member)this.Tag;
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");
}
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
activeMember = (Member)this.Tag;
lblName.Text = "Welcome " + activeMember.Username;
LoadMovies();
}
public static void ValidateUsername(Member validate)
{
List<Member> checkList = new List<Member>();
checkList = GetMemberList();
foreach(Member m in checkList)
{
if(validate.Username == m.Username)
{
throw new Exception("Username in use");
}
}
}
public static bool UpdateMember(Member updateMember)
{
//Connect too SQL DATABASE
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;Connect Timeout=30";;
//Create a command object
SqlCommand objCommand;
int rowsAffected;
//command to execute on database
string sqlCommand = "UPDATE MEMBER SET Username=@Username, FirstName=@FirstName, LastName=@LastName, Address=@Address, City=@City, State=@State, Zipcode=@Zipcode, Phone=@Phone, Password=@Password WHERE MemberID=@MemberID";
using (SqlConnection objConn = new SqlConnection(connectionString))
{
//open connection
objConn.Open();
//create command object
using (objCommand = new SqlCommand(sqlCommand, objConn))
{
DateTime currentTime = DateTime.Now;
objCommand.Parameters.AddWithValue("@Username", updateMember.Username);
objCommand.Parameters.AddWithValue("@FirstName", updateMember.FirstName);
objCommand.Parameters.AddWithValue("@LastName", updateMember.LastName);
objCommand.Parameters.AddWithValue("@Address", updateMember.Address);
objCommand.Parameters.AddWithValue("@City", updateMember.City);
objCommand.Parameters.AddWithValue("@State", updateMember.State);
objCommand.Parameters.AddWithValue("@Zipcode", updateMember.Zipcode);
objCommand.Parameters.AddWithValue("@Phone", updateMember.Phone);
objCommand.Parameters.AddWithValue("@Password", updateMember.Password);
objCommand.Parameters.AddWithValue("@MemberID", updateMember.MemberNumber);
//test how many rows affected
rowsAffected = objCommand.ExecuteNonQuery();
if (rowsAffected < 1)
{
throw new Exception("Incorrect");
}
else
{
throw new Exception("Successfully Updated");
}
}
}
}
public static int GetLogin(Member login)
{
// connection string
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;Connect Timeout=30";;
// command object
SqlCommand objCommand;
// create a reader
SqlDataReader memberReader;
List<string> Usernames = new List<string>();
string sqlCommand = "SELECT Username, Password, MemberID FROM MEMBER WHERE Username=@Username";
int memberNumber = 0;
using (SqlConnection objConn = new SqlConnection(connectionString))
{
//open connection
objConn.Open();
//create command
using (objCommand = new SqlCommand(sqlCommand, objConn))
{
// assign Username
objCommand.Parameters.AddWithValue("@Username", login.Username);
//
using ((memberReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)))
{
string Password = "";
string sNumber = "";
//loop through logins
while(memberReader.Read())
{
Password = memberReader["Password"].ToString();
sNumber = memberReader["MemberID"].ToString();
}
memberNumber = Convert.ToInt32(sNumber);
if (Password == login.Password)
{
return memberNumber;
}
else
{
return 0;
}
}
}
}
}
public static Member FindMember(int memberNumberFind)
{
// 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 MemberID, Username, FirstName, LastName, Address, City, State, Zipcode, Phone, Password FROM MEMBER where MemberID=@MemberID";
//sql reader
SqlDataReader memberReader = null;
//return member
Member objMember = new Member();
int membernumber = 0;
using(SqlConnection objConn = new SqlConnection(connectionString))
{
//open sesame
objConn.Open();
using(objCommand = new SqlCommand(command, objConn))
{
objCommand.Parameters.AddWithValue("@MemberID", memberNumberFind);
using (memberReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
while(memberReader.Read())
{
membernumber = Convert.ToInt32(memberReader["MemberID"]);
if(membernumber == memberNumberFind)
{
objMember.MemberNumber = memberReader["MemberID"].ToString();
objMember.LastName = memberReader["LastName"].ToString();
objMember.FirstName = memberReader["FirstName"].ToString();
objMember.Address = memberReader["Address"].ToString();
objMember.City = memberReader["City"].ToString();
objMember.State = memberReader["State"].ToString();
objMember.Zipcode = memberReader["Zipcode"].ToString();
objMember.Phone = memberReader["Phone"].ToString();
objMember.Username = memberReader["Username"].ToString();
objMember.Password = memberReader["Password"].ToString();
//
return objMember;
}
}
throw new Exception("No member found");
}
}
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
Member updateMember = new Member();
if (TestMemberInput())
{
updateMember.MemberNumber = txtMemberNumber.Text.Trim();
updateMember.Username = txtUsername.Text.Trim();
updateMember.FirstName = txtFirstName.Text.Trim();
updateMember.LastName = txtLastName.Text.Trim();
updateMember.Address = txtAddress.Text.Trim();
updateMember.City = txtCity.Text.Trim();
updateMember.State = txtState.Text.Trim();
updateMember.Zipcode = txtZipcode.Text.Trim();
updateMember.Phone = txtPhone.Text.Trim();
updateMember.Password = txtPassword.Text.Trim();
try
{
MemberCRUD.ValidateUsername(updateMember);
MemberCRUD.UpdateMember(updateMember);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void btnFind_Click(object sender, EventArgs e)
{
string testFind = txtMemberNumber.Text.Trim();
int memberNumberFind = 0;
bool change = Int32.TryParse(testFind, out memberNumberFind);
Member editMember = new Member();
try
{
if (change)
{
editMember = MemberCRUD.FindMember(memberNumberFind);
txtUsername.Text = editMember.Username;
txtFirstName.Text = editMember.FirstName;
txtLastName.Text = editMember.LastName;
txtAddress.Text = editMember.Address;
txtCity.Text = editMember.City;
txtState.Text = editMember.State;
txtZipcode.Text = editMember.Zipcode;
txtPhone.Text = editMember.Phone;
txtPassword.Text = editMember.Password;
}
else
{
MessageBox.Show("Enter a valid number");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
