public void loadItems() { children = dbConnection.selectAllChildren(); parents = dbConnection.selectAllParents(); employees = dbConnection.selectAllStaff(); contacts = dbConnection.selectAllContacts(); Child exampleChild1 = new Child(); Parent exampleParent1 = new Parent(); exampleParent1.FirstName = "Lewis"; exampleParent1.LastName = "Sharp"; exampleParent1.Title = "Mr"; exampleParent1.Gender = 'M'; exampleParent1.WorkPhone = "01234567890"; exampleParent1.HomePhone = "07704123874"; exampleParent1.Email = "*****@*****.**"; exampleChild1.ChildID = 12; exampleChild1.FirstName = "Gemima"; exampleChild1.LastName = "Sharp"; exampleChild1.Gender = 'F'; exampleChild1.DOB = new DateTime(2008, 04, 15); exampleChild1.FirstLanguage = "English"; exampleChild1.RoomAttending = "Teddy's Transformers"; exampleChild1.DateApplied = new DateTime(2009, 06, 17); exampleChild1.DateLeft = new DateTime(); exampleChild1.Attendance = new bool[5] { true, false, false, true, true }; exampleChild1.ExtraDays = 2; exampleChild1.Teas = 3; exampleChild1.ParentsIDs.Add(exampleParent1.ParentID); children.Add(exampleChild1); parents.Add(exampleParent1); }
public frmEditChild(Child childToEdit) { InitializeComponent(); dbConnection = new Database(); ChildToEdit = childToEdit; editing = true; txtFirstName.Text = ChildToEdit.FirstName; txtLastName.Text = ChildToEdit.LastName; txtExtra.Text = ChildToEdit.ExtraDays.ToString(); txtTeas.Text = ChildToEdit.Teas.ToString(); if (ChildToEdit.Gender == 'M' ) { cmbGender.Text = "Male"; } else if (ChildToEdit.Gender == 'F') { cmbGender.Text = "Female"; } cmbLanguage.Text = ChildToEdit.FirstLanguage; cmbRoom.Text = ChildToEdit.RoomAttending; dtpDOB.Value = ChildToEdit.DOB; if (ChildToEdit.DateLeft != new DateTime(0001, 1, 1, 0, 0, 0)) { dtpLeaveDate.Value = ChildToEdit.DateLeft; } dtpStartDate.Value = ChildToEdit.DateApplied; chk1.Checked = ChildToEdit.Attendance[0]; chk2.Checked = ChildToEdit.Attendance[1]; chk3.Checked = ChildToEdit.Attendance[2]; chk4.Checked = ChildToEdit.Attendance[3]; chk5.Checked = ChildToEdit.Attendance[4]; }
private Child constructChild() { Child newChild = new Child(); newChild.FirstName = txtFirstName.Text; newChild.LastName = txtLastName.Text; newChild.Gender = cmbGender.Text[0]; newChild.DOB = dtpDOB.Value; newChild.FirstLanguage = cmbLanguage.Text; newChild.RoomAttending = cmbRoom.Text; newChild.DateApplied = dtpStartDate.Value; newChild.DateLeft = dtpLeaveDate.Value; newChild.ExtraDays = Convert.ToInt16(txtExtra.Text); newChild.Teas = Convert.ToInt16(txtTeas.Text); Control[] attendanceBoxes = this.Controls.Find("chk", false); newChild.Attendance[0] = chk1.Checked; newChild.Attendance[1] = chk2.Checked; newChild.Attendance[2] = chk3.Checked; newChild.Attendance[3] = chk4.Checked; newChild.Attendance[4] = chk5.Checked; return newChild; }
/*----------------------UPDATES-----------------------*/ public bool updateChild(Child childToUpdate) { MySqlConnection connection = OpenConnection(); if (connection == null) return false; MySqlTransaction transaction = connection.BeginTransaction(); try { MySqlCommand updateCommand = new MySqlCommand(null, connection); MySqlCommand idCommand = new MySqlCommand("SELECT LAST_INSERT_ID()", connection); //updateCommand.Transaction = transaction; // Create and prepare an SQL statement. updateCommand.CommandText = @"UPDATE child SET First_Name = @firstname, Last_Name = @lastname, Gender = @gender, DOB = @dob, First_Language = @firstlanguage, Room_Attending = @roomattending, Sibling = @sibling, Date_Applied = @dateapplied,Date_Left = @dateleft, Attendance = @attendance, Extra_Days = @extra, Teas = @teas, Medical_Information = @medical;"; //insertMedical(childToUpdate.MedicalInfo); //int medicalID = (int)idCommand.ExecuteScalar(); updateCommand.Parameters.AddWithValue("@firstname", childToUpdate.FirstName); updateCommand.Parameters.AddWithValue("@lastname", childToUpdate.LastName); updateCommand.Parameters.AddWithValue("@gender", childToUpdate.Gender); updateCommand.Parameters.AddWithValue("@dob", childToUpdate.DOB); updateCommand.Parameters.AddWithValue("@firstlanguage", childToUpdate.FirstLanguage); updateCommand.Parameters.AddWithValue("@roomattending", childToUpdate.RoomAttending); //updateCommand.Parameters.AddWithValue("@sibling", chileT); updateCommand.Parameters.AddWithValue("@dateapplied", childToUpdate.DateApplied); updateCommand.Parameters.AddWithValue("@dateleft", null); updateCommand.Parameters.AddWithValue("@attendance", 1/*childToUpdate.Attendance*/); updateCommand.Parameters.AddWithValue("@extra", childToUpdate.ExtraDays); updateCommand.Parameters.AddWithValue("@teas", childToUpdate.Teas); updateCommand.Parameters.AddWithValue("@medical", 1/*medicalID*/); // Prepare statement updateCommand.Prepare(); Console.WriteLine("Executing: [ " + updateCommand.CommandText + "]."); updateCommand.ExecuteNonQuery(); //childID = (int)idCommand.ExecuteScalar(); //insertAttendance(childToUpdate); //insertMedical(childToUpdate.MedicalInfo); //insertMedical(childToUpdate.MedicalInfo); //insertParent(parent); //parentID = (int)idCommand.ExecuteScalar(); //linkParentChild(childToUpdate, parent); //insertEmergencyContact(ec); //contactID = (int)idCommand.ExecuteScalar(); //linkECChild(ec, childToUpdate); //Perform transaction transaction.Commit(); } catch (MySqlException mysqle) { transaction.Rollback(); //Something went wrong, rollback return false; } return (CloseConnection(connection)); //Successful or not }
private void btnSave_Click(object sender, EventArgs e) { if (!validateForm()) return; if (ChildToAdd.ParentsIDs[0] == 0) { MessageBox.Show("Add a parent first"); } if (editing) { ChildToEdit = constructChild(); //Update to Database if (dbConnection.updateChild(ChildToAdd)) MessageBox.Show("Child updated successfully"); else MessageBox.Show("Problem occurred while updating child"); } else { ChildToAdd = constructChild(); //Add to Database if (dbConnection.insertChild(ChildToAdd)) MessageBox.Show("Child added successfully"); else MessageBox.Show("Problem occurred while adding child"); } this.Close(); }
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); }
public void linkParentChild(Child childToAdd, Parent parent) { MySqlConnection connection = OpenConnection(); if (connection == null) return; MySqlCommand insertCommand = new MySqlCommand(null, connection); insertCommand.CommandText = "INSERT INTO child_has_parent_guardian VALUES (@childID, @parentID);"; insertCommand.Parameters.AddWithValue("@childID", childToAdd.ChildID); insertCommand.Parameters.AddWithValue("@parentID", parent.ParentID); Console.WriteLine("Executing: [ " + insertCommand.CommandText + "]."); insertCommand.Prepare(); insertCommand.ExecuteNonQuery(); CloseConnection(connection); }
//TODO: Entity Framework and Linq? //TODO: Indexes //TODO: Prepared Statements! //TODO: Locks/Logs/Priveleges //TODO: STORED PROCEDURES! //TODO: VIEWS for levels of access! //TODO: TRANSACTIONS! //TODO: Joins instead of multiple selects? /*--------------INSERTS/UPDATES---------------*/ public bool insertChild(Child childToAdd) { MySqlConnection connection = OpenConnection(); if (connection == null) return false; MySqlTransaction transaction = connection.BeginTransaction(); try { MySqlCommand insertCommand = new MySqlCommand(null, connection); //updateCommand.Transaction = transaction; // Create and prepare an SQL statement. insertCommand.CommandText = @"INSERT INTO child (First_Name, Last_Name, Gender, DOB, First_Language, Room_Attending, Sibling, Date_Applied, Date_Left, Attendance, Extra_Days, Teas, Medical_Information) VALUES (@firstname, @lastname, @gender, @dob, @firstlanguage, @roomattending, @sibling, @dateapplied, @dateleft, @attendance, @extra, @teas, @medical);"; insertCommand.Parameters.AddWithValue("@firstname", childToAdd.FirstName); insertCommand.Parameters.AddWithValue("@lastname", childToAdd.LastName); insertCommand.Parameters.AddWithValue("@gender", childToAdd.Gender); insertCommand.Parameters.AddWithValue("@dob", childToAdd.DOB); insertCommand.Parameters.AddWithValue("@firstlanguage", childToAdd.FirstLanguage); insertCommand.Parameters.AddWithValue("@roomattending", childToAdd.RoomAttending); //updateCommand.Parameters.Add(siblingParam); insertCommand.Parameters.AddWithValue("@dateapplied", childToAdd.DateApplied); insertCommand.Parameters.AddWithValue("@dateleft", null); insertCommand.Parameters.AddWithValue("@attendance", lastAttendanceID); insertCommand.Parameters.AddWithValue("@extra", childToAdd.ExtraDays); insertCommand.Parameters.AddWithValue("@teas", childToAdd.Teas); insertCommand.Parameters.AddWithValue("@medical", lastMedicalID); // Prepare statement Console.WriteLine("Executing: [ " + insertCommand.CommandText + "]."); insertCommand.Prepare(); Console.WriteLine("Executing: [ " + insertCommand.CommandText + "]."); insertCommand.ExecuteNonQuery(); //Remember the last medical id MySqlCommand idCommand = new MySqlCommand("SELECT LAST_INSERT_ID()", connection); object test = idCommand.ExecuteScalar(); try { lastChildID = int.Parse(test.ToString()); } catch (ArgumentNullException) { } catch (FormatException) { } //insertAttendance(childToAdd); //insertMedical(childToUpdate.MedicalInfo); //insertMedical(childToUpdate.MedicalInfo); //insertParent(parent); //parentID = (int)idCommand.ExecuteScalar(); //linkParentChild(childToUpdate, parent); //insertEmergencyContact(ec); //contactID = (int)idCommand.ExecuteScalar(); //linkECChild(ec, childToUpdate); //Perform transaction //transaction.Commit(); } catch (MySqlException mysqle) { transaction.Rollback(); //Something went wrong, rollback return false; } return (CloseConnection(connection)); //Successful or not }
public void insertAttendance(Child childToAdd) { MySqlConnection connection = OpenConnection(); if (connection == null) return; MySqlCommand insertCommand = new MySqlCommand(null, connection); insertCommand.CommandText = @"INSERT INTO attendance VALUES (@child, @monday, @tuesday, @wednesday, @thursday, @friday);"; insertCommand.Parameters.AddWithValue("@child", childToAdd.ChildID); insertCommand.Parameters.AddWithValue("@monday", childToAdd.Attendance[0]); insertCommand.Parameters.AddWithValue("@tuesday", childToAdd.Attendance[1]); insertCommand.Parameters.AddWithValue("@wednesday", childToAdd.Attendance[2]); insertCommand.Parameters.AddWithValue("@thursday", childToAdd.Attendance[3]); insertCommand.Parameters.AddWithValue("@friday", childToAdd.Attendance[4]); Console.WriteLine("Executing: [ " + insertCommand.CommandText + "]."); insertCommand.Prepare(); insertCommand.ExecuteNonQuery(); //Remember the last attendance id //Remember the last medical id MySqlCommand idCommand = new MySqlCommand("SELECT LAST_INSERT_ID()", connection); object test = idCommand.ExecuteScalar(); try { lastAttendanceID = int.Parse(test.ToString()); } catch (ArgumentNullException) { } catch (FormatException) { } CloseConnection(connection); }
private Child constructChild(MySqlDataReader childrenReader) { Child newChild = new Child(); newChild.ChildID = childrenReader.GetInt32("Child_Id"); newChild.FirstName = childrenReader.GetString("First_Name"); newChild.LastName = childrenReader.GetString("Last_Name"); newChild.Gender = childrenReader.GetChar("Gender"); newChild.DOB = childrenReader.GetDateTime("DOB"); newChild.FirstLanguage = childrenReader.GetString("First_Language"); newChild.RoomAttending = childrenReader.GetString("Room_Attending"); //newChild.Sibling = SafeGetInt(childrenReader, "Sibling"); newChild.DateApplied = childrenReader.GetDateTime("Date_Applied"); newChild.DateLeft = SafeGetDateTime(childrenReader, "Date_Left"); newChild.Attendance = constructAttendance(childrenReader); newChild.ExtraDays = childrenReader.GetInt16("Extra_Days"); newChild.Teas = childrenReader.GetInt16("Teas"); newChild.MedicalInfo = constructMedical(childrenReader); //newChild.ParentsIDs.Add(childrenReader.GetInt16(16)); //newChild.EmergencyContactsIDs.Add(childrenReader.GetInt16(16)); //Get parents //foreach (int parentID in selectChildsParentIDs(newChild.ChildID)) // newChild.Parents.Add(constructParent(childrenReader, 16)); ////Get Emergency Contacts //foreach (int contactID in selectChildsContactIDs(newChild.ChildID)) // newChild.EmergencyContacts.Add(selectEmergencyContact(contactID), 30); return newChild; }
public frmChildReport(Child childtoDisplay,DataContainer data) { InitializeComponent(); child = childtoDisplay; this.data = data; }
public frmChildReport(Child childtoDisplay) { InitializeComponent(); child = childtoDisplay; data = new DataContainer(); }