static void Main(string[] args)
{
using (var softUniEntities = new SoftUniEntities())
{
using (var dbContextTransaction = softUniEntities.Database.BeginTransaction())
{
try
{
var employees = new List<Employee>
{
new Employee
{
FirstName = "Gosho",
Address = new Address
{
AddressText = "str. Georgi rakovski 21",
Town = new Town
{
Name = "Kaspichan2"
}
},
LastName = "Goshev",
MiddleName = "Goshev",
DepartmentID = softUniEntities
.Departments
.FirstOrDefault(d => d.Name == "Engineering")
.DepartmentID,
JobTitle = "Momche za vsichko",
HireDate = DateTime.Now
}
};
var project = new Project
{
// Uncomment this to add project to database.
// Name = "Test Project",
Description = "My best project",
Employees = employees,
StartDate = DateTime.Now
};
softUniEntities.Projects.Add(project);
softUniEntities.SaveChanges();
dbContextTransaction.Commit();
Console.WriteLine("Project has been added to database.");
}
catch (Exception e)
{
dbContextTransaction.Rollback();
Console.WriteLine("Project is not added to database. See Exception: \n " + e.Message);
}
}
}
}
public static void RemoveTown(int townId)
{
var softUniEntities = new SoftUniEntities();
var town = softUniEntities.Towns.Find(townId);
var removedTownName = town.Name;
softUniEntities.Towns.Remove(town);
softUniEntities.SaveChanges();
Console.WriteLine(removedTownName + " is removed!");
}
public static void ChangeTownName(int townId, string newTownName)
{
var sofUniEntities = new SoftUniEntities();
var town = sofUniEntities.Towns.Find(townId);
var oldTownName = town.Name;
town.Name = newTownName;
sofUniEntities.SaveChanges();
Console.WriteLine(oldTownName + " now is: " + town.Name);
}
static void Main(string[] args)
{
// Before executing this code you must create stored procedure in sql server and update Entities model in my first project.
// You can find DDL for creating stored procedure in homework folder.
using (var softUniEntities = new SoftUniEntities())
{
var count = softUniEntities.usp_FindTotalProjectsForEmployee("Rob", "Walters").FirstOrDefault();
Console.WriteLine("Projects count: " + count);
}
}
static void Main(string[] args)
{
var softUniEntities = new SoftUniEntities();
var concurrencySoftUniEntities = new SoftUniEntities();
var guy = softUniEntities.Employees.FirstOrDefault(e => e.FirstName == "Roberto");
var guy2 = concurrencySoftUniEntities.Employees.FirstOrDefault(e => e.FirstName == "Roberto");
guy.FirstName = "Gosho";
guy2.FirstName = "Pesho";
softUniEntities.SaveChanges();
concurrencySoftUniEntities.SaveChanges();
}
public static int InsertTown(string name)
{
var softuniEntities = new SoftUniEntities();
var town = new Town
{
Name = name
};
softuniEntities.Towns.Add(town);
softuniEntities.SaveChanges();
Console.WriteLine("Town " + name + " inserted!");
return town.TownID;
}
public static void FindEmployeesByDepartmentAndManager(string departmentName, string managerFirstName, string managerLastName)
{
using (var softUniEntities = new SoftUniEntities())
{
var manager = softUniEntities
.Employees
.FirstOrDefault(e =>
e.FirstName == managerFirstName || e.LastName == managerLastName
);
var employeeByDepartmentAndManager =
softUniEntities
.Employees
.FirstOrDefault(e =>
e.Department.Name == departmentName || e.ManagerID == manager.EmployeeID
);
Console.WriteLine("Employee is " + employeeByDepartmentAndManager.FirstName + " " + employeeByDepartmentAndManager.LastName);
}
}
public static void FindEmployeesWithProjects(int projectStartDateYear)
{
var softUniEntities = new SoftUniEntities();
var query = "SELECT [e].[FirstName]" +
"FROM Employees [e]" +
"JOIN EmployeesProjects [ep]" +
"ON [ep].[EmployeeID] = [e].[EmployeeID]" +
"JOIN Projects [p]" +
"ON [p].[ProjectID] = [ep].[ProjectID]" +
"WHERE YEAR([p].[StartDate]) = '{0}'" +
"GROUP BY [e].[FirstName]" +
"ORDER BY [e].[FirstName]";
var employeeFirstNames = softUniEntities.Database.SqlQuery<string>(String.Format(query, projectStartDateYear)).ToList();
foreach (var employeeFirstName in employeeFirstNames)
{
Console.WriteLine(employeeFirstName);
}
}
public static void FindEmployeesWithProjects(int projectStartDateYear)
{
var softUniEntities = new SoftUniEntities();
var employees = softUniEntities.Employees
.Where(e => e.Projects
.Any(p => p.StartDate.Year == projectStartDateYear))
.OrderBy(e => e.FirstName)
.ToList();
foreach (var employee in employees)
{
Console.WriteLine(employee.FirstName + ": ");
foreach (var project in employee.Projects)
{
Console.WriteLine(" Project: " + project.Name + " " + project.StartDate.ToString("dd-MM-yyyy"));
}
Console.WriteLine();
}
}
public static Town GetTownByName(string townName)
{
var softUniEntities = new SoftUniEntities();
return softUniEntities.Towns.FirstOrDefault(t => t.Name == townName);
}
