/// <summary> /// Loads a Person from the database. /// </summary> public PersonProperties Load(string socialSecurityNumber) { PersonProperties properties = new PersonProperties(); string query = "Select * from Person where SSN = '" + socialSecurityNumber + "'"; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, conn); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet, "Person"); DataTable table = dataSet.Tables["Person"]; bool socialSecurityNumberExists = (table.Rows.Count > 0); if (socialSecurityNumberExists) { DataRow row = table.Rows[0]; string name = row["Name"].ToString(); string birthDate = row["Birthdate"].ToString(); properties.SocialSecurityNumber = socialSecurityNumber; properties.Name = name; properties.BirthDate = birthDate; } else { throw new ArgumentException("The person with social security number, " + socialSecurityNumber + ", was not found."); } return(properties); }
/// <summary> /// Gets the core state fields, /// Social Security Number, Name and Birth Date. /// Used by PersonManager. /// </summary> /// <returns></returns> public PersonProperties GetState() { Trace.Assert(IsValid, "Require valid state."); PersonProperties properties = new PersonProperties(); properties.SocialSecurityNumber = this.SocialSecurityNumber; properties.Name = this.Name; properties.BirthDate = this.BirthDate; return(properties); }
/// <summary> /// Saves a Person to the database. /// </summary> public void Save(Person person) { PersonProperties properties = person.GetState(); string query = "Select * from Person where SSN = '" + properties.SocialSecurityNumber + "'"; OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, conn); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet, "Person"); DataTable table = dataSet.Tables["Person"]; bool socialSecurityNumberExists = (table.Rows.Count > 0); if (socialSecurityNumberExists) // Update { // Note: if Person is new the existing record will just be overwritten. // In a production environment we would disallow this. DataRow row = table.Rows[0]; row["SSN"] = properties.SocialSecurityNumber; row["Name"] = properties.Name; row["Birthdate"] = properties.BirthDate; // Update it OleDbCommandBuilder cb = new OleDbCommandBuilder(dataAdapter); dataAdapter.Update(dataSet, "Person"); } else // Insert { Trace.Assert(person.IsNew || person.IsDirty, "Person should be new or modified."); OleDbConnection dbConn = new OleDbConnection(conn); try { dbConn.Open(); string insertQuery = "INSERT INTO Person (SSN, Name, Birthdate) VALUES ('" + properties.SocialSecurityNumber + "', '" + properties.Name + "', '" + properties.BirthDate + "')"; OleDbCommand cmd = new OleDbCommand(insertQuery, dbConn); int numInserted = cmd.ExecuteNonQuery(); } finally { dbConn.Close(); } } }
/// <summary> /// Loads a Person from the database. /// Precondition: Must not be editing. /// Precondition: Must be new. /// Postcondition: Object is in a valid state. /// </summary> public void Load(string socialSecurityNumber) { Trace.Assert(!isEditing, "Must not be editing."); Trace.Assert(isNew, "Must be new."); // Load the object PersonManager manager = new PersonManager(); PersonProperties properties = manager.Load(socialSecurityNumber); Trace.Assert(properties.IsPopulated, "Person must have all its properties set."); this.SetState(properties); isNew = false; Trace.Assert(IsValid, "Should be valid."); }
/// <summary> /// Sets the state fields, /// Social Security Number, Name and Birth Date. private void SetState(PersonProperties properties) { // Ensure that the properties are valid // before setting into object RuleBroken("Social Security Number", properties.SocialSecurityNumber.Trim().Length != SocialSecurityNumberLength); RuleBroken("Name", properties.Name.Trim() == String.Empty); RuleBroken("Birth Date", IsDate(properties.BirthDate) == false); Trace.Assert(IsValid, "Must be valid state."); this.socialSecurityNumber = properties.SocialSecurityNumber; this.name = properties.Name; this.birthDate = properties.BirthDate; this.birthDateDt = Convert.ToDateTime(this.birthDate); this.age = CalculateAge(); }