/// <summary>
/// Загружает контрагентов из базы данных, возвращая объект List<Contractor>
/// </summary>
/// <returns></returns>
public static List <Contractor> Load()
{
using (SqliteConnection database = new SqliteConnection($"Filename={DatabasePath}"))
{
database.Open();
List <Contractor> result = new List <Contractor>(10);
SqliteCommand selectCommand = new SqliteCommand("SELECT * FROM ContractorTable", database);
SqliteDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
result.Add(new Contractor
{
ID = reader.GetInt32(0),
Name = reader.GetString(1),
PhoneNumber = reader.GetString(2),
Email = reader.GetString(3),
});
object photo = reader.GetValue(4);
result.ElementAt(result.Count - 1).Photo = DBNull.Value.Equals(photo) ?
null : XamlImageConverter.ConvertToImage((byte[])photo).Result;
}
database.Close();
return(result);
}
}
/// <summary>
/// Добавляет нового агента в базу данных
/// </summary>
/// <param name="c">Контрагент для добавления</param>
public static void AddContractor(Contractor c)
{
using (SqliteConnection database = new SqliteConnection($"Filename={DatabasePath}"))
{
database.Open();
SqliteCommand insertCommand = new SqliteCommand("INSERT INTO ContractorTable " +
"(Name, Phonenumber, Email, Photo)" +
"VALUES (@name, @phonenumber, @email, @photo)", database);
insertCommand.Parameters.AddWithValue("@name", c.Name);
insertCommand.Parameters.AddWithValue("@phonenumber", c.PhoneNumber);
insertCommand.Parameters.AddWithValue("@email", c.Email);
byte[] photo = XamlImageConverter.ConvertToByteArray(c.Photo);
if (photo == null)
{
insertCommand.Parameters.AddWithValue("@photo", DBNull.Value);
}
else
{
insertCommand.Parameters.AddWithValue("@photo", photo);
}
insertCommand.ExecuteReader();
c.ID = Convert.ToInt32(new SqliteCommand("SELECT MAX(ID) FROM ContractorTable", database).ExecuteScalar());
database.Close();
}
}
/// <summary>
/// Обновляет данные о контрагенте, присутствующем в базе данных
/// </summary>
/// <param name="c">Объект контрагента, данные которого подлежат обновлению</param>
public static void UpdateContractor(Contractor c)
{
using (SqliteConnection database = new SqliteConnection($"Filename={DatabasePath}"))
{
database.Open();
SqliteCommand updateCommand = new SqliteCommand("UPDATE ContractorTable SET Name=@name, Phonenumber=@phonenumber," +
" Email=@email, Photo=@photo WHERE ID=@id", database);
updateCommand.Parameters.AddWithValue("@id", c.ID);
updateCommand.Parameters.AddWithValue("@name", c.Name);
updateCommand.Parameters.AddWithValue("@phonenumber", c.PhoneNumber);
updateCommand.Parameters.AddWithValue("@email", c.Email);
byte[] photo = XamlImageConverter.ConvertToByteArray(c.Photo);
if (photo == null)
{
updateCommand.Parameters.AddWithValue("@photo", DBNull.Value);
}
else
{
updateCommand.Parameters.AddWithValue("@photo", photo);
}
updateCommand.ExecuteReader();
database.Close();
}
}