public static List<Employee> GetAllEmployees()
{
List<Employee> employees = new List<Employee>();
SqlConnection conn = GetInventoryDbConnection();
try
{
conn.Open();
SqlCommand sqlCmd = new SqlCommand("proc_GetEmployees", conn);
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var employee = new Employee(reader.GetInt32(reader.GetOrdinal("UserID")))
{
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
PhoneNumber = reader.GetString(reader.GetOrdinal("PhoneNumber")),
RoleID = reader.GetInt32(reader.GetOrdinal("RoleID")),
Active = reader.GetBoolean(reader.GetOrdinal("Active"))
};
employees.Add(employee);
}
}
reader.Close();
}
#region Exceptions
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
conn.Close();
}
return employees;
#endregion
}
public static Boolean Add(Employee employee,String password)
{
var connection = GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_InsertIntoUsers", connection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@RoleID", employee.RoleID);
mySqlCommand.Parameters.AddWithValue("@FirstName", employee.FirstName);
mySqlCommand.Parameters.AddWithValue("@LastName", employee.LastName);
mySqlCommand.Parameters.AddWithValue("@PhoneNumber", employee.PhoneNumber);
mySqlCommand.Parameters.AddWithValue("@Password", password);
mySqlCommand.Parameters.AddWithValue("@Active", employee.Active ? 1 : 0);
connection.Open();
if (mySqlCommand.ExecuteNonQuery() == 1)
{
return true;
}
}
#region Exceptions
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
connection.Close();
}
#endregion
return false;
}
private void btnAdd_Click_1(object sender, EventArgs e)
{
Employee newEmployee;
bool validEmployee = true;
string errorMessage = "Please correct the following errors:\n";
if (txtPassword.TextLength > 50)
{
validEmployee = false;
errorMessage += "\nThe password must not exceed 50 characters.";
txtPhone.Focus();
}
if (Validation.IsNullOrEmpty(txtPassword.Text) || Validation.IsBlank(txtPassword.Text))
{
validEmployee = false;
errorMessage += "\nPlease enter a password.";
txtPassword.Focus();
}
if (txtPhone.TextLength > 14)
{
validEmployee = false;
errorMessage += "\nThe phone number must not exceed 14 characters.";
txtPhone.Focus();
}
if (Validation.IsNullOrEmpty(txtPhone.Text) || Validation.IsBlank(txtPhone.Text))
{
validEmployee = false;
errorMessage += "\nPlease enter a phone number.";
txtPhone.Focus();
}
if (txtLName.TextLength > 25)
{
validEmployee = false;
errorMessage += "\nThe last name must not exceed 25 characters.";
txtLName.Focus();
}
if (Validation.IsNullOrEmpty(txtLName.Text) || Validation.IsBlank(txtLName.Text))
{
validEmployee = false;
errorMessage += "\nPlease enter a last name.";
txtLName.Focus();
}
if (txtFName.TextLength > 25)
{
validEmployee = false;
errorMessage += "\nThe first name must not exceed 25 characters.";
txtFName.Focus();
}
if (Validation.IsNullOrEmpty(txtFName.Text) || Validation.IsBlank(txtFName.Text))
{
validEmployee = false;
errorMessage += "\nPlease enter a first name.";
txtFName.Focus();
}
if (validEmployee)
{
newEmployee = new Employee()
{
FirstName = txtFName.Text,
LastName = txtLName.Text,
PhoneNumber = txtPhone.Text,
RoleID = ((KeyValuePair<int, string>)cbRole.SelectedItem).Key,
Active = Convert.ToBoolean(cbActive.SelectedItem)
};
if (EmployeeManager.Add(newEmployee, txtPassword.Text))
{
this.DialogResult = DialogResult.OK;
MessageBox.Show("The employee was created.");
}
else
{
MessageBox.Show("The employee was not created.");
}
}
else
{
MessageBox.Show(errorMessage);
}
}
public static Boolean Update(Employee old, Employee edited)
{
return EmployeeDAL.Update(old, edited);
}
public static Boolean Add(Employee newEmployee, String password)
{
return EmployeeDAL.Add(newEmployee, password);
}
public static Boolean SetActive(bool active, Employee employee)
{
var connection = GetInventoryDbConnection();
try
{
var mySqlCommand = new SqlCommand("proc_UpdateActiveEmployee", connection)
{
CommandType = CommandType.StoredProcedure
};
mySqlCommand.Parameters.AddWithValue("@UserID", employee.Id);
mySqlCommand.Parameters.AddWithValue("@Active", active ? 1 : 0);
connection.Open();
if (mySqlCommand.ExecuteNonQuery() == 1)
{
return true;
}
}
#region Exceptions
catch (DataException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("DatabaseException"), ex);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("SqlException"), ex);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new ApplicationException(Messeges.GetMessage("Exception"), ex);
}
finally
{
connection.Close();
}
#endregion
return false;
}
