public string Update(Employee anEmployee)
{
string message = "";
try
{
SqlConnectionObj.Open();
string query =
String.Format(
"UPDATE tbl_EmployeeInfo SET Name='{0}',Email='{1}',Address='{2}',DesignationId = {3} WHERE Id = {4}",
anEmployee.Name, anEmployee.Email, anEmployee.Address, anEmployee.Designation.Id,
anEmployee.Id);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
message = "Information has been updated";
}
catch(Exception ex)
{
throw new Exception("Error occurred during employee update operation", ex);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return message;
}
public string Delete(Employee selectedEmployee)
{
string message = "";
try
{
SqlConnectionObj.Open();
string query = String.Format("DELETE FROM tbl_EmployeeInfo WHERE Id={0}", selectedEmployee.Id);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
message = "Employee: " + selectedEmployee.Name + " has been deleted.";
}
catch(Exception exception)
{
throw new Exception("Error occurred during employee delete operation " + selectedEmployee.Name, exception);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return message;
}
public Employee FindEmployee(string email)
{
Employee anEmployee = null;
SqlConnectionObj.Open();
string query = String.Format("SELECT * FROM tbl_EmployeeInfo WHERE Email='{0}'", email);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader != null)
{
while (reader.Read())
{
anEmployee = new Employee();
anEmployee.Id = Convert.ToInt32(reader["Id"]);
anEmployee.Name = reader["Name"].ToString();
anEmployee.Email = reader["Email"].ToString();
anEmployee.Address = reader["Address"].ToString();
anEmployee.Designation = designationGateway.GetDesignation(Convert.ToInt32(reader["DesignationId"]));
SqlConnectionObj.Close();
return anEmployee;
}
}
SqlConnectionObj.Close();
return null;
}
public string Save(Employee anEmployee)
{
string message = "";
try
{
SqlConnectionObj.Open();
string query = String.Format("INSERT INTO tbl_EmployeeInfo VALUES('{0}','{1}','{2}','{3}')", anEmployee.Name,anEmployee.Email,anEmployee.Address,anEmployee.Designation.Id);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
message = "Employee: " + anEmployee.Name + " has been saved";
}
catch(Exception ex)
{
throw new Exception("Error occurred during employee save operation. Try again", ex);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return message;
}
private void FillFieldsWith(Employee employee)
{
nameTextBox.Text = employee.Name;
addressTextBox.Text = employee.Address;
emailTextBox.Text = employee.Email;
designationComboBox.SelectedValue = employee.Designation.Id;
}
public EmployeeInformationUI(Employee employee)
: this()
{
saveEmployeeButton.Text = @"Update";
FillFieldsWith(employee);
employeeId = employee.Id;
}
private void saveEmployeeButton_Click(object sender, EventArgs e)
{
Employee anEmployee = new Employee();
anEmployee.Id = employeeId;
anEmployee.Name = nameTextBox.Text;
anEmployee.Email = emailTextBox.Text;
anEmployee.Address = addressTextBox.Text;
anEmployee.Designation = (Designation) designationComboBox.SelectedItem;
if (nameTextBox.Text != "" &&
emailTextBox.Text != "" &&
addressTextBox.Text != "" &&
designationComboBox.Text != "")
{
if (saveEmployeeButton.Text != @"Update")
{
string message = "";
message = employeeManager.Save(anEmployee);
MessageBox.Show(message, @"Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
ClearEmployee();
}
else
{
string message = employeeManager.Update(anEmployee);
MessageBox.Show(message, @"Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
ClearEmployee();
this.Close();
}
}
else
{
MessageBox.Show(@"Please fill-up the employee's information properly", @"Error", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
public string Save(Employee anEmployee)
{
if (employeeGateway.HasEmployeeWithEmail(anEmployee.Email))
{
throw new Exception("Your system already has an employee with this email address. Try again");
}
else
{
return employeeGateway.Save(anEmployee);
}
}
public string Update(Employee anEmployee)
{
Employee selectedEmployee = employeeGateway.FindEmployee(anEmployee.Email);
if (selectedEmployee.Id != anEmployee.Id)
{
return "Your system already has an employee with this email address. Try again";
}
else
{
return employeeGateway.Update(anEmployee);
}
}
public string Delete(Employee selectedEmployee)
{
string message = "";
SqlConnectionObj.Open();
string query = String.Format("DELETE FROM tbl_EmployeeInfo WHERE Id={0}", selectedEmployee.Id);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
SqlConnectionObj.Close();
message = "Employee: " + selectedEmployee.Name + " has been deleted.";
return message;
}
public Employee FindEmployee(string email)
{
Employee anEmployee = null;
try
{
SqlConnectionObj.Open();
string query = String.Format("SELECT * FROM tbl_EmployeeInfo WHERE Email='{0}'", email);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader != null)
{
while(reader.Read())
{
anEmployee = new Employee();
anEmployee.Id = Convert.ToInt32(reader["Id"]);
anEmployee.Name = reader["Name"].ToString();
anEmployee.Email = reader["Email"].ToString();
anEmployee.Address = reader["Address"].ToString();
anEmployee.Designation = designationGateway.GetDesignation(Convert.ToInt32(reader["DesignationId"]));
return anEmployee;
}
}
return null;
}
catch (Exception exception)
{
throw new Exception("Error occurred during employee loading from your system.", exception);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
}
public string DeleteEmployee(Employee selectedEmployee)
{
return employeeGateway.Delete(selectedEmployee);
}
public List<Employee> GetAllEmployee(string partOfName)
{
List<Employee> employees = new List<Employee>();
try
{
SqlConnectionObj.Open();
string query = String.Format("SELECT * FROM tbl_EmployeeInfo");
if (partOfName != "")
{
query += String.Format(" WHERE Name LIKE '%{0}%'", partOfName);
}
query += " ORDER BY Name";
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
Employee anEmployee = new Employee();
anEmployee.Id = Convert.ToInt32(reader["Id"]);
anEmployee.Name = reader["Name"].ToString();
anEmployee.Email = reader["Email"].ToString();
anEmployee.Address = reader["Address"].ToString();
anEmployee.Designation = designationGateway.GetDesignation(Convert.ToInt32(reader["DesignationId"]));
employees.Add(anEmployee);
}
}
catch (Exception exception)
{
throw new Exception("Error occurred during employee loading from your system.", exception);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return employees;
}
public List<Employee> GetAllEmployee(string partOfName)
{
List<Employee> employees = new List<Employee>();
SqlConnectionObj.Open();
string query = String.Format("SELECT * FROM tbl_EmployeeInfo");
if (partOfName != "")
{
query += String.Format(" WHERE Name LIKE '%{0}%'", partOfName);
}
query += " ORDER BY Name";
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
Employee anEmployee = new Employee();
anEmployee.Id = Convert.ToInt32(reader["Id"]);
anEmployee.Name = reader["Name"].ToString();
anEmployee.Email = reader["Email"].ToString();
anEmployee.Address = reader["Address"].ToString();
anEmployee.Designation = designationGateway.GetDesignation(Convert.ToInt32(reader["DesignationId"]));
employees.Add(anEmployee);
}
SqlConnectionObj.Close();
return employees;
}
public string Update(Employee anEmployee)
{
string message = "";
SqlConnectionObj.Open();
string query =
String.Format(
"UPDATE tbl_EmployeeInfo SET Name='{0}',Email='{1}',Address='{2}',DesignationId = {3} WHERE Id = {4}",
anEmployee.Name, anEmployee.Email, anEmployee.Address, anEmployee.Designation.Id,
anEmployee.Id);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
SqlConnectionObj.Close();
message = "Information has been updated";
return message;
}
public string Save(Employee anEmployee)
{
string message = "";
SqlConnectionObj.Open();
string query = String.Format("INSERT INTO tbl_EmployeeInfo VALUES('{0}','{1}','{2}','{3}')", anEmployee.Name,
anEmployee.Email, anEmployee.Address, anEmployee.Designation.Id);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
SqlConnectionObj.Close();
message = "Employee: " + anEmployee.Name + " has been saved";
return message;
}