/*
#####################################################################
# Q1
# person._Person._PERSONCODE
# person._Person._ADDRESSCODE
# person._Person._FIRSTNAME
# person._Person._LASTNAME
# person._Person._EMAIL
# person._Person._DOB
#
# person._Address._ADDRESSCODE
# person._Address._STREET_NO
# person._Address._STREET_ADDRESS
# person._Address._CITY
# person._Address._STATE
# person._Address._ZIP
# person._Address._X
# person._Address._Y
#
# person._Phone._PERSONCODE
# person._Phone._PHONENUMBER
# person._Phone._TYPE
#####################################################################
*/
public static List <PersonPhoneAddress> SearchPersons(string name, string family, string email)
{
List <PersonPhoneAddress> persons = new List <PersonPhoneAddress>();
PersonPhoneAddress person;
if (Connect())
{
using (SqlCommand sqlCommand = new SqlCommand("Q1", _Connection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_SearchPersons";
sqlCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = name;
sqlCommand.Parameters.Add("@family", SqlDbType.VarChar).Value = family;
sqlCommand.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
SqlDataReader reader;
try
{
reader = sqlCommand.ExecuteReader();
}
catch (Exception ex)
{
return(null);
}
while (reader.Read())
{
person = new PersonPhoneAddress();
person._Person._PERSONCODE = Convert.ToInt32(reader[0]);
person._Person._ADDRESSCODE = Convert.ToInt32(reader[1]);
person._Person._FIRSTNAME = Convert.ToString(reader[2]);
person._Person._LASTNAME = Convert.ToString(reader[3]);
person._Person._EMAIL = Convert.ToString(reader[4]);
person._Person._DOB = Convert.ToString(reader[5]);
person._Address._ADDRESSCODE = Convert.ToInt32(reader[1]);
person._Address._STREET_NO = Convert.ToString(reader[6]);
person._Address._STREET_ADDRESS = Convert.ToString(reader[7]);
person._Address._CITY = Convert.ToString(reader[8]);
person._Address._ZIP = Convert.ToString(reader[9]);
person._Address._X = Convert.ToString(reader[10]);
person._Address._Y = Convert.ToString(reader[11]);
person._Phone._PERSONCODE = Convert.ToInt32(reader[0]);
person._Phone._PHONENUMBER = Convert.ToString(reader[12]);
person._Phone._TYPE = Convert.ToString(reader[13]);
persons.Add(person);
}
reader.Close();
}
}
else
{
return(null);
}
return(persons);
}
/*
#####################################################################
# Q1
# person._Person._PERSONCODE
# person._Person._ADDRESSCODE
# person._Person._FIRSTNAME
# person._Person._LASTNAME
# person._Person._EMAIL
# person._Person._DOB
#
# person._Address._ADDRESSCODE
# person._Address._STREET_NO
# person._Address._STREET_ADDRESS
# person._Address._CITY
# person._Address._STATE
# person._Address._ZIP
# person._Address._X
# person._Address._Y
#
# person._Phone._PERSONCODE
# person._Phone._PHONENUMBER
# person._Phone._TYPE
#####################################################################
*/
public static List <PersonPhoneAddress> SearchCustomers(string name, string family, string email)
{
string Q1Query = "select p.PERSONCODE, a.ADDRESSCODE, p.FIRSTNAME, p.LASTNAME, " +
"p.EMAIL, p.DOB, a.STREET_NO, a.STREET_ADDRESS, a.CITY, a.STATE, a.ZIP, a.X, a.Y, " +
"ph.PHONE_NUMBER, ph.PHONE_TYPE " +
"from person p, address a, PHONE ph " +
"where p.firstname like '%" + name + "%' and p.lastname like '%" + family + "%' and p.email like '%" + email + "%' " +
"and p.ADDRESSCODE = a.ADDRESSCODE " +
"and ph.PERSONCODE = p.PERSONCODE " +
"and not exists (select * from WORKS_IN w where w.PERSONCODE = p.PERSONCODE) " +
"order by p.LASTNAME asc, p.FIRSTNAME asc, p.PERSONCODE asc ";
List <PersonPhoneAddress> persons = new List <PersonPhoneAddress>();
//PersonPhoneAddress person;
if (Connect())
{
//we are going to call query called Q1
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = Q1Query;
sqlCommand.Connection = _Connection;
SqlDataReader reader;
try { reader = sqlCommand.ExecuteReader(); }
catch (Exception ex) {
return(null);
}
//Converting query results to PersonPhoneAddress objects
while (reader.Read())
{
PersonPhoneAddress person = new PersonPhoneAddress();
person._Person._PERSONCODE = Convert.ToInt32(reader[0]);
person._Person._ADDRESSCODE = Convert.ToInt32(reader[1]);
person._Person._FIRSTNAME = Convert.ToString(reader[2]);
person._Person._LASTNAME = Convert.ToString(reader[3]);
person._Person._EMAIL = Convert.ToString(reader[4]);
person._Person._DOB = Convert.ToString(reader[5]);
person._Address._ADDRESSCODE = Convert.ToInt32(reader[1]);
person._Address._STREET_NO = Convert.ToString(reader[6]);
person._Address._STREET_ADDRESS = Convert.ToString(reader[7]);
person._Address._CITY = Convert.ToString(reader[8]);
person._Address._STATE = Convert.ToString(reader[9]);
person._Address._ZIP = Convert.ToString(reader[10]);
person._Address._X = Convert.ToString(reader[11]);
person._Address._Y = Convert.ToString(reader[12]);
person._Phone._PERSONCODE = Convert.ToInt32(reader[0]);
person._Phone._PHONENUMBER = Convert.ToString(reader[13]);
person._Phone._TYPE = Convert.ToString(reader[14]);
persons.Add(person);
}
reader.Close();
}
}
return(persons);
}