Eaeb97b81092455b2b39fd74a04304ef

Please help me make this code more maintainable.

Thanks in advance

public interface IName
{
    string getLastName();
    string getFirstName();
    string getMiddleName();
    string getSuffixName();
}

public class Name : IName
{
    private string _lastName;
    private string _firstName;
    private string _middleName;
    private string _suffixName;

    // Validation Methods
    // private set methods

    #region IName Members

    public string getLastName()
    {
        return _lastName;
    }

    public string getFirstName()
    {
        return _firstName;
    }

    public string getMiddleName()
    {
        return _middleName;
    }

    public string getSuffixName()
    {
        return _suffixName;
    }

    #endregion
}


public interface IAddress
{
    string getAddressNumber();
    string getAddressDirection();
    string getAddressStreet();
    string getAddressStreetType();
    string getAddressAptNum();
    string getAddressCity();
    string getAddressState();
    string getAddressZipcode();
    string getAddressCounty();
}

public class Address : IAddress
{
    private string _addressNumber;
    private string _addressDirection;
    private string _addressStreet;
    private string _addressStreetType;
    private string _addressAptNum;
    private string _addressrCity;
    private string _addressState;
    private string _addressZipcode;
    private string _addressCounty;

    // Validation Methods
    // private Set Methods
    // public get methods


    #region IAddress Members

    public string getAddressNumber()
    {
        return _addressNumber;
    }

    public string getAddressDirection()
    {
        return _addressDirection;
    }

    public string getAddressStreet()
    {
        return _addressStreet;
    }

    public string getAddressStreetType()
    {
        return _addressStreetType;
    }

    public string getAddressAptNum()
    {
        return _addressAptNum;
    }

    public string getAddressCity()
    {
        return _addressrCity;
    }

    public string getAddressState()
    {
        return _addressState;
    }

    public string getAddressZipcode()
    {
        return _addressZipcode;
    }

    public string getAddressCounty()
    {
        return _addressCounty;
    }

    #endregion
}
public interface IPerson
{
    int getId();
    IName getCurrentName();
    IName getFormerName();

    IAddress getCurrentAddress();
    IAddress getFormerAddress();
    IAddress getMailingAddress();

    DateTime getBirthdate();
    string getGender();
    string getSSNumber();
    string getPersonType();
    DateTime getRegistrationDate();
    string getIPAddress();
    string getBrowserInfo();

    string getHomePhone();
    string getWorkPhone();
    string getCellPhone();
    string getFaxNumber();
    string getEmailAddress();
    string getSecondaryEmailAddress();
    string getDriversLicense();

    bool save();
    void load();

}

public class Person : IPerson
{
    private int _id;
    private IName _currentName;
    private IName _formerName;

    private IAddress _currentAddress;
    private IAddress _formerAddress;
    private IAddress _mailingAddress;

    private DateTime _birthdate;
    private string _gender;
    private string _ssNumber;
    private string _personType;
    private DateTime _registrationDate;
    private string _ipAddress;
    private string _browserInfo;
    private string _driversLicense;

    private string _homePhone;
    private string _workPhone;
    private string _cellPhone;
    private string _faxNumber;
    private string _emailAddress;
    private string _secondaryEmailAddress;
    
    
    // private set methods

    #region IPerson Members

    public bool save()
    {
        DataLayer dl = new DataLayer();
        if (_id == 0)
            return dl.insertPerson(this);
        else
            return dl.updatePerson(this);
    }

    public void load()
    {
    }

    public int getId()
    {
        return _id;
    }

    public IName getCurrentName()
    {
        return _currentName;
    }

    public IName getFormerName()
    {
        return _formerName;
    }

    public IAddress getCurrentAddress()
    {
        return _currentAddress;
    }

    public IAddress getFormerAddress()
    {
        return _formerAddress;
    }

    public IAddress getMailingAddress()
    {
        return _mailingAddress;
    }

    public DateTime getBirthdate()
    {
        return _birthdate;
    }

    public string getGender()
    {
        return _gender;
    }

    public string getSSNumber()
    {
        return _ssNumber;
    }

    public string getPersonType()
    {
        return _personType;
    }

    public DateTime getRegistrationDate()
    {
        return _registrationDate;
    }

    public string getIPAddress()
    {
        return _ipAddress;
    }

    public string getBrowserInfo()
    {
        return _browserInfo;
    }

    public string getHomePhone()
    {
        return _homePhone;
    }

    public string getWorkPhone()
    {
        return _workPhone;
    }

    public string getCellPhone()
    {
        return _cellPhone;
    }

    public string getFaxNumber()
    {
        return _faxNumber;
    }

    public string getEmailAddress()
    {
        return _emailAddress;
    }

    public string getSecondaryEmailAddress()
    {
        return _secondaryEmailAddress;
    }

    public string getDriversLicense()
    {
        return _driversLicense;
    }

    #endregion
}

public class DataLayer
{

    public bool insertPerson(IPerson person)
    {
        bool inserted = false;
        SqlConnection cnDB = DatabaseConnection.GetOpenDBConnection();
        try
        {
            SqlCommand cmDB = new SqlCommand("sp_InsertName", cnDB);
            cmDB.CommandType = CommandType.StoredProcedure;
            cmDB.Parameters.Add("@last_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@last_name"].Value = person.getCurrentName().getLastName();
            cmDB.Parameters.Add("@first_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@first_name"].Value = person.getCurrentName().getFirstName();
            cmDB.Parameters.Add("@middle_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@middle_name"].Value = person.getCurrentName().getMiddleName();
            cmDB.Parameters.Add("@suffix_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@suffix_name"].Value = person.getCurrentName().getSuffixName();
            int id = cmDB.ExecuteNonQuery();

            cmDB = new SqlCommand("sp_InsertName", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@former_last_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_last_name"].Value = person.getFormerName().getLastName();
            cmDB.Parameters.Add("@former_first_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_first_name"].Value = person.getFormerName().getFirstName();
            cmDB.Parameters.Add("@former_middle_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_middle_name"].Value = person.getFormerName().getMiddleName();
            cmDB.Parameters.Add("@former_suffix_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_suffix_name"].Value = person.getFormerName().getSuffixName();
            cmDB.ExecuteNonQuery();

            // Insert Current Address

            cmDB = new SqlCommand("sp_InsertAddress", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@address_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_number"].Value = person.getCurrentAddress().getAddressNumber();
            cmDB.Parameters.Add("@address_direction", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_direction"].Value = person.getCurrentAddress().getAddressDirection();
            cmDB.Parameters.Add("@address_street", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street"].Value = person.getCurrentAddress().getAddressStreet();
            cmDB.Parameters.Add("@address_street_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street_type"].Value = person.getCurrentAddress().getAddressStreetType();
            cmDB.Parameters.Add("@address_apt_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_apt_number"].Value = person.getCurrentAddress().getAddressAptNum();
            cmDB.Parameters.Add("@address_city", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_city"].Value = person.getCurrentAddress().getAddressCity();
            cmDB.Parameters.Add("@address_state", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_state"].Value = person.getCurrentAddress().getAddressCity();
            cmDB.Parameters.Add("@address_zipcode", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_zipcode"].Value = person.getCurrentAddress().getAddressZipcode();
            cmDB.Parameters.Add("@address_county", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_county"].Value = person.getCurrentAddress().getAddressCounty();
            cmDB.ExecuteNonQuery();

            // Insert Former Address

            cmDB = new SqlCommand("sp_InsertAddress", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@address_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_number"].Value = person.getFormerAddress().getAddressNumber();
            cmDB.Parameters.Add("@address_direction", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_direction"].Value = person.getFormerAddress().getAddressDirection();
            cmDB.Parameters.Add("@address_street", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street"].Value = person.getFormerAddress().getAddressStreet();
            cmDB.Parameters.Add("@address_street_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street_type"].Value = person.getFormerAddress().getAddressStreetType();
            cmDB.Parameters.Add("@address_apt_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_apt_number"].Value = person.getFormerAddress().getAddressAptNum();
            cmDB.Parameters.Add("@address_city", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_city"].Value = person.getFormerAddress().getAddressCity();
            cmDB.Parameters.Add("@address_state", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_state"].Value = person.getFormerAddress().getAddressCity();
            cmDB.Parameters.Add("@address_zipcode", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_zipcode"].Value = person.getFormerAddress().getAddressZipcode();
            cmDB.Parameters.Add("@address_county", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_county"].Value = person.getFormerAddress().getAddressCounty();
            cmDB.ExecuteNonQuery();

            // Insert Mailing Address

            cmDB = new SqlCommand("sp_InsertAddress", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@address_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_number"].Value = person.getMailingAddress().getAddressNumber();
            cmDB.Parameters.Add("@address_direction", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_direction"].Value = person.getMailingAddress().getAddressDirection();
            cmDB.Parameters.Add("@address_street", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street"].Value = person.getMailingAddress().getAddressStreet();
            cmDB.Parameters.Add("@address_street_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street_type"].Value = person.getMailingAddress().getAddressStreetType();
            cmDB.Parameters.Add("@address_apt_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_apt_number"].Value = person.getMailingAddress().getAddressAptNum();
            cmDB.Parameters.Add("@address_city", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_city"].Value = person.getMailingAddress().getAddressCity();
            cmDB.Parameters.Add("@address_state", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_state"].Value = person.getMailingAddress().getAddressCity();
            cmDB.Parameters.Add("@address_zipcode", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_zipcode"].Value = person.getMailingAddress().getAddressZipcode();
            cmDB.Parameters.Add("@address_county", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_county"].Value = person.getMailingAddress().getAddressCounty();
            cmDB.ExecuteNonQuery();

            // insert Personal Info

            cmDB = new SqlCommand("sp_InsertPersonalInfo", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@birthdate", System.Data.SqlDbType.DateTime);
            cmDB.Parameters["@birthdate"].Value = person.getBirthdate();
            cmDB.Parameters.Add("@gender", System.Data.SqlDbType.VarChar,1);
            cmDB.Parameters["@gender"].Value = person.getGender();
            cmDB.Parameters.Add("@ss_number", System.Data.SqlDbType.VarChar,20);
            cmDB.Parameters["@ss_number"].Value = person.getSSNumber();
            cmDB.Parameters.Add("@person_type", System.Data.SqlDbType.VarChar,20);
            cmDB.Parameters["@person_type"].Value = person.getPersonType();
            cmDB.Parameters.Add("@registration_date", System.Data.SqlDbType.DateTime);
            cmDB.Parameters["@registration_date"].Value = person.getRegistrationDate();
            cmDB.Parameters.Add("@ip_address", System.Data.SqlDbType.VarChar,20);
            cmDB.Parameters["@ip_address"].Value = person.getIPAddress();
            cmDB.Parameters.Add("@browser_info", System.Data.SqlDbType.VarChar,20);
            cmDB.Parameters["@browser_info"].Value = person.getBrowserInfo();
            cmDB.Parameters.Add("@drivers_license", System.Data.SqlDbType.VarChar,20);
            cmDB.Parameters["@drivers_license"].Value = person.getDriversLicense();
            cmDB.ExecuteNonQuery();

            //insert email address contact type
            cmDB = new SqlCommand("sp_InsertContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar,50);
            cmDB.Parameters["@contact_type"].Value = "Email";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@gender"].Value = person.getEmailAddress();
            cmDB.ExecuteNonQuery();

            //insert secondary email address contact type
            cmDB = new SqlCommand("sp_InsertContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact_type"].Value = "Secondary Email";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@gender"].Value = person.getSecondaryEmailAddress();
            cmDB.ExecuteNonQuery();

            //insert home phone contact type
            cmDB = new SqlCommand("sp_InsertContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact_type"].Value = "Home Phone";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact"].Value = person.getHomePhone();
            cmDB.ExecuteNonQuery();

            //insert work phone contact type
            cmDB = new SqlCommand("sp_InsertContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact_type"].Value = "Work Phone";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact"].Value = person.getWorkPhone();
            cmDB.ExecuteNonQuery();

            //insert cell phone contact type
            cmDB = new SqlCommand("sp_InsertContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact_type"].Value = "Cell Phone";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact"].Value = person.getCellPhone();
            cmDB.ExecuteNonQuery();

            //insert cell phone contact type
            cmDB = new SqlCommand("sp_InsertContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact_type"].Value = "Fax Number";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact"].Value = person.getFaxNumber();
            cmDB.ExecuteNonQuery();


            inserted = true;
        }
        catch (SqlException sqlEx)
        {
            throw new Exception(GetSqlExceptionMessage(sqlEx.Number));
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (cnDB.State == ConnectionState.Open)
                cnDB.Close();
        }
        return inserted;
    }

    public bool updatePerson(IPerson person)
    {
        bool inserted = false;
        SqlConnection cnDB = DatabaseConnection.GetOpenDBConnection();
        try
        {
            string id = person.getId();

            SqlCommand cmDB = new SqlCommand("sp_UpdateName", cnDB);
            cmDB.CommandType = CommandType.StoredProcedure;
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@last_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@last_name"].Value = person.getCurrentName().getLastName();
            cmDB.Parameters.Add("@first_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@first_name"].Value = person.getCurrentName().getFirstName();
            cmDB.Parameters.Add("@middle_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@middle_name"].Value = person.getCurrentName().getMiddleName();
            cmDB.Parameters.Add("@suffix_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@suffix_name"].Value = person.getCurrentName().getSuffixName();
            cmDB.ExecuteNonQuery();

            cmDB = new SqlCommand("sp_UpdateName", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@former_last_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_last_name"].Value = person.getFormerName().getLastName();
            cmDB.Parameters.Add("@former_first_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_first_name"].Value = person.getFormerName().getFirstName();
            cmDB.Parameters.Add("@former_middle_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_middle_name"].Value = person.getFormerName().getMiddleName();
            cmDB.Parameters.Add("@former_suffix_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_suffix_name"].Value = person.getFormerName().getSuffixName();
            cmDB.ExecuteNonQuery();

            // Update Current Address

            cmDB = new SqlCommand("sp_UpdateAddress", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@address_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_number"].Value = person.getCurrentAddress().getAddressNumber();
            cmDB.Parameters.Add("@address_direction", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_direction"].Value = person.getCurrentAddress().getAddressDirection();
            cmDB.Parameters.Add("@address_street", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street"].Value = person.getCurrentAddress().getAddressStreet();
            cmDB.Parameters.Add("@address_street_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street_type"].Value = person.getCurrentAddress().getAddressStreetType();
            cmDB.Parameters.Add("@address_apt_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_apt_number"].Value = person.getCurrentAddress().getAddressAptNum();
            cmDB.Parameters.Add("@address_city", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_city"].Value = person.getCurrentAddress().getAddressCity();
            cmDB.Parameters.Add("@address_state", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_state"].Value = person.getCurrentAddress().getAddressCity();
            cmDB.Parameters.Add("@address_zipcode", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_zipcode"].Value = person.getCurrentAddress().getAddressZipcode();
            cmDB.Parameters.Add("@address_county", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_county"].Value = person.getCurrentAddress().getAddressCounty();
            cmDB.ExecuteNonQuery();

            // Update Former Address

            cmDB = new SqlCommand("sp_UpdateAddress", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@address_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_number"].Value = person.getFormerAddress().getAddressNumber();
            cmDB.Parameters.Add("@address_direction", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_direction"].Value = person.getFormerAddress().getAddressDirection();
            cmDB.Parameters.Add("@address_street", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street"].Value = person.getFormerAddress().getAddressStreet();
            cmDB.Parameters.Add("@address_street_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street_type"].Value = person.getFormerAddress().getAddressStreetType();
            cmDB.Parameters.Add("@address_apt_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_apt_number"].Value = person.getFormerAddress().getAddressAptNum();
            cmDB.Parameters.Add("@address_city", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_city"].Value = person.getFormerAddress().getAddressCity();
            cmDB.Parameters.Add("@address_state", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_state"].Value = person.getFormerAddress().getAddressCity();
            cmDB.Parameters.Add("@address_zipcode", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_zipcode"].Value = person.getFormerAddress().getAddressZipcode();
            cmDB.Parameters.Add("@address_county", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_county"].Value = person.getFormerAddress().getAddressCounty();
            cmDB.ExecuteNonQuery();

            // Update Mailing Address

            cmDB = new SqlCommand("sp_UpdateAddress", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@address_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_number"].Value = person.getMailingAddress().getAddressNumber();
            cmDB.Parameters.Add("@address_direction", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_direction"].Value = person.getMailingAddress().getAddressDirection();
            cmDB.Parameters.Add("@address_street", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street"].Value = person.getMailingAddress().getAddressStreet();
            cmDB.Parameters.Add("@address_street_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_street_type"].Value = person.getMailingAddress().getAddressStreetType();
            cmDB.Parameters.Add("@address_apt_number", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_apt_number"].Value = person.getMailingAddress().getAddressAptNum();
            cmDB.Parameters.Add("@address_city", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_city"].Value = person.getMailingAddress().getAddressCity();
            cmDB.Parameters.Add("@address_state", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_state"].Value = person.getMailingAddress().getAddressCity();
            cmDB.Parameters.Add("@address_zipcode", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_zipcode"].Value = person.getMailingAddress().getAddressZipcode();
            cmDB.Parameters.Add("@address_county", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@address_county"].Value = person.getMailingAddress().getAddressCounty();
            cmDB.ExecuteNonQuery();

            // Update Personal Info

            cmDB = new SqlCommand("sp_UpdatePersonalInfo", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@birthdate", System.Data.SqlDbType.DateTime);
            cmDB.Parameters["@birthdate"].Value = person.getBirthdate();
            cmDB.Parameters.Add("@gender", System.Data.SqlDbType.VarChar, 1);
            cmDB.Parameters["@gender"].Value = person.getGender();
            cmDB.Parameters.Add("@ss_number", System.Data.SqlDbType.VarChar, 20);
            cmDB.Parameters["@ss_number"].Value = person.getSSNumber();
            cmDB.Parameters.Add("@person_type", System.Data.SqlDbType.VarChar, 20);
            cmDB.Parameters["@person_type"].Value = person.getPersonType();
            cmDB.Parameters.Add("@registration_date", System.Data.SqlDbType.DateTime);
            cmDB.Parameters["@registration_date"].Value = person.getRegistrationDate();
            cmDB.Parameters.Add("@ip_address", System.Data.SqlDbType.VarChar, 20);
            cmDB.Parameters["@ip_address"].Value = person.getIPAddress();
            cmDB.Parameters.Add("@browser_info", System.Data.SqlDbType.VarChar, 20);
            cmDB.Parameters["@browser_info"].Value = person.getBrowserInfo();
            cmDB.Parameters.Add("@drivers_license", System.Data.SqlDbType.VarChar, 20);
            cmDB.Parameters["@drivers_license"].Value = person.getDriversLicense();
            cmDB.ExecuteNonQuery();

            //Update email address contact type
            cmDB = new SqlCommand("sp_UpdateContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact_type"].Value = "Email";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@gender"].Value = person.getEmailAddress();
            cmDB.ExecuteNonQuery();

            //Update secondary email address contact type
            cmDB = new SqlCommand("sp_UpdateContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact_type"].Value = "Secondary Email";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@gender"].Value = person.getSecondaryEmailAddress();
            cmDB.ExecuteNonQuery();

            //Update home phone contact type
            cmDB = new SqlCommand("sp_UpdateContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact_type"].Value = "Home Phone";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact"].Value = person.getHomePhone();
            cmDB.ExecuteNonQuery();

            //Update work phone contact type
            cmDB = new SqlCommand("sp_UpdateContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact_type"].Value = "Work Phone";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact"].Value = person.getWorkPhone();
            cmDB.ExecuteNonQuery();

            //Update cell phone contact type
            cmDB = new SqlCommand("sp_UpdateContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact_type"].Value = "Cell Phone";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact"].Value = person.getCellPhone();
            cmDB.ExecuteNonQuery();

            //Update cell phone contact type
            cmDB = new SqlCommand("sp_UpdateContactType", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@contact_type", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact_type"].Value = "Fax Number";
            cmDB.Parameters.Add("@contact", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@contact"].Value = person.getFaxNumber();
            cmDB.ExecuteNonQuery();
            inserted = true;
        }
        catch (SqlException sqlEx)
        {
            throw new Exception(GetSqlExceptionMessage(sqlEx.Number));
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (cnDB.State == ConnectionState.Open)
                cnDB.Close();
        }
        return inserted;

    }
}

Refactorings

No refactoring yet !

F9a9ba6663645458aa8630157ed5e71e

Ants

March 24, 2009, March 24, 2009 09:22, permalink

1 rating. Login to rate!

How does sp_InsertAddress and sp_UpdateAddress figure out that you are inserting/updating a current address as opposed to a former or mailing address? Is this based on the @address_street_type parameter containing "Current Address", "Former Address", or "Mailing Address" ? Or is @address_street_type actually suppose to contain "Avenue", "Street", "Road", "Place", "Lane", "Boulevard", etc?

Can I assume that using the @gender at lines 442, 452, 654, 664 typos? Same for getAddressCity() being put into @address_state?

Can the interfaces IName, IAddress, and IPerson be changed or deleted? With your code above it doesn't look like they are actually used much.

Below is a quick refactoring, but there is still room for a lot more that can be done. 

using System.Data;

// IName, Name, IAddress, Address, IPerson and Person same as original code above.

public class DataLayer
{
    public bool insertPerson(IPerson person)
    {
        SqlConnection cnDB = DatabaseConnection.GetOpenDBConnection();
        try
        {
            SqlCommand cmDB = new SqlCommand("sp_InsertName", cnDB);
            cmDB.CommandType = CommandType.StoredProcedure;
            cmDB.Parameters.Add("@last_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@last_name"].Value = person.getCurrentName().getLastName();
            cmDB.Parameters.Add("@first_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@first_name"].Value = person.getCurrentName().getFirstName();
            cmDB.Parameters.Add("@middle_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@middle_name"].Value = person.getCurrentName().getMiddleName();
            cmDB.Parameters.Add("@suffix_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@suffix_name"].Value = person.getCurrentName().getSuffixName();
            int id = cmDB.ExecuteNonQuery();

            cmDB = new SqlCommand("sp_InsertName", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@former_last_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_last_name"].Value = person.getFormerName().getLastName();
            cmDB.Parameters.Add("@former_first_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_first_name"].Value = person.getFormerName().getFirstName();
            cmDB.Parameters.Add("@former_middle_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_middle_name"].Value = person.getFormerName().getMiddleName();
            cmDB.Parameters.Add("@former_suffix_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_suffix_name"].Value = person.getFormerName().getSuffixName();
            cmDB.ExecuteNonQuery();

            InsertAddress(cnDB, id, person.getCurrentAddress());
            InsertAddress(cnDB, id, person.getFormerAddress());
            InsertAddress(cnDB, id, person.getMailingAddress());

            // insert Personal Info

            cmDB = new SqlCommand("sp_InsertPersonalInfo", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@birthdate", System.Data.SqlDbType.DateTime);
            cmDB.Parameters["@birthdate"].Value = person.getBirthdate();
            cmDB.Parameters.Add("@gender", System.Data.SqlDbType.VarChar,1);
            cmDB.Parameters["@gender"].Value = person.getGender();
            cmDB.Parameters.Add("@ss_number", System.Data.SqlDbType.VarChar,20);
            cmDB.Parameters["@ss_number"].Value = person.getSSNumber();
            cmDB.Parameters.Add("@person_type", System.Data.SqlDbType.VarChar,20);
            cmDB.Parameters["@person_type"].Value = person.getPersonType();
            cmDB.Parameters.Add("@registration_date", System.Data.SqlDbType.DateTime);
            cmDB.Parameters["@registration_date"].Value = person.getRegistrationDate();
            cmDB.Parameters.Add("@ip_address", System.Data.SqlDbType.VarChar,20);
            cmDB.Parameters["@ip_address"].Value = person.getIPAddress();
            cmDB.Parameters.Add("@browser_info", System.Data.SqlDbType.VarChar,20);
            cmDB.Parameters["@browser_info"].Value = person.getBrowserInfo();
            cmDB.Parameters.Add("@drivers_license", System.Data.SqlDbType.VarChar,20);
            cmDB.Parameters["@drivers_license"].Value = person.getDriversLicense();
            cmDB.ExecuteNonQuery();

            InsertContactType(cnDB, "Email", id, person.getEmailAddress());
            InsertContactType(cnDB, "Secondary Email", id, person.getSecondaryEmailAddress());
            InsertContactType(cnDB, "Home Phone", id, person.getHomePhone());
            InsertContactType(cnDB, "Work Phone", id, person.getWorkPhone());
            InsertContactType(cnDB, "Cell Phone", id, person.getCellPhone());
            InsertContactType(cnDB, "Fax Number", id, person.getFaxNumber());
        }
        catch (SqlException sqlEx)
        {
            throw new Exception(GetSqlExceptionMessage(sqlEx.Number));
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (cnDB.State == ConnectionState.Open)
                cnDB.Close();
        }
        return true;
    }

    public bool updatePerson(IPerson person)
    {
        SqlConnection cnDB = DatabaseConnection.GetOpenDBConnection();
        try
        {
            string id = person.getId();

            SqlCommand cmDB = new SqlCommand("sp_UpdateName", cnDB);
            cmDB.CommandType = CommandType.StoredProcedure;
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@last_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@last_name"].Value = person.getCurrentName().getLastName();
            cmDB.Parameters.Add("@first_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@first_name"].Value = person.getCurrentName().getFirstName();
            cmDB.Parameters.Add("@middle_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@middle_name"].Value = person.getCurrentName().getMiddleName();
            cmDB.Parameters.Add("@suffix_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@suffix_name"].Value = person.getCurrentName().getSuffixName();
            cmDB.ExecuteNonQuery();

            cmDB = new SqlCommand("sp_UpdateName", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@former_last_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_last_name"].Value = person.getFormerName().getLastName();
            cmDB.Parameters.Add("@former_first_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_first_name"].Value = person.getFormerName().getFirstName();
            cmDB.Parameters.Add("@former_middle_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_middle_name"].Value = person.getFormerName().getMiddleName();
            cmDB.Parameters.Add("@former_suffix_name", System.Data.SqlDbType.VarChar, 50);
            cmDB.Parameters["@former_suffix_name"].Value = person.getFormerName().getSuffixName();
            cmDB.ExecuteNonQuery();

            UpdateAddress(cnDB, id, person.getCurrentAddress());
            UpdateAddress(cnDB, id, person.getFormerAddress());
            UpdateAddress(cnDB, id, person.getMailingAddress());


            // Update Personal Info

            cmDB = new SqlCommand("sp_UpdatePersonalInfo", cnDB);
            cmDB.Parameters.Add("@person_id", System.Data.SqlDbType.Int);
            cmDB.Parameters["@person_id"].Value = id;
            cmDB.Parameters.Add("@birthdate", System.Data.SqlDbType.DateTime);
            cmDB.Parameters["@birthdate"].Value = person.getBirthdate();
            cmDB.Parameters.Add("@gender", System.Data.SqlDbType.VarChar, 1);
            cmDB.Parameters["@gender"].Value = person.getGender();
            cmDB.Parameters.Add("@ss_number", System.Data.SqlDbType.VarChar, 20);
            cmDB.Parameters["@ss_number"].Value = person.getSSNumber();
            cmDB.Parameters.Add("@person_type", System.Data.SqlDbType.VarChar, 20);
            cmDB.Parameters["@person_type"].Value = person.getPersonType();
            cmDB.Parameters.Add("@registration_date", System.Data.SqlDbType.DateTime);
            cmDB.Parameters["@registration_date"].Value = person.getRegistrationDate();
            cmDB.Parameters.Add("@ip_address", System.Data.SqlDbType.VarChar, 20);
            cmDB.Parameters["@ip_address"].Value = person.getIPAddress();
            cmDB.Parameters.Add("@browser_info", System.Data.SqlDbType.VarChar, 20);
            cmDB.Parameters["@browser_info"].Value = person.getBrowserInfo();
            cmDB.Parameters.Add("@drivers_license", System.Data.SqlDbType.VarChar, 20);
            cmDB.Parameters["@drivers_license"].Value = person.getDriversLicense();
            cmDB.ExecuteNonQuery();

            UpdateContactType(cnDB, "Email", id, person.getEmailAddress());
            UpdateContactType(cnDB, "Secondary Email", id, person.getSecondaryEmailAddress());
            UpdateContactType(cnDB, "Home Phone", id, person.getHomePhone());
            UpdateContactType(cnDB, "Work Phone", id, person.getWorkPhone());
            UpdateContactType(cnDB, "Cell Phone", id, person.getCellPhone());
            UpdateContactType(cnDB, "Fax Number", id, person.getFaxNumber());
        }
        catch (SqlException sqlEx)
        {
            throw new Exception(GetSqlExceptionMessage(sqlEx.Number));
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (cnDB.State == ConnectionState.Open)
                cnDB.Close();
        }
        return true;
    }

    void InsertContactType(SqlConnection connection, int id, string contactType, string value)
    {
        InsertUpdateContactType("sp_InsertContactType", connection, id, contactType, value);
    }

    void UpdateContactType(SqlConnection connection, int id, string contactType, string value)
    {
        InsertUpdateContactType("sp_UpdateContactType", connection, id, contactType, value);
    }

    void InsertUpdateContactType(string storedProc, SqlConnection connection, int id, string contactType, string value)
    {
        SqlCommand command = new SqlCommand(storedProc, connection);
        command.Parameters.Add("@person_id",    SqlDbType.Int)        .Value = id;
        command.Parameters.Add("@contact_type", SqlDbType.VarChar, 50).Value = contactType;
        command.Parameters.Add("@contact",      SqlDbType.VarChar, 50).Value = value;
        command.ExecuteNonQuery();
    }

    void InsertAddress(SqlConnection connection, int id, IAddress address)
    {
        InsertUpdateAddress("sp_InsertAddress", connection, id, address)
    }

    void UpdateAddress(SqlConnection connection, int id, IAddress address)
    {
        InsertUpdateAddress("sp_UpdateAddress", connection, id, address)
    }

    void InsertUpdateAddress(string storedProc, SqlConnection connection, int id, IAddress address)
    {
        SqlCommand command = new SqlCommand(storedProc, connection);
        command.Parameters.Add("@person_id",           SqlDbType.Int)        .Value = id;
        command.Parameters.Add("@address_number",      SqlDbType.VarChar, 50).Value = address.getAddressNumber();
        command.Parameters.Add("@address_direction",   SqlDbType.VarChar, 50).Value = address.getAddressDirection();
        command.Parameters.Add("@address_street",      SqlDbType.VarChar, 50).Value = address.getAddressStreet();
        command.Parameters.Add("@address_street_type", SqlDbType.VarChar, 50).Value = address.getAddressStreetType();
        command.Parameters.Add("@address_apt_number",  SqlDbType.VarChar, 50).Value = address.getAddressAptNum();
        command.Parameters.Add("@address_city",        SqlDbType.VarChar, 50).Value = address.getAddressCity();
        command.Parameters.Add("@address_state",       SqlDbType.VarChar, 50).Value = address.getAddressState();
        command.Parameters.Add("@address_zipcode",     SqlDbType.VarChar, 50).Value = address.getAddressZipcode();
        command.Parameters.Add("@address_county",      SqlDbType.VarChar, 50).Value = address.getAddressCounty();
        command.ExecuteNonQuery();
    }
}
Eaeb97b81092455b2b39fd74a04304ef

zsysop.myopenid.com

March 24, 2009, March 24, 2009 22:41, permalink

No rating. Login to rate!

How does sp_InsertAddress and sp_UpdateAddress figure out that you are inserting/updating a current address as opposed to a former or mailing address? Is this based on the @address_street_type parameter containing "Current Address", "Former Address", or "Mailing Address" ? Or is @address_street_type actually suppose to contain "Avenue", "Street", "Road", "Place", "Lane", "Boulevard", etc?

-- I forgot to add a parameter for @address_type. This would be used to detect if it is a current/former/mailing address.

Can I assume that using the @gender at lines 442, 452, 654, 664 typos? Same for getAddressCity() being put into @address_state?

-- Yes sorry about that.

Can the interfaces IName, IAddress, and IPerson be changed or deleted? With your code above it doesn't look like they are actually used much.

-- Yes they can be changed.

Below is a quick refactoring, but there is still room for a lot more that can be done.

-- Thanks for your help!

Your refactoring





Format Copy from initial code

or Cancel