public List<Department> GetAll()
{
List<Department> departments = new List<Department>();
SqlConnection connection = new SqlConnection(connectionString);
string sql = "SELECT * FROM Departments";
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader();
Department nullDepartment = new Department();
nullDepartment.Id = 0;
nullDepartment.Name = "Select a Department";
departments.Add(nullDepartment);
while (reader.Read())
{
Department aDepartment = new Department();
aDepartment.Id = Convert.ToInt32(reader["Id"].ToString());
aDepartment.Name = reader["Name"].ToString();
departments.Add(aDepartment);
}
connection.Close();
return departments;
}
public ActionResponse Save(Department aDepartment)
{
ActionResponse response = new ActionResponse();
try
{
bool isDepartmentCodeExists = aDepartmentGateway.IsDepartmentCodeExists(aDepartment.DepartmentCode);
if (isDepartmentCodeExists)
{
response.Class = "warning";
response.Message = "Department Code [" + aDepartment.DepartmentCode + "] is already exists.";
return response;
}
bool isDepartmentNameExists = aDepartmentGateway.IsDepartmentNameExists(aDepartment.DepartmentName);
if (isDepartmentNameExists)
{
response.Class = "warning";
response.Message = "Department Name [" + aDepartment.DepartmentName + "] is already exists.";
return response;
}
aDepartmentGateway.Save(aDepartment);
response.Class = "success";
response.Message = "Department Saved Successfully";
}
catch (SqlException ex)
{
response.Class = "danger";
response.Message = ex.Message;
}
return response;
}
public void Save(Department aDepartment)
{
string query = "INSERT INTO Departments (DepartmentCode, DepartmentName) VALUES (@code, @name)";
using (connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.Clear();
command.Parameters.Add("code", sqlDbType: SqlDbType.NVarChar);
command.Parameters["code"].Value = aDepartment.DepartmentCode;
command.Parameters.Add("name", sqlDbType: SqlDbType.NVarChar);
command.Parameters["name"].Value = aDepartment.DepartmentName;
connection.Open();
command.ExecuteNonQuery();
}
}
public List<Department> GetAllDepartments()
{
string query = "SELECT * FROM Departments ORDER BY DepartmentName ASC";
using (connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(query, connection))
{
List<Department> departments = new List<Department>();
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Department aDepartment = new Department
{
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
DepartmentCode = reader["DepartmentCode"].ToString(),
DepartmentName = reader["DepartmentName"].ToString()
};
departments.Add(aDepartment);
}
return departments;
}
}
public ActionResult Save(Department aDepartment)
{
ViewBag.response = aDepartmentManager.Save(aDepartment);
return View();
}
