/// <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();
}
}
}
}
