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