// GET: Computers
public ActionResult Index(string searchString)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT c.Id, c.Make, c.Model, c.PurchaseDate, e.FirstName
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 + "%"));
}
SqlDataReader reader = cmd.ExecuteReader();
List <ComputerDetailsViewModel> computers = new List <ComputerDetailsViewModel>();
while (reader.Read())
{
ComputerDetailsViewModel computer = new ComputerDetailsViewModel
{
ComputerId = reader.GetInt32(reader.GetOrdinal("Id")),
Make = reader.GetString(reader.GetOrdinal("Make")),
Model = reader.GetString(reader.GetOrdinal("Model")),
PurchaseDate = reader.GetDateTime(reader.GetOrdinal("PurchaseDate")),
};
if (!reader.IsDBNull(reader.GetOrdinal("FirstName")))
{
computer.employee = new Employee
{
FirstName = reader.GetString(reader.GetOrdinal("FirstName"))
};
}
else
{
computer.employee = new Employee
{
FirstName = null
};
}
computers.Add(computer);
}
reader.Close();
return(View(computers));
}
}
}
private ComputerDetailsViewModel GetComputerById(int id)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT c.Id, c.Make, c.Model, c.DecomissionDate, c.PurchaseDate, COALESCE(e.[FirstName] + ' ' + e.LastName, 'N/A' ) AS EmployeeName
FROM Computer c
LEFT JOIN Employee e ON c.Id = e.ComputerId
WHERE c.Id = @id";
cmd.Parameters.Add(new SqlParameter("@id", id));
var reader = cmd.ExecuteReader();
ComputerDetailsViewModel computer = null;
if (reader.Read())
{
computer = new ComputerDetailsViewModel()
{
ComputerId = reader.GetInt32(reader.GetOrdinal("Id")),
Make = reader.GetString(reader.GetOrdinal("Make")),
Model = reader.GetString(reader.GetOrdinal("Model")),
PurchaseDate = reader.GetDateTime(reader.GetOrdinal("PurchaseDate")),
employee = new Employee
{
FirstName = reader.GetString(reader.GetOrdinal("EmployeeName"))
}
};
if (!reader.IsDBNull(reader.GetOrdinal("DecomissionDate")))
{
computer.DecomissionDate = reader.GetDateTime(reader.GetOrdinal("DecomissionDate"));
}
else
{
computer.DecomissionDate = null;
}
}
reader.Close();
return(computer);
}
}
}