private static void Write_Programm_to_DB(LibHost.Program programm, MySqlConnection connection) //метод который пишет информацию о программах в БД { if (Sql_Query_Execute("SELECT EXISTS(SELECT id FROM programs WHERE name_version_hash=" + programm.hash + ");", connection) == "1") { programm.program_id = int.Parse(Sql_Query_Execute("SELECT id FROM programs WHERE name_version_hash=" + programm.hash + ";", connection)); } else { programm.program_id = int.Parse(Sql_Query_Execute("INSERT INTO programs (name_version_hash, name, version, vendor) VALUES (" + programm.hash + ",'" + programm.name + "','" + programm.version + "', '" + programm.vendor + "' ); SELECT LAST_INSERT_ID();", connection)); } }
private static void SearchChangePrograms(LibHost.Host host, MySqlConnection connection) //поиск изменений в составе программ, работает аналогично предыдущему, с небольшой разницей { List <int> oldProgramHash = new List <int>(); List <int> newProgramHash = new List <int>(); MySqlDataReader reader = Get_Table_From_DB("SELECT name_version_hash FROM programs WHERE id IN (SELECT program_id FROM host_programs WHERE host_id=" + host.host_id + ");", connection); if (reader.HasRows) { while (reader.Read()) { oldProgramHash.Add(reader.GetInt32("name_version_hash")); } } reader.Close(); Sql_Query_Execute("DELETE FROM host_programs WHERE host_id=" + host.host_id + ";", connection); foreach (LibHost.Program item in host.Programs) { newProgramHash.Add(item.hash); } List <int> installedPrograms = newProgramHash.Except(oldProgramHash).ToList(); List <int> uninstalledPrograms = oldProgramHash.Except(newProgramHash).ToList(); foreach (int item in installedPrograms) { int program_id; if (Sql_Query_Execute("SELECT EXISTS(SELECT id FROM programs WHERE name_version_hash=" + item + "); ", connection) == "1") //проверяем есть ли запись о такой программе в БД { //если есть то получаем id program_id = int.Parse(Sql_Query_Execute("SELECT id FROM programs WHERE name_version_hash=" + item + "; ", connection)); } else { //если енет то добавляем запись и получаем id LibHost.Program temp = host.Programs.Find(x => x.hash == item); program_id = int.Parse(Sql_Query_Execute("INSERT INTO programs (name_version_hash, name, version, vendor) VALUES (" + temp.hash + ",'" + temp.name + "','" + temp.version + "', '" + temp.vendor + "' ); SELECT LAST_INSERT_ID();", connection)); } Sql_Query_Execute("INSERT INTO host_program_history (host_id, program_id,action,looked,date) VALUES (" + host.host_id + "," + program_id + ", 1, 0, '" + DateTime.Now + "');", connection); } foreach (int item in uninstalledPrograms) { Sql_Query_Execute("INSERT INTO host_program_history (host_id, program_id,action,looked,date) VALUES (" + host.host_id + "," + Sql_Query_Execute("SELECT id FROM programs WHERE name_version_hash=" + item + "; ", connection).ToString() + ", 0, 0, '" + DateTime.Now + "');", connection); } }