/// <summary>
/// Finds a Workplace by name.
/// </summary>
/// <param name="input">The Workplace name to find.</param>
/// <returns>The Workplace list or null.</returns>
public Models.Workplaces FindWorkplaceByName(string input)
{
try
{
// Open a connection to the database.
SqlConnection sqlConnection = new SqlConnection(url);
sqlConnection.Open();
// Find the items in the Workplaces table with the stored procedure.
Models.Workplaces workplaces = new Models.Workplaces();
SqlCommand sqlCommand = new SqlCommand("spFindWorkplaceByName", sqlConnection);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@name", input);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
// Store the items in a Workplace list.
while (sqlDataReader.Read())
{
workplaces.Items.Add(new Models.Workplace(sqlDataReader.GetInt32(0), sqlDataReader.GetString(1), sqlDataReader.GetBoolean(2)));
}
// Close the dataReader and the connection.
sqlDataReader.Close();
sqlConnection.Close();
return(workplaces);
}
catch
{
return(null);
}
}
/// <summary>
/// Finds an Relation by Workplace.
/// </summary>
/// <param name="input">The Relation Workplace to find.</param>
/// <returns>The Relation list or null.</returns>
public Models.Relations FindRelationByWorkplace(string input)
{
try
{
// Open a connection to the database.
SqlConnection sqlConnection = new SqlConnection(url);
sqlConnection.Open();
// Find the items in the Workplaces table with the stored procedure.
Models.Workplaces workplaces = new Models.Workplaces();
SqlCommand sqlCommand1 = new SqlCommand("spFindWorkplaceByName", sqlConnection);
sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand1.Parameters.AddWithValue("@name", input);
SqlDataReader sqlDataReader1 = sqlCommand1.ExecuteReader();
// Store the items in an Workplace list.
while (sqlDataReader1.Read())
{
workplaces.Items.Add(new Models.Workplace(sqlDataReader1.GetInt32(0), sqlDataReader1.GetString(1), sqlDataReader1.GetBoolean(2)));
}
// Close the dataReader.
sqlDataReader1.Close();
// There is at least one item that was found.
int input2 = 0;
if (workplaces.Items.Count > 0)
{
// Because the previous search was on an Id, there should only be one item found.
input2 = workplaces.Items[0].WorkplaceId;
}
// Find the items in the Relations table with the stored procedure.
Models.Relations relations = new Models.Relations();
SqlCommand sqlCommand = new SqlCommand("spFindRelationByWorkplace", sqlConnection);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@workplace", input2);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
// Store the items in an Relation list.
while (sqlDataReader.Read())
{
relations.Items.Add(new Models.Relation(sqlDataReader.GetInt32(0), sqlDataReader.GetInt32(1), sqlDataReader.GetInt32(2)));
}
// Close the dataReader and the connection.
sqlDataReader.Close();
sqlConnection.Close();
return(relations);
}
catch
{
return(null);
}
}
/// <summary>
/// Lists the database tables.
/// </summary>
/// <returns>The Index view.</returns>
public ActionResult Index()
{
// List the items from the tables.
Models.Workplaces workplaces = businessLogicLayer.ListWorkplaces();
Models.Employees employees = businessLogicLayer.ListEmployees();
Models.Relations relations = businessLogicLayer.ListRelations();
// Store the items in the ViewBag so the Index view can use them.
ViewBag.Workplaces = workplaces;
ViewBag.Employees = employees;
ViewBag.Relations = relations;
return(View());
}
/// <summary>
/// Adds a Relation.
/// </summary>
/// <returns>The AddRelation view.</returns>
// Load the view.
public ActionResult AddRelation()
{
// List the Relations and store them in the ViewBag.
Models.Relations relations = businessLogicLayer.ListRelations();
ViewBag.Relations = relations;
// List the Workplaces and store them in the ViewBag.
Models.Workplaces workplaces = businessLogicLayer.ListWorkplaces();
ViewBag.Workplaces = workplaces;
// List the Employees and store them in the ViewBag.
Models.Employees employees = businessLogicLayer.ListEmployees();
ViewBag.Employees = employees;
return(View());
}
/// <summary>
/// Edits a Relation.
/// </summary>
/// <param name="id">The relationId that was selected.</param>
/// <returns>The EditRelation view.</returns>
// Load the view.
public ActionResult EditRelation(int id)
{
// Find the selected Relation and store it in the ViewBag.
Models.Relation relation = businessLogicLayer.ListRelations().Items.Find(x => x.RelationId == id);
ViewBag.Relation = relation;
// List the Workplaces and store them in the ViewBag.
Models.Workplaces workplaces = businessLogicLayer.ListWorkplaces();
ViewBag.Workplaces = workplaces;
// Lists the Employees and store them in the ViewBag.
Models.Employees employees = businessLogicLayer.ListEmployees();
ViewBag.Employees = employees;
return(View());
}
public ActionResult FindWorkplace(FormCollection formCollection)
{
// The input conversion is valid.
if (ModelState.IsValid)
{
Models.Workplaces result = new Models.Workplaces();
if (!string.IsNullOrEmpty(formCollection["workplaceName"]))
{
// The workplaceName query field was not empty so find the items.
result = businessLogicLayer.FindWorkplaceByName(formCollection["workplaceName"]);
}
else
{
// The workplaceName query field was empty so list all items.
result = businessLogicLayer.ListWorkplaces();
}
// Store the result in the ViewBag.
ViewBag.Workplaces = result;
}
return(View());
}
/// <summary>
/// Lists the Workplaces.
/// </summary>
/// <returns>The list of Workplaces or null.</returns>
public Models.Workplaces ListWorkplaces()
{
try
{
// Open a connection to the database.
SqlConnection sqlConnection = new SqlConnection(url);
sqlConnection.Open();
// List the items from the Workplaces table with the stored procedure.
Models.Workplaces workplaces = new Models.Workplaces();
SqlCommand sqlCommand = new SqlCommand("spListWorkplaces", sqlConnection);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
// Store the items in a Workplace list.
while (sqlDataReader.Read())
{
workplaces.Items.Add(new Models.Workplace(sqlDataReader.GetInt32(0), sqlDataReader.GetString(1), sqlDataReader.GetBoolean(2)));
}
// Close the dataReader and the connection.
sqlDataReader.Close();
sqlConnection.Close();
return workplaces;
}
catch
{
return null;
}
}
/// <summary>
/// Finds an Relation by Workplace.
/// </summary>
/// <param name="input">The Relation Workplace to find.</param>
/// <returns>The Relation list or null.</returns>
public Models.Relations FindRelationByWorkplace(string input)
{
try
{
// Open a connection to the database.
SqlConnection sqlConnection = new SqlConnection(url);
sqlConnection.Open();
// Find the items in the Workplaces table with the stored procedure.
Models.Workplaces workplaces = new Models.Workplaces();
SqlCommand sqlCommand1 = new SqlCommand("spFindWorkplaceByName", sqlConnection);
sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand1.Parameters.AddWithValue("@name", input);
SqlDataReader sqlDataReader1 = sqlCommand1.ExecuteReader();
// Store the items in an Workplace list.
while (sqlDataReader1.Read())
{
workplaces.Items.Add(new Models.Workplace(sqlDataReader1.GetInt32(0), sqlDataReader1.GetString(1), sqlDataReader1.GetBoolean(2)));
}
// Close the dataReader.
sqlDataReader1.Close();
// There is at least one item that was found.
int input2 = 0;
if (workplaces.Items.Count > 0)
{
// Because the previous search was on an Id, there should only be one item found.
input2 = workplaces.Items[0].WorkplaceId;
}
// Find the items in the Relations table with the stored procedure.
Models.Relations relations = new Models.Relations();
SqlCommand sqlCommand = new SqlCommand("spFindRelationByWorkplace", sqlConnection);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@workplace", input2);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
// Store the items in an Relation list.
while (sqlDataReader.Read())
{
relations.Items.Add(new Models.Relation(sqlDataReader.GetInt32(0), sqlDataReader.GetInt32(1), sqlDataReader.GetInt32(2)));
}
// Close the dataReader and the connection.
sqlDataReader.Close();
sqlConnection.Close();
return relations;
}
catch
{
return null;
}
}
public ActionResult FindWorkplace(FormCollection formCollection)
{
// The input conversion is valid.
if (ModelState.IsValid)
{
Models.Workplaces result = new Models.Workplaces();
if (!string.IsNullOrEmpty(formCollection["workplaceName"]))
{
// The workplaceName query field was not empty so find the items.
result = businessLogicLayer.FindWorkplaceByName(formCollection["workplaceName"]);
}
else
{
// The workplaceName query field was empty so list all items.
result = businessLogicLayer.ListWorkplaces();
}
// Store the result in the ViewBag.
ViewBag.Workplaces = result;
}
return View();
}
