/// <summary> /// Помечает водителя как удалённого. /// </summary> /// <param name="DriverToDelete"></param> public void DeleteDriver(DriverClass DriverToDelete) { using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName)) { DBConnection.Open(); using (SQLiteCommand Command = new SQLiteCommand(DBConnection)) { Command.CommandText = @"UPDATE drivers SET deleted = 1 WHERE ID = " + DriverToDelete.DriverDBID + ";"; MyDBLogger("Delete driver by ID: " + Command.CommandText); Command.ExecuteNonQuery(); } } }
/// <summary> /// Считывает водителя из базы по ID. /// </summary> /// <param name="DriverID"></param> public DriverClass ReadDriverDB(string DriverID) { DriverClass ReadedDriver = new DriverClass(); ReadedDriver.DriverDBID = DriverID; using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName)) { DBConnection.Open(); using (SQLiteCommand Command = new SQLiteCommand(DBConnection)) { // Считываем информацию о водителе Command.CommandText = @"SELECT name, photoFileName, experienceFrom FROM drivers WHERE ID = '" + ReadedDriver.DriverDBID + "';"; MyDBLogger("Select Driver by ID: " + Command.CommandText); using (SQLiteDataReader Reader = Command.ExecuteReader()) { Reader.Read(); ReadedDriver.FIOdriver = Reader.GetString(0); ReadedDriver.PhotoDriver = Reader.GetString(1); ReadedDriver.ExpirienceDriver = Reader.GetValue(2).ToString(); } // Считываем привычки водителя Command.CommandText = @"SELECT driverHabitsID FROM driverHabitsBinding WHERE driverID = '" + ReadedDriver.DriverDBID + "';"; MyDBLogger("Select Driver habbits by Driver ID: " + Command.CommandText); using (SQLiteDataReader Reader = Command.ExecuteReader()) { while (Reader.Read()) { if (Reader.GetInt32(0) == 1) { ReadedDriver.DriverHabitDrugs = true; } if (Reader.GetInt32(0) == 2) { ReadedDriver.DriverHabitDrink = true; } if (Reader.GetInt32(0) == 3) { ReadedDriver.DriverHabitSmoke = true; } } } } } return(ReadedDriver); }
/// <summary> /// Записывает в базу нового водителя. /// </summary> /// <param name="NewDriverAdd"></param> public void AddNewDriverDB(DriverClass NewDriverAdd) { using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName)) { DBConnection.Open(); using (SQLiteCommand Command = new SQLiteCommand(DBConnection)) { int AddedDviverID; Command.CommandText = @"INSERT INTO drivers (name, photoFileName, experienceFrom) VALUES ('" + NewDriverAdd.FIOdriver.ToUpper() + "','" + NewDriverAdd.PhotoDriver + "','" + NewDriverAdd.ExpirienceDriver + "');"; MyDBLogger("Create driver with SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); Command.CommandText = @"SELECT ID from drivers ORDER by ID DESC LIMIT 1;"; MyDBLogger("Get last driver with SQL-command: " + Command.CommandText); using (SQLiteDataReader Reader = Command.ExecuteReader()) { Reader.Read(); AddedDviverID = Reader.GetInt32(0); MyDBLogger("Last record Driver ID is: " + AddedDviverID); } if (NewDriverAdd.DriverHabitSmoke) { Command.CommandText = @"INSERT INTO driverHabitsBinding (driverID, driverHabitsID) VALUES (" + AddedDviverID + "," + 3 + ");"; MyDBLogger("Driver habbits (smoke) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } if (NewDriverAdd.DriverHabitDrink) { Command.CommandText = @"INSERT INTO driverHabitsBinding (driverID, driverHabitsID) VALUES (" + AddedDviverID + "," + 2 + ");"; MyDBLogger("Driver habbits (smoke) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } if (NewDriverAdd.DriverHabitDrugs) { Command.CommandText = @"INSERT INTO driverHabitsBinding (driverID, driverHabitsID) VALUES (" + AddedDviverID + "," + 1 + ");"; MyDBLogger("Driver habbits (smoke) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } } } }
/// <summary> /// Редактирует водителя. /// </summary> /// <param name="DriverEdit"></param> public void EditDriverDB(DriverClass DriverEdit) { using (SQLiteConnection DBConnection = new SQLiteConnection("data source=" + DBFileName)) { DBConnection.Open(); using (SQLiteCommand Command = new SQLiteCommand(DBConnection)) { Command.CommandText = @"UPDATE drivers SET name = '" + DriverEdit.FIOdriver.ToUpper() + "', " + "photoFileName ='" + DriverEdit.PhotoDriver + "', " + "experienceFrom = '" + DriverEdit.ExpirienceDriver + "' " + "WHERE ID = '" + DriverEdit.DriverDBID + "';"; MyDBLogger("Edit driver driver whis SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); // Удаляем все упоминания о вредных привычках и заново их устанавливаем Command.CommandText = @"DELETE FROM driverHabitsBinding WHERE driverID = '" + DriverEdit.DriverDBID + "';"; MyDBLogger("Delete all habits binding SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); if (DriverEdit.DriverHabitSmoke) { Command.CommandText = @"INSERT INTO driverHabitsBinding (driverID, driverHabitsID) VALUES ('" + DriverEdit.DriverDBID + "','" + 3 + "');"; MyDBLogger("Driver habbits (smoke) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } if (DriverEdit.DriverHabitDrink) { Command.CommandText = @"INSERT INTO driverHabitsBinding (driverID, driverHabitsID) VALUES ('" + DriverEdit.DriverDBID + "','" + 2 + "');"; MyDBLogger("Driver habbits (smoke) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } if (DriverEdit.DriverHabitDrugs) { Command.CommandText = @"INSERT INTO driverHabitsBinding (driverID, driverHabitsID) VALUES ('" + DriverEdit.DriverDBID + "','" + 1 + "');"; MyDBLogger("Driver habbits (smoke) SQL-command: " + Command.CommandText); Command.ExecuteNonQuery(); } } } }