Beispiel #1
0
 // CONSTRUCTOR
 /// <summary>
 /// constructor for adress
 /// </summary>
 /// <param name="propertyName">the property name</param>
 /// <param name="propertyNumber">the property number</param>
 /// <param name="postCode">the postcode</param>
 public Address(string propertyName, int propertyNumber,
     PostCode postCode)
 {
     this.propertyName = propertyName;
     this.propertyNumber = propertyNumber;
     this.postCode = postCode;
 }
        /// <summary>
        /// gets all hourly paid employees
        /// with associated address, postcode and supervisor objects
        /// </summary>
        /// <returns>list of employees</returns>
        public List<Employee> GetAllHourlyPaid()
        {
            List<Employee> result = new List<Employee>();

            StringBuilder sqlb = new StringBuilder(
                    "SELECT employeeid,name,username,supervisor, phonenumber, propertyname, propertynumber, postcode ");
            sqlb.Append("FROM EMPLOYEES e, ADDRESSES a ");
            sqlb.Append("WHERE e.addressID = a.addressID ");
            sqlb.Append(@"AND discriminator = 'h'");
            string sql = sqlb.ToString();

            SqlCeCommand comm = new SqlCeCommand(sql, conn);
            if (conn.State == ConnectionState.Closed) conn.Open();
            SqlCeDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            while(reader.Read())
            {
                Employee emp = null;

                int employeeID = reader.GetInt32(0);
                string name = reader.GetString(1);
                string username = reader.GetString(2);
                int supervisor = -1;
                if(!reader.IsDBNull(3))
                    supervisor = reader.GetInt32(3);
                string phonenumber = reader.GetString(4);
                string propertyname = reader.GetString(5);
                int propertynumber = reader.GetInt32(6);
                string postcode = reader.GetString(7);

                if (identityMap.ContainsKey(employeeID))
                {
                    emp = identityMap[employeeID];
                }
                else
                {
                    PostCode pc = new PostCode(postcode);
                    Address ad = new Address(propertyname, propertynumber, pc);
                    emp = new HourlyPaidEmployee(employeeID, name, username, ad, phonenumber);
                    emp.Supervisor = GetById(supervisor);
                    identityMap.Add(employeeID, emp);
                }
                result.Add(emp);
            }

            //conn.Close();

            return result;
        }
        /// <summary>
        /// gets specified employee with associated address and postcode objects
        /// </summary>
        /// <param name="id">the employee id</param>
        /// <returns>an employee object</returns>
        public Employee GetById(int id)
        {
            Employee result = null;

            // check whether target object is already loaded
            if (identityMap.ContainsKey(id))
            {
                result = identityMap[id];
            }
            else
            {
                StringBuilder sqlb = new StringBuilder(
                    "SELECT employeeid,name,username,phonenumber, discriminator, paygrade, propertyname, propertynumber, postcode ");
                sqlb.Append("FROM EMPLOYEES e, ADDRESSES a ");
                sqlb.Append("WHERE e.addressID = a.addressID ");
                sqlb.Append("AND employeeID = ");
                sqlb.Append(id.ToString());
                string sql = sqlb.ToString();

                SqlCeCommand comm = new SqlCeCommand(sql, conn);
                if(conn.State==ConnectionState.Closed) conn.Open();
                SqlCeDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (reader.Read())
                {
                    int employeeID = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    string username = reader.GetString(2);
                    string phonenumber = reader.GetString(3);
                    string discriminator = reader.GetString(4);
                    int paygrade = 0;
                    if(!reader.IsDBNull(5))
                        paygrade = reader.GetInt32(5);
                    string propertyname = reader.GetString(6);
                    int propertynumber = reader.GetInt32(7);
                    string postcode = reader.GetString(8);

                    PostCode pc = new PostCode(postcode);
                    Address ad = new Address(propertyname, propertynumber, pc);

                    if (discriminator.Equals("S"))
                    {
                        // need to include pay grade in database schema and adjust query
                        result = new SalariedEmployee(employeeID, name, username, ad, phonenumber, paygrade);
                    }
                    else if (discriminator.Equals("H"))
                    {
                        result = new HourlyPaidEmployee(employeeID, name, username, ad, phonenumber);
                    }
                    else
                    {
                        throw new Exception("Invalid employee type");
                    }
                }

                //conn.Close();
                identityMap.Add(id, result);
            }
            return result;
        }