// GET: Computers
public IActionResult Index(string searchString)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT c.Id, c.PurchaseDate, c.Make, c.Model, e.FirstName, e.LastName, e.ComputerId
FROM Computer c
LEFT JOIN Employee e
ON e.ComputerId = c.Id
WHERE 1=1";
if (searchString != null)
{
cmd.CommandText += " AND Make LIKE @searchString OR Model LIKE @searchString";
cmd.Parameters.Add(new SqlParameter("@searchString", "%" + searchString + "%"));
}
var reader = cmd.ExecuteReader();
List <ComputerAddEmployeeViewModel> computers = new List <ComputerAddEmployeeViewModel>();
while (reader.Read())
{
ComputerAddEmployeeViewModel computer = new ComputerAddEmployeeViewModel
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
PurchaseDate = reader.GetDateTime(reader.GetOrdinal("PurchaseDate")),
Make = reader.GetString(reader.GetOrdinal("Make")),
Model = reader.GetString(reader.GetOrdinal("Model"))
};
if (!reader.IsDBNull(reader.GetOrdinal("FirstName")))
{
computer.employee = new Employee
{
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName"))
};
}
else
{
computer.employee = new Employee
{
FirstName = null
};
}
computers.Add(computer);
}
reader.Close();
return(View(computers));
}
}
}
// GET: Computers/Create
public ActionResult Create()
{
var employee = GetEmployees();
var employeeOptions = GetEmployeeOptions();
var viewModel = new ComputerAddEmployeeViewModel()
{
EmployeeOptions = employeeOptions
};
return(View(viewModel));
}
public ActionResult Create(ComputerAddEmployeeViewModel computer)
{
try
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO Computer (PurchaseDate, Make, Model)
OUTPUT INSERTED.Id
VALUES (@purchaseDate, @make, @model);
";
cmd.Parameters.Add(new SqlParameter("@purchaseDate", computer.PurchaseDate));
cmd.Parameters.Add(new SqlParameter("@make", computer.Make));
cmd.Parameters.Add(new SqlParameter("@model", computer.Model));
cmd.Parameters.Add(new SqlParameter("@id", computer.EmployeeId));
var id = (int)cmd.ExecuteScalar();
computer.Id = id;
}
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"UPDATE Employee
SET ComputerId = @computerId
WHERE Id = @id";
cmd.Parameters.Add(new SqlParameter("@id", computer.EmployeeId));
cmd.Parameters.Add(new SqlParameter("@computerId", computer.Id));
cmd.ExecuteNonQuery();
}
return(RedirectToAction(nameof(Index)));
}
}
catch (Exception ex)
{
return(View());
}
}
// COMPUTER HELPER METHOD
private ComputerAddEmployeeViewModel GetComputerById(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT c.Id, c.PurchaseDate, c.Make, c.Model, COALESCE(e.[FirstName] + ' ' + e.LastName, 'N/A' ) AS EmployeeName, e.ComputerId
FROM Computer c
LEFT JOIN Employee e
ON e.ComputerId = c.Id
WHERE c.Id = @id";
cmd.Parameters.Add(new SqlParameter("@id", id));
var reader = cmd.ExecuteReader();
ComputerAddEmployeeViewModel computer = null;
if (reader.Read())
{
computer = new ComputerAddEmployeeViewModel
{
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("EmployeeName")),
}
};
}
reader.Close();
return(computer);
}
}
}
