public void RemoveLocation(ContactBookLogic contactbooklogic, long countLocation, SQLConnection sql, int value) { var CommandText = $"SELECT COUNT(l.LocationID) FROM locations l, contacts c WHERE {value} = c.LocationID AND {value} = l.LocationID;"; // check ob locationID von der zu löschenden location in contacts vorhanden ist - wenn ja -> nicht löschen long count = sql.ExecuteScalar(CommandText); CommandText = $"SELECT * FROM locations WHERE LocationID = {value}"; long c = sql.ExecuteScalar(CommandText); if (count == 0 && c > 0) { CommandText = $"DELETE FROM locations WHERE LocationID = {value};"; sql.ExecuteNonQuery(CommandText); //TODOL: location successfully deleted message } //TODOL: all messages for : alternatives else 1. no location with index value found 2. you can not delete location that is associated to a contact 3. invalid input }
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 EditLocation(int inputindex, string c, SQLConnection sql, string newValue) { var CommandText = ""; 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) //TODOH: warum kann man keine location ändern die einem contact zugewiesen ist { if (c == "1") { CommandText = $"UPDATE locations SET Address = '{newValue}' WHERE LocationID = {inputindex};"; sql.ExecuteNonQuery(CommandText); } else if (c == "2") { CommandText = $"UPDATE locations SET CityName = '{newValue}' WHERE LocationID = {inputindex};"; sql.ExecuteNonQuery(CommandText); } 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 }
//--------------------------------------------------EDIT------------------------------------------------------------------------ public void EditContact(int inputindex, string c, SQLConnection sql, string newValue) { //TODOL: message successfully changed old to new for all 3 options on correct position bool newValueIsNumber = long.TryParse(newValue, out long newphoneno); //TODOL: this does not work if name is int var CommandText = ""; if (c == "1") { CommandText = $"UPDATE contacts SET Name = '{newValue}' WHERE ContactID = {inputindex};"; sql.ExecuteNonQuery(CommandText); } else if (c == "2") { CommandText = $"UPDATE contacts SET phoneNumber = '{newphoneno}' WHERE ContactID = {inputindex};"; sql.ExecuteNonQuery(CommandText); } else if (c == "3") { CommandText = $"UPDATE contacts SET MailAddress = '{newValue}' WHERE ContactID = {inputindex};"; sql.ExecuteNonQuery(CommandText); } Contact contact = sql.OutputSingleContact(inputindex); CommandText = $"SELECT COUNT(*) FROM contacts c INNER JOIN locations l ON c.LocationID = l.LocationID WHERE c.Name = '{contact.Name}' AND c.PhoneNumber = {contact.PhoneNumber} AND c.LocationID = {contact.LocationID} AND c.MailAddress = '{contact.MailAddress}' AND c.Gender = '{contact.Gender}' "; long dupecount = sql.ExecuteScalar(CommandText); if (dupecount >= 2) { CommandText = $"DELETE FROM contacts WHERE ContactID = '{inputindex}';"; sql.ExecuteNonQuery(CommandText); //TODOL: message Contact with index: {inputindex} was a duplicate after editing and got removed. } //TODOL: show old and new value after edit //GET OLD VALUE SAMPLE //CommandText = $"SELECT Name FROM contacts WHERE ContactID = {inputindex};"; //string beforeEditValue = sql.GetBeforeEditValueString(inputindex, CommandText); }