// GET: Computers/Create
public ActionResult Create()
{
ComputerCreateViewModel viewModel =
new ComputerCreateViewModel(_configuration.GetConnectionString("DefaultConnection"));
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.Id, c.PurchaseDate, c.DecomissionDate, 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 <ComputerCreateViewModel> computers = new List <ComputerCreateViewModel>();
while (reader.Read())
{
ComputerCreateViewModel computer = new ComputerCreateViewModel()
{
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"))
};
}
if (!reader.IsDBNull(reader.GetOrdinal("DecomissionDate")))
{
computer.DecomissionDate = reader.GetDateTime(reader.GetOrdinal("DecomissionDate"));
}
computers.Add(computer);
}
reader.Close();
return(View(computers));
}
}
}
// GET: Computers/Create
public ActionResult Create()
{
var viewModel = new ComputerCreateViewModel();
var employees = GetAllEmployees();
var selectItems = employees.ToList();
viewModel.Employees = selectItems;
return(View(viewModel));
}
// GET: Computer/Create
public ActionResult Create()
{
var viewModel = new ComputerCreateViewModel()
{
Employees = GetAllEmployees()
};
return(View(viewModel));
}
public async Task <ActionResult> Create(ComputerCreateViewModel viewModel)
{
try
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO Computer
(Make, Manufacturer, PurchaseDate)
OUTPUT INSERTED.Id
VALUES (@make, @manufacturer, @purchaseDate)";
cmd.Parameters.Add(new SqlParameter("@make", viewModel.computer.Make));
cmd.Parameters.Add(new SqlParameter("@manufacturer", viewModel.computer.Manufacturer));
cmd.Parameters.Add(new SqlParameter("@purchaseDate", viewModel.computer.PurchaseDate));
int i = 0;
object a = await cmd.ExecuteScalarAsync();
if (a != null)
{
i = (int)a;
}
//int compId = (int?)await cmd.ExecuteScalarAsync();
viewModel.computer.Id = i;
if (viewModel.employeeId != 0)
{
cmd.CommandText = @"INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate)
OUTPUT INSERTED.Id
VALUES (@employeeId, @computerId, @assignDate, null)";
DateTime currentDate = DateTime.Now;
cmd.Parameters.Add(new SqlParameter("@employeeId", viewModel.employeeId));
cmd.Parameters.Add(new SqlParameter("@computerId", i));
cmd.Parameters.Add(new SqlParameter("@assignDate", currentDate));
int newCEId = (int)cmd.ExecuteScalar();
cmd.CommandText = @"UPDATE ComputerEmployee SET UnassignDate = @unassignDate WHERE employeeID = @employeeId AND computerId != @computerId";
cmd.Parameters.Add(new SqlParameter("@unassignDate", currentDate));
cmd.ExecuteScalar();
}
return(RedirectToAction(nameof(Index)));
}
}
}
catch
{
return(View());
}
}
// GET: Computers/Create
public ActionResult Create()
{
ComputerCreateViewModel viewModel = new ComputerCreateViewModel
{
Employees = GetAllUnassignedEmployees()
};
return(View(viewModel));
}
// GET: Computers/Create
public ActionResult Create()
{
var employeeOptions = GetEmployeeOptions();
var viewModel = new ComputerCreateViewModel()
{
EmployeeOptions = employeeOptions
};
return(View(viewModel));
}
// GET: Computers/Create
public ActionResult Create()
{
var employeeOptions = GetEmployeeOptions();
var viewModel = new ComputerCreateViewModel()
{
PurchaseDate = DateTime.Now,
EmployeeOptions = employeeOptions
};
return(View(viewModel));
}
public ActionResult Create(ComputerCreateViewModel 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));
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());
}
}
public ActionResult Create(ComputerCreateViewModel viewModel)
{
try
{
var newComputer = viewModel.Computer;
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", newComputer.PurchaseDate));
cmd.Parameters.Add(new SqlParameter("@Make", newComputer.Make));
cmd.Parameters.Add(new SqlParameter("@Manufacturer", newComputer.Manufacturer));
int newComputerId = 0;
object newComputerObject = cmd.ExecuteScalar();
newComputerId = (Int32)newComputerObject;
newComputer.Id = newComputerId;
if (newComputer.EmployeeId != 0)
{
cmd.CommandText = @"INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate)
OUTPUT INSERTED.Id
VALUES (@EmployeeId, @ComputerId, @AssignDate);";
DateTime AssignDate = DateTime.Now;
cmd.Parameters.Add(new SqlParameter("@EmployeeId", newComputer.EmployeeId));
cmd.Parameters.Add(new SqlParameter("@ComputerId", newComputerId));
cmd.Parameters.Add(new SqlParameter("@AssignDate", AssignDate));
int newComputerEmployeeId = (Int32)cmd.ExecuteScalar();
}
}
}
return(RedirectToAction(nameof(Index)));
}
catch
{
return(View());
}
}
public ActionResult Create(ComputerCreateViewModel computer)
{
try
{
// TODO: Add insert logic here
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));
var Id = (int)cmd.ExecuteScalar();
computer.Id = Id;
if (computer.EmployeeId != 0)
{
cmd.CommandText = @"UPDATE Employee
SET ComputerID = @computerId
WHERE Id = @employeeId";
cmd.Parameters.Add(new SqlParameter("@computerId", computer.Id));
cmd.Parameters.Add(new SqlParameter("@employeeId", computer.EmployeeId));
var rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected < 1)
{
return(NotFound());
}
}
return(RedirectToAction(nameof(Index)));
}
}
}
catch (Exception ex)
{
return(View());
}
}
public ActionResult Create(ComputerCreateViewModel viewModel)
{
try
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
if (viewModel.Computer.Employee.Id != 0)
{
cmd.CommandText = @"insert into Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) OUTPUT Inserted.Id values (@PurchaseDate, @DecomissionDate, @Make, @Manufacturer);";
cmd.Parameters.Add(new SqlParameter("@PurchaseDate", viewModel.Computer.PurchaseDate));
cmd.Parameters.Add(new SqlParameter("@DecomissionDate", SqlDateTime.Null));
cmd.Parameters.Add(new SqlParameter("@Make", viewModel.Computer.Make));
cmd.Parameters.Add(new SqlParameter("@Manufacturer", viewModel.Computer.Manufacturer));
int insertedID = Convert.ToInt32(cmd.ExecuteScalar());
cmd.CommandText = @"insert into ComputerEmployee (ComputerId, EmployeeId, AssignDate) values(@ComputerId, @EmployeeId, @AssignDate)";
cmd.Parameters.Add(new SqlParameter("@ComputerId", insertedID));
cmd.Parameters.Add(new SqlParameter("@EmployeeId", viewModel.Computer.Employee.Id));
cmd.Parameters.Add(new SqlParameter("@AssignDate", DateTime.Now));
cmd.ExecuteNonQuery();
return(RedirectToAction(nameof(Index)));
}
else
{
cmd.Parameters.Clear();
cmd.CommandText = @"insert into Computer (PurchaseDate, DecomissionDate, Make, Manufacturer) OUTPUT Inserted.Id values (@PurchaseDate, @DecomissionDate, @Make, @Manufacturer);";
cmd.Parameters.Add(new SqlParameter("@PurchaseDate", viewModel.Computer.PurchaseDate));
cmd.Parameters.Add(new SqlParameter("@DecomissionDate", SqlDateTime.Null));
cmd.Parameters.Add(new SqlParameter("@Make", viewModel.Computer.Make));
cmd.Parameters.Add(new SqlParameter("@Manufacturer", viewModel.Computer.Manufacturer));
cmd.ExecuteNonQuery();
return(RedirectToAction(nameof(Index)));
}
}
}
}
catch
{
return(View());
}
}
public ActionResult Create(ComputerCreateViewModel viewModel)
{
try
{
int newId;
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO Computer (Make, Manufacturer, PurchaseDate)
OUTPUT INSERTED.Id
VALUES (@make, @manufacturer, @purchaseDate)";
cmd.Parameters.Add(new SqlParameter("@make", viewModel.Computer.Make));
cmd.Parameters.Add(new SqlParameter("@manufacturer", viewModel.Computer.Manufacturer));
cmd.Parameters.Add(new SqlParameter("@purchaseDate", viewModel.Computer.PurchaseDate));
newId = (int)cmd.ExecuteScalar();
}
}
using (SqlConnection conn2 = Connection)
{
conn2.Open();
using (SqlCommand cmd = conn2.CreateCommand())
{
if (viewModel.EmployeeId != null)
{
cmd.CommandText = @"INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate)
VALUES (@employeeId, @computerId, @assignDate)";
cmd.Parameters.Add(new SqlParameter("@employeeId", viewModel.EmployeeId));
cmd.Parameters.Add(new SqlParameter("@computerId", newId));
cmd.Parameters.Add(new SqlParameter("@assignDate", DateTime.Now));
cmd.ExecuteNonQuery();
}
}
}
return(RedirectToAction(nameof(Index)));
}
catch
{
return(View(viewModel));
}
}
// GET: Computers/Create
public ActionResult Create()
{
var viewModel = new ComputerCreateViewModel();
var employees = GetAllEmployees();
var selectItems = employees
.Select(employee => new SelectListItem
{
Text = $"{employee.FirstName} {employee.LastName}",
Value = employee.Id.ToString()
})
.ToList();
selectItems.Insert(0, new SelectListItem
{
Text = "Assign to employee...",
Value = "0"
});
viewModel.Employees = selectItems;
return(View(viewModel));
}
public async Task <ActionResult> Create(ComputerCreateViewModel computerEmployee)
{
try
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO Computer
(Make, Manufacturer, PurchaseDate, DecomissionDate)
OUTPUT INSERTED.Id
VALUES
(@make, @manufacturer, @purchaseDate, null);";
cmd.Parameters.Add(new SqlParameter("@make", computerEmployee.Computer.Make));
cmd.Parameters.Add(new SqlParameter("@manufacturer", computerEmployee.Computer.Manufacturer));
cmd.Parameters.Add(new SqlParameter("@purchaseDate", computerEmployee.Computer.PurchaseDate));
computerEmployee.Computer.Id = (int)await cmd.ExecuteScalarAsync();
cmd.CommandText = @"INSERT INTO ComputerEmployee (EmployeeId, ComputerId, AssignDate, UnassignDate)
VALUES (@employeeId, @computerId, GETDATE(), null)";
cmd.Parameters.Add(new SqlParameter("@computerId", computerEmployee.Computer.Id));
cmd.Parameters.Add(new SqlParameter("@employeeId", computerEmployee.Employee.Id));
cmd.ExecuteNonQuery();
return(RedirectToAction(nameof(Index)));
}
}
}
catch
{
return(View());
}
}
