public static void AddNewProject( string projectName, DateTime startDate, DateTime? endDate = null, string projectDescription = null, params int[] employeesId ) { var project = new Project { Name = projectName, Description = projectDescription, StartDate = startDate, EndDate = endDate, }; using (var db = new SoftUniEntities()) { foreach (var id in employeesId) { project.Employees.Add(db.Employees.Find(id)); } db.Projects.Add(project); db.SaveChanges(); } }
public static IEnumerable FindAllEmplooyeesWithProjectsSql() { var query = @"SELECT * FROM [SoftUni].[dbo].[Employees] e JOIN EmployeesProjects ep ON e.EmployeeID = ep.EmployeeID JOIN Projects p ON p.ProjectID = ep.ProjectID WHERE YEAR(p.StartDate) >= 2002"; using (var db = new SoftUniEntities()) { var emoloyeesWithProjects = db.Database.SqlQuery<Employee>(query).ToList(); var employees = new Dictionary<int, string>(); foreach (var employee in emoloyeesWithProjects) { if (!employees.ContainsKey(employee.EmployeeID)) { employees.Add(employee.EmployeeID, String.Format("{0} {1}", employee.FirstName, employee.LastName)); } } return employees; } }
//06.Adding a new address and updating Employee private static void AddAddress(SoftUniEntities context) { //var address = new Address() //{ // AddressText = "Vitoshka 15", // TownID = 4 //}; //context.Addresses.Add(address); //context.SaveChanges(); //Employee emp = context.Employees.FirstOrDefault(e => e.LastName == "Nakov"); //emp.Address = address; //context.SaveChanges(); var address = new Address() { AddressText = "Vitoshka 15", TownID = 4 }; Employee emp = context.Employees.FirstOrDefault(e => e.LastName == "Nakov"); emp.Address = address; context.SaveChanges(); var addresses = context.Employees.OrderByDescending(e => e.AddressID).Take(10).ToList(); foreach (var e in addresses) { Console.WriteLine(e.Address.AddressText); } }
private static void CallStoredProcedure(SoftUniEntities context) { string[] name = Console.ReadLine().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).ToArray(); SqlParameter firstName = new SqlParameter("@firstName", name[0]); SqlParameter lastName = new SqlParameter("@lastName", name[1]); //Procedure body //CREATE PROCEDURE GetEmployeeProjects @firstName NVARCHAR(MAX), //@lastName NVARCHAR(MAX) //AS //SELECT CONCAT(p.Name, ' - ', SUBSTRING(p.Description, 1, 30), '..., ', p.StartDate) FROM Employees AS e //LEFT JOIN EmployeesProjects AS ep //ON e.EmployeeID = ep.EmployeeID //LEFT JOIN Projects AS p //ON ep.ProjectID = p.ProjectID //WHERE e.FirstName = @firstName and e.LastName = @lastName var projects = context.Database.SqlQuery <string>($"execute GetEmployeeProjects @firstName,@lastName", firstName, lastName).ToList(); foreach (var projcet in projects) { Console.WriteLine(projcet); } }
public static DbRawSqlQuery<Project> GetAllProjectsForGivenEmployee(string firstName, string lastName) { var db = new SoftUniEntities(); var projects = db.Database.SqlQuery<Project>("upsGetAllProjectsForGivenEmployee @firstName = {0}, @lastName = {1}", firstName, lastName); return projects; }
public static void Add(Employee employee) { var context = new SoftUniEntities(); context.Employees.Add(employee); context.SaveChanges(); }
public static void Delete(Employee employee) { var context = new SoftUniEntities(); context.Employees.Remove(employee); context.SaveChanges(); }
public static void DeleteEmployee(int employeeId) { using (var db = new SoftUniEntities()) { db.Employees.Remove(db.Employees.Find(employeeId)); db.SaveChanges(); } }
public static void InsertEmployee(Employee employee) { using (var db = new SoftUniEntities()) { db.Employees.Add(employee); db.SaveChanges(); } }
//13.Find Employee by First name starting with 'SA' private static void FindEmployeeByFirstName(SoftUniEntities context) { var employees = context.Employees.Where(e => e.FirstName.ToLower().StartsWith("sa")); foreach (var e in employees) { Console.WriteLine($"{e.FirstName} {e.LastName} - {e.JobTitle} - (${e.Salary:F4})"); } }
public static void Modify(Employee employee) { var context = new SoftUniEntities(); context.Employees.Attach(employee); context.Entry(employee).State = EntityState.Modified; context.SaveChanges(); }
public static IEnumerable FindAllEmployeesWithProjectsAfter(int year) { using (var db = new SoftUniEntities()) { var employees = db.Employees.Where(p => p.Projects.All(q => q.StartDate.Year >= year)).ToList(); return employees; } }
public static ICollection EmployeesByDepartmentAndManager(string departmentName, string managerFirstName, string managerLastName) { using (var db = new SoftUniEntities()) { var employees = db.Employees.Where(p => p.Departments.All(q => q.Name == departmentName) && p.FirstName == managerFirstName && p.LastName == managerLastName).ToList(); return employees; } }
static void Main(string[] args) { var context = new SoftUniEntities(); //17.Call Stored Procedure //CallStoredProcedure(context); //18.Employees Max Salaries MaxSalaries(context); }
//09.Employee with id 147 private static void EmployeeWithId(SoftUniEntities context) { var e = context.Employees.Find(147); Console.WriteLine($"{e.FirstName} {e.LastName} {e.JobTitle}"); foreach (var p in e.Projects.OrderBy(p => p.Name)) { Console.WriteLine($"{p.Name}"); } }
//04.Employees with salary over 50 000 private static void SalaryOver(SoftUniEntities context) { var employees = context.Employees .Where(e => e.Salary > 50000) .Select(c => c.FirstName); foreach (var emp in employees) { Console.WriteLine(emp); } }
private static void MaxSalaries(SoftUniEntities context) { foreach (var dep in context.Departments) { decimal maxSalary = context.Employees.Where(e => e.Department.Name == dep.Name).ToList().Max(x => x.Salary); if (maxSalary < 30000 || maxSalary > 70000) { Console.WriteLine($"{dep.Name} - {maxSalary:F2}"); } } }
//05.Employees from Seattle private static void EmployeesFromSeattle(SoftUniEntities context) { var employees = context.Employees .Where(e => e.Department.Name == "Research and Development") .OrderBy(e => e.Salary) .ThenByDescending(e => e.FirstName); foreach (var emp in employees) { Console.WriteLine($"{emp.FirstName} {emp.LastName} from {emp.Department.Name} - ${emp.Salary:F2}"); } }
//08.Address bt Town Name private static void AddressByTown(SoftUniEntities context) { var addresses = context.Addresses .OrderByDescending(a => a.Employees.Count) .ThenBy(t => t.Town.Name) .Take(10) .ToList(); foreach (var a in addresses) { Console.WriteLine($"{a.AddressText}, {a.Town.Name} - {a.Employees.Count} employees"); } }
//11.Find latest 10 projects private static void FindLatestProjects(SoftUniEntities context) { CultureInfo ci = new CultureInfo("en-US"); Thread.CurrentThread.CurrentCulture = ci; var projects = context.Projects .OrderByDescending(p => p.StartDate) .Take(10) .OrderBy(p => p.Name); foreach (var p in projects) { Console.WriteLine($"{p.Name} {p.Description} {p.StartDate:M/d/yyyy h:mm:ss tt} {p.EndDate:M/d/yyyy h:mm:ss tt}"); } }
public void Initialize() { this.db = new SoftUniEntities(); this.employee = new Employee { EmployeeID = int.MaxValue, FirstName = "Test", MiddleName = "User", LastName = "Name", DepartmentID = 1, JobTitle = "CCleaner", HireDate = new DateTime(2000, 03, 07), Salary = 2000.50m }; }
//07.Find Employees in period private static void EmployeesInPeriod(SoftUniEntities context) { var employyes = context.Employees .Where(e => e.Projects.Count(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003) > 0) .Take(30); foreach (var e in employyes) { Console.WriteLine($"{e.FirstName} {e.LastName} {e.Employee1.FirstName}"); foreach (var p in e.Projects) { Console.WriteLine($"--{p.Name} {p.StartDate} {p.EndDate}"); } } }
//10.Departments with more than 5 employees private static void DepartmentsWithMoreEmployees(SoftUniEntities context) { var department = context.Departments .Where(d => d.Employees.Count > 5) .OrderBy(c => c.Employees.Count).ToList(); foreach (var d in department) { Console.WriteLine($"{d.Name} {d.Employee.FirstName}"); foreach (var em in d.Employees) { Console.WriteLine($"{em.FirstName} {em.LastName} {em.JobTitle}"); } } }
//15.Delete project by ID private static void DeletProject(SoftUniEntities context) { var projects = context.Projects.Find(2); foreach (var emp in projects.Employees) { emp.Projects.Remove(projects); } context.Projects.Remove(projects); context.SaveChanges(); var result = context.Projects.Take(10); foreach (var pr in result) { Console.WriteLine(pr.Name); } }
//12.Increase Salary private static void IncreaseSalary(SoftUniEntities context) { IEnumerable <Employee> employees = context.Employees .Where( e => e.Department.Name == "Engineering" || e.Department.Name == "Tool Design" || e.Department.Name == "Marketing" || e.Department.Name == "Information Services"); foreach (Employee e in employees) { e.Salary += e.Salary * 0.12M; Console.WriteLine($"{e.FirstName} {e.LastName} (${e.Salary})"); } context.SaveChanges(); }
public static void Main(string[] args) { var grContext = new GringottsContext(); var context = new SoftUniEntities(); //FullInformation(context); //SalaryOver(context); //EmployeesFromSeattle(context); //AddAddress(context); //EmployeesInPeriod(context); //AddressByTown(context); //EmployeeWithId(context); //DepartmentsWithMoreEmployees(context); //FindLatestProjects(context); //IncreaseSalary(context); //FindEmployeeByFirstName(context); //FirstLetter(grContext); //DeletProject(context); }
//03.Employees Full Information private static void FullInformation(SoftUniEntities context) { var employes = context.Employees .Select(c => new { c.EmployeeID, c.FirstName, c.LastName, c.MiddleName, c.JobTitle, c.Salary }) .OrderBy(e => e.EmployeeID) .ToList(); foreach (var emp in employes) { Console.WriteLine($"{emp.FirstName} {emp.LastName} {emp.MiddleName} {emp.JobTitle} {emp.Salary}"); } }
public static void Main() { using (var db = new SoftUniEntities()) { //var employee = db.Employees.Find(1); //Employee.ModifyEmployee(employee, FirstName: "Gay"); // TEST //Console.WriteLine(db.Employees.Find(1).FirstName); // TEST //Console.WriteLine(Employee.EntityObjectToString(employee)); //var emoloyeesWithProjects = Employee.FindAllEmployeesWithProjectsAfter(2002); //var emoloyeesWithProjects = Employee.FindAllEmplooyeesWithProjectsSql(); //foreach (var emp in emoloyeesWithProjects) //{ // TEST //Console.WriteLine(emp); //} // TEST //Employee.EmployeesByDepartmentAndManager("", "", "") // TEST //Project.AddNewProject("Nina", DateTime.Now, employeesId: new []{1, 2, 3}); var projects = Project.GetAllProjectsForGivenEmployee("Gay", "Gilbert"); foreach (Project project in projects) { Console.WriteLine("Name: {0}; Description: {1}; Start Date: {2}; End Date: {3}", project.Name, project.Description ?? "None", project.StartDate, project.EndDate); } } }
public static void ModifyEmployee( Employee employee, string FirstName = null, string MiddleName = null, string LastName = null, string JobTitle = null, int? DepartmentID = null, int? ManagerID = null, DateTime? HireDate = null, decimal? Salary = null, int? AddressID = null ) { using (var db = new SoftUniEntities()) { var emp = db.Employees.Find(employee.EmployeeID); if (FirstName != null) { emp.FirstName = FirstName; } if (MiddleName != null) { emp.MiddleName = MiddleName; } if (LastName != null) { emp.LastName = LastName; } if (JobTitle != null) { emp.JobTitle = JobTitle; } if (DepartmentID != null) { emp.DepartmentID = (int)DepartmentID; } if (ManagerID != null) { emp.ManagerID = ManagerID; } if (HireDate != null) { emp.HireDate = (DateTime)HireDate; } if (Salary != null) { emp.Salary = (decimal)Salary; } if (AddressID != null) { emp.AddressID = AddressID; } db.SaveChanges(); } }
public static void Main() { // Problem 3 var context = new SoftUniEntities(); // Task 1 var employees = context.Employees .Where(e => e.Projects.Count(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003) > 0) .Select(e => new { e.FirstName, e.LastName, Manager = context.Employees.Where(m => m.EmployeeID == e.ManagerID).Select(m => m.FirstName + " " + m.LastName).FirstOrDefault(), Projects = e.Projects.Select(p => new { p.Name, p.StartDate, p.EndDate }) }); // Task 2 var addresses = context.Addresses .OrderByDescending(a => a.Employees.Count) .ThenBy(a => a.Town.Name).Take(10) .Select(a => new { a.AddressText, Town = a.Town.Name, EmployeeCount = a.Employees.Count }); foreach (var address in addresses) { Console.WriteLine("{0}, {1} - {2} employees", address.AddressText, address.Town, address.EmployeeCount); } // Task 3 var employee = context.Employees.Where(e => e.EmployeeID == 147).Select(e => new { e.FirstName, e.LastName, e.JobTitle, Projects = e.Projects.OrderByDescending(p => p.Name).Select(p => p.Name) }).FirstOrDefault(); Console.WriteLine("{0} {1} ({2}) - {3}", employee.FirstName, employee.LastName, employee.JobTitle, string.Join(", ", employee.Projects)); // Task 4 var departments = context.Departments .Where(d => d.Employees.Count > 5) .OrderBy(d => d.Employees.Count) .Select(d => new { d.Name, Manager = context.Employees.Where(m => m.EmployeeID == d.ManagerID).Select(m => m.FirstName + " " + m.LastName).FirstOrDefault(), Employees = d.Employees.Select(e => new { e.FirstName, e.LastName, e.HireDate, e.JobTitle }) }); foreach (var department in departments) { Console.WriteLine("--{0} - Manager: {1}, Employees: {2}", department.Name, department.Manager, department.Employees.Count()); } }
public static Employee FindByKey(object key) { var context = new SoftUniEntities(); return(context.Employees.Find(key)); }