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