public ActionResult Create(ComputerEmployeeViewModel computerEmployees)
{
try
{
using (SqlConnection conn = Connection)
{
conn.Open();
int computerId = 0;
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
INSERT INTO Computer(
Make,
Manufacturer,
PurchaseDate
)
OUTPUT INSERTED.Id
Values(
@Make,
@Manufacturer,
@PurchaseDate)";
cmd.Parameters.AddWithValue("@Make", computerEmployees.Computer.Make);
cmd.Parameters.AddWithValue("@Manufacturer", computerEmployees.Computer.Manufacturer);
cmd.Parameters.AddWithValue("@PurchaseDate", computerEmployees.Computer.PurchaseDate);
cmd.Parameters.AddWithValue("@ComputerId", computerEmployees.Computer.Id);
computerId = (Int32)cmd.ExecuteScalar();
}
if (computerEmployees.EmployeeId > 0)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO ComputerEmployee(
ComputerId,
EmployeeId,
AssignDate
) Values(
@ComputerId,
@EmployeeId,
@AssignDate
)";
cmd.Parameters.AddWithValue("@EmployeeId", computerEmployees.EmployeeId);
cmd.Parameters.AddWithValue("@ComputerId", computerId);
cmd.Parameters.Add(new SqlParameter("@AssignDate", SqlDbType.DateTime)
{
Value = DateTime.Today
});
cmd.ExecuteNonQuery();
}
}
}
return(RedirectToAction(nameof(Index)));
}
catch
{
return(View());
}
}
public ActionResult Create(ComputerEmployeeViewModel viewModel)
{
try
{
// TODO: Add insert logic here
var computer = viewModel.Computer;
var selectedEmployeeId = viewModel.SelectedEmployeeId;
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO Computer
( PurchaseDate, Make, Manufacturer )
OUTPUT Inserted.ID
VALUES
( @PurchaseDate, @Make, @Manufacturer );
";
cmd.Parameters.Add(new SqlParameter("@PurchaseDate", computer.PurchaseDate));
cmd.Parameters.Add(new SqlParameter("@Make", computer.Make));
cmd.Parameters.Add(new SqlParameter("@Manufacturer", computer.Manufacturer));
int computerId = (Int32)cmd.ExecuteScalar();
if (selectedEmployeeId != 0)
{
cmd.CommandText = @"INSERT INTO ComputerEmployee
( ComputerId, EmployeeId, AssignDate )
VALUES
( @ComputerId, @EmployeeId, GetDate() );
";
cmd.Parameters.Add(new SqlParameter("@ComputerId", computerId));
cmd.Parameters.Add(new SqlParameter("@EmployeeId", selectedEmployeeId));
cmd.ExecuteNonQuery();
}
}
}
return(RedirectToAction(nameof(Index)));
}
catch (Exception ex)
{
var exception = ex;
return(View());
}
}
// GET: Computers/Create
public ActionResult Create()
{
var viewModel = new ComputerEmployeeViewModel();
var employees = GetAllEmployees();
var selectedEmployees = employees
.Select(e => new SelectListItem
{
Text = $"{e.FirstName} {e.LastName}",
Value = e.Id.ToString()
}).ToList();
selectedEmployees.Insert(0, new SelectListItem
{
Text = "Choose Employee....",
Value = "0"
});
viewModel.Employees = selectedEmployees;
return(View(viewModel));
}
// GET: Computers
public ActionResult Index(string SearchString)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT
c.PurchaseDate,
IsNull(c.DecomissionDate, '') AS DecomissionDate,
c.Make,
c.Manufacturer,
isNull(e.FirstName + ' ' +
e.LastName, '') AS ComputerEmployee,
e.Id AS EmployeeId,
c.Id AS ComputerId
FROM ComputerEmployee ce
LEFT JOIN Employee e ON e.Id = ce.EmployeeId
FULL OUTER JOIN Computer c ON c.Id = ce.ComputerId";
if (SearchString != null)
{
cmd.CommandText += @" WHERE
c.Make LIKE '%' + @SearchString + '%' OR
c.Manufacturer LIKE '%' + @SearchString + '%'
" ;
cmd.Parameters.Add(new SqlParameter("@SearchString", SearchString));
}
SqlDataReader reader = cmd.ExecuteReader();
List <ComputerEmployeeViewModel> computersEmployees = new List <ComputerEmployeeViewModel>();
while (reader.Read())
{
ComputerEmployeeViewModel ComputerEmployee = new ComputerEmployeeViewModel();
Computer aComputer = new Computer
{
Id = reader.GetInt32(reader.GetOrdinal("ComputerId")),
PurchaseDate = reader.GetDateTime(reader.GetOrdinal("PurchaseDate")),
DecomissionDate = reader.GetDateTime(reader.GetOrdinal("DecomissionDate")),
Make = reader.GetString(reader.GetOrdinal("Make")),
Manufacturer = reader.GetString(reader.GetOrdinal("Manufacturer"))
};
if (!reader.IsDBNull(reader.GetOrdinal("EmployeeId")))
{
Employee anEmployee = new Employee
{
Id = reader.GetInt32(reader.GetOrdinal("EmployeeId")),
FirstName = reader.GetString(reader.GetOrdinal("ComputerEmployee")),
};
ComputerEmployee.Employee = anEmployee;
}
ComputerEmployee.Computer = aComputer;
computersEmployees.Add(ComputerEmployee);
}
;
reader.Close();
return(View(computersEmployees));
}
}
}
