public static void InsertEmployee(Employee employee)
{
var db = new SoftUniEntities();
db.Employees.Add(employee);
db.SaveChanges();
Console.WriteLine(employee.FirstName + " " + employee.LastName + " Inserted!!!");
}
static void ProjectAdderExplicitlyStartTransaction()
{
var db = new SoftUniEntities();
using (var transaction = db.Database.BeginTransaction())
{
var firstEmployee = db.Employees.Find(121);
var lastEmployee = db.Employees.Find(3);
var project = new Project
{
Name = "DatabaseTeamWork2",
StartDate = new DateTime(2015, 2, 15, 0, 0, 0),
EndDate = new DateTime(2015, 3, 20, 0, 0, 0),
Employees = new Collection<Employee>
{
firstEmployee,
lastEmployee
},
Description = "TeamWork2"
};
try
{
db.Projects.Add(project);
db.SaveChanges();
transaction.Commit();
Console.WriteLine("Adding end succesfully => Commit Transaction2");
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine("Adding finish unsuccesfully => Rollback Transaction2");
}
}
}
public static void CallStoreProcedure(string firstName, string lastName)
{
using (var db = new SoftUniEntities())
{
var projectCount = db.usp_ProjectsOfEmployee(firstName, lastName).Single();
Console.WriteLine(string.Format("{0} {1} has {2} projects!", firstName, lastName, projectCount));
}
}
public static void UpdateEmployeeSalaryByEmployeeId(int id, decimal newSalary)
{
var db = new SoftUniEntities();
var employee = db.Employees.Find(id);
employee.Salary = newSalary;
db.SaveChanges();
Console.WriteLine(employee.FirstName + " " + employee.LastName + " new salary -> " + newSalary);
}
public static void RemoveEmployeeById(int employeeId)
{
var db = new SoftUniEntities();
var employee = db.Employees.Find(employeeId);
db.Employees.Remove(employee);
db.SaveChanges();
Console.WriteLine(employee.FirstName + " " + employee.LastName + " Deleted!!!");
}
public static void Main()
{
using (var db = new SoftUniEntities())
{
foreach (var emp in db.Employees)
{
Console.WriteLine(emp.FirstName + " " + emp.LastName);
}
}
}
static void FindEmployeesByDepartmentAndManager(string departmentName, string managerFirstName, string managerLastName)
{
using (var db = new SoftUniEntities())
{
var employees = db.Employees.Where(
e => e.Department.Name == departmentName &&
e.ManagerID == (db.Employees.FirstOrDefault(m => m.FirstName == managerFirstName ||
m.LastName == managerLastName).EmployeeID));
foreach (var employee in employees)
{
Console.WriteLine(employee.FirstName + " " + employee.LastName + " -> " + employee.Department.Name);
}
}
}
public static void FindEmployeesWithProjects(int projectStartDateYear)
{
var db = new SoftUniEntities();
var employees = from e in db.Employees
where e.Projects.Any(p => p.StartDate.Year == projectStartDateYear)
select e;
foreach (var employee in employees)
{
Console.WriteLine(employee.FirstName + " " + employee.LastName);
}
Console.WriteLine(employees.Count());
}
public static void Main()
{
using (var db = new SoftUniEntities())
{
var query = @"SELECT *
FROM Employees e JOIN EmployeesProjects ep
ON e.EmployeeID = ep.EmployeeID
JOIN Projects p
ON ep.ProjectID = p.ProjectID
WHERE YEAR(p.StartDate) = '2002'";
var employees = db.Employees.SqlQuery(query).Distinct();
foreach (var employee in employees)
{
Console.WriteLine(employee.FirstName + " " + employee.LastName);
}
Console.WriteLine("Employees Count: " + employees.Count());
}
}