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