/// <summary> /// Adds one item to the database. /// </summary> /// <param name="item">The item.</param> /// <exception cref="ArgumentNullException"></exception> public string AddOne(Member item) { if (item == null) { throw new ArgumentNullException(); } string id = string.Empty; const string statement = "INSERT INTO Customer (fname, lname, minit, phone, Address_id)" + " VALUES (@Fname, @Lname, @Minit, @Phone, @Address)"; var connection = new MySqlConnection(this.CONNECTION_STRING); using (var command = new MySqlCommand(statement)) { command.Parameters.AddWithValue("@Fname", item.Fname); command.Parameters.AddWithValue("@Lname", item.Lname); command.Parameters.AddWithValue("@Minit", item.Minit); command.Parameters.AddWithValue("@Phone", item.PhoneNumber); command.Parameters.AddWithValue("@Address", item.MemberAddress.Id); command.Connection = connection; try { command.Connection.Open(); command.ExecuteNonQuery(); id = command.LastInsertedId.ToString(); } finally { command.Connection.Close(); } } return id; }
private void saveCustomerButton_Click(object sender, EventArgs e) { if (this.fnameTextBox.Text == string.Empty || this.lNameTextBox.Text == string.Empty || this.phoneTextBox.Text == string.Empty || this.addressTextBox.Text == string.Empty) { ErrorHandler.DisplayErrorBox("Invalid Data", "Please enter member information into every textbox."); return; } if (this.phoneTextBox.Text.Length != 10) { ErrorHandler.DisplayErrorBox("Phone Number Invalid", "Please enter a valid 10 digit phone number."); return; } try { long.Parse(this.phoneTextBox.Text); } catch (Exception) { ErrorHandler.DisplayErrorBox("Phone Number Invalid", "Please enter a valid 10 digit phone number."); return; } var theMember = new Member { Fname = this.fnameTextBox.Text, Minit = this.minitTextBox.Text, Lname = this.lNameTextBox.Text, PhoneNumber = this.phoneTextBox.Text, MemberAddress = (new AddressRepository()).GetById(this.addressTextBox.Text) }; try { this.controller.AddMember(theMember); this.loadMembers(); this.InternalState = CustomerStates.Main; } catch (Exception exception) { ErrorHandler.DisplayErrorMessageToUserAndLog("Error", "Failed to add member to database. Please try again.", exception); } }
/// <summary> /// Adds the member to the database. /// </summary> /// <param name="aMember">a member.</param> public void AddMember(Member aMember) { this.memberRepository.AddOne(aMember); }
/// <summary> /// Updates the item by identifier. /// </summary> /// <param name="item">The item.</param> public void UpdateById(Member item) { throw new NotImplementedException(); }
/// <summary> /// Deletes the specified item from the database. /// </summary> /// <param name="item">The item.</param> /// <exception cref="NotImplementedException"></exception> public void Delete(Member item) { throw new NotImplementedException(); }
/// <summary> /// Searches the database by identifier or phone. /// </summary> /// <param name="id">The identifier.</param> /// <returns></returns> public IList<Member> SearchByIdOrPhone(string id) { var members = new List<Member>(); const string query = "SELECT * FROM Customer, Address WHERE (Customer.id LIKE @id or Customer.phone LIKE @id) AND Customer.Address_id=Address.id"; var connection = new MySqlConnection(this.CONNECTION_STRING); using (var command = new MySqlCommand(query)) { command.Connection = connection; command.Parameters.AddWithValue("@id", id + "%"); try { command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { var theMember = new Member(); theMember.Id = (int)reader["id"]; theMember.Fname = reader["fname"] == DBNull.Value ? string.Empty : (string)reader["fname"]; theMember.Minit = reader["minit"] == DBNull.Value ? string.Empty : (string)reader["minit"]; theMember.Lname = reader["lname"] == DBNull.Value ? string.Empty : (string)reader["lname"]; theMember.PhoneNumber = reader["phone"] == DBNull.Value ? string.Empty : (string)reader["phone"]; this.loadAddress(theMember, reader); members.Add(theMember); } } finally { command.Connection.Close(); } } return members; }
private void loadAddress(Member theMember, MySqlDataReader reader) { theMember.MemberAddress.Id = reader["Address_id"] == DBNull.Value ? string.Empty : reader["Address_id"].ToString(); theMember.MemberAddress.Street1 = reader["street1"] == DBNull.Value ? string.Empty : reader["street1"].ToString(); theMember.MemberAddress.Street2 = reader["street2"] == DBNull.Value ? string.Empty : reader["street2"].ToString(); theMember.MemberAddress.City = reader["city"] == DBNull.Value ? string.Empty : reader["city"].ToString(); theMember.MemberAddress.State = reader["state"] == DBNull.Value ? string.Empty : reader["state"].ToString(); theMember.MemberAddress.Zip = reader["zip"] == DBNull.Value ? string.Empty : reader["zip"].ToString(); }
/// <summary> /// Gets all the items in the database. /// </summary> /// <returns></returns> public IList<Member> GetAll() { var members = new List<Member>(); const string query = "SELECT * FROM Customer, Address WHERE Customer.Address_id=Address.id"; var connection = new MySqlConnection(this.CONNECTION_STRING); using (var command = new MySqlCommand(query)) { command.Connection = connection; try { command.Connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { var theMember = new Member { Id = (int) reader["id"], Fname = reader["fname"] == DBNull.Value ? string.Empty : (string) reader["fname"], Minit = reader["minit"] == DBNull.Value ? string.Empty : (string) reader["minit"], Lname = reader["lname"] == DBNull.Value ? string.Empty : (string) reader["lname"], PhoneNumber = reader["phone"] == DBNull.Value ? string.Empty : (string) reader["phone"] }; this.loadAddress(theMember, reader); members.Add(theMember); } } finally { command.Connection.Close(); } } return members; }