Esempio n. 1
0
        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();
            }
        }
Esempio n. 2
0
        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();
            }
        }