//Get a computer by Id
private ComputerDetailViewModel GetComputerById(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT c.Id, c.PurchaseDate, c.DecomissionDate, c.Make, c.Model, e.FirstName, e.LastName FROM Computer c LEFT JOIN Employee e ON e.ComputerId = c.Id WHERE c.Id = @id AND e.ComputerId IS NOT NULL";
cmd.Parameters.Add(new SqlParameter("@id", id));
var reader = cmd.ExecuteReader();
ComputerDetailViewModel computer = null;
if (reader.Read())
{
computer = new ComputerDetailViewModel()
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
PurchaseDate = reader.GetDateTime(reader.GetOrdinal("PurchaseDate")),
Make = reader.GetString(reader.GetOrdinal("Make")),
Model = reader.GetString(reader.GetOrdinal("Model")),
employee = new Employee
{
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
}
};
if (!reader.IsDBNull(reader.GetOrdinal("DecomissionDate")))
{
computer.DecomissionDate = reader.GetDateTime(reader.GetOrdinal("DecomissionDate"));
}
}
reader.Close();
return(computer);
}
}
}
// GET: Computers/Details/5
public ActionResult Details(int id)
{
Computer computer = GetComputerById(id);
if (computer == null)
{
return(NotFound());
}
ComputerDetailViewModel viewModel = new ComputerDetailViewModel
{
Id = id,
Make = computer.Make,
Manufacturer = computer.Manufacturer,
PurchaseDate = computer.PurchaseDate,
DecomissionDate = computer.DecomissionDate
};
return(View(viewModel));
}
// GET: Computers/Details/5
public async Task <IActionResult> Details(int?id)
{
if (id == null)
{
return(NotFound());
}
var computer = await _context.Computer
.SingleOrDefaultAsync(m => m.Id == id);
if (computer == null)
{
return(NotFound());
}
List <Employee> employees = await _context.Employee.Where(e => e.DepartmentId == id).ToListAsync();
ComputerDetailViewModel viewModel = new ComputerDetailViewModel();
viewModel.Computer = computer;
viewModel.Employees = employees;
return(View(viewModel));
}