//04.Implement previous by using native SQL query private static List<Employee> ListAllEmployeeWithProjectSQL(SoftUniEntities db) { List<Employee> employees; //BOOM!! in MSSQL Doesn't blow employees = db.Employees.SqlQuery("SELECT COUNT(Name), FirstName + ' ' + LastName FROM Employees JOIN EmployeesProjects ON Employees.EmployeeID = EmployeesProjects.EmployeeID JOIN Projects ON EmployeesProjects.ProjectID = Projects.ProjectID GROUP BY FirstName + ' ' + LastName ORDER BY COUNT(Name) DESC").ToList(); return employees; }
static void Main() { var db = new SoftUniEntities(); var firstId = 0; //EmployeeID FirstName LastName MiddleName JobTitle DepartmentID ManagerID HireDate Salary AddressID for (int i = 314; i <= 317; i++) { InsertNewEmployee(db, "Sashko", "Sashev", "Sashev", "Production Technician", "Engineering", "svetlin nakov", DateTime.Now, 1234.32, "108 Lakeside Court"); firstId = i; } for (int i = firstId; i <= firstId+3; i++) { ModifyEmployee(db, i, "Pesho", "Georgiev", "Minchev", "Production Technician", "Engineering", "svetlin nakov", DateTime.Now.AddYears(-10), 187235.32, "108 Lakeside Court"); } for (int i = firstId; i <= firstId+3; i++) { DeleteEmployee(db, i); } var employeesWithProjects = ListAllEmployeeWithProjectSQL(db).OrderByDescending(e => e.Projects.Count); foreach (var employee in employeesWithProjects) { Console.WriteLine("|{0,-5}|{1,-20}|{2,-15}|{3,-15}|{4,-25}|{5,-5}|{6,-40}|", employee.EmployeeID, employee.FirstName, employee.LastName, employee.Salary, employee.JobTitle, employee.Projects.Count, employee.Address.AddressText); Console.WriteLine(new string('-', 150)); } SelectAllManagersByDept(db, "Research and Development", "Ken Sanchez"); }
private static void InsertNewEmployee(SoftUniEntities db, string firstName, string lastName, string middleName, string jobTitle, string department, string manager, DateTime dateTime, double salary, string address) { var idDepartment = db.Departments.FirstOrDefault(d => d.Name == department).DepartmentID; var idManager = db.Employees.FirstOrDefault(e => e.FirstName + " " + e.LastName == manager).EmployeeID; var idAddress = db.Addresses.FirstOrDefault(a => a.AddressText == address).AddressID; var newEmployee = new Employee { FirstName = firstName, LastName = lastName, MiddleName = middleName, AddressID = idAddress, ManagerID = idManager, DepartmentID = idDepartment, JobTitle = jobTitle, HireDate = dateTime, Salary = (decimal)salary }; db.Employees.Add(newEmployee); db.SaveChanges(); }
private static void ModifyEmployee(SoftUniEntities db, int id, string firstName, string lastName, string middleName, string jobTitle, string department, string manager, DateTime dateTime, double salary, string address) { var employee = db.Employees.FirstOrDefault(e => e.EmployeeID == id); if (employee == null) { throw new NullReferenceException(); } employee.DepartmentID = db.Departments.FirstOrDefault(d => d.Name == department).DepartmentID; employee.ManagerID = db.Employees.FirstOrDefault(e => e.FirstName + " " + e.LastName == manager).EmployeeID; employee.AddressID = db.Addresses.FirstOrDefault(a => a.AddressText == address).AddressID; employee.FirstName = firstName; employee.LastName = lastName; employee.MiddleName = middleName; employee.Salary = (decimal)salary; employee.HireDate = dateTime; employee.JobTitle = jobTitle; db.Entry(db.Employees.FirstOrDefault(e => e.EmployeeID == id)).CurrentValues.SetValues(employee); db.SaveChanges(); }
//03. finds employees with projects private static List<Employee> ListAllEmployeeWithProject(SoftUniEntities db) { var employees = db.Employees.Where(e => e.Projects.Count != 0).ToList(); return employees; }
//05.Your task is to write a method that finds all employees by specified department (name) and manager (first name and last name). private static void SelectAllManagersByDept(SoftUniEntities db, string departmentName, string fullName) { var managerId = db.Employees.FirstOrDefault(m => m.ManagerID == null && m.FirstName + " " + m.LastName == fullName).EmployeeID; var departmentId = db.Departments.FirstOrDefault(d => d.Name == departmentName).DepartmentID; var employees = db.Employees.Where(e => e.DepartmentID == departmentId && e.ManagerID == managerId); foreach (var manager in employees) { Console.WriteLine(manager.FirstName + " " + manager.LastName); } }
private static void DeleteEmployee(SoftUniEntities db, int id) { var employee = db.Employees.FirstOrDefault(e => e.EmployeeID == id); db.Employees.Remove(employee); db.SaveChanges(); }