static public List <Contact> read()
 {
     CountryDA.read();
     AddressDA.read();
     using (connect = new SqlConnection(conString))
     {
         connect.Open();
         CtactList.Clear();
         string command = "SELECT * FROM Contacts";
         sqlCommand = new SqlCommand(command, connect);
         try
         {
             //List<Contact> conList = new List<Contact>();
             data = sqlCommand.ExecuteReader();
             while (data.Read())
             {
                 Contact contact = null;
                 foreach (var item in AddressDA.AddList)
                 {
                     if (item.AddressID == (int)data[6])
                     {
                         contact = new Contact(
                             (string)data[1],
                             (string)data[2],
                             (int)data[3],
                             (string)data[4],
                             (string)data[5],
                             (int)data[6],
                             (long)data[7],
                             item);
                     }
                 }
                 if (contact == null)
                 {
                     contact = new Contact();
                 }
                 contact.ContactId = (int)data[0];
                 CtactList.Add(contact);
             }
             return(CtactList);
         }
         catch (Exception ex)
         {
             Console.WriteLine("ERROR DATA ACCESS - READ_CONTACTS: " + ex.Message);
             Console.ReadLine();
             throw;
         }
     }
 }
예제 #2
0
        public static void ConnectToDB(SqlConnection connect, string command, SqlCommand sqlCommand, Phone myPhone, List <Address> addList, List <Country> ctryList, string cmd)
        {
            switch (cmd)
            {
            case "INSERT_PHONE":
                command = $"INSERT INTO Phone " +
                          "VALUES (@phoneName, @firstName, @lastName, @phoneNumber, @email);";
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
                {
                    connect.Open();
                    using (sqlCommand = new SqlCommand(command, connect))
                    {
                        //myPhone = (Phone)myPhone;

                        sqlCommand.Parameters.AddWithValue("@phoneName", myPhone.PhoneName);
                        sqlCommand.Parameters.AddWithValue("@firstName", myPhone.Fname);
                        sqlCommand.Parameters.AddWithValue("@lastName", myPhone.Lname);
                        sqlCommand.Parameters.AddWithValue("@phoneNumber", myPhone.PhoneNumber);
                        sqlCommand.Parameters.AddWithValue("@email", myPhone.Email);
                        sqlCommand.ExecuteNonQuery();
                    }
                };
                connect.Close();
                break;

            case "READ_PHONE":
                //connect = new SqlConnection(conString);
                connect.Open();
                command    = "SELECT * FROM PHONE";
                sqlCommand = new SqlCommand(command, connect);
                SqlDataReader data = sqlCommand.ExecuteReader();
                while (data.Read())
                {
                    myPhone.PhoneName   = (string)data[0];
                    myPhone.Fname       = (string)data[1];
                    myPhone.Lname       = (string)data[2];
                    myPhone.PhoneNumber = (long)data[3];
                    myPhone.Email       = (string)data[4];
                }
                connect.Close();
                break;

            case "READ_CONTACTS":
                CountryDA.read(ctryList);

                /*connect.Open();
                 * command = "SELECT * FROM COUNTRY";
                 * sqlCommand = new SqlCommand(command, connect);
                 * data = sqlCommand.ExecuteReader();
                 * ctryList.RemoveAll(contact => contact.CountryID >= 0);
                 * while (data.Read())
                 * {
                 *  Country country = new Country();
                 *  country.CountryID = (int)data[0];
                 *  country.CountryName = (string)data[1];
                 *  country.CountryCode = (int)data[2];
                 *
                 *  ctryList.Add(country);
                 * }
                 * connect.Close();*/

                AddressDA.read(addList, ctryList);


                /*connect.Open();
                 * command = "SELECT * FROM ADDRESS";
                 * sqlCommand = new SqlCommand(command, connect);
                 * data = sqlCommand.ExecuteReader();
                 * addList.RemoveAll(address => address.AddressID >= 0);
                 * while (data.Read())
                 * {
                 *  Address address = null;
                 *  foreach (var item in ctryList)
                 *  {
                 *      if(item.CountryID == (int)data[5])
                 *      {
                 *          address = new Address(
                 *              (string)data[1],
                 *              (string)data[2],
                 *              (string)data[3],
                 *              (int)data[4],
                 *              (int)data[5],
                 *              item);
                 *      }
                 *  }
                 *  address.AddressID = (int)data[0];
                 *
                 *  addList.Add(address);
                 * }
                 * connect.Close();*/

                myPhone.contactList = ContactDA.read(addList);

                /*connect.Open();
                 * command = "SELECT * FROM CONTACTS";
                 * sqlCommand = new SqlCommand(command, connect);
                 * data = sqlCommand.ExecuteReader();
                 * myPhone.contactList = new List<Contact>();
                 * while (data.Read())
                 * {
                 *  Contact contact = null;
                 *  foreach (var item in addList)
                 *  {
                 *      if (item.AddressID == (int)data[5])
                 *      {
                 *          contact = new Contact(item);
                 *      }
                 *  }
                 *  contact.ContactId = (int)data[0];
                 *  contact.FName = (string)data[1];
                 *  contact.LName = (string)data[2];
                 *  contact.Age = (int)data[3];
                 *  contact.Gender = (string)data[4];
                 *  contact.AddressID = (int)data[5];
                 *  contact.PhoneNumber = (long)data[6];
                 *
                 *  myPhone.contactList.Add(contact);
                 * }
                 * connect.Close();*/

                break;

            default:
                break;
            }
        }