public void RetrieveDataFromDB(int choice) { SqlConnection connection = new SqlConnection(connectionString); try { using (connection) { SqlCommand command = new SqlCommand("spRetrieveContact", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@choice", choice); connection.Open(); SqlDataReader dr = command.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { ContactPerson person = new ContactPerson(dr.GetInt32(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), (long)dr.GetInt32(7), (long)dr.GetInt32(8)); List <ContactPerson> persons = this.personList.FindAll(person => person.firstName.Equals(dr.GetString(1))); bool flag = false; foreach (ContactPerson personOb in persons) { flag = person.Equals(personOb); if (flag == true) { Console.WriteLine("ID {0}. Duplicate Entry Found.", dr.GetInt32(0)); break; } } if (flag == false) { this.personList.Add(person); this.cityPersonMap.Add(person, dr.GetString(4)); this.statePersonMap.Add(person, dr.GetString(5)); id++; Console.WriteLine(person.toString()); } } } else { Console.WriteLine("No data found"); } dr.Close(); } } catch (Exception e) { throw new Exception(e.Message); } finally { //connection.Close(); } }
public void AddContact() { SqlConnection connection = new SqlConnection(connectionString); Console.WriteLine("Create a contact"); Console.WriteLine("----------------"); Console.WriteLine("Enter the first name:"); string firstName = Console.ReadLine(); while (!reName.IsMatch(firstName)) { Console.WriteLine("\nENTER A VALID fIRST NAME"); firstName = Console.ReadLine(); } Console.WriteLine("\nEnter the last name:"); string lastName = Console.ReadLine(); while (!reName.IsMatch(lastName)) { Console.WriteLine("\nENTER A VALID LAST NAME"); lastName = Console.ReadLine(); } Console.WriteLine("\nEnter the address:"); string adddress = Console.ReadLine(); Console.WriteLine("\nEnter the city:"); string city = Console.ReadLine(); Console.WriteLine("\nEnter the state:"); string state = Console.ReadLine(); Console.WriteLine("\nEnter the zip:"); string zip = Console.ReadLine(); while (!reZip.IsMatch(zip)) { Console.WriteLine("\nENTER A VALID ZIP"); zip = Console.ReadLine(); } Console.WriteLine("\nEnter the email:"); string email = Console.ReadLine(); while (!reEmail.IsMatch(email)) { Console.WriteLine("\nENTER A VALID EMAIL"); email = Console.ReadLine(); } Console.WriteLine("\nEnter the phone number:"); string phoneNumber = Console.ReadLine(); while (!rePhone.IsMatch(phoneNumber)) { Console.WriteLine("\nENTER A VALID PHONE NUMBER"); phoneNumber = Console.ReadLine(); } ContactPerson newPerson = new ContactPerson(id, firstName, lastName, adddress, city, state, email, long.Parse(zip), long.Parse(phoneNumber)); List <ContactPerson> persons = this.personList.FindAll(person => person.firstName.Equals(firstName)); bool flag = false; foreach (ContactPerson person in persons) { flag = newPerson.Equals(person); if (flag == true) { Console.WriteLine("\nContact Already Exists"); break; } } if (flag == false) { this.personList.Add(newPerson); this.cityPersonMap.Add(newPerson, city); this.statePersonMap.Add(newPerson, state); id++; } try { using (connection) { SqlCommand command = new SqlCommand("SpAddContact", this.connection); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@id", id); command.Parameters.AddWithValue("@first_name", firstName); command.Parameters.AddWithValue("@last_name", lastName); command.Parameters.AddWithValue("@address", adddress); command.Parameters.AddWithValue("@city", city); command.Parameters.AddWithValue("@state", state); command.Parameters.AddWithValue("@email", email); command.Parameters.AddWithValue("@zip", zip); command.Parameters.AddWithValue("@phone_number", phoneNumber); connection.Open(); var result = command.ExecuteNonQuery(); if (result != 0) { Console.WriteLine("{0} rows affected", result); } Console.WriteLine("Error while Adding contact"); } } catch (Exception e) { throw new Exception(e.Message); } finally { this.connection.Close(); } }