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();
}
}