static void Main(string[] args) { var context = new SoftUniEntities(); var totalCount = context.Employees.Count(); var sw = new Stopwatch(); sw.Start(); PrintNamesWithNativeQuery(); Console.WriteLine("\nNative: {0}\n\n", sw.Elapsed); sw.Restart(); PrintNamesWithLinqQuery_LINQtoEntities(); Console.WriteLine("\n\nLINQtoEntities: {0}\n\n", sw.Elapsed); sw.Restart(); PrintNamesWithLinqQuery_WithExtensionMethods(); Console.WriteLine("\n\nLINQWithExtensionMethods: {0}", sw.Elapsed); sw.Stop(); }
public static void DepartmentsAndEmployees() { var db = new SoftUniEntities(); using (db) { var departments = db.Departments .Where(d => d.Employees.Count > 5) .Join(db.Employees, (d => d.ManagerID), (m => m.EmployeeID), (d, m) => new { DepartmentName = d.Name, ManagerName = m.LastName, Employees = d.Employees.Select(e => new { e.FirstName, e.LastName, e.HireDate, e.JobTitle }) }) .OrderBy(d => d.Employees.Count()); Console.WriteLine(departments.Count()); foreach (var department in departments) { Console.WriteLine("--{0} - Manager: {1}, Employees: {2}", department.DepartmentName, department.ManagerName, department.Employees.Count()); } } }
public static void EmployeesProjects() { var db = new SoftUniEntities(); using (db) { var employees = db.Employees .Where(e => e.Projects .Any(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003)) .Join(db.Employees, (e => e.ManagerID), (m => m.EmployeeID), (e, m) => new { ManagerName = m.FirstName + " " + m.LastName, Projects = e.Projects }) .ToList(); foreach (var employee in employees) { foreach (var project in employee.Projects) { Console.WriteLine("Project Name: {0}", project.Name); Console.WriteLine("Start Date: {0:dd-MM-yyyy}", project.StartDate); Console.WriteLine("End Date: {0:dd-MM-yyyy}", project.EndDate); Console.WriteLine("Project Manager: {0}", employee.ManagerName); Console.WriteLine(); } } } }
public static void EmployeeData() { var db = new SoftUniEntities(); using (db) { 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) }) .FirstOrDefault(); Console.WriteLine("Name: {0} {1}", employee.FirstName, employee.LastName); Console.WriteLine("Job Title: {0}", employee.JobTitle); Console.WriteLine("Projects:"); foreach (var project in employee.Projects) { Console.WriteLine("-- " + project.Name); } } }
public static void Main() { var context = new SoftUniEntities(); Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); var framework = context.Employees.Where(e => e.Projects.Count(p => p.StartDate.Year == 2002) > 0) .Select(e => e.FirstName).ToList(); stopwatch.Stop(); Console.WriteLine("Linq: {0}", stopwatch.ElapsedMilliseconds); stopwatch.Reset(); stopwatch.Start(); var native = context.Database.SqlQuery<string>( "select e.FirstName from Employees e inner JOIN EmployeesProjects ep on e.EmployeeID = ep.EmployeeID inner join Projects p on ep.ProjectID = p.ProjectID where year(p.StartDate) = 2002") .ToList(); stopwatch.Stop(); Console.WriteLine("Native: {0}", stopwatch.ElapsedMilliseconds); }
public static void DeleteEmployee(SoftUniEntities db, int employeeId) { Employee employee = GetEmployeeById(db, employeeId); db.Employees.Remove(employee); db.SaveChanges(); }
public static void FindEmployeesByCriteria() { var context = new SoftUniEntities(); var employees = from e in context.Employees select new { name = e.FirstName + " " + e.LastName, job = e.JobTitle, projects = e.Projects.OrderBy(p => p.Name) }; foreach (var e in employees) { Console.WriteLine("NAME: {0} JOB: {1}", e.name, e.job); Console.WriteLine("PROJECTS:"); Console.WriteLine("====================================="); foreach (var p in e.projects) { Console.WriteLine("PROJECT NAME: {0}", p.Name); } Console.WriteLine("====================================="); } }
public static Employee GetEmployeeById(SoftUniEntities entity, int employeeId) { Employee employee = entity.Employees .FirstOrDefault(p => p.EmployeeID == employeeId); return(employee); }
static void Main() { var context = new SoftUniEntities(); var stopwatch = new Stopwatch(); stopwatch.Start(); var employeesWithSaidProjects = context.Employees .Where(e => e.Projects.Any(p => p.StartDate.Year == 2002)) .Select(e => e.FirstName).ToList(); stopwatch.Stop(); Console.WriteLine(stopwatch.ElapsedMilliseconds); stopwatch.Reset(); stopwatch.Start(); var SQLemployeesWithSaidProjects = context.Database.SqlQuery <string>("SELECT FirstName FROM Employees AS a " + "INNER JOIN EmployeesProjects AS ep " + "ON a.EmployeeID = ep.EmployeeID " + "INNER JOIN Projects AS p " + "ON p.ProjectID = ep.ProjectID " + "WHERE YEAR(p.StartDate) = 2002").ToList(); stopwatch.Stop(); Console.WriteLine(stopwatch.ElapsedMilliseconds); }
public static void Insert(Employee e) { SoftUniEntities db = new SoftUniEntities(); db.Employees.Add(e); db.SaveChanges(); }
public static Employee GetEmployeeById(SoftUniEntities db, int employeeId) { Employee employee = db.Employees.FirstOrDefault( e => e.EmployeeID == employeeId); return(employee); }
public static Project GetProductById(SoftUniEntities entity, int projectId) { Project project = entity.Projects .FirstOrDefault(p => p.ProjectID == projectId); return(project); }
static void Main() { var context = new SoftUniEntities(); using (var dbContextTransaction = context.Database.BeginTransaction()) { try { context.Database.ExecuteSqlCommand("UPDATE Employees " + "SET Salary = Salary * 1.2 WHERE LastName = 'Wilson'"); var empsQuery = context.Employees.Where( e => e.Projects.Count() >= 3); foreach (var emp in empsQuery) { emp.JobTitle = "Senior " + emp.JobTitle; } context.SaveChanges(); context.Database.ExecuteSqlCommand("UPDATE Employees " + "SET Salary = NULL WHERE LastName = 'Brown'"); dbContextTransaction.Commit(); } catch (Exception ex) { dbContextTransaction.Rollback(); Console.WriteLine("Error: " + ex.Message); } } }
private static void AddTownAddressEvent(SoftUniEntities context) { Console.WriteLine("Adding town, address and event..."); var town = new Town() { Name = "Borovets" }; context.Towns.Add(town); var address = new Address() { AddressText = "Rila 12", Town = town }; context.Addresses.Add(address); var conf = new Event() { Name = "SoftUni Conf 2015", Date = new DateTime(2015, 9, 15), Address = address, }; context.Events.Add(conf); context.SaveChanges(); Console.WriteLine("done"); }
private static void EditTownOptimisticConcurrencyFirstWins() { // The first user changes some record var contextFirst = new SoftUniEntities(); var lastTownFirstUser = contextFirst.Towns.OrderByDescending(t => t.TownID).First(); lastTownFirstUser.Name = "Changed by the First User"; // The second user changes the same record var contextSecondUser = new SoftUniEntities(); var lastTownSecondUser = contextSecondUser.Towns.OrderByDescending(t => t.TownID).First(); lastTownSecondUser.Name = "Changed by the Second User"; // Conflicting changes: first wins; second gets an exception contextFirst.SaveChanges(); try { contextSecondUser.SaveChanges(); } catch (DbUpdateConcurrencyException ex) { Console.WriteLine("Error: concurrent change occurred."); Console.WriteLine(ex.Message); } }
public static void Main() { using (var dbFirst = new SoftUniEntities()) { using (var dbSecond = new SoftUniEntities()) { dbFirst.Employees.Add(new Employee() { FirstName = "Stoy", LastName = "Cholakov", JobTitle = "Cleaner", DepartmentID = 3, HireDate = DateTime.Now, Salary = 3000 }); dbSecond.Employees.Add(new Employee() { FirstName = "Gancho", LastName = "Ganchev", JobTitle = "Cleaner", DepartmentID = 3, HireDate = DateTime.Now, Salary = 3000 }); dbSecond.SaveChanges(); dbFirst.SaveChanges(); dbSecond.SaveChanges(); } } }
private static void InsertTownAddressEvent() { var context = new SoftUniEntities(); var town = new Town(); town.Name = "Developer City " + DateTime.Now.Ticks; context.Towns.Add(town); var addr = new Address(); addr.AddressText = (DateTime.Now.Ticks % 1000) + ", Developer Rd."; addr.Town = town; context.Addresses.Add(addr); var ev = new Event(); ev.Address = addr; ev.Name = "Party"; ev.Date = new DateTime(2015, 1, 1); context.Events.Add(ev); Console.WriteLine("Inserting new town, address and event..."); context.SaveChanges(); Console.WriteLine("Done."); }
static void Main() { var db = new SoftUniEntities(); using (db) { //var employee = new Employee() //{ // FirstName = "Ivan", // LastName = "Ivanov", // JobTitle = "Marketing Assistant", // DepartmentID = db.Departments // .Where(x => x.Name == "Marketing") // .Select(x => x.DepartmentID) // .FirstOrDefault(), // HireDate = DateTime.Now.AddDays(23), // Salary = 9500 //}; //EmployeeDao.Add(employee); //var foundEmployee = EmployeeDao.FindByKey(EmployeeId); //Console.WriteLine(foundEmployee.EmployeeID); var employee2 = db.Employees.Find(EmployeeId); employee2.FirstName = "Misho"; EmployeeDao.Modify(employee2); EmployeeDao.Delete(employee2); } }
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 ModifyEmployeeName(SoftUniEntities db, int employeeId, string newName) { Employee employee = GetEmployeeById(db, employeeId); employee.FirstName = newName; db.SaveChanges(); }
static void Main(string[] args) { SoftUniEntities dbo = new SoftUniEntities(); Employee firstEmp = new Employee() { EmployeeID = 329, FirstName = "Aleks", LastName = "Aleksieva", AddressID = 3, DepartmentID = 5, JobTitle = "Neshto si", Salary = 600, HireDate = DateTime.Now.AddDays(10) }; DAO.Insert(firstEmp); Employee empToModifyOrDelete = dbo.Employees.Where(x => x.FirstName == "Aleks").FirstOrDefault(); Employee e = DAO.FindByKey(205); Console.WriteLine(e); DAO.Modify(empToModifyOrDelete); DAO.Delete(empToModifyOrDelete, dbo); }
public static void DeleteProduct(int productId) { var softUniEntities = new SoftUniEntities(); Project project = GetProductById(softUniEntities, productId); softUniEntities.Projects.Remove(project); softUniEntities.SaveChanges(); }
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)); } }
/// <summary> /// Updates changes made to an object of type Employee and saves to the database /// </summary> /// <param name="emp">An object of type Employee</param> public static void ModifyEmployee(Employee emp) { using (SoftUniEntities context = new SoftUniEntities()) { context.Entry(emp).State = EntityState.Modified; context.SaveChanges(); } }
public static void Add(Employee employee) { using (var context = new SoftUniEntities()) { context.Employees.Add(employee); context.SaveChanges(); } }
public static void ModifyProductName(int productId, string newName) { var softUniEntities = new SoftUniEntities(); Project project = GetProductById(softUniEntities, productId); project.Name = newName; softUniEntities.SaveChanges(); }
public static Employee FindByKey(Object key) { SoftUniEntities dbe = new SoftUniEntities(); Employee e = dbe.Employees.Find(key); return(e); }
public static void InsertEmployee(Employee employee) { var db = new SoftUniEntities(); db.Employees.Add(employee); db.SaveChanges(); Console.WriteLine(employee.FirstName + " " + employee.LastName + " Inserted!!!"); }
static string[] GetNamesWithLinqQuery(SoftUniEntities context) { var employeeNamesQuery = from employee in context.Employees where employee.Projects.Any(p => p.StartDate.Year == 2002) select employee.FirstName; return employeeNamesQuery.ToArray(); }
public static Employee FindByKey(int key) { using (var context = new SoftUniEntities()) { var employee = context.Employees.Find(key); return employee; } }
// Task 09. // Stored procedure usp_GetProjectsCountByEmployee has been created in SQL SERVER // EF Models were updated with the new stored procedure /// <summary> /// Method returns the total count of the projects for all found Employees with the given names. /// </summary> /// <param name="firstName">Employee FirstName</param> /// <param name="lastName">Employee LastName</param> /// <returns>Zero - If no employee with the given names is found; Zero - If the given Employee has no projects</returns> public static int GetProjectsCountByEmployee(string firstName, string lastName) { using (SoftUniEntities softuniDbContext = new SoftUniEntities()) { var projectsCount = softuniDbContext.usp_GetProjectsCountByEmployee(firstName, lastName).FirstOrDefault(); return(projectsCount.Value); } }
public static void DeleteEmployee(int employeeId) { var softUniEntities = new SoftUniEntities(); Employee employee = GetEmployeeById(softUniEntities, employeeId); softUniEntities.Employees.Remove(employee); softUniEntities.SaveChanges(); }
public static void ModifyEmployeeName(int employeeId, string newFirstName) { var softUniEntities = new SoftUniEntities(); Employee employee = GetEmployeeById(softUniEntities, employeeId); employee.FirstName = newFirstName; softUniEntities.SaveChanges(); }
private static void DeleteEmployeeSlow(int empId) { var context = new SoftUniEntities(); var emp = context.Employees.Find(empId); context.Employees.Remove(emp); context.SaveChanges(); }
public static void Delete(Employee employee) { using (var ctx = new SoftUniEntities()) { ctx.Entry(employee).State = EntityState.Deleted; ctx.SaveChanges(); } }
public static int SelectEmployeesCount() { SoftUniEntities northwindEntities = new SoftUniEntities(); string nativeSqlQuery = "SELECT count(*) FROM dbo.Employees"; var queryResult = northwindEntities.Database.SqlQuery <int>(nativeSqlQuery); int customersCount = queryResult.FirstOrDefault(); return(customersCount); }
static string[] GetNamesWithNativeQuery(SoftUniEntities context) { const string Query = "SELECT [Extent1].[FirstName] AS [FirstName] FROM [dbo].[Employees] AS [Extent1] WHERE EXISTS (SELECT 1 AS [C1] FROM [dbo].[EmployeesProjects] AS [Extent2]" + "INNER JOIN [dbo].[Projects] AS [Extent3] ON [Extent3].[ProjectID] = [Extent2].[ProjectID]" + "WHERE ([Extent1].[EmployeeID] = [Extent2].[EmployeeID]) AND (2002 = (DATEPART (year, [Extent3].[StartDate]))) AND (DATEPART (year, [Extent3].[StartDate]) IS NOT NULL) ) "; var result = context.Database.SqlQuery<string>(Query); return result.ToArray(); }
public static void Delete(Employee employee) { using (var context = new SoftUniEntities()) { context.Employees.Attach(employee); context.Employees.Remove(employee); context.SaveChanges(); } }
public static void UpdateProject(Project product, string newName) { using (SoftUniEntities northwindEntities = new SoftUniEntities()) { northwindEntities.Projects.Attach(product); // This line is required! product.Name = newName; northwindEntities.SaveChanges(); } }
public static void Modify(Employee employee) { using (var context = new SoftUniEntities()) { context.Employees.Attach(employee); context.Entry(employee).State = EntityState.Modified; context.SaveChanges(); } }
private static void PrintNamesWithNativeQuesry(SoftUniEntities contex) { var employees = contex.Database.SqlQuery<string> ("SELECT e.FirstName" + "FROM Employees e" + "JOIN EmployeesProjects ep ON ep.EmployeeID = e.EmployeeID" + "JOIN Projects p ON ep.ProjectID = p.ProjectID" + "WHERE YEAR(p.StartDate) = 2002" + "GROUP BY e.FirstName, p.StartDate"); }
/// <summary> /// Deletes an employee from the database /// </summary> /// <param name="emp">An object of type Employee</param> public static void Delete(Employee emp) { using (SoftUniEntities context = new SoftUniEntities()) { context.Entry(emp).State = EntityState.Unchanged; context.Employees.Remove(emp); context.SaveChanges(); Console.WriteLine("An employee with Id: {0} was deleted!", emp.EmployeeID); } }
static void Main() { using (var context = new SoftUniEntities()) { AddTownAddressEvent(context); //unique constraint in the DB causes exception: AddTownAddressEvent(context); } }
/// <summary> /// Searches Employees by key and retrieves the match /// </summary> /// <param name="key">Object of type Id according to DB specifications</param> /// <returns>An object of type Employee</returns> public static Employee FindByKey(object key) { using (SoftUniEntities context = new SoftUniEntities()) { Employee emp = context.Employees.Find(key); if (emp == null) { throw new NullReferenceException("There is no employee with such Id"); } return emp; } }
private static List<Employee> GetEmployeesWithProjectsIn2002() { var context = new SoftUniEntities(); var employees = context.Employees .Where(e => e.Projects .Any(p => p.StartDate >= new DateTime(2002, 1, 1) && p.StartDate <= new DateTime(2002, 12, 31))) .OrderBy(e => e.FirstName + " " + e.LastName) .ToList(); return employees; }
public static void GetProjectsByEmployee(string firstName, string lastName) { var context = new SoftUniEntities(); var projects = context.usp_GetProjectsByEmployee(firstName, lastName) .Select(p => new Project { Name = p.Name, Description = p.Description, StartDate = p.StartDate }).ToList(); PrintProjects(projects); }
private static void PrintNamesWithLinqQuery_WithExtensionMethods() { using (var ctx = new SoftUniEntities()) { var queryResult = ctx.Employees .Where(emp => emp.Projects.Any(p => p.StartDate.Year == 2002)) .Select(emp => emp.FirstName); var employees = queryResult.ToList(); Console.WriteLine(string.Join(" | ", employees)); } }
private static List<Employee> GetEmployeesNative() { var context = new SoftUniEntities(); var employees = context.Employees.SqlQuery(@" SELECT * FROM Employees e JOIN EmployeesProjects ep ON e.EmployeeID = ep.EmployeeID JOIN Projects p ON p.ProjectID = ep.ProjectID WHERE p.StartDate >= '2002-1-1' AND p.StartDate <= '2002-12-31'").ToList(); return employees; }
public static void Main() { var db = new SoftUniEntities(); var concurrentDB = new SoftUniEntities(); var person = db.Employees.FirstOrDefault(e => e.EmployeeID == 1); var concurrentPerson = concurrentDB.Employees.FirstOrDefault(e => e.EmployeeID == 1); person.LastName = "First"; concurrentPerson.LastName = "Second"; db.SaveChanges(); concurrentDB.SaveChanges(); }
private static void PrintNamesWithLinqQuery_LINQtoEntities() { using(var ctx = new SoftUniEntities()) { var queryResult = (from e in ctx.Employees where (from p in e.Projects where p.StartDate.Year == 2002 select p).Any() select e.FirstName); //https://smehrozalam.wordpress.com/2010/06/29/entity-framework-queries-involving-many-to-many-relationship-tables/ Console.WriteLine(string.Join(" | ", queryResult)); } }
static void PrintNamesWithLinqQuery() { using (var context = new SoftUniEntities()) { var employeesFirstNames = from employee in context.Employees where employee.Projects.Any(p => p.StartDate.Year == 2002) select employee.FirstName; foreach (var employee in employeesFirstNames) { Console.WriteLine(employee); } } }
public static void Main() { var contex1 = new SoftUniEntities(); var contex2 = new SoftUniEntities(); var employee1 = contex1.Employees.FirstOrDefault(e => e.EmployeeID == 5); var employee2 = contex2.Employees.FirstOrDefault(e => e.EmployeeID == 5); employee1.FirstName = "B1"; employee2.FirstName = "B2"; contex1.SaveChanges(); contex2.SaveChanges(); }
public static void Main() { //Problem 4. Native SQL Query var contex = new SoftUniEntities(); var sw = new Stopwatch(); sw.Start(); PrintNamesWithNativeQuesry(contex); Console.WriteLine("Native: {0}", sw.Elapsed); sw.Restart(); PrintNamesWithLinqQuery(contex); Console.WriteLine("Linq: {0}", sw.Elapsed); }
static void PrintNamesWithNativeQuery() { using (var context = new SoftUniEntities()) { var employeesFirstNames = context.Database.SqlQuery<string>("SELECT e.FirstName FROM EmployeesProjects AS ep" + " INNER JOIN Employees AS e ON e.EmployeeID = ep.EmployeeID" + " INNER JOIN Projects AS p ON p.ProjectID = ep.ProjectID" + " WHERE YEAR(p.StartDate) = 2002"); foreach (var employee in employeesFirstNames) { Console.WriteLine(employee); } } }
/// <summary> /// Adds an employee ot type Employee to the database. Prints the key and returns it as an int. /// </summary> /// <param name="emp">An object of type Employee</param> public static int Add(Employee emp) { using (SoftUniEntities context = new SoftUniEntities()) { if (emp == null) { throw new ArgumentNullException("emp", "You need to enter a valid Employee object"); } context.Employees.Add(emp); context.SaveChanges(); int key = emp.EmployeeID; Console.WriteLine(key); return key; } }
static void Main(string[] args) { var contextOne = new SoftUniEntities(); var contextTwo = new SoftUniEntities(); var tempEmployeeOne = contextOne.Employees.Find(147); var tempEmployeeTwo = contextTwo.Employees.Find(147); tempEmployeeOne.MiddleName = tempEmployeeOne.MiddleName + "Changed From Context One"; tempEmployeeTwo.MiddleName = tempEmployeeTwo.MiddleName + "Changed From Context Two"; contextOne.Entry(tempEmployeeOne).State = EntityState.Modified; contextTwo.Entry(tempEmployeeTwo).State = EntityState.Modified; contextOne.SaveChanges(); contextTwo.SaveChanges(); }
public static void Main() { var contextOne = new SoftUniEntities(); var contextTwo = new SoftUniEntities(); var employee = contextOne.Employees.Find(1); employee.FirstName = "Pesho"; var theSameEmployee = contextTwo.Employees.Find(1); theSameEmployee.FirstName = "Gosho"; contextOne.SaveChanges(); contextTwo.SaveChanges(); //// If [Concurrency Mode] is None the result is "Gosho" //// If [Concurrency Mode] is Fixed the result is "Pesho" }
static void Main(string[] args) { using (var context = new SoftUniEntities()) { var totalCount = context.Employees.Count(); var sw = new Stopwatch(); sw.Start(); PrintNamesWithNativeQuery(); Console.WriteLine("Native: {0}", sw.Elapsed); sw.Restart(); PrintNamesWithLinqQuery(); Console.WriteLine("Linq: {0}", sw.Elapsed); } }
static void Main() { // FIRST DB var firstDb = new SoftUniEntities(); // EMPLOYEE var firstDbEmployee = firstDb.Employees.Find(1); firstDbEmployee.FirstName = "Gosho"; // SECOND DB var secondDb = new SoftUniEntities(); // EMPLOYEE var secondDbEmployee = secondDb.Employees.Find(1); secondDbEmployee.FirstName = "Vanka"; // SAVE BOTH firstDb.SaveChanges(); secondDb.SaveChanges(); }