public static Employee FindByKey(object key)
{
var context = new SoftUniEntities();
Employee emp = context.Employees.Find(key);
return emp;
}
public static void Delete(Employee employee)
{
var context = new SoftUniEntities();
context.Employees.Remove(employee);
context.SaveChanges();
}
public static void Add(Employee emlpoyee)
{
var context = new SoftUniEntities();
context.Employees.Add(emlpoyee);
context.SaveChanges();
}
public static void Main(string[] args)
{
var db = new SoftUniEntities();
////Problem 02
//var today = DateTime.Now;
//var testEmployee = new Employee
//{
// FirstName = "Test",
// MiddleName = "Test",
// LastName = "Test",
// JobTitle = "Test",
// DepartmentID = 1,
// HireDate = today,
// Salary = 1000
//};
//ManageEmployeesDao.InsertEmployee(db, testEmployee);
//ManageEmployeesDao.DeleteEmployee(db, "Test", "Test", "Test");
//ManageEmployeesDao.ModifyEmployee(db, 298, "FirstName", "Pesho");
//ManageEmployeesDao.PrintEmployee(db, 298);
////Problem 03
//ManageEmployeesDao.FindEmployeesStartDateInYear(db, new DateTime(2012, 01, 01));
//Problem 04
//ManageEmployeesDao.FindEmployeesStartDateInYearNativeSql(db, new DateTime(2012, 01, 01));
//Problem 05
ManageEmployeesDao.FindEmployeesByDepartmentAndManager(db, "Production", "Jo", "Brown");
}
public static void Main()
{
var db = new SoftUniEntities();
var departments =
db.Departments.Where(d => d.Employees.Count > 5).OrderBy(d => d.Employees.Count).Select(d => new
{
DepartmentName = d.Name,
MName = d.Employee.FirstName + " " + d.Employee.LastName,
Employees = d.Employees.Select(e => new
{
e.FirstName,
e.LastName,
e.HireDate,
e.JobTitle
})
.ToList()
})
.ToList();
foreach (var department in departments)
{
Console.WriteLine("-- {0} - Manager: {1}, Employees: {2}",
department.DepartmentName, department.MName, department.Employees.Count);
//Uncomment this if you want to see all employees in the current department
//foreach (var employee in department.Employees)
//{
// Console.WriteLine(employee.FirstName+" "+employee.LastName);
//}
}
}
public static void Modify(int id, string firstName)
{
var db = new SoftUniEntities();
Employee employee = db.Employees.Find(id);
employee.FirstName=firstName;
db.SaveChanges();
}
public static void Delete(int id)
{
var db = new SoftUniEntities();
Employee employee = db.Employees.Find(id);
db.Employees.Remove(employee);
db.SaveChanges();
}
public static void Add(Employee emlpoyee)
{
var context = new SoftUniEntities();
context.Employees.Add(emlpoyee);
context.SaveChanges();
}
public static void Delete(Employee employee)
{
var context = new SoftUniEntities();
context.Employees.Remove(employee);
context.SaveChanges();
}
public static void DeleteEmployee(SoftUniEntities db, string firstName, string middleName, string lastName)
{
Employee employeeToDelete =
db.Employees.First(e => e.FirstName == firstName && e.MiddleName == middleName && e.LastName == lastName);
db.Employees.Remove(employeeToDelete);
db.SaveChanges();
}
public static Employee FindByKey(object key)
{
var context = new SoftUniEntities();
Employee emp = context.Employees.Find(key);
return(emp);
}
public static void Main()
{
var db = new SoftUniEntities();
var projects = db.usp_AllProjectsForGivenEmployee("Kevin", "Brown");
foreach (var project in projects)
{
Console.WriteLine(project.Name + ", " + project.Description + ", " + project.StartDate);
}
}
public static void PrintEmployee(SoftUniEntities db, int employeeId)
{
var employeeToPrint = db.Employees.Find(employeeId);
foreach (var prop in employeeToPrint.GetType().GetProperties())
{
Console.WriteLine("{0} = {1}", prop.Name, prop.GetValue(employeeToPrint, null));
}
}
private static void PrintNamesWithNativeQuery(SoftUniEntities context)
{
string query = "SELECT e.FirstName FROM dbo.Employees e JOIN dbo.EmployeesProjects ep ON ep.EmployeeID = e.EmployeeID JOIN dbo.Projects p ON p.ProjectID = ep.ProjectID GROUP BY e.FirstName, p.StartDate HAVING YEAR(p.StartDate) = 2002";
var nativeQueryResult = context.Database.SqlQuery <string>(query);
foreach (var item in nativeQueryResult)
{
Console.WriteLine(item);
}
}
public static void Main()
{
var db = new SoftUniEntities();
var addresses = db.Addresses.OrderByDescending(a => a.Employees.Count).ThenBy(a => a.Town.Name).Take(10);
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2} employees", address.AddressText, address.Town.Name, address.Employees.Count);
}
}
public static void Main()
{
var db1 = new SoftUniEntities();
var db2 = new SoftUniEntities();
db1.Employees.Find(1).FirstName = "Ivan";
db2.Employees.Find(1).FirstName = "Gosho";
db1.SaveChanges();
db2.SaveChanges();
//Gosho has overwritten the first name in Employee Table and Ivan was completely ignored.
}
static void CallStoredProcedure(string firstName, string lastName)
{
var context = new SoftUniEntities();
var projects = context.usp_GetProjectsByEmployee(firstName, lastName);
foreach (var p in projects)
{
Console.WriteLine(" {0} - {1}, {2}", p.Name, p.Description, p.StartDate);
}
}
static void CallStoredProcedure(string firstName, string lastName)
{
var context = new SoftUniEntities();
var projects = context.usp_GetProjectsByEmployee(firstName, lastName);
foreach (var p in projects)
{
Console.WriteLine(" {0} - {1}, {2}", p.Name, p.Description, p.StartDate);
}
}
public static void FindEmployeesStartDateInYearNativeSql(SoftUniEntities db, DateTime date)
{
var year = date.Year;
const string SqlQuery = "SELECT e.FirstName, e.LastName, p.Name, p.StartDate FROM Employees e JOIN EmployeesProjects ep ON e.EmployeeID = ep.EmployeeID JOIN Projects p ON ep.ProjectID = p.ProjectID WHERE p.StartDate BETWEEN ('2002-01-01') AND ('2002-12-31')";
var employeesWProjectsWStartDateInYear = db.Database.SqlQuery <string>(SqlQuery).ToList();
foreach (var employee in employeesWProjectsWStartDateInYear)
{
Console.WriteLine(employee);
}
}
public static void ModifyEmployee(SoftUniEntities db, int employeeId, string propertyName, object newValue)
{
var employeeToModify = db.Employees.Find(employeeId);
if (employeeToModify != null)
{
employeeToModify.GetType().GetProperty(propertyName).SetValue(employeeToModify, newValue);
db.SaveChanges();
}
else
{
Console.WriteLine("No such employee.");
}
}
private static void PrintNamesWithLINQ(SoftUniEntities context)
{
var emps = context.Employees
.Where(e => e.Projects.Any(p => p.StartDate.Year == 2002))
.Select(e => new
{
e.FirstName
});
foreach (var e in emps)
{
Console.WriteLine(e.FirstName);
}
}
public static void Add(string firstName, string lastName, string jobTitle, string department, DateTime hireDate, int salary)
{
var db = new SoftUniEntities();
var depName = db.Departments.First(d => d.Name == department.ToString());
var employee = new Employee()
{
FirstName = firstName,
LastName = lastName,
JobTitle = jobTitle,
DepartmentID = depName.DepartmentID,
HireDate = hireDate,
Salary = salary
};
db.Employees.Add(employee);
db.SaveChanges();
}
public static void FindEmployeesStartDateInYear(SoftUniEntities db, DateTime date)
{
var year = date.Year;
var employeesWProjectsWStartDateInYear =
db.Employees.Where(e => e.Projects.Any(p => p.StartDate.Year == year));
foreach (var employee in employeesWProjectsWStartDateInYear)
{
Console.WriteLine("Employee Name: {0} {1}", employee.FirstName, employee.LastName);
Console.WriteLine("Projects Names: ");
foreach (var project in employee.Projects)
{
Console.WriteLine("{0}; Start Date: {1}", project.Name, project.StartDate);
}
Console.WriteLine();
}
}
public static void Main()
{
var db = new SoftUniEntities();
var employee = db.Employees
.Where(e => e.EmployeeID == 147)
.Select(e => new
{
e.FirstName,
e.LastName,
e.JobTitle,
Projects = e.Projects.OrderBy(p=>p.Name)
});
foreach (var emp in employee)
{
Console.WriteLine("{0} {1} - {2}",emp.FirstName,emp.LastName,emp.JobTitle);
foreach(var projects in emp.Projects)
{
Console.WriteLine(projects.Name);
}
}
}
public static void FindEmployeesByDepartmentAndManager(
SoftUniEntities db,
string departmentName,
string managerFirstName,
string managerLastName)
{
var manager =
db.Employees.FirstOrDefault(m => m.FirstName == managerFirstName && m.LastName == managerLastName);
var employees =
db.Employees.Where(e => e.Department.Name == departmentName && e.ManagerID == manager.EmployeeID);
foreach (var employee in employees)
{
Console.WriteLine("Employee Name: {0} {1}", employee.FirstName, employee.LastName);
Console.WriteLine("Department Name: {0}", employee.Department.Name);
if (manager != null)
{
Console.WriteLine("Manager Name: {0} {1}", manager.FirstName, manager.LastName);
}
Console.WriteLine();
}
}
public static void Main()
{
var db = new SoftUniEntities();
var employees = db.Employees
.Where(e => e.Projects.Any(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003))
.Select(e => new
{
e.FirstName,
e.LastName,
Projects = e.Projects.Where(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003),
MFirstName = e.Employee1.FirstName,
MLastName = e.Employee1.LastName
});
foreach(var employee in employees)
{
Console.WriteLine("-- Employee: {0} {1} || Manager: {2} {3}", employee.FirstName, employee.LastName,employee.MFirstName,employee.MLastName);
foreach(var project in employee.Projects)
{
Console.WriteLine("* {0} {1} - {2}", project.Name, project.StartDate, project.EndDate);
}
Console.WriteLine();
}
}
static void Main()
{
//step 1
var context = new SoftUniEntities();
var employeeNames = context.Employees
.Where(e => e.Salary > 50000)
.Select(e => e.FirstName);
var employees =
from e in context.Employees
where e.Salary > 50000
select e.FirstName;
foreach (var item in employees)
{
Console.WriteLine(item);
}
// task 3
// task 4
var context = new SoftUniEntities();
var address = new Address()
{
AddressText = "Vitoshka 15",
TownID = 4
};
Employee employee = context.Employees
.Where(e => e.LastName == "Nakov")
.FirstOrDefault();
employee.Address = address;
context.SaveChanges();
var empl = context.Employees.FirstOrDefault(e => e.LastName == "Nakov");
Console.WriteLine(empl.Address.AddressText);
// Task 5- NO
var context = new SoftUniEntities();
var project = context.Projects.Find(2);
var empProjects = project.Employees.ToList();
foreach (var empl in empProjects)
{
if (empl.Projects.Contains(project))
{
context.Employees.Remove(empl);
};
}
// context.Projects.Remove(project);
context.SaveChanges();
}
public static void InsertEmployee(SoftUniEntities db, Employee employee)
{
db.Employees.Add(employee);
db.SaveChanges();
}
public static void Modify(Employee employee)
{
var context = new SoftUniEntities();
}
public static Employee FindByID(int id)
{
var db = new SoftUniEntities();
Employee employee = db.Employees.Find(id);
return employee;
}
static void Main()
{
var context = new SoftUniEntities();
//// Problem 2
/* Employee michael = new Employee()
{
FirstName = "Michael",
LastName = "Jordanov",
JobTitle = "playa",
DepartmentID = 2,
HireDate = DateTime.Now,
Salary = 1000000
};
DAOClass.Add(michael);
DAOClass.Delete(michael);*/
//// Problem 3
// 3.1
/*
var employees = context.Employees
.Where(e => e.Projects.Any(p => p.StartDate >= new DateTime(2001, 1, 1) && p.EndDate < new DateTime(2004, 1, 1)))
.Select(e => new
{
e.FirstName,
e.LastName,
ManagerName = e.Manager.FirstName + " " + e.Manager.LastName,
Projects = e.Projects.Where(p => p.StartDate >= new DateTime(2001, 1, 1) && p.EndDate < new DateTime(2004, 1, 1))
.Select(p => new
{
p.Name,
p.StartDate,
p.EndDate
})
});
foreach (var e in employees)
{
Console.WriteLine("Employee: {0} {1}, manager: {2}", e.FirstName, e.LastName, e.ManagerName);
Console.WriteLine("Projects:");
foreach (var p in e.Projects)
{
Console.WriteLine("{0}: start: {1}, end: {2}", p.Name, p.StartDate, p.EndDate);
}
Console.WriteLine();
}
*/
// 3.2
/*
var addresses = context.Addresses
.OrderByDescending(a => a.Employees.Count)
.ThenBy(a => a.Town.Name)
.Select(a => new
{
Address = a.AddressText,
Town = a.Town.Name,
EmployeeCount = a.Employees.Count
})
.Take(10);
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2} employees",
address.Address,
address.Town,
address.EmployeeCount
);
}
*/
// 3.3
/*
var employeeById = context.Employees
.Where(e => e.EmployeeID == 147)
.Select(e => new
{
e.FirstName,
e.LastName,
e.JobTitle,
Projets = e.Projects.OrderBy(p => p.Name).Select(p => p.Name)
});
foreach (var e in employeeById)
{
Console.WriteLine("{0} {1}, {2}", e.FirstName, e.LastName, e.JobTitle);
Console.WriteLine("Projects:");
foreach (var p in e.Projets)
{
Console.WriteLine(p);
}
}
*/
// 3.4
/*
var departments = context.Departments
.Where(d => d.Employees.Count > 5)
.OrderBy(d => d.Employees.Count)
.Select(d => new
{
DepartmentName = d.Name,
DepartmentManager = d.Manager.LastName,
EmployeesCount = d.Employees.Count,
Employees = d.Employees
});
foreach (var d in departments)
{
Console.WriteLine("--{0} - Manager: {1}, Employees: {2}",d.DepartmentName, d.DepartmentManager, d.EmployeesCount);
Console.WriteLine("Employees:");
foreach (var e in d.Employees)
{
Console.WriteLine("{0} {1}, hire date: {2}, job: {3}",
e.FirstName, e.LastName, e.HireDate, e.JobTitle);
}
Console.WriteLine();
}
*/
//// Problem 4
//var sw = new Stopwatch();
//sw.Start();
//PrintNamesWithNativeQuery(context);
//Console.WriteLine("Native: {0}", sw.Elapsed);
//sw.Restart();
//PrintNamesWithLINQ(context);
//Console.WriteLine("LINQ: {0}", sw.Elapsed);
//// Problem 6
// updated model from database to add the stored procedures
CallStoredProcedure("Ruth", "Ellerbrock");
}
static void Main()
{
var context = new SoftUniEntities();
//// Problem 2
/* Employee michael = new Employee()
* {
* FirstName = "Michael",
* LastName = "Jordanov",
* JobTitle = "playa",
* DepartmentID = 2,
* HireDate = DateTime.Now,
* Salary = 1000000
* };
* DAOClass.Add(michael);
*
* DAOClass.Delete(michael);*/
//// Problem 3
// 3.1
/*
* var employees = context.Employees
* .Where(e => e.Projects.Any(p => p.StartDate >= new DateTime(2001, 1, 1) && p.EndDate < new DateTime(2004, 1, 1)))
* .Select(e => new
* {
* e.FirstName,
* e.LastName,
* ManagerName = e.Manager.FirstName + " " + e.Manager.LastName,
* Projects = e.Projects.Where(p => p.StartDate >= new DateTime(2001, 1, 1) && p.EndDate < new DateTime(2004, 1, 1))
* .Select(p => new
* {
* p.Name,
* p.StartDate,
* p.EndDate
* })
* });
*
* foreach (var e in employees)
* {
* Console.WriteLine("Employee: {0} {1}, manager: {2}", e.FirstName, e.LastName, e.ManagerName);
* Console.WriteLine("Projects:");
* foreach (var p in e.Projects)
* {
* Console.WriteLine("{0}: start: {1}, end: {2}", p.Name, p.StartDate, p.EndDate);
* }
* Console.WriteLine();
* }
*/
// 3.2
/*
* var addresses = context.Addresses
* .OrderByDescending(a => a.Employees.Count)
* .ThenBy(a => a.Town.Name)
* .Select(a => new
* {
* Address = a.AddressText,
* Town = a.Town.Name,
* EmployeeCount = a.Employees.Count
* })
* .Take(10);
*
*
* foreach (var address in addresses)
* {
* Console.WriteLine("{0}, {1} - {2} employees",
* address.Address,
* address.Town,
* address.EmployeeCount
* );
* }
*/
// 3.3
/*
* var employeeById = context.Employees
* .Where(e => e.EmployeeID == 147)
* .Select(e => new
* {
* e.FirstName,
* e.LastName,
* e.JobTitle,
* Projets = e.Projects.OrderBy(p => p.Name).Select(p => p.Name)
* });
*
* foreach (var e in employeeById)
* {
* Console.WriteLine("{0} {1}, {2}", e.FirstName, e.LastName, e.JobTitle);
* Console.WriteLine("Projects:");
* foreach (var p in e.Projets)
* {
* Console.WriteLine(p);
* }
* }
*/
// 3.4
/*
* var departments = context.Departments
* .Where(d => d.Employees.Count > 5)
* .OrderBy(d => d.Employees.Count)
* .Select(d => new
* {
* DepartmentName = d.Name,
* DepartmentManager = d.Manager.LastName,
* EmployeesCount = d.Employees.Count,
* Employees = d.Employees
* });
*
* foreach (var d in departments)
* {
* Console.WriteLine("--{0} - Manager: {1}, Employees: {2}",d.DepartmentName, d.DepartmentManager, d.EmployeesCount);
* Console.WriteLine("Employees:");
* foreach (var e in d.Employees)
* {
* Console.WriteLine("{0} {1}, hire date: {2}, job: {3}",
* e.FirstName, e.LastName, e.HireDate, e.JobTitle);
* }
* Console.WriteLine();
* }
*/
//// Problem 4
//var sw = new Stopwatch();
//sw.Start();
//PrintNamesWithNativeQuery(context);
//Console.WriteLine("Native: {0}", sw.Elapsed);
//sw.Restart();
//PrintNamesWithLINQ(context);
//Console.WriteLine("LINQ: {0}", sw.Elapsed);
//// Problem 6
// updated model from database to add the stored procedures
CallStoredProcedure("Ruth", "Ellerbrock");
}
private static void PrintNamesWithNativeQuery(SoftUniEntities context)
{
string query = "SELECT e.FirstName FROM dbo.Employees e JOIN dbo.EmployeesProjects ep ON ep.EmployeeID = e.EmployeeID JOIN dbo.Projects p ON p.ProjectID = ep.ProjectID GROUP BY e.FirstName, p.StartDate HAVING YEAR(p.StartDate) = 2002";
var nativeQueryResult = context.Database.SqlQuery<string>(query);
foreach (var item in nativeQueryResult)
{
Console.WriteLine(item);
}
}
static void Main()
{
//step 1
var context = new SoftUniEntities();
var employeeNames = context.Employees
.Where(e => e.Salary > 50000)
.Select(e => e.FirstName);
var employees =
from e in context.Employees
where e.Salary > 50000
select e.FirstName;
foreach (var item in employees)
{
Console.WriteLine(item);
}
// task 3
// task 4
var context = new SoftUniEntities();
var address = new Address()
{
AddressText = "Vitoshka 15",
TownID = 4
};
Employee employee = context.Employees
.Where(e => e.LastName == "Nakov")
.FirstOrDefault();
employee.Address = address;
context.SaveChanges();
var empl = context.Employees.FirstOrDefault(e => e.LastName == "Nakov");
Console.WriteLine(empl.Address.AddressText);
// Task 5- NO
var context = new SoftUniEntities();
var project = context.Projects.Find(2);
var empProjects = project.Employees.ToList();
foreach (var empl in empProjects)
{
if (empl.Projects.Contains(project))
{
context.Employees.Remove(empl);
}
;
}
// context.Projects.Remove(project);
context.SaveChanges();
}
private static void PrintNamesWithLINQ(SoftUniEntities context)
{
var emps = context.Employees
.Where(e => e.Projects.Any(p => p.StartDate.Year == 2002))
.Select(e => new
{
e.FirstName
});
foreach (var e in emps)
{
Console.WriteLine(e.FirstName);
}
}
public static void Modify(Employee employee)
{
var context = new SoftUniEntities();
}
