public static void Modify(Employee employee,string firstName) { var context = new SoftUniEntities(); var newEmployee=context.Employees.FirstOrDefault(e => e.FirstName == employee.FirstName); newEmployee.FirstName = firstName; context.SaveChanges(); }
public static void Delete(Employee employee) { var context = new SoftUniEntities(); var departments = employee.Departments.ToList(); foreach (var department in departments) { department.Employee = null; } context.Employees.Remove(employee); context.SaveChanges(); }
private static void PrintNameWithNativeQuery() { var context = new SoftUniEntities(); var employees= context.Database.SqlQuery<String> ("SELECT e.FirstName from Employees e join EmployeesProjects ep on e.EmployeeID=ep.EmployeeID join Projects p on ep.ProjectID=p.ProjectID where Year(p.StartDate)='2002'"); //foreach (var employee in employees) //{ // Console.WriteLine(employee); //} }
static void Main(string[] args) { var employee = new Employee(); employee.FirstName = "Filip"; employee.LastName = "Ivanov"; employee.JobTitle = "dr."; employee.DepartmentID = 3; employee.HireDate = DateTime.Now; employee.Salary = 1234; EmployeeDAO.Add(employee); var emp=EmployeeDAO.FindByKey(3); Console.WriteLine(emp.FirstName); EmployeeDAO.Modify(employee,"Ivan"); //EmployeeDAO.Delete(employee); //Problem 3. Database Search Queries var context = new SoftUniEntities(); var employees = context.Employees.Select(e => new { e.FirstName, e.LastName, Projects=e.Projects .Where(p=>p.StartDate.Year>=2001 && p.StartDate.Year<=2003) .Select(p=>new {p.Name,p.StartDate,p.EndDate}) }); foreach (var em in employees) { foreach (var pro in em.Projects) { //Console.WriteLine("{0} {1}, Project Name:{2}---{3}-{4}",em.FirstName,em.LastName,pro.Name,pro.StartDate,pro.EndDate); } } //Task 2 var addresses = context.Addresses .OrderByDescending(a => a.Employees.Count) .ThenBy(a => a.Town.Name) .Take(10) .Select(a => new { a.AddressText, a.Town.Name, a.Employees.Count }); foreach (var address in addresses) { Console.WriteLine("{0}, {1} - {2}",address.AddressText,address.Name,address.Count); } Console.WriteLine(); // Task 3 var empl = context.Employees.Select(e => new { e.EmployeeID, e.FirstName, e.LastName, e.JobTitle, ProjectsName=e.Projects.Select(p=>new {p.Name}) }).FirstOrDefault(e => e.EmployeeID == 147); Console.WriteLine("{0} {1} {2} {3}",empl.FirstName,empl.LastName,empl.JobTitle,empl.ProjectsName.First().Name); //Task 4 var departments = context.Departments .Where(d => d.Employees.Count > 5) .Select(d => new { d.Employees.Count, d.Name, ManagerFullName = d.ManagerID, d.Employee.HireDate, d.Employee.JobTitle }) .OrderBy(p => p.Count) .GroupBy(d => d.Name); Console.WriteLine(departments.Count()); foreach (var department in departments) { foreach (var dep in department) { Console.WriteLine("--{0} - Manger:{1}, Employees:{2}",dep.Name,dep.ManagerFullName,dep.Count); } } Console.WriteLine(); //Problem 4.Native SQL Query var totalCount = context.Employees.Count(); var sw = new Stopwatch(); sw.Start(); PrintNameWithNativeQuery(); Console.WriteLine("Native: {0}", sw.Elapsed); sw.Restart(); PrintNameWithLinqQuery(); Console.WriteLine("Linq: {0}", sw.Elapsed); //Problem 6. Call a Stored Procedure Console.WriteLine(); var projects = context.usp_GetProjectsByEmployee("Guy", "Gilbert"); foreach (var project in projects) { Console.WriteLine("{0}, {1}, {2}",project.Name,project.Description,project.StartDate); } }
private static void PrintNameWithLinqQuery() { var context = new SoftUniEntities(); var employees = context.Employees.Select(e => new { e.FirstName, Projects = e.Projects.Where(p => p.StartDate.Year == 2002) }); //foreach (var employee in employees) //{ // Console.WriteLine(employee.FirstName); //} }
public static Employee FindByKey(object key) { var context = new SoftUniEntities(); Employee employee = context.Employees.FirstOrDefault(e => e.EmployeeID == (int)key); return employee; }
public static void Add(Employee employee) { var context = new SoftUniEntities(); context.Employees.Add(employee); context.SaveChanges(); }