Loading an Employee

Now it's time to see whether we can load Employee objects from the database. Listing 37-26 shows the first test. As you can see, I didn't cut any corners in writing it. It first saves an employee object, using the SqlPayrollDatabase.AddEmployee() method, which we've already written and tested. Then the test attempts to load the employee, using SqlPayrollDatabase.GetEmployee(). Each aspect of the loaded Employee object is checked, including the payment schedule, payment method, and payment classification. The test obviously fails at first, and much work is needed before it will pass.

Listing 37-26. SqlPayrollDatabaseTest.cs (partial)

public void LoadEmployee() {   employee.Schedule = new BiWeeklySchedule();   employee.Method =     new DirectDepositMethod("1st Bank", "0123456");   employee.Classification =     new SalariedClassification(5432.10);   database.AddEmployee(employee);   Employee loadedEmployee = database.GetEmployee(123);   Assert.AreEqual(123, loadedEmployee.EmpId);   Assert.AreEqual(employee.Name,  loadedEmployee.Name);   Assert.AreEqual(employee.Address, loadedEmployee.Address);   PaymentSchedule schedule = loadedEmployee.Schedule;   Assert.IsTrue(schedule is BiWeeklySchedule);   PaymentMethod method = loadedEmployee.Method;   Assert.IsTrue(method is DirectDepositMethod);   DirectDepositMethod ddMethod = method as DirectDepositMethod;   Assert.AreEqual("1st Bank", ddMethod.Bank);   Assert.AreEqual("0123456", ddMethod.AccountNumber);   PaymentClassification classification =     loadedEmployee.Classification;   Assert.IsTrue(classification is SalariedClassification);   SalariedClassification salariedClassification =     classification as SalariedClassification;   Assert.AreEqual(5432.10, salariedClassification.Salary); }

The last refactoring we did when we implemented the AddEmployee() method was to extract a class, SaveEmployeeOperation, that contained all the code to fulfill its one purpose: to save an employee. We'll use this same pattern right off the bat when implementing the code to load an employee. Of course, we'll be doing this test first as well. However, there is going to be one fundamental difference. In testing the ability to load an employee, we will not touch the database, save the preceding test. We will thoroughly test the ability to load an employee, but we'll do it all without connecting to the database.

Listing 37-27 is the beginning of the LoadEmployeeOperationTest case. The first test, LoadEmployeeDataCommand, creates a new LoadEmployeeOperation object, using an employee ID and null for the database connection. The test then gets the SqlCommand for loading the data from the Employee table and tests its structure. We could execute this command against the database, but what does that buy us? First, it complicates the test, since we'd have to load data before we could execute the query. Second, we're already testing the ability to connect to the database in SqlPayrollDatabaseTest.LoadEmployee(). There's no need to test it over and over again. Listing 37-28 shows the start of LoadEmployeeOperation, along with the code that satisfies this first test.

Listing 37-27. LoadEmployeeOperationTest.cs

using System.Data; using System.Data.SqlClient; using NUnit.Framework; using Payroll; namespace PayrollDB {   [TestFixture]   public class LoadEmployeeOperationTest   {     private LoadEmployeeOperation operation;     private Employee employee;     [SetUp]     public void SetUp()     {       employee = new Employee(123, "Jean", "10 Rue de Roi");       operation = new LoadEmployeeOperation(123, null);       operation.Employee = employee;     }     [Test]     public void LoadingEmployeeDataCommand()     {       operation = new LoadEmployeeOperation(123, null);       SqlCommand command = operation.LoadEmployeeCommand;       Assert.AreEqual("select * from Employee " +         "where EmpId=@EmpId", command.CommandText);       Assert.AreEqual(123, command.Parameters["@EmpId"].Value);     }   } }

Listing 37-28. LoadEmployeeOperation.cs

using System.Data.SqlClient; using Payroll; namespace PayrollDB {   public class LoadEmployeeOperation   {     private readonly int empId;     private readonly SqlConnection connection;     private Employee employee;     public LoadEmployeeOperation(       int empId, SqlConnection connection)     {       this.empId = empId;       this.connection = connection;     }     public SqlCommand LoadEmployeeCommand     {       get       {         string sql = "select * from Employee " +           "where EmpId=@EmpId";         SqlCommand command = new SqlCommand(sql, connection);         command.Parameters.Add("@EmpId", empId);         return command;       }     }   } }

The tests pass at this point, so we've got a good start. But the command alone doesn't get us very far; we'll have to create an Employee object from the data that's retrieved from the database. One way to load data from the database is to dump it into a DataSet object, as we did in earlier tests. This technique is quite convenient because our tests can create a DataSet that would look exactly like what would be created were we really querying the database. The test in Listing 37-29 shows how this is done, and Listing 37-30 has the corresponding production code.

Listing 37-29. LoadEmployeeOperationTest.LoadEmployeeData()

[Test] public void LoadEmployeeData() {   DataTable table = new DataTable();   table.Columns.Add("Name");   table.Columns.Add("Address");   DataRow row = table.Rows.Add(     new object[]{"Jean", "10 Rue de Roi"});   operation.CreateEmplyee(row);   Assert.IsNotNull(operation.Employee);   Assert.AreEqual("Jean", operation.Employee.Name);   Assert.AreEqual("10 Rue de Roi",     operation.Employee.Address); }

Listing 37-30. LoadEmployeeOperation.cs (partial)

public void CreateEmplyee(DataRow row) {   string name = row["Name"].ToString();   string address = row["Address"].ToString();   employee = new Employee(empId, name, address); }

With this test passing, we can move on to loading payment schedules. Listings 37-31 and 37-32 show the test and production code that loads the first of the PaymentSchedule classes: WeeklySchedule.

Listing 37-31. LoadEmployeeOperationTest.LoadingSchedules()

[Test] public void LoadingSchedules() {   DataTable table = new DataTable();   table.Columns.Add("ScheduleType");   DataRow row = table.NewRow();   row.ItemArray = new object[] {"weekly"};   operation.AddSchedule(row);   Assert.IsNotNull(employee.Schedule);   Assert.IsTrue(employee.Schedule is WeeklySchedule); }

Listing 37-32. LoadEmployeeOperation.cs (partial)

public void AddSchedule(DataRow row) {   string scheduleType = row["ScheduleType"].ToString();   if(scheduleType.Equals("weekly"))     employee.Schedule = new WeeklySchedule(); }

With a little refactoring, we can easily test the loading of all the PaymentSchedule types. Since we've been creating a few DataTable objects so far in the tests and will be creating many more, extracting this dry task out into a new method will turn out to be handy. See Listings 37-33 and 37-34 for the changes.

Listing 37-33. LoadEmployeeOperationTest.LoadingSchedules() (refactored)

[Test] public void LoadingSchedules() {   DataRow row = ShuntRow("ScheduleType", "weekly");   operation.AddSchedule(row);   Assert.IsTrue(employee.Schedule is WeeklySchedule);   row = ShuntRow("ScheduleType", "biweekly");   operation.AddSchedule(row);   Assert.IsTrue(employee.Schedule is BiWeeklySchedule);   row = ShuntRow("ScheduleType", "monthly");   operation.AddSchedule(row);   Assert.IsTrue(employee.Schedule is MonthlySchedule); } private static DataRow ShuntRow(   string columns, params object[] values) {   DataTable table = new DataTable();   foreach(string columnName in columns.Split(','))     table.Columns.Add(columnName);   return table.Rows.Add(values); }

Listing 37-34. LoadEmployeeOperation.cs (partial)

public void AddSchedule(DataRow row) {   string scheduleType = row["ScheduleType"].ToString();   if(scheduleType.Equals("weekly"))     employee.Schedule = new WeeklySchedule();   else if(scheduleType.Equals("biweekly"))     employee.Schedule = new BiWeeklySchedule();   else if(scheduleType.Equals("monthly"))     employee.Schedule = new MonthlySchedule(); }

Next, we can work on loading the payment methods. See Listings 37-35 and 37-36.

Listing 37-35. LoadEmployeeOperationTest.LoadingHoldMethod()

[Test] public void LoadingHoldMethod() {   DataRow row = ShuntRow("PaymentMethodType", "hold");   operation.AddPaymentMethod(row);   Assert.IsTrue(employee.Method is HoldMethod); }

Listing 37-36. LoadEmployeeOperation.cs (partial)

public void AddPaymentMethod(DataRow row) {   string methodCode = row["PaymentMethodType"].ToString();   if(methodCode.Equals("hold"))     employee.Method = new HoldMethod(); }

That was easy. However, loading the rest of the payment methods is not easy. Consider loading an Employee with a DirectDepositMethod. First, we'll read the Employee table. In the PaymentMethodType column the value "directdeposit" tells us that we need to create a DirectDepositMethod object for this employee. To create a DirectDepositMethod, we'll need the bank account data stored in the DirectDepositAccount table. Therefore, the LoadEmployeeOperation.AddPaymentMethod() method will have to create a new sql command to retrieve that data. To test this, we'll have to put data into the DirectDepositAccount table first.

In order to properly test the ability to load payment methods without touching the database, we'll have to create a new class: LoadPaymentMethodOperation. This class will be responsible for determining which PaymentMethod to create and for loading the data to create it. Listing 37-37 shows the new test fixture: LoadPaymentMethod-OperationTest with the test to load HoldMethod objects. Listing 37-38 shows the LoadPaymentMethod class with the first bit of code, and Listing 37-39 shows how LoadEmployeeOperation uses this new class.

Listing 37-37. LoadPaymentMethodOperationTest.cs

using NUnit.Framework; using Payroll; namespace PayrollDB {   [TestFixture]   public class LoadPaymentMethodOperationTest   {     private Employee employee;     private LoadPaymentMethodOperation operation;     [SetUp]     public void SetUp()     {       employee = new Employee(567, "Bill", "23 Pine Ct");     }     [Test]     public void LoadHoldMethod()     {       operation = new LoadPaymentMethodOperation(           employee, "hold", null);       operation.Execute();       PaymentMethod method = this.operation.Method;       Assert.IsTrue(method is HoldMethod);     }   } }

Listing 37-38. LoadPaymentMethodOperation.cs

using System; using System.Data; using System.Data.SqlClient; using Payroll; namespace PayrollDB {   public class LoadPaymentMethodOperation   {     private readonly Employee employee;     private readonly string methodCode;     private PaymentMethod method;     public LoadPaymentMethodOperation(       Employee employee, string methodCode)     {       this.employee = employee;       this.methodCode = methodCode;     }     public void Execute()     {       if(methodCode.Equals("hold"))         method = new HoldMethod();     }     public PaymentMethod Method     {       get { return method; }     }   } }

Listing 37-39. LoadEmployeeOperation.cs (partial)

public void AddPaymentMethod(DataRow row) {   string methodCode = row["PaymentMethodType"].ToString();   LoadPaymentMethodOperation operation =     new LoadPaymentMethodOperation(employee, methodCode);   operation.Execute();   employee.Method = operation.Method; }

Again, loading the HoldMethod proves easy. For loading the DirectDepositMethod, we'll have to create an SqlCommand that will be used to retrieve the data, and then we'll have to create an instance of DirectDepositMethod from the loaded data. Listings 37-40 and 37-41 show tests and production code to do this. Note that the test CreateDirectDepositMethodFromRow borrows the ShuntRow method, from LoadEmployeeOperationTest. It's a handy method so we'll let it slide for now. But at some point, we'll have to find a better place for ShuntRow to be shared.

Listing 37-40. LoadPaymentMethodOperationTest.cs (partial)

[Test] public void LoadDirectDepositMethodCommand() {   operation = new LoadPaymentMethodOperation(     employee, "directdeposit") ;   SqlCommand command = operation.Command;   Assert.AreEqual("select * from DirectDepositAccount " +     "where EmpId=@EmpId", command.CommandText);   Assert.AreEqual(employee.EmpId,     command.Parameters["@EmpId"].Value); } [Test] public void CreateDirectDepositMethodFromRow() {   operation = new LoadPaymentMethodOperation(     employee, "directdeposit");   DataRow row = LoadEmployeeOperationTest.ShuntRow(     "Bank,Account", "1st Bank", "0123456");   operation.CreatePaymentMethod(row);   PaymentMethod method = this.operation.Method;   Assert.IsTrue(method is DirectDepositMethod);   DirectDepositMethod ddMethod =     method as DirectDepositMethod;   Assert.AreEqual("1st Bank", ddMethod.Bank);   Assert.AreEqual("0123456", ddMethod.AccountNumber); }

Listing 37-41. LoadPaymentMethodOperation.cs (partial)

public SqlCommand Command {   get   {     string sql = "select * from DirectDepositAccount" +       "where EmpId=@EmpId";     SqlCommand command = new SqlCommand(sql);     command.Parameters.Add("@EmpId", employee.EmpId);     return command;   } } public void CreatePaymentMethod(DataRow row) {   string bank = row["Bank"].ToString();   string account = row["Account"].ToString();   method = new DirectDepositMethod(bank, account); }

That leaves the loading of MailMethod objects. Listing 37-42 shows a test for creating the SQL. In attempting to implement the production code, things get interesting. In the Command property, we need an if/else statement to determine which table name will be used in the query. In the Execute() method, we'll need another if/else statement to determine which type of PaymentMethod to instantiate. This seems familiar. As before, duplicate if/else statements are a smell to be avoided.

The LoadPaymentMethodOperation class has to be restructured so that only one if/else is needed. With a little creativity and the use of delegates, the problem is solved. Listing 37-43 shows a restructured LoadPaymentMethodOperation.

Listing 37-42. LoadPaymentMethodOperationTest.LoadMailMethodCommand()

[Test] public void LoadMailMethodCommand() {   operation = new LoadPaymentMethodOperation(employee, "mail");   SqlCommand command = operation.Command;   Assert.AreEqual("select * from PaycheckAddress " +     "where EmpId=@EmpId", command.CommandText);   Assert.AreEqual(employee.EmpId,     command.Parameters["@EmpId"].Value); }

Listing 37-43. LoadPaymentMethodOperation.cs (refactored)

public class LoadPaymentMethodOperation {   private readonly Employee employee;   private readonly string methodCode;   private PaymentMethod method;   private delegate void PaymentMethodCreator(DataRow row);   private PaymentMethodCreator paymentMethodCreator;   private string tableName;   public LoadPaymentMethodOperation(     Employee employee, string methodCode)   {     this.employee = employee;     this.methodCode = methodCode;   }   public void Execute()   {     Prepare();     DataRow row = LoadData();     CreatePaymentMethod(row);   }   public void CreatePaymentMethod(DataRow row)   {     paymentMethodCreator(row);   }   public void Prepare()   {     if(methodCode.Equals("hold"))       paymentMethodCreator =     new PaymentMethodCreator(CreateHoldMethod);     else if(methodCode.Equals("directdeposit"))     {       tableName = "DirectDepositAccount";       paymentMethodCreator = new PaymentMethodCreator(     CreateDirectDepositMethod);     }     else if(methodCode.Equals("mail"))     {       tableName = "PaycheckAddress";     }   }   private DataRow LoadData()   {     if(tableName != null)       return LoadEmployeeOperation.LoadDataFromCommand(Command);     else       return null;   }   public PaymentMethod Method   {     get { return method; }   }   public SqlCommand Command   {     get     {       string sql = String.Format(         "select * from {0} where EmpId=@EmpId", tableName);       SqlCommand command = new SqlCommand(sql);       command.Parameters.Add("@EmpId", employee.EmpId);       return command;     }   }   public void CreateDirectDepositMethod(DataRow row)   {     string bank = row["Bank"].ToString();     string account = row["Account"].ToString();     method = new DirectDepositMethod(bank, account);   }   private void CreateHoldMethod(DataRow row)   {     method = new HoldMethod();   } }

This refactoring was a little more involved than most. It required a change to the tests. The tests need to call Prepare() before they get the command to load the PaymentMethod. Listing 37-44 shows this change and the final test for creating the MailMethod. Listing 37-45 contains the final bit of code in the LoadPaymentMethodOperation class.

Listing 37-44. LoadPaymentMethodOperationTest.cs (partial)

[Test] public void LoadMailMethodCommand() {   operation = new LoadPaymentMethodOperation(employee, "mail");   operation.Prepare();   SqlCommand command = operation.Command;   Assert.AreEqual("select * from PaycheckAddress " +     "where EmpId=@EmpId", command.CommandText);   Assert.AreEqual(employee.EmpId,     command.Parameters["@EmpId"].Value); } [Test] public void CreateMailMethodFromRow() {   operation = new LoadPaymentMethodOperation(employee, "mail");   operation.Prepare();   DataRow row = LoadEmployeeOperationTest.ShuntRow(     "Address", "23 Pine Ct");   operation.CreatePaymentMethod(row);   PaymentMethod method = this.operation.Method;   Assert.IsTrue(method is MailMethod);   MailMethod mailMethod = method as MailMethod;   Assert.AreEqual("23 Pine Ct", mailMethod.Address); }

Listing 37-45. LoadPaymentMethodOperation.cs (partial)

public void Prepare() {   if(methodCode.Equals("hold"))     paymentMethodCreator =       new PaymentMethodCreator(CreateHoldMethod);   else if(methodCode.Equals("directdeposit"))   {     tableName = "DirectDepositAccount";     paymentMethodCreator =       new PaymentMethodCreator(CreateDirectDepositMethod);   }   else if(methodCode.Equals("mail"))   {     tableName = "PaycheckAddress";     paymentMethodCreator =       new PaymentMethodCreator(CreateMailMethod);   } } private void CreateMailMethod(DataRow row) {   string address = row["Address"].ToString();   method = new MailMethod(address); }

With all the PaymentMethods loaded, we are left with the PaymentClassifications to do. To load the classifications, we'll create a new class, LoadPayment-ClassificationOperation, and the corresponding test fixture. This is very similar to what we've done so far and is left up to you to complete.

After that's complete, we can go back to the SqlPayrollDatabaseTest.Load-Employee test. Hmm. It still fails. It seems that we've forgotten a bit of wiring. Listing 37-46 shows the changes that have to be made to make the test pass.

Listing 37-46. LoadEmployeeOperation.cs (partial)

public void Execute() {   string sql = "select *  from Employee where EmpId = @EmpId";   SqlCommand command = new SqlCommand(sql, connection);   command.Parameters.Add("@EmpId", empId);   DataRow row = LoadDataFromCommand(command);   CreateEmplyee(row);   AddSchedule(row);   AddPaymentMethod(row);   AddClassification(row); } public void AddSchedule(DataRow row) {   string scheduleType = row["ScheduleType"].ToString();   if(scheduleType.Equals("weekly"))     employee.Schedule = new WeeklySchedule();   else if(scheduleType.Equals("biweekly"))     employee.Schedule = new BiWeeklySchedule();   else if(scheduleType.Equals("monthly"))     employee.Schedule = new MonthlySchedule(); } private void AddPaymentMethod(DataRow row) {   string methodCode = row["PaymentMethodType"].ToString();   LoadPaymentMethodOperation operation =     new LoadPaymentMethodOperation(employee, methodCode);   operation.Execute();   employee.Method = operation.Method; } private void AddClassification(DataRow row) {   string classificationCode = row["PaymentClassificationType"].ToString();   LoadPaymentClassificationOperation operation =     new LoadPaymentClassificationOperation(employee, classificationCode);   operation.Execute();   employee.Classification = operation.Classification; }

You may notice that there is plenty of duplication in the LoadOperation classes. Also, the tendency to refer to this group of classes as LoadOperations suggests that they should derive from a common base class. Such a base class would provide a home for all the duplicate code shared among its would-be derivatives. This refactoring is left to you.