public void ImportEntriesFromCsvIntoList(ContactBookLogic contactbooklogic, string csvFileName, SQLConnection sql) { int csvLoop = 1; var csvFilePath = $@"C:\Users\nwolff\Desktop\git\contactbook\CsvFiles\{csvFileName}.csv"; //TODOL: Importing CSV-File from: {csvFilePath} using (StreamReader sr = new StreamReader(csvFilePath)) { string csvLine; sr.ReadLine(); while ((csvLine = sr.ReadLine()) != null) { ++csvLoop; var a = csvLine.Split(','); // turns csvLine into SplitStringArray a = a.Where(x => !string.IsNullOrEmpty(x)).ToArray(); //CSV CONTACT if (a.Length == 6) { ReadAndAddContactFromCsvLine(csvLine, sql); } //CSV LOCATION else if (a.Length == 2) { ReadAndAddLocationFromCsvFile(csvLine, sql); } // TODOL: else message Invalid Entry on line {csvLoop} : {csvLine}"); } } }
//TODO: confirmation messages etc. (maybe use events, tuples with boolean return value or premethods which check input) //ADD CONTACT METHOD public bool AddContact(ContactBookLogic contactbooklogic, SQLConnection sql, long countLocations, string name, Location location, long phoneNumber, string mailAddress, string gender) { int LocationID = (int)sql.GetLocationID(location); Contact contact = new Contact { Name = name, LocationID = LocationID, PhoneNumber = phoneNumber, MailAddress = mailAddress, Gender = gender }; 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) { var CommandText = $"INSERT INTO contacts(Name, LocationID, PhoneNumber, MailAddress, Gender) VALUES('{contact.Name}', '{LocationID}', '{contact.PhoneNumber}', '{contact.MailAddress}', '{contact.Gender}');"; sql.ExecuteNonQuery(CommandText); } return(conIsDupe); }
//---------------------------------------ADD OR GET LOCATION----------------------------------------------------------------------- public Location AddOrGetLocation(ContactBookLogic contactbooklogic, SQLConnection sql, long countLocations, string address, string cityName) { Location location = new Location() { Address = address, CityName = cityName, }; 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: info ob location geaddet oder dupe war und nicht geaddet } return(location); }
//----------------------------------------REMOVE METHOD------------------------------------------------------------------------------ public void RemoveContact(ContactBookLogic contactbooklogic, long countContact, SQLConnection sql, int value) { var CommandText = $"DELETE FROM contacts WHERE ContactID = '{value}';"; sql.ExecuteNonQuery(CommandText); //TODOL: contact successfully deleted message + invalid input message }
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 }