/// <summary> /// Запрос всех данных по заказу из связанных таблиц /// </summary> public static void GetFullOrderInformationMeasured(string connectionString) { Stopwatch stopwatch = new Stopwatch(); using (var context = new AutoRepairContext(connectionString)) { stopwatch.Start(); var order = context.Orders .Include(order => order.Customer) .Include(order => order.RepairItems) .Include(order => order.Vehicle) .Include(order => order.Worker) .First(); stopwatch.Stop(); var elapsedTime = stopwatch.ElapsedMilliseconds; var requestTimeToString = $"Request time is {elapsedTime} ms."; var orderInfoToString = $@"Order details: Id: {order.Id}, Customer: {order.Customer.Name}, Vehicle: {order.Vehicle.Make} {order.Vehicle.Model} ({order.Vehicle.RegistrationPlate}), Worker: {order.Worker.Name}, {order.Worker.Position}, Customer's problem: {order.ProblemDescription}, Solution: {order.Solution}"; Console.WriteLine(orderInfoToString); Console.WriteLine(requestTimeToString); } }
static void CRUDExampleOnParts(string connectionString) { using (var context = new AutoRepairContext(connectionString)) { context.Parts.Add(new Part() { Name = "Gear", Price = 100 }); //Create context.SaveChanges(); Console.WriteLine("Запись создана"); Console.ReadLine(); var part = context.Parts.First(x => x.Name == "Gear"); //Read Console.WriteLine($"Запись получена: {part.Id} {part.Name} {part.Price}"); Console.ReadLine(); part.Price = 150; context.Update(part); //Update context.SaveChanges(); Console.WriteLine("Запись обновлена"); Console.ReadLine(); context.Remove(part); //Delete context.SaveChanges(); Console.WriteLine("Запись удалена"); Console.ReadLine(); } }
static IEnumerable <RepairItem> GetRepairItemsPage(AutoRepairContext context, int page, int pageSize) { var count = context.RepairItems.Count(); var items = context.RepairItems.Skip((page - 1) * pageSize).Take(pageSize); return(items.ToList()); }
/// <summary> /// Запрос стоимости всех запчастей для ремонта /// </summary> public static void GetTotalCostMeasured(string connectionString) { Stopwatch stopwatch = new Stopwatch(); using (var context = new AutoRepairContext(connectionString)) { stopwatch.Start(); var sum = context.Orders .Include(order => order.RepairItems) .ThenInclude(item => item.Part) .First() .RepairItems .Sum(x => x.Part.Price * x.Qty); stopwatch.Stop(); var elapsedTime = stopwatch.ElapsedMilliseconds; var sumToString = $"Total cost of the order is {sum}"; var requestTimeToString = $"Request time is {elapsedTime} ms."; Console.WriteLine(sumToString); Console.WriteLine(requestTimeToString); } }
/// <summary> /// Форматированный вывод всех записей из таблиц /// </summary> /// <param name="connectionString"></param> public static void GetAllEntries(string connectionString) { using (var context = new AutoRepairContext(connectionString)) { var allCustomers = context.Customers.ToList(); var allOrders = context.Orders.ToList(); var allParts = context.Parts.ToList(); var allRepairItems = context.RepairItems.ToList(); var allVehicles = context.Vehicles.ToList(); var allWorkers = context.Workers.ToList(); allCustomers.ForEach(customer => Console.WriteLine($"{customer.Id} {customer.Name} {customer.Address}")); Console.WriteLine(); allOrders.ForEach(order => Console.WriteLine($"{order.Id} customerId: {order.CustomerId} vehicleId: {order.VehicleId} workerId: {order.WorkerId}")); Console.WriteLine(); allParts.ForEach(part => Console.WriteLine($"{part.Id} {part.Name} {part.Price}")); Console.WriteLine(); allRepairItems.ForEach(item => Console.WriteLine($"{item.Id} orderId: {item.OrderId} partId: {item.PartId} quantity: {item.Qty}")); Console.WriteLine(); allVehicles.ForEach(vehicle => Console.WriteLine($"{vehicle.Id} {vehicle.Make} {vehicle.Model} {vehicle.RegistrationPlate} {vehicle.Year}")); Console.WriteLine(); allWorkers.ForEach(worker => Console.WriteLine($"{worker.Id} {worker.Name} {worker.Position}")); } }
protected virtual void Dispose(bool disposing) { if (disposing) { if (_context != null) { _context.Dispose(); _context = null; } } }
public void Delete(int id) { using (var context = new AutoRepairContext()) { ////get existing vehicle by id //var existingVehicle = context.Vehicles.FirstOrDefault(a => a.Identifier == id); ////don't forget to save chagnes //context.Entry(existingVehicle).State = Microsoft.EntityFrameworkCore.EntityState.Deleted; //context.SaveChanges(); } }
public void Put(int id, [FromBody] Vehicle updatedVehicle) { using (var context = new AutoRepairContext()) { //get existing vehicle by id //var existingVehicle = context.Vehicles.FirstOrDefault(a=>a.Identifier == id); ////update existing vehicle from the db with the data passd into the api //existingVehicle.Year = updatedVehicle.Year; //existingVehicle.Make = updatedVehicle.Make; //existingVehicle.Model = updatedVehicle.Model; ////don't forget to save chagnes //context.Entry(existingVehicle).State = Microsoft.EntityFrameworkCore.EntityState.Modified; //context.SaveChanges(); } }
static void CRUDExampleOnCustomers(string connectionString) { using (var context = new AutoRepairContext(connectionString)) { context.Customers.Add(new Customer() { Name = "Johnnie Doe", Address = "New York" }); //Create var customer = context.Customers.First(x => x.Name.Contains("Doe")); //Read customer.Name = "John Doe"; context.Update(customer); //Update context.Remove(customer); //Delete } }
static void CRUDExampleOnWorkers(string connectionString) { using (var context = new AutoRepairContext(connectionString)) { context.Workers.Add(new Worker() { Name = "Edward Nieves", Position = "Mechanic" }); //Create var worker = context.Workers.First(x => x.Name.StartsWith("Edward")); worker.Position = "Sr. Mechanic"; context.Update(worker); //Update context.Remove(worker); //Delete } }
static void CRUDExampleOnVehicles(string connectionString) { using (var context = new AutoRepairContext(connectionString)) { context.Vehicles.Add(new Vehicle() { Make = "Chevrolet", Model = "Silverado", RegistrationPlate = "GHT430", Year = 2003 }); //Create var vehicle = context.Vehicles.First(x => x.Make == "Chevrolet" && x.Year == 2003); vehicle.Year = 2004; context.Update(vehicle); //Update context.Remove(vehicle); //Delete } }
static void Main(string[] args) { configuration = new ConfigurationBuilder() .AddJsonFile("appsettings.json", true, true) .Build(); var connectionString = configuration.GetConnectionString("PostgresAutorepairDB"); //Прогрев контекста using (var context = new AutoRepairContext(connectionString)) { context.Customers.FirstOrDefault(); } //Эти запросы пересоздадут базу, заполнят таблицы тестовыми данными //Перед началом демонтрации программы, выполнить ТОЛЬКО их и закомментировать // Scenarios.ResetAndFillDatabase(connectionString); // Scenarios.ExampleOfOrderWorkflow(connectionString); //Запросы с замером времени Scenarios.GetTheOldestCarMeasured(connectionString); Scenarios.GetTotalCostMeasured(connectionString); Scenarios.GetFullOrderInformationMeasured(connectionString); // Pagination example var pageSize = configuration.GetValue <int>("PageSize"); using (var context = new AutoRepairContext(connectionString)) { int itemsCount = context.Workers.Count(); //количество строк в таблице int pagesCount = (int)Math.Ceiling((double)itemsCount / pageSize); for (int pageNumber = 1; pageNumber <= pagesCount; pageNumber++) { var itemsOnPage = GetWorkersPage(context, pageNumber, pageSize); //получим элементы со страницы pageNumber Console.WriteLine($"Page {pageNumber}: " + string.Join(", ", itemsOnPage.Select(x => x.Name))); //выведем только имена } } //CRUD Examples //CRUDExampleOnCustomers(connectionString); //CRUDExampleOnParts(connectionString); //CRUDExampleOnVehicles(connectionString); //CRUDExampleOnWorkers(connectionString); Console.ReadLine(); }
/// <summary> /// Запрос на вывод самого старого автомобиля /// </summary> public static void GetTheOldestCarMeasured(string connectionString) { Stopwatch stopwatch = new Stopwatch(); using (var context = new AutoRepairContext(connectionString)) { stopwatch.Start(); var oldestCar = context.Vehicles.OrderBy(x => x.Year).First(); stopwatch.Stop(); var elapsedTime = stopwatch.ElapsedMilliseconds; var sumToString = $"The oldest car is {oldestCar.Make} {oldestCar.Model}, year: {oldestCar.Year}"; var requestTimeToString = $"Request time is {elapsedTime} ms."; Console.WriteLine(sumToString); Console.WriteLine(requestTimeToString); } }
/// <summary> /// Пример заполнения всех данных для Заказа на ремонт /// </summary> public static void ExampleOfOrderWorkflow(string connectionString) { using (var context = new AutoRepairContext(connectionString)) { //Берем заказчика var customer = context.Customers.First(x => x.Name == "Joan Romero"); //Берем автомобиль var vehicle = context.Vehicles.First(x => x.RegistrationPlate == "GHT430"); //Создаем заказ на ремонт автомобиля IOrdersService ordersService = new OrdersService(context); var order = ordersService.OpenOrder(customer, vehicle, "Check engine light is blinking. Please check."); //Назначаем первого подходящего мастера нужного уровня на заказ var mechanic = context.Workers.First(x => x.Position == "Mechanic"); ordersService.AssingWorker(order, mechanic); //Выбираем детали для ремонта var starterGear = context.Parts.First(x => x.Name == "Starter Pinion Gear"); var differential = context.Parts.First(x => x.Name == "Differential"); var clip = context.Parts.First(x => x.Name == "Front Clip"); //Добавляем в заказ необходимое количество деталей ordersService.AddPart(order, differential, 1); ordersService.AddPart(order, starterGear, 2); ordersService.AddPart(order, clip, 4); //Записываем результат работы ordersService.AddRepairSummary(order, "Change differential. All is ok now."); //Закрываем заказ ordersService.SaveOrder(order); //Для того, чтобы перезапускать пример, удалим сохраненные данные //ordersService.DeleteOrder(order); } }
public AppoimtmentsRepository(AutoRepairContext context) { _context = context ?? throw new ArgumentNullException(nameof(context)); }
public OrdersService(AutoRepairContext context) { _context = context ?? throw new ArgumentNullException(nameof(context)); }
public DiscountRepository(AutoRepairContext context) { _context = context ?? throw new ArgumentNullException(nameof(context)); }
/// <summary> /// Пересоздает базу (удаляет и создает заново), заполняет её тестовыми данными /// </summary> public static void ResetAndFillDatabase(string connectionString) { using (var context = new AutoRepairContext(connectionString, resetDatabase: true)) { //Заполняем заказчиков context.Customers.Add(new Customer() { Name = "Hedy Greene", Address = "Ap #696-3279 Viverra. Avenue Latrobe DE 38100" }); context.Customers.Add(new Customer() { Name = "Joan Romero", Address = "Lacinia Avenue Idaho Falls Ohio" }); context.Customers.Add(new Customer() { Name = "Davis Patrick", Address = "2546 Sociosqu Rd. Bethlehem Utah" }); //Заполняем машины context.Vehicles.Add(new Vehicle() { Make = "Ford", Model = "F-Series", RegistrationPlate = "798 PAK", Year = 2007 }); context.Vehicles.Add(new Vehicle() { Make = "Chevrolet", Model = "Silverado", RegistrationPlate = "GHT430", Year = 2003 }); context.Vehicles.Add(new Vehicle() { Make = "Suzuki", Model = "Aerio", RegistrationPlate = "50D24H8", Year = 2011 }); //Заполняем список запчастей context.Parts.Add(new Part() { Name = "Front Clip", Price = 5 }); context.Parts.Add(new Part() { Name = "Airbag sensor", Price = 10 }); context.Parts.Add(new Part() { Name = "Starter Pinion Gear", Price = 50 }); context.Parts.Add(new Part() { Name = "Differential", Price = 1500 }); context.Parts.Add(new Part() { Name = "Backup Camera", Price = 115 }); context.Parts.Add(new Part() { Name = "Door Contact", Price = 75 }); //Заполняем механиков context.Workers.Add(new Worker() { Name = "Leilani Boyer", Position = "Mechanic" }); context.Workers.Add(new Worker() { Name = "Edward Nieves", Position = "Mechanic" }); context.Workers.Add(new Worker() { Name = "Christian Emerson", Position = "St. Mechanic" }); context.Workers.Add(new Worker() { Name = "Raymond Levy", Position = "Jr. Mechanic" }); context.Workers.Add(new Worker() { Name = "Tom Adams", Position = "Jr. Mechanic" }); context.Workers.Add(new Worker() { Name = "John Patel", Position = "Jr. Mechanic" }); context.Workers.Add(new Worker() { Name = "Pat Quinn", Position = "Jr. Mechanic" }); context.Workers.Add(new Worker() { Name = "Mike Usman", Position = "Jr. Mechanic" }); context.Workers.Add(new Worker() { Name = "Diego Yakub", Position = "Jr. Mechanic" }); context.SaveChanges(); } }