public int CreateNewDepartment(Department department)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewDepartment";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("DepartmentTitle", department.Title);
cmd.Parameters.AddWithValue("AddressID", department.Address);
cmd.Parameters.AddWithValue("DepartmentHead", department.Head);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public ActionResult EditDepartment()
{
// Null handling
if (Session["loggedInState"] == null)
{
return Redirect("/403.html");
}
// Checks if logged in
bool state = (bool)Session["loggedInState"];
if (state == true)
{
// Creates an department placeholder
var d = new Department();
// Setup address edit
d.Id = int.Parse(Request.Form["id"]);
d.Title = Request.Form["title"];
d.Head = int.Parse(Request.Form["head"]);
d.Address = int.Parse(Request.Form["address"]);
// Establish department model
var dm = new DepartmentModel();
// Conduct edit
dm.EditDepartment(d);
// TODO: Redirect to appropriate page (Add address?)
// Passes back to the view
return View();
}
else
{
// If not logged in
return Redirect("/login.html");
}
}
// GET: Department
public ActionResult Index()
{
// Ensures logged in
if (Session["loggedInState"] == null)
{
return Redirect("/403.html");
}
// Checks if logged in
bool state = (bool)Session["loggedInState"];
if (state == true)
{
// Establishes a department model
DepartmentModel departmentModel = new DepartmentModel();
// Holds the new department
Department newDepartment = new Department();
// Stored details for the department
newDepartment.Title = Request.Form[1];
newDepartment.Head = int.Parse(Request.Form[2]);
newDepartment.Address = int.Parse(Request.Form[3]);
// Commences save to database
departmentModel.CreateNewDepartment(newDepartment);
// Return created department to view
return View(newDepartment);
}
else
{
// If not logged in
return Redirect("/login.html");
}
}
public void EditDepartment(Department d)
{
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "EditDepartment";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("DepartmentID", d.Id);
cmd.Parameters.AddWithValue("DepartmentTitle", d.Title);
cmd.Parameters.AddWithValue("AddressID", d.Address);
cmd.Parameters.AddWithValue("DepartmentHead", d.Head);
cmd.ExecuteNonQuery();
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
}
// Overload to allow getting department be either id or a department object.
public Department SearchDepartment(Department department)
{
return this.SearchDepartment(department.Id);
}
// Get department by id.
// This is the impmented method others should call it.
public Department SearchDepartment(int ID)
{
var department = new Department();
using (connect = new MySqlConnection(_connectionString))
{
try
{
string query = "GetDepartment";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("DepartmentID", ID);
connect.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
try
{
department.Id = (int)reader["Department_ID"];
department.Title = reader["Department_Title"].ToString();
department.AddressID = (int)reader["Address_ID"];
department.DHeadID = (int)reader["Department_Head"];
}
catch (Exception) { }
}
connect.Close();
}
catch (InvalidOperationException ioException)
{
connect.Close();
}
return department;
}
}
public List<Department> GetDepartmentsList()
{
var departmentList = new List<Department>();
using (connect = new MySqlConnection(_connectionString))
{
try
{
string query = "ListDepartment";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
connect.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var d = new Department();
d.Id = (int)reader["Department_ID"];
d.Title = reader["Department_Title"].ToString();
d.AddressID = (int)reader["Address_ID"];
d.DHeadID = (int)reader["Department_Head"];
departmentList.Add(d);
}
connect.Close();
}
catch (InvalidOperationException ioException)
{
connect.Close();
}
return departmentList;
}
}