public EmergencyContact selectEmergencyContact(int ecIDToSelect)
{
MySqlConnection connection = OpenConnection();
if (connection == null)
return null;
MySqlCommand selectCommand = new MySqlCommand(null, connection);
selectCommand.CommandText = @"SELECT * , homeAddr.Address_1 as home1, homeAddr.City as homeCity, homeAddr.County as homeCounty, homeAddr.PostCode as homePostCode,
workAddr.Address_1 as work1, workAddr.City as workCity, workAddr.County as workCounty, workAddr.PostCode as workPostCode
FROM emergency_contact
INNER JOIN address homeAddr ON emergency_contact.Home_Address = homeAddr.Address_1
INNER JOIN address workAddr ON emergency_contact.Work_Address = workAddr.Address_1
WHERE emergency_contact.Contact_ID = @contactID;";
selectCommand.Parameters.AddWithValue("@contactID", ecIDToSelect);
Console.WriteLine("Executing: [ " + selectCommand.CommandText + "].");
selectCommand.Prepare();
MySqlDataReader ECReader = selectCommand.ExecuteReader();
//Package into EmergencyContact domain entity object
EmergencyContact newEC = new EmergencyContact();
while (ECReader.Read())
{
newEC = constructEmergencyContact(ECReader);
}
ECReader.Close();
CloseConnection(connection);
return newEC;
}
public void linkECChild(EmergencyContact ecToAdd, Child childToAdd)
{
MySqlConnection connection = OpenConnection();
if (connection == null)
return;
MySqlCommand insertCommand = new MySqlCommand(null, connection);
insertCommand.CommandText = "INSERT INTO child_has_emergency_contact VALUES (@contactID, @childID);";
insertCommand.Parameters.AddWithValue("@contactID", ecToAdd.ContactID);
insertCommand.Parameters.AddWithValue("@childID", childToAdd.ChildID);
Console.WriteLine("Executing: [ " + insertCommand.CommandText + "].");
insertCommand.Prepare();
insertCommand.ExecuteNonQuery();
CloseConnection(connection);
}
private EmergencyContact constructEmergencyContact(MySqlDataReader ECReader)
{
EmergencyContact newEC = new EmergencyContact();
newEC.ContactID = ECReader.GetInt32("Contact_ID");
newEC.Title = ECReader.GetString("Title");
newEC.FirstName = ECReader.GetString("First_Name");
newEC.LastName = ECReader.GetString("Last_Name");
newEC.Relationship = ECReader.GetString("Relationship");
newEC.HomePhone = ECReader.GetString("Home_Phone");
newEC.WorkPhone = ECReader.GetString("Work_Phone");
newEC.MobilePhone = ECReader.GetString("Mobile_Phone");
newEC.HomeAddress = constructMultipleAddress(ECReader, "home");
newEC.WorkAddress = constructMultipleAddress(ECReader, "work");
newEC.Gender = ECReader.GetChar("Gender");
newEC.Email = ECReader.GetString("Email");
return newEC;
}
public void insertEmergencyContact(EmergencyContact ecToAdd)
{
MySqlConnection connection = OpenConnection();
if (connection == null)
return;
MySqlCommand insertCommand = new MySqlCommand(null, connection);
insertCommand.CommandText = @"INSERT INTO emergency_contact (Title, First_Name, Last_Name, Relationship, Home_Phone, Work_Phone, Mobile_Phone, Home_Address, Work_Address, Gender, Email)
VALUES (@title, @firstname, @lastname, @relationship, @homephone, @workphone, @mobilephone, @homeaddress, @workaddress, @gender, @email);";
//MySqlParameter idParam = new MySqlParameter("@id", ecToAdd.ContactID);
//MySqlParameter fNameParam = new MySqlParameter("@firstname", ecToAdd.FirstName);
//MySqlParameter lNameParam = new MySqlParameter("@lastname", ecToAdd.LastName);
//MySqlParameter genderParam = new MySqlParameter("@gender", ecToAdd.Gender);
//MySqlParameter titleParam = new MySqlParameter("@title", ecToAdd.Title);
//MySqlParameter relParam = new MySqlParameter("@relationship", ecToAdd.Relationship);
//MySqlParameter workPhoneParam = new MySqlParameter("@workphone", ecToAdd.WorkPhone);
//MySqlParameter homePhoneParam = new MySqlParameter("@homephone", ecToAdd.HomePhone);
//MySqlParameter mobilePhoneParam = new MySqlParameter("@mobilephone", ecToAdd.MobilePhone);
//MySqlParameter homeAddrParam = new MySqlParameter("@homeaddress", ecToAdd.HomeAddress.Address1);
//MySqlParameter workAddrParamParam = new MySqlParameter("@workaddress", ecToAdd.WorkAddress.Address1);
//MySqlParameter emailParam = new MySqlParameter("@email", ecToAdd.Email);
insertCommand.Parameters.AddWithValue("@title", ecToAdd.Title);
insertCommand.Parameters.AddWithValue("@firstname", ecToAdd.FirstName);
insertCommand.Parameters.AddWithValue("@lastname", ecToAdd.LastName);
insertCommand.Parameters.AddWithValue("@relationship", ecToAdd.Relationship);
insertCommand.Parameters.AddWithValue("@homephone", ecToAdd.HomePhone);
insertCommand.Parameters.AddWithValue("@workphone", ecToAdd.WorkPhone);
insertCommand.Parameters.AddWithValue("@mobilephone", ecToAdd.MobilePhone);
insertCommand.Parameters.AddWithValue("@homeaddress", ecToAdd.HomeAddress.Address1);
insertCommand.Parameters.AddWithValue("@workaddress", ecToAdd.WorkAddress.Address1);
insertCommand.Parameters.AddWithValue("@gender", ecToAdd.Gender);
insertCommand.Parameters.AddWithValue("@email", ecToAdd.Email);
Console.WriteLine("Executing: [ " + insertCommand.CommandText + "].");
insertCommand.Prepare();
insertCommand.ExecuteNonQuery();
//Remember the last medical id
MySqlCommand idCommand = new MySqlCommand("SELECT LAST_INSERT_ID()", connection);
object test = idCommand.ExecuteScalar();
try
{
lastContactID = int.Parse(test.ToString());
}
catch (ArgumentNullException)
{
}
catch (FormatException)
{
}
CloseConnection(connection);
}
public frmParentReport(EmergencyContact ec)
{
InitializeComponent();
//this.Text = title;
}