/// <summary>
/// Gets all the items in the database.
/// </summary>
/// <returns></returns>
public IList<Employee> GetAll()
{
var employees = new List<Employee>();
const string query = "SELECT Employee.fname, Employee.lname, Employee.ssn, Employee.phone, Employee.IsAdmin, Employee.id, Employee.Address_id, " +
"Address.* " +
"FROM Employee, Address " +
"WHERE Employee.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 employee = new Employee();
employee.Id = ((int) reader["id"]).ToString();
employee.FirstName = reader["fname"] == DBNull.Value ? string.Empty : (string) reader["fname"];
employee.LastName = reader["lname"] == DBNull.Value ? string.Empty : (string) reader["lname"];
employee.PhoneNumber = reader["phone"] == DBNull.Value ? string.Empty : (string) reader["phone"];
this.loadAddress(employee, reader);
employee.SSN = reader["ssn"] == DBNull.Value ? string.Empty : (string) reader["ssn"];
employee.IsAdmin = reader["IsAdmin"] == DBNull.Value ? false : (bool) reader["IsAdmin"];
employees.Add(employee);
}
}
finally
{
command.Connection.Close();
}
}
return employees;
}
private void loadAddress(Employee employee, MySqlDataReader reader)
{
employee.EmployeeAddress.Id = reader["Address_id"] == DBNull.Value
? string.Empty
: reader["Address_id"].ToString();
employee.EmployeeAddress.Street1 = reader["street1"] == DBNull.Value
? string.Empty
: reader["street1"].ToString();
employee.EmployeeAddress.Street2 = reader["street2"] == DBNull.Value
? string.Empty
: reader["street2"].ToString();
employee.EmployeeAddress.City = reader["city"] == DBNull.Value
? string.Empty
: reader["city"].ToString();
employee.EmployeeAddress.State = reader["state"] == DBNull.Value
? string.Empty
: reader["state"].ToString();
employee.EmployeeAddress.Zip = reader["zip"] == DBNull.Value
? string.Empty
: reader["zip"].ToString();
}
/// <summary>
/// Adds one item to the database.
/// </summary>
/// <param name="item">The item.</param>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
public string AddOne(Employee item)
{
throw new NotImplementedException();
}
/// <summary>
/// Adds the one.
/// </summary>
/// <param name="employee">The employee.</param>
/// <param name="loginSession">The login session.</param>
/// <exception cref="ArgumentNullException"></exception>
public void AddOne(Employee employee, LoginSession loginSession)
{
if (employee == null || loginSession == null || loginSession.Password == null)
{
throw new ArgumentNullException();
}
const string statement = "INSERT INTO Employee (fname, lname, phone, Address_id, IsAdmin, ssn, password)" +
" VALUES (@Fname, @Lname, @Phone, @Address, @Admin, @Ssn, @Password)";
var connection = new MySqlConnection(this.CONNECTION_STRING);
using (var command = new MySqlCommand(statement))
{
command.Parameters.AddWithValue("@Fname", employee.FirstName);
command.Parameters.AddWithValue("@Lname", employee.LastName);
command.Parameters.AddWithValue("@Phone", employee.PhoneNumber);
command.Parameters.AddWithValue("@Address", employee.EmployeeAddress.Id);
command.Parameters.AddWithValue("@Admin", employee.IsAdmin);
command.Parameters.AddWithValue("@Ssn", employee.SSN);
command.Parameters.AddWithValue("@Password", loginSession.Password);
command.Connection = connection;
try
{
command.Connection.Open();
command.ExecuteNonQuery();
}
finally
{
command.Connection.Close();
}
}
}
/// <summary>
/// Updates the item by identifier.
/// </summary>
/// <param name="item">The item.</param>
public void UpdateById(Employee item)
{
throw new NotImplementedException();
}
public void Delete(Employee item)
{
throw new NotImplementedException();
}
private void saveButton_Click(object sender, EventArgs e)
{
if (this.addressTextBox.Text == string.Empty || this.firstNameTextBox.Text == string.Empty ||
this.lastNameTextBox.Text == string.Empty || this.phoneTextBox.Text == string.Empty ||
this.ssnTextBox.Text == string.Empty || this.passwordTextBox.Text == string.Empty)
{
MessageBox.Show(@"Please enter a value for every field.");
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;
}
if (this.ssnTextBox.Text.Length != 9)
{
ErrorHandler.DisplayErrorBox("SSN Invalid", "Please enter a valid 9 digit SSN.");
return;
}
try
{
uint.Parse(this.ssnTextBox.Text);
}
catch (Exception)
{
ErrorHandler.DisplayErrorBox("SSN Invalid", "Please enter a valid 9 digit SSN.");
return;
}
try
{
Employee anEmployee = new Employee
{
FirstName = this.firstNameTextBox.Text,
LastName = this.lastNameTextBox.Text,
PhoneNumber = this.phoneTextBox.Text,
SSN = this.ssnTextBox.Text,
IsAdmin = this.adminCheckBox.Checked,
EmployeeAddress = {Id = this.addressTextBox.Text}
};
this.theController.AddEmployee(anEmployee, this.passwordTextBox.Text);
this.loadEmployees();
this.InternalState = EmployeeStates.Main;
}
catch (Exception exception)
{
ErrorHandler.DisplayErrorMessageToUserAndLog("Error",
"Failed to add employee to database. Please try again.", exception);
}
}
/// <summary>
/// Adds the employee to the database.
/// </summary>
/// <param name="employee">The employee.</param>
/// <param name="password">The password.</param>
public void AddEmployee(Employee employee, string password)
{
this.employeeRepository.AddOne(employee, new LoginSession(0, password));
}
