/// <summary> /// Добавляет новый заказ. /// </summary> /// <param name="NewOrder"></param> public void AddNewOrderDB(OrderClass NewOrder) { using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName)) { int LastAddedOrder; DBConnection.Open(); using (SQLiteCommand Command = new SQLiteCommand(DBConnection)) { Command.CommandText = @"INSERT INTO orders (date, carID, driverID, duration, clientID, address) VALUES ('" + NewOrder.DataRequest + "','" + NewOrder.CarRequest.IDCar + "','" + NewOrder.DriverRequest.DriverDBID + "','" + NewOrder.TimeRequest + "','" + NewOrder.CustomerRequest.IDcustomer + "','" + NewOrder.AddressRequest.ToUpper() + "');"; MyDBLogger("Create order with SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); Command.CommandText = @"SELECT ID from orders ORDER by ID DESC LIMIT 1;"; MyDBLogger("Get last order with SQL-command: " + Command.CommandText); using (SQLiteDataReader Reader = Command.ExecuteReader()) { Reader.Read(); LastAddedOrder = Reader.GetInt32(0); MyDBLogger("Last record Orders ID is: " + LastAddedOrder); } if (NewOrder.KidsChair) { Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + LastAddedOrder + "," + 1 + ");"; MyDBLogger("Order service (KidsChair) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } if (NewOrder.WinterTires) { Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + LastAddedOrder + "," + 2 + ");"; MyDBLogger("Order service (WinterTires) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } if (NewOrder.SportFastenings) { Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + LastAddedOrder + "," + 3 + ");"; MyDBLogger("Order service (SportFastenings) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } if (NewOrder.Gps) { Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + LastAddedOrder + "," + 4 + ");"; MyDBLogger("Order service (Gps) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } } } }
/// <summary> /// Возвращает экземпляр заказа по ID. /// </summary> /// <param name="OrderID"></param> /// <returns></returns> public OrderClass ReadOrderDB(string OrderID) { OrderClass ReadOrder = new OrderClass(); ReadOrder.IDRequest = OrderID; using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName)) { DBConnection.Open(); using (SQLiteCommand Command = new SQLiteCommand(DBConnection)) { Command.CommandText = @"SELECT date, duration, address, carID, clientID, driverID FROM orders WHERE ID = '" + ReadOrder.IDRequest + "';"; MyDBLogger("Select Order by ID: " + Command.CommandText); using (SQLiteDataReader Reader = Command.ExecuteReader()) { Reader.Read(); ReadOrder.DataRequest = Reader.GetString(0); ReadOrder.TimeRequest = Reader.GetValue(1).ToString(); ReadOrder.AddressRequest = Reader.GetString(2); ReadOrder.CarRequest = ReadCarDB(Reader.GetValue(3).ToString()); ReadOrder.CustomerRequest = ReadCustomerDB(Reader.GetValue(4).ToString()); ReadOrder.DriverRequest = ReadDriverDB(Reader.GetValue(5).ToString()); } // Считываем дополнительные услуги Command.CommandText = @"SELECT additionalServicesID FROM additionalServicesBinding WHERE orderID = '" + ReadOrder.IDRequest + "';"; MyDBLogger("Select additional services for order: " + Command.CommandText); using (SQLiteDataReader Reader = Command.ExecuteReader()) { while (Reader.Read()) { if (Reader.GetInt32(0) == 1) { ReadOrder.KidsChair = true; } if (Reader.GetInt32(0) == 2) { ReadOrder.WinterTires = true; } if (Reader.GetInt32(0) == 3) { ReadOrder.SportFastenings = true; } if (Reader.GetInt32(0) == 4) { ReadOrder.Gps = true; } } } } } return(ReadOrder); }
/// <summary> /// Метод для редактирования заказов. /// </summary> /// <param name="OrderToEdit"></param> public void EditOrderDB(OrderClass OrderToEdit) { using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName)) { DBConnection.Open(); using (SQLiteCommand Command = new SQLiteCommand(DBConnection)) { Command.CommandText = @"UPDATE orders SET " + "date = '" + OrderToEdit.DataRequest + "', " + "carID = '" + OrderToEdit.CarRequest.IDCar + "', " + "driverID = '" + OrderToEdit.DriverRequest.DriverDBID + "', " + "duration = '" + OrderToEdit.TimeRequest + "', " + "clientID = '" + OrderToEdit.CustomerRequest.IDcustomer + "', " + "address = '" + OrderToEdit.AddressRequest.ToUpper() + "' " + "WHERE ID = '" + OrderToEdit.IDRequest + "';"; MyDBLogger("Edit order with SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); /// Удаляем доп. услуги. Command.CommandText = @"DELETE FROM additionalServicesBinding WHERE driverID = '" + OrderToEdit.IDRequest + "';"; MyDBLogger("Delete additional services with SQL-command: " + Command.CommandText); /// Устанавливаем доп. услуги. if (OrderToEdit.KidsChair) { Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + OrderToEdit.IDRequest + "," + 1 + ");"; MyDBLogger("Order service (KidsChair) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } if (OrderToEdit.WinterTires) { Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + OrderToEdit.IDRequest + "," + 2 + ");"; MyDBLogger("Order service (WinterTires) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } if (OrderToEdit.SportFastenings) { Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + OrderToEdit.IDRequest + "," + 3 + ");"; MyDBLogger("Order service (SportFastenings) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } if (OrderToEdit.Gps) { Command.CommandText = @"INSERT INTO additionalServicesBinding (orderID, additionalServicesID) VALUES (" + OrderToEdit.IDRequest + "," + 4 + ");"; MyDBLogger("Order service (Gps) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } } } }
/// <summary> /// Метод для удаления заказов. /// </summary> /// <param name="OrderToEdit"></param> public void DeleteOrderDB(OrderClass OrderToDelete) { using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName)) { DBConnection.Open(); using (SQLiteCommand Command = new SQLiteCommand(DBConnection)) { Command.CommandText = @"DELETE FROM orders WHERE ID = " + OrderToDelete.IDRequest + ";"; MyDBLogger("Delete Order by ID: " + Command.CommandText); Command.ExecuteNonQuery(); } } }