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;
}