public void EditLocation(int inputindex, string c, SQLConnection sql, string newValue) { var CommandText = ""; var beforeEditValue = ""; bool newValueIsCorrectInput = false; Location location = sql.OutputSingleLocation(inputindex); CommandText = $"SELECT COUNT(*) FROM contacts c INNER JOIN locations l WHERE l.LocationID = c.LocationID AND l.Address = '{location.Address}' AND l.CityName = '{location.CityName}';"; long locHasConCount = sql.ExecuteScalar(CommandText); if (locHasConCount == 0) { if (c == "1") { CommandText = $"SELECT Address FROM locations WHERE LocationID = {inputindex};"; beforeEditValue = sql.GetBeforeEditValueString(inputindex, CommandText); newValueIsCorrectInput = InputChecker.NoEmptyInputCheck(newValue); CommandText = $"UPDATE locations SET Address = '{newValue}' WHERE LocationID = {inputindex};"; } else if (c == "2") { CommandText = $"SELECT CityName FROM locations WHERE LocationID = {inputindex};"; beforeEditValue = sql.GetBeforeEditValueString(inputindex, CommandText); newValueIsCorrectInput = InputChecker.NoEmptyInputCheck(newValue); CommandText = $"UPDATE locations SET CityName = '{newValue}' WHERE LocationID = {inputindex};"; } sql.ExecuteNonQuery(CommandText); //TODOL: message Contactname successfully changed from {beforeEditValue} to {newValue}! CommandText = $"SELECT COUNT(*) FROM locations l WHERE l.Address = '{location.Address}' AND l.CityName = '{location.CityName}';"; long dupecount = sql.ExecuteScalar(CommandText); if (dupecount >= 2) { CommandText = $"DELETE FROM locations WHERE LocationID = '{inputindex}';"; sql.ExecuteNonQuery(CommandText); //TODOL: message Location with index: {inputindex} was a duplicate after editing and got removed. } } //TODOL: message WARNING: You can't edit a location that is linked to an existing contact }
public void ReadAndAddLocationFromCsvFile(string csvLine, SQLConnection sql) { string[] parts = csvLine.Split(','); parts = parts.Where(x => !string.IsNullOrEmpty(x)).ToArray(); string addressPart = InputChecker.CsvEmptyInputCheck(parts[0]); string cityNamePart = InputChecker.CsvEmptyInputCheck(parts[1]); if (addressPart != "wronginput" && cityNamePart != "wronginput") { Location location = new Location() { Address = addressPart, CityName = cityNamePart, }; List <Location> locationslist = sql.OutputLocationTableToList(); var locIsDupe = false; var CommandText = ""; foreach (var loc1 in locationslist) { if ((location.Address == loc1.Address) && (location.CityName == loc1.CityName)) { locIsDupe = true; } } if (!locIsDupe) { CommandText = $"INSERT INTO locations(Address, CityName) VALUES ('{location.Address}', '{location.CityName}')"; sql.ExecuteNonQuery(CommandText); } //TODOL: message INFO: Location is duplicate and will not be added } //TODOL: message WARNING: Wrong input on line: {csvLine} }
public void ReadAndAddContactFromCsvLine(string csvLine, SQLConnection sql) { string[] parts = csvLine.Split(','); string namePart = InputChecker.CsvEmptyInputCheck(parts[0]); string addressPart = InputChecker.CsvEmptyInputCheck(parts[1]); string cityNamePart = InputChecker.CsvEmptyInputCheck(parts[2]); long.TryParse(parts[3], out long a); long phoneNumberPart = a; string mailAddressPart = InputChecker.CsvMailFormatCheck(parts[4]); string genderPart = InputChecker.CsvGenderCheck(parts[5]); if (phoneNumberPart != 0 && genderPart != "wronginput" && namePart != "wronginput" && addressPart != "wronginput" && cityNamePart != "wronginput") { Location location = new Location() { Address = addressPart, CityName = cityNamePart, }; List <Location> locationslist = sql.OutputLocationTableToList(); var locIsDupe = false; var CommandText = ""; foreach (var loc1 in locationslist) { if ((location.Address == loc1.Address) && (location.CityName == loc1.CityName)) { locIsDupe = true; } } if (!locIsDupe) { CommandText = $"INSERT INTO locations(Address, CityName) VALUES ('{location.Address}', '{location.CityName}')"; sql.ExecuteNonQuery(CommandText); } // TODOL: Message INFO: Location is duplicate and will not be added long LocationID = sql.GetLocationID(location); var contact = new Contact { Name = namePart, LocationID = (int)LocationID, PhoneNumber = phoneNumberPart, MailAddress = mailAddressPart, Gender = genderPart }; List <Contact> contactslist = sql.OutputContactTableToList(); var conIsDupe = false; foreach (var con1 in contactslist) { if ((contact.Name == con1.Name) && (contact.LocationID == con1.LocationID) && (contact.PhoneNumber == con1.PhoneNumber) && (contact.MailAddress == con1.MailAddress) && (contact.Gender == con1.Gender)) { conIsDupe = true; } } if (!conIsDupe) { CommandText = $"INSERT INTO contacts(Name, LocationID, PhoneNumber, MailAddress, Gender) VALUES('{contact.Name}', '{contact.LocationID}', '{contact.PhoneNumber}', '{contact.MailAddress}', '{contact.Gender}');"; sql.ExecuteNonQuery(CommandText); } // TODOL: Message INFO: Contact on {csvLine} is duplicate and will not be added } // TODOL: message WARNING: Wrong input on line: {csvLine} }