public ActionResult FindRelation(FormCollection formCollection)
{
// The input conversion is valid.
if (ModelState.IsValid)
{
Models.Relations result = new Models.Relations();
if (!string.IsNullOrEmpty(formCollection["relationEmployee"]) && !string.IsNullOrEmpty(formCollection["relationWorkplace"]))
{
// The relationEmployee and relationWorkplace query fields were not empty so find the items.
result = businessLogicLayer.FindRelationByEmployeeAndWorkplace(formCollection["relationEmployee"], formCollection["relationWorkplace"]);
}
else if (!string.IsNullOrEmpty(formCollection["relationEmployee"]) && string.IsNullOrEmpty(formCollection["relationWorkplace"]))
{
// The relationWorkplace was empty, but the relationEmployee was not, so find the items based on the relationEmployee query field.
result = businessLogicLayer.FindRelationByEmployee(formCollection["relationEmployee"]);
}
else if (string.IsNullOrEmpty(formCollection["relationEmployee"]) && !string.IsNullOrEmpty(formCollection["relationWorkplace"]))
{
// The relationEmployee was empty, but the relationWorkplace was not, so find the items based on the relationWorkplace query field.
result = businessLogicLayer.FindRelationByWorkplace(formCollection["relationWorkplace"]);
}
else
{
// The relationEmployee and relationWorkplace query fields were empty so list all items.
result = businessLogicLayer.ListRelations();
}
// Store the result in the ViewBag.
ViewBag.Relations = result;
}
return(View());
}
/// <summary>
/// Lists the Relations.
/// </summary>
/// <returns>The list of Relations or null.</returns>
public Models.Relations ListRelations()
{
try
{
// Open a connection to the database.
SqlConnection sqlConnection = new SqlConnection(url);
sqlConnection.Open();
// List the items from the Relations table with the stored procedure.
Models.Relations relations = new Models.Relations();
SqlCommand sqlCommand = new SqlCommand("spListRelations", sqlConnection);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
// Store the items in a 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>
/// 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>
/// Lists the Relations.
/// </summary>
/// <returns>The list of Relations or null.</returns>
public Models.Relations ListRelations()
{
try
{
// Open a connection to the database.
SqlConnection sqlConnection = new SqlConnection(url);
sqlConnection.Open();
// List the items from the Relations table with the stored procedure.
Models.Relations relations = new Models.Relations();
SqlCommand sqlCommand = new SqlCommand("spListRelations", sqlConnection);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
// Store the items in a 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>
/// 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 FindRelation(FormCollection formCollection)
{
// The input conversion is valid.
if (ModelState.IsValid)
{
Models.Relations result = new Models.Relations();
if (!string.IsNullOrEmpty(formCollection["relationEmployee"]) && !string.IsNullOrEmpty(formCollection["relationWorkplace"]))
{
// The relationEmployee and relationWorkplace query fields were not empty so find the items.
result = businessLogicLayer.FindRelationByEmployeeAndWorkplace(formCollection["relationEmployee"], formCollection["relationWorkplace"]);
}
else if (!string.IsNullOrEmpty(formCollection["relationEmployee"]) && string.IsNullOrEmpty(formCollection["relationWorkplace"]))
{
// The relationWorkplace was empty, but the relationEmployee was not, so find the items based on the relationEmployee query field.
result = businessLogicLayer.FindRelationByEmployee(formCollection["relationEmployee"]);
}
else if (string.IsNullOrEmpty(formCollection["relationEmployee"]) && !string.IsNullOrEmpty(formCollection["relationWorkplace"]))
{
// The relationEmployee was empty, but the relationWorkplace was not, so find the items based on the relationWorkplace query field.
result = businessLogicLayer.FindRelationByWorkplace(formCollection["relationWorkplace"]);
}
else
{
// The relationEmployee and relationWorkplace query fields were empty so list all items.
result = businessLogicLayer.ListRelations();
}
// Store the result in the ViewBag.
ViewBag.Relations = result;
}
return View();
}
