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