private Employee MapTableToEmployeeClass(SqlCeDataReader reader) { Employee emp = null; // Strip out the values from the query . int id = (int) reader["EmployeeId"]; string name =(string) reader["Name"]; string userName = (string)reader["UserName"]; string phoneNumber = (string)reader["PhoneNumber"]; // Optional field, Nulls possible. int supervisorId = 0; if(reader["SupervisorId"] != System.DBNull.Value) supervisorId = (int)reader["SupervisorId"]; // Can be null. Dooooooohh! Thicko. int deptId = (int)reader["DeptId"]; int addressId = (int)reader["AddressId"]; string Type = ((byte)reader["Type"]).ToString(); // optional field. int paygrade = 0; if (reader["PayGrade"] != System.DBNull.Value) paygrade = (int)reader["PayGrade"]; string deptname = (string)reader["DeptName"]; string postcode = (string)reader["PostCode"]; string propertyName = (string)reader["PropertyName"]; // Optional field. int propertyNumber = 0; if (reader["propertyNumber"] != System.DBNull.Value) int.TryParse((string)reader["PropertyNumber"], out propertyNumber); // Construct a PostCode class PostCode postCode = new PostCode(postcode); // Construct an Address class Address address = new Address(propertyName, propertyNumber, postCode); // Construct the Department class Department department = new Department(); department.DepartmentName = deptname; // Instantiate the relevent employee class if (Type == "1") emp = new SalariedEmployee(id, name, userName, address, phoneNumber, paygrade); else emp = new HourlyPaidEmployee(id, name, userName, address, phoneNumber); return emp; }
/// <summary> /// Store an Hourly Paid employee in the underlying database /// </summary> /// <param name="employee">The Hourly Paid Employee</param> /// <returns>The Identity of the employee just added.</returns> public int StoreHourlyPaid(HourlyPaidEmployee employee) { // Construct an Address Record, from Address and Postcode (within Address) int addressId = employee.EmployeeId; string propertyName = employee.Address.PropertyName; string propertyNumber = employee.Address.PropertyNumber.ToString(); string postCode = employee.Address.PostCode.FullCode; // Construct the Employee record string name = employee.Name; string userName = employee.Username; string phoneNumber = employee.PhoneNumber; int supervisor = employee.Supervisor.EmployeeId; int DeptId = 1; // The sample data is not populating this. byte Type = 2; // This is the type for HourlyPaid employee. int? payGrade = 0; int newId = 0; using (SqlCeConnection EmployeeSqlConn = new SqlCeConnection(constEmployeeConnectionString)) { // Set up the SQL Command SqlCeCommand cmd = EmployeeSqlConn.CreateCommand(); cmd.CommandType = System.Data.CommandType.Text; // Use the InsertAddress command cmd.CommandText = constInsertAddressSQL; cmd.Parameters.AddWithValue("@PropertyName", propertyName); cmd.Parameters.AddWithValue("@PropertyNumber", propertyNumber); cmd.Parameters.AddWithValue("@PostCode", postCode); EmployeeSqlConn.Open(); // Add the address to the database and get id back (INSERT) // Normally we would check if the Address existed first. var rowsAffected = cmd.ExecuteNonQuery(); cmd.CommandText = constGetIDSQL; SqlCeDataReader rdr = cmd.ExecuteReader(); rdr.Read(); var tmp = rdr[0].ToString(); addressId = Convert.ToInt32(tmp); // Add the employee and get the Id back cmd.CommandText = constInsertEmployeeSQL; cmd.Parameters.AddWithValue("@Name", name); cmd.Parameters.AddWithValue("@UserName", userName); cmd.Parameters.AddWithValue("@PhoneNumber", phoneNumber); cmd.Parameters.AddWithValue("@SupervisorId", supervisor); cmd.Parameters.AddWithValue("@DeptId", DeptId); cmd.Parameters.AddWithValue("@AddressId", addressId); cmd.Parameters.AddWithValue("@Type", Type); cmd.Parameters.AddWithValue("@PayGrade", payGrade); rowsAffected = cmd.ExecuteNonQuery(); cmd.CommandText = constGetIDSQL; rdr = cmd.ExecuteReader(); rdr.Read(); tmp = rdr[0].ToString(); newId = Convert.ToInt32(tmp); } // Return the new Employee Id return newId; }