37. The Payroll Case Study: The Database
“Experts often possess more data than judgement.”
—Colin Powell
In previous chapters, we implemented all the business logic for the payroll application. That implementation had a class, PayrollDatabase, that stored all the payroll data in RAM. This worked fine for our purpose at the time. However, it seems obvious that this system will need a more persistent form of data storage. This chapter explains how to provide that persistence by storing the data in a relational database.
Building the Database
The choice of database technology is usually made more for political reasons than for technical reasons. Database and platform companies have done a good job of convincing the market that these choices are of critical importance. Loyalties and allegiances form around database and platform providers for reasons that are more human than technical. So you should not read too much into our choice of Microsoft SQL Server to persist the data for our application.
The schema that we’ll be using is shown in Figure 37-1. The Employee table is central. It stores the immediate data for an employee, along with string constants that determine the PaymentSchedule, PaymentMethod, and PaymentClassification. PaymentClassifications have data of their own that will be persisted in the corresponding HourlyClassification, SalariedClassification, and CommissionedClassification tables. Each references the Employee it belongs to, via the EmpId column. This column has a constraint to make sure that an Employee record with the given EmpId exists in the Employee table. DirectDepositAccount and PaycheckAddress hold the data appropriate to their PaymentMethod and are likewise constrained by the EmpId column. SalesReceipt and TimeCard are straightforward. The Affiliation table holds such data as the union members and is linked to the Employee table via EmpoyeeAffiliation.
A Flaw in the Code Design
You may recall that the PayrollDatabase was filled with nothing but public static methods. This decision is no longer appropriate. How do we start using a real database in the code without breaking all the tests that use the static methods? We don’t want to overwrite the PayrollDatabase class to use a real database. That would force all our existing unit tests to use the real database. It would be nice if PayrollDatabase were an interface so we could easily swap out different implementations. One implementation would store data in memory like it does now, so that our tests can continue to run quickly. Another implementation would store data in a real database.
To achieve this new design, we’ll have to perform a few refactorings, running the unit tests after each step to make sure we’re not breaking the code. First, we’ll create an instance of the PayrollDatabase and store it in a static variable: instance. Then we’ll go through each static method in PayrollDatabase and rename it to include the word static. Then we’ll extract the method body into a new non-static method of the same name. (See Listing 37-1.)
Listing 37-1. Example refactorin
public class PayrollDatabase
{
private static PayrollDatabase instance;
public static void AddEmployee_Static(Employee employee)
{
instance.AddEmployee(employee);
}
public void AddEmployee(Employee employee)
{
employees[employee.EmpId] = employee;
}
Now we need to find every call to PayrollDatabase.AddEmployee_Static() and replace it with PayrollDatabase.instance.AddEmployee(). Once they have all been changed, we can delete the static version of the method. The same has to be done with each static method, of course.
That leaves every database invocation going through the PayrollDatabase. instance variable. We would like PayrollDatabase to be an interface. So we need to find another home for that instance variable. Certainly, PayrollTest should hold such a variable, since it can then be used by all the tests. For the application, a good place is in each Transaction derivative. The PayrollDatabase instance will have to be passed into the constructor and stored as an instance variable of each Transaction. Rather than duplicate this code, let’s simply put the PayrollDatabase instance in the Transaction base class. Transaction is an interface, so we’ll have to convert it into an abstract class, as in Listing 37-2.
public abstract class Transaction
{
protected readonly PayrollDatabase database;
public Transaction(PayrollDatabase database)
{
this.database = database;
}
public abstract void Execute();
}
Now that nobody is using the PayrollDatabase.instance, we can delete it. Before we convert PayrollDatabase into an interface, we need a new implementation that extends PayrollDatabase. Since the current implementation stores everything in memory, we’ll call the new class InMemoryPayrollDatabase (Listing 37-3) and use it wherever PayrollDatabase is instantiated. Finally, PayrollDatabase can be reduced to an interface (Listing 37-4) and we can begin work on the real database implementation.
Listing 37-3. InMemoryPayrollDatabase.cs
public class InMemoryPayrollDatabase : PayrollDatabase
{
private static Hashtable employees = new Hashtable();
private static Hashtable unionMembers = new Hashtable();
public void AddEmployee(Employee employee)
{
employees[employee.EmpId] = employee;
}
// etc...
}
Listing 37-4. PayrollDatabase.cs
public interface PayrollDatabase
{
void AddEmployee(Employee employee);
Employee GetEmployee(int id);
void DeleteEmployee(int id);
void AddUnionMember(int id, Employee e);
Employee GetUnionMember(int id);
void RemoveUnionMember(int memberId);
ArrayList GetAllEmployeeIds();
}
Adding an Employee
With our design refactored, we can now create SqlPayrollDatabase. This class implements the PayrollDatabase interface to persist data in an SQL Server database with the schema in Figure 37-1. Along with SqlPayrollDatabase, we’ll create SqlPayroll-DatabaseTest for unit tests. Listing 37-5 shows the first test.
Listing 37-5. SqlPayrollDatabaseTest.cs
[TestFixture]
public class Blah
{
private SqlPayrollDatabase database;
[SetUp]
public void SetUp()
{
database = new SqlPayrollDatabase();
}
[Test]
public void AddEmployee()
{
Employee employee = new Employee(123,
"George", "123 Baker St.");
employee.Schedule = new MonthlySchedule();
employee.Method =
new DirectDepositMethod("Bank 1", "123890");
employee.Classification =
new SalariedClassification(1000.00);
database.AddEmployee(123, employee);
SqlConnection connection = new SqlConnection(
"Initial Catalog=Payroll;Data Source=localhost;" +
"user id=sa;password=abc");
SqlCommand command = new SqlCommand(
"select * from Employee", connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
DataTable table = dataset.Tables["table"];
Assert.AreEqual(1, table.Rows.Count);
DataRow row = table.Rows[0];
Assert.AreEqual(123, row["EmpId"]);
Assert.AreEqual("George", row["Name"]);
Assert.AreEqual("123 Baker St.", row["Address"]);
}
}
This test makes a call to AddEmployee(), then queries the database to make sure that the data was saved. Listing 37-6 shows the brute-force code to make it pass.
Listing 37-6. SqlPayrollDatabase.cs
public class SqlPayrollDatabase : PayrollDatabase
{
private readonly SqlConnection connection;
public SqlPayrollDatabase()
{
connection = new SqlConnection(
"Initial Catalog=Payroll;Data Source=localhost;" +
"user id=sa;password=abc");
connection.Open();
}
public void AddEmployee(Employee employee)
{
string sql = "insert into Employee values (" +
"@EmpId, @Name, @Address, @ScheduleType, " +
"@PaymentMethodType, @PaymentClassificationType)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@EmpId", employee.EmpId);
command.Parameters.Add("@Name", employee.Name);
command.Parameters.Add("@Address", employee.Address);
command.Parameters.Add("@ScheduleType",
employee.Schedule.GetType().ToString());
command.Parameters.Add("@PaymentMethodType",
employee.Method.GetType().ToString());
command.Parameters.Add("@PaymentClassificationType",
employee.Classification.GetType().ToString());
command.ExecuteNonQuery();
}
}
This test passes once but fails every other time it is run. We get an exception from SQL Server, saying that we can’t insert duplicate keys. So we’ll have to clear the Employee table before each test. Listing 37-7 shows how this can be added to the SetUp method.
Listing 37-7. SqlPayrollDatabaseTest.SetUp()
[SetUp]
public void SetUp()
{
database = new SqlPayrollDatabase();
SqlConnection connection = new SqlConnection(
"Initial Catalog=Payroll;Data Source=localhost;" +
"user id=sa;password=abc");connection.Open();
SqlCommand command = new SqlCommand(
"delete from Employee", connection);
command.ExecuteNonQuery();
connection.Close();
}
This code does the trick, but it’s sloppy. A connection is created in SetUp and in the AddEmployee test. One connection created in SetUp and closed in TearDown should be enough. Listing 37-8 shows a refactored version.
Listing 37-8. SqlPayrollDatabaseTest.cs
[TestFixture]
public class Blah
{
private SqlPayrollDatabase database;
private SqlConnection connection;
[SetUp]
public void SetUp()
{
database = new SqlPayrollDatabase();
connection = new SqlConnection(
"Initial Catalog=Payroll;Data Source=localhost;" +
"user id=sa;password=abc");
connection.Open();
new SqlCommand("delete from Employee",
this.connection).ExecuteNonQuery();
}
[TearDown]
public void TearDown()
{
connection.Close();
}
[Test]
public void AddEmployee()
{
Employee employee = new Employee(123,
"George", "123 Baker St.");
employee.Schedule = new MonthlySchedule();
employee.Method =
new DirectDepositMethod("Bank 1", "123890");
employee.Classification =
new SalariedClassification(1000.00);
database.AddEmployee(employee);
SqlCommand command = new SqlCommand(
"select * from Employee", connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
DataTable table = dataset.Tables["table"];
Assert.AreEqual(1, table.Rows.Count);
DataRow row = table.Rows[0];
Assert.AreEqual(123, row["EmpId"]);
Assert.AreEqual("George", row["Name"]);
Assert.AreEqual("123 Baker St.", row["Address"]);
}
}
In Listing 37-6, you can see that the Employee table columns ScheduleType, PaymentMethodType, and PaymentClassificationType were populated with class names. Althoug this works, it’s a bit lengthy. Instead, we’ll use more concise keywords. Starting with the schedule type, Listing 37-9 shows how MonthlySchedules are saved. Listing 37-10 shows the part of SqlPayrollDatabase that satisfies this test.
Listing 37-9. SqlPayrollDatabaseTest.ScheduleGetsSaved()
[Test]
public void ScheduleGetsSaved()
{
Employee employee = new Employee(123,
"George", "123 Baker St.");
employee.Schedule = new MonthlySchedule();
employee.Method = new DirectDepositMethod();
employee.Classification = new SalariedClassification(1000.00);
database.AddEmployee(123, employee);
SqlCommand command = new SqlCommand(
"select * from Employee", connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
DataTable table = dataset.Tables["table"];
Assert.AreEqual(1, table.Rows.Count);
DataRow row = table.Rows[0];
Assert.AreEqual("monthly", row["ScheduleType"]);
}
Listing 37-10. SqlPayrollDatabase.cs (partial)
public void AddEmployee(int id, Employee employee)
{
...
command.Parameters.Add("@ScheduleType",
ScheduleCode(employee.Schedule));
...
}
private static string ScheduleCode(PaymentSchedule schedule)
{
if(schedule is MonthlySchedule)
return "monthly";
else
return "unknown";
}
The observant reader will notice the beginning of an OCP violation in Listing 37-10. The ScheduleCode() method contains an if/else statement to determine whether the schedule is a MonthlySchedule. Soon we’ll add another if/else clause for WeeklySchedule and then another for BiweeklySchedule. Every time a new type of payment schedule is added to the system, this if/else chain will have to be modified again.
One alternative is to get the schedule code from the PaymentSchedule hierarchy. We could add a polymorphic property, such as string DatabaseCode, that returns the appropriate value. But that would introduce an SRP violation to the PaymentSchedule hierarchy.
The SRP violation is ugly. It creates an unnecessary coupling between the database and the application and invites other modules to extend this coupling by making use of the ScheduleCode. On the other hand, the OCP violation is encapsulated within the Sql-PayrollDatabase class and is not likely to leak out. So for the time being, we’ll live with the OCP violation.
In writing the next test case, we find plenty of opportunity to remove duplicate code. Listing 37-11 shows the SqlPayrollDatabaseTest after some refactoring and with some new test cases. Listing 37-12 shows the SqlPayrollDatabase changes that make the test pass.
Listing 37-11. SqlPayrollDatabaseTest.cs (partial)
[SetUp]
public void SetUp()
{
...
CleanEmployeeTable();
employee = new Employee(123, "George", "123 Baker St.");
employee.Schedule = new MonthlySchedule();
employee.Method = new DirectDepositMethod();
employee.Classification= new SalariedClassification(1000.00);
}
private void ClearEmployeeTable()
{
new SqlCommand("delete from Employee",
this.connection).ExecuteNonQuery();
}
private DataTable LoadEmployeeTable()
{
SqlCommand command = new SqlCommand(
"select * from Employee", connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables["table"];
}
[Test]
public void ScheduleGetsSaved()
{
CheckSavedScheduleCode(new MonthlySchedule(), "monthly");
ClearEmployeeTable();
CheckSavedScheduleCode(new WeeklySchedule(), "weekly");
ClearEmployeeTable();
CheckSavedScheduleCode(new BiWeeklySchedule(), "biweekly");
}
private void CheckSavedScheduleCode(
PaymentSchedule schedule, string expectedCode)
{
employee.Schedule = schedule;
database.AddEmployee(123, employee);
DataTable table = LoadEmployeeTable();
DataRow row = table.Rows[0];
Assert.AreEqual(expectedCode, row["ScheduleType"]);
}
Listing 37-12. SqlPayrollDatabase.cs (partial)
private static string ScheduleCode(PaymentSchedule schedule)
{
if(schedule is MonthlySchedule)
return "monthly";
if(schedule is WeeklySchedule)
return "weekly";
if(schedule is BiWeeklySchedule)
return "biweekly";
else
return "unknown";
}
Listing 37-13 shows a new test for saving the PaymentMethods. This code follows the pattern used of saving the schedules. Listing 37-14 shows the new database code.
Listing 37-13. SqlPayrollDatabaseTest.cs (partial)
[Test]
public void PaymentMethodGetsSaved()
{
CheckSavedPaymentMethodCode(new HoldMethod(), "hold");
ClearEmployeeTable();
CheckSavedPaymentMethodCode(
new DirectDepositMethod("Bank -1", "0987654321"),
"directdeposit");
ClearEmployeeTable();
CheckSavedPaymentMethodCode(
new MailMethod("111 Maple Ct."), "mail");
}
private void CheckSavedPaymentMethodCode(
PaymentMethod method, string expectedCode)
{
employee.Method = method;
database.AddEmployee(employee);
DataTable table = LoadTable("Employee");
DataRow row = table.Rows[0];
Assert.AreEqual(expectedCode, row["PaymentMethodType"]);
}
Listing 37-14. SqlPayrollDatabase.cs (partial)
public void AddEmployee(int id, Employee employee)
{
...
command.Parameters.Add("@PaymentMethodType",
PaymentMethodCode(employee.Method));
...
}
private static string PaymentMethodCode(PaymentMethod method)
{
if(method is HoldMethod)
return "hold";
if(method is DirectDepositMethod)
return "directdeposit";
if(method is MailMethod)
return "mail";
else
return "unknown";
}
All the tests pass. But hold on a minute: DirectDepositMethod and MailMethod have data of their own that needs to be saved. The DirectDepositAccount and PaycheckAddress tables need to be populated when saving an Employee with either payment method. Listing 37-15 shows the test for saving DirectDepositMethod.
Listing 37-15. SqlPayrollDatabaseTest.cs (partial)
[Test]
public void DirectDepositMethodGetsSaved()
{
CheckSavedPaymentMethodCode(
new DirectDepositMethod("Bank -1", "0987654321"),
"directdeposit");
SqlCommand command = new SqlCommand(
"select * from DirectDepositAccount", connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
DataTable table = dataset.Tables["table"];
Assert.AreEqual(1, table.Rows.Count);
DataRow row = table.Rows[0];
Assert.AreEqual("Bank -1", row["Bank"]);
Assert.AreEqual("0987654321", row["Account"]);
Assert.AreEqual(123, row["EmpId"]);
}
While looking at the code to figure out how to make this test pass, we realized that we’ll need another if/else statement. The first, we added to figure out what value to stick in the PaymentMethodType column, which is bad enough. The second one is to figure out which table needs to be populated. These if/else OCP violations are starting to accumulate. We need a solution that uses only one if/else statement. It’s shown in Listing 37-16, where we introduce some member variables to help out.
Listing 37-16. SqlPayrollDatabase.cs (partial)
public void AddEmployee(int id, Employee employee)
{
string sql = "insert into Employee values (" +
"@EmpId, @Name, @Address, @ScheduleType, " +
"@PaymentMethodType, @PaymentClassificationType)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@EmpId", id);
command.Parameters.Add("@Name", employee.Name);
command.Parameters.Add("@Address", employee.Address);
command.Parameters.Add("@ScheduleType",
ScheduleCode(employee.Schedule));
SavePaymentMethod(employee);
command.Parameters.Add("@PaymentMethodType", methodCode);
command.Parameters.Add("@PaymentClassificationType",
employee.Classification.GetType().ToString());
command.ExecuteNonQuery();
}
private void SavePaymentMethod(Employee employee)
{
PaymentMethod method = employee.Method;
if(method is HoldMethod)
methodCode = "hold";
if(method is DirectDepositMethod)
{
methodCode = "directdeposit";
DirectDepositMethod ddMethod =
method as DirectDepositMethod;
string sql = "insert into DirectDepositAccount" +
"values (@Bank, @Account, @EmpId)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@Bank", ddMethod.Bank);
command.Parameters.Add("@Account", ddMethod.AccountNumber);
command.Parameters.Add("@EmpId", employee.EmpId);
command.ExecuteNonQuery();
}
if(method is MailMethod)
methodCode = "mail";
else
methodCode = "unknown";
}
The tests fail! Oops. There’s an error coming from SQL Server, saying that we can’t add an entry to DirectDepositAccount, because the related Employee record doesn’t exist. So the DirectDepositAcount table has to be populated after the Employee table is populated. But this brings up an interesting dilemma. What if the command to insert the employee succeeds but the command to insert the payment method fails? The data becomes corrupt. We end up with an employee with no payment method, and we can’t have that.
A common solution is to use transactions. With transactions, if any part of the transaction fails, the whole transaction is canceled, and nothing is saved. It’s still unfortunate when a save fails, but saving nothing is better than corrupting the database. Before we tackle this problem, let’s get our current tests passing. Listing 37-17 continues the code evolution.
Listing 37-17. SqlPayrollDatabase.cs (partial)
public void AddEmployee(int id, Employee employee)
{
PrepareToSavePaymentMethod(employee);
string sql = "insert into Employee values (" +
"@EmpId, @Name, @Address, @ScheduleType, " +
"@PaymentMethodType, @PaymentClassificationType)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@EmpId", id);
command.Parameters.Add("@Name", employee.Name);
command.Parameters.Add("@Address", employee.Address);
command.Parameters.Add("@ScheduleType",
ScheduleCode(employee.Schedule));
SavePaymentMethod(employee);
command.Parameters.Add("@PaymentMethodType", methodCode);
command.Parameters.Add("@PaymentClassificationType",
employee.Classification.GetType().ToString());
command.ExecuteNonQuery();
if(insertPaymentMethodCommand != null)
insertPaymentMethodCommand.ExecuteNonQuery();
}
private void PrepareToSavePaymentMethod(Employee employee)
{
PaymentMethod method = employee.Method;
if(method is HoldMethod)
methodCode = "hold";
else if(method is DirectDepositMethod)
{
methodCode = "directdeposit";
DirectDepositMethod ddMethod =
method as DirectDepositMethod;
string sql = "insert into DirectDepositAccount" +
"values (@Bank, @Account, @EmpId)";
insertPaymentMethodCommand =
new SqlCommand(sql, connection);
insertPaymentMethodCommand.Parameters.Add(
"@Bank", ddMethod.Bank);
insertPaymentMethodCommand.Parameters.Add(
"@Account", ddMethod.AccountNumber);
insertPaymentMethodCommand.Parameters.Add(
"@EmpId", employee.EmpId);
}
else if(method is MailMethod)
methodCode = "mail";
else
methodCode = "unknown";
}
Frustratingly, this still does not pass the tests. This time, the database is complaining when we clear the Employee table, because that would leave the DirectDepositAccount table with a missing reference. So we’ll have to clear both tables in the SetUp method. After being careful to clear the DirectDepositAccount table first, I’m rewarded with a green bar. That’s nice.
The MailMethod still needs to be saved. Let’s take care of this before venturing on to transactions. To test that the PaycheckAddress table is populated, we’ll have to load it. This will be the third time duplicating the code to load a table, so it’s past time to refactor. Renaming LoadEmployeeTable to LoadTable and adding the table name as a parameter makes the code sparkle. Listing 37-18 shows this change, along with the new test.
Listing 37-19 contains the code that makes it pass—after adding a statement to clear the PaycheckAddress table in the SetUp method, that is.
Listing 37-18. SqlPayrollDatabaseTest.cs (partial)
private DataTable LoadTable(string tableName)
{
SqlCommand command = new SqlCommand(
"select * from " + tableName, connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables["table"];
}
[Test]
public void MailMethodGetsSaved()
{
CheckSavedPaymentMethodCode(
new MailMethod("111 Maple Ct."), "mail");
DataTable table = LoadTable("PaycheckAddress");
Assert.AreEqual(1, table.Rows.Count);
DataRow row = table.Rows[0];
Assert.AreEqual("111 Maple Ct.", row["Address"]);
Assert.AreEqual(123, row["EmpId"]);
}
Listing 37-19. SqlPayrollDatabase.cs (partial)
private void PrepareToSavePaymentMethod(Employee employee)
{
...
else if(method is MailMethod)
{
methodCode = "mail";
MailMethod mailMethod = method as MailMethod;
string sql = "insert into PaycheckAddress " +
"values (@Address, @EmpId)";
insertPaymentMethodCommand =
new SqlCommand(sql, connection);
insertPaymentMethodCommand.Parameters.Add(
"@Address", mailMethod.Address);
insertPaymentMethodCommand.Parameters.Add(
"@EmpId", employee.EmpId);
}
...
}
Transactions
Now it’s time to make this database operation transactional. Performing SQL Server transaction with .NET is a breeze. The System.Data.SqlClient.SqlTransaction class is all you need. However, we can’t use it without first having a failing test. How do you test that a database operation is transactional?
If we can start the database operation by allowing the first command to execute successfully and then force a failure in a subsequent command, we can check the database to make sure that no data was saved. So how do you get an operation to succeed and another to fail? Well, let’s take as an example our Employee with a DirectDepositMethod . We know that the employee data gets saved first, followed by the direct-deposit account data. If we can force the insert into the DirectDepositAccount table to fail, that’ll do the trick. Passing a null value into the DirectDepositMethod object should cause a failure, especially considering that the DirectDepositAccount table doesn’t allow any null values. With Listing 37-20, we’re off.
Listing 37-20. SqlPayrollDatabaseTest.cs (partial)
[Test]
public void SaveIsTransactional()
{
// Null values won't go in the database.
DirectDepositMethod method =
new DirectDepositMethod(null, null);
employee.Method = method;
try
{
database.AddEmployee(123, employee);
Assert.Fail("An exception needs to occur" +
"for this test to work.");
}
catch(SqlException)
{}
DataTable table = LoadTable("Employee");
Assert.AreEqual(0, table.Rows.Count);
}
This does indeed cause a failure. The Employee record was added to the database, and the DirectDepositAccount record was not added. This is the situation that must be avoided. Listing 37-21 demonstrates the use of the SqlTransaction class to make our database operation transactional.
Listing 37-21. SqlPayrollDatabase.cs (partial)
public void AddEmployee(int id, Employee employee)
{
SqlTransaction transaction =
connection.BeginTransaction("Save Employee");
try
{
PrepareToSavePaymentMethod(employee);
string sql = "insert into Employee values (" +
"@EmpId, @Name, @Address, @ScheduleType, " +
"@PaymentMethodType, @PaymentClassificationType)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@EmpId", id);
command.Parameters.Add("@Name", employee.Name);
command.Parameters.Add("@Address", employee.Address);
command.Parameters.Add("@ScheduleType",
ScheduleCode(employee.Schedule));
command.Parameters.Add("@PaymentMethodType", methodCode);
command.Parameters.Add("@PaymentClassificationType",
employee.Classification.GetType().ToString());
command.Transaction = transaction;
command.ExecuteNonQuery();
if(insertPaymentMethodCommand != null)
{
insertPaymentMethodCommand.Transaction = transaction;
insertPaymentMethodCommand.ExecuteNonQuery();
}
transaction.Commit();
}
catch(Exception e)
{
transaction.Rollback();
throw e;
}
}
The tests pass! That was easy. Now to clean up the code. See Listing 37-22.
Listing 37-22. SqlPayrollDatabase.cs (partial)
public void AddEmployee(int id, Employee employee)
{
PrepareToSavePaymentMethod(employee);
PrepareToSaveEmployee(employee);
SqlTransaction transaction =
connection.BeginTransaction("Save Employee");
try
{
ExecuteCommand(insertEmployeeCommand, transaction);
ExecuteCommand(insertPaymentMethodCommand, transaction);
transaction.Commit();
}
catch(Exception e)
{
transaction.Rollback();
throw e;
}
}
private void ExecuteCommand(SqlCommand command,
SqlTransaction transaction)
{
if(command != null)
{
command.Connection = connection;
command.Transaction = transaction;
command.ExecuteNonQuery();
}
}
private void PrepareToSaveEmployee(Employee employee)
{
string sql = "insert into Employee values (" +
"@EmpId, @Name, @Address, @ScheduleType, " +
"@PaymentMethodType, @PaymentClassificationType)";
insertEmployeeCommand = new SqlCommand(sql);
insertEmployeeCommand.Parameters.Add(
"@EmpId", employee.EmpId);
insertEmployeeCommand.Parameters.Add(
"@Name", employee.Name);
insertEmployeeCommand.Parameters.Add(
"@Address", employee.Address);
insertEmployeeCommand.Parameters.Add(
"@ScheduleType",ScheduleCode(employee.Schedule));
insertEmployeeCommand.Parameters.Add(
"@PaymentMethodType", methodCode);
insertEmployeeCommand.Parameters.Add(
"@PaymentClassificationType",
employee.Classification.GetType().ToString());
}
private void PrepareToSavePaymentMethod(Employee employee)
{
PaymentMethod method = employee.Method;
if(method is HoldMethod)
methodCode = "hold";
else if(method is DirectDepositMethod)
{
methodCode = "directdeposit";
DirectDepositMethod ddMethod =
method as DirectDepositMethod;
insertPaymentMethodCommand =
CreateInsertDirectDepositCommand(ddMethod, employee);
}
else if(method is MailMethod)
{
methodCode = "mail";
MailMethod mailMethod = method as MailMethod;
insertPaymentMethodCommand =
CreateInsertMailMethodCommand(mailMethod, employee);
}
else
methodCode = "unknown";
}
private SqlCommand CreateInsertDirectDepositCommand(
DirectDepositMethod ddMethod, Employee employee)
{
string sql = "insert into DirectDepositAccount " +
"values (@Bank, @Account, @EmpId)";
SqlCommand command = new SqlCommand(sql);
command.Parameters.Add("@Bank", ddMethod.Bank);
command.Parameters.Add("@Account", ddMethod.AccountNumber);
command.Parameters.Add("@EmpId", employee.EmpId);
return command;
}
private SqlCommand CreateInsertMailMethodCommand(
MailMethod mailMethod, Employee employee)
{
string sql = "insert into PaycheckAddress " +
"values (@Address, @EmpId)";
SqlCommand command = new SqlCommand(sql);
command.Parameters.Add("@Address", mailMethod.Address);
command.Parameters.Add("@EmpId", employee.EmpId);
return command;
}
At this point, the PaymentClassification still remains unsaved. Implementing this portion of code involves no new tricks and is left up to the reader.
As your humble narrator completed this last task, a flaw in the code became apparent. SqlPayrollDatabase will likely be instantiated very early on in the application life cycle and used extensively. With this in mind, take a look at the insertPaymentMethodCommand member variable. This variable is given a value when saving an employee with either a direct-deposit or mail-payment method but not when saving an employee with a hold-payment method. Yet the variable is never cleared. What would happen if we save a employee with a mail-payment method and then another with a hold-payment method? Listing 37-23 puts the scenario in a test case.
Listing 37-23. SqlPayrollDatabaseTest.cs (partial)
[Test]
public void SaveMailMethodThenHoldMethod()
{
employee.Method = new MailMethod("123 Baker St.");
database.AddEmployee(employee);
Employee employee2 = new Employee(321, "Ed", "456 Elm St.");
employee2.Method = new HoldMethod();
database.AddEmployee(employee2);
DataTable table = LoadTable("PaycheckAddress");
Assert.AreEqual(1, table.Rows.Count);
}
The test fails because the two records were added to the PaycheckAddress table. The insertPaymentMethodCommand is loaded with a command to add the MailMethod for the first employee. When the second employe was saved, the residual command was left behind because the HoldMethod doesn’t require any extra command, and it was executed a second time.
There are several ways to fix this, but something else bugs me. We originally set off to implement the SqlPayrollDatabase.AddEmployee method, and in doing so, we created a plethora of private helper methods. This has really cluttered the poor SqlPayrollDatabase class. It’s time to create a class that will handle the saving of an employee: a SaveEmployeeOperation class. AddEmployee() will create a new instance of SaveEmployeeOperation every time it’s called. This way, we won’t have to null the commands, and the SqlPayrollDatabase becomes much cleaner. We’re not changing any functionality with this change. It’s simply a refactoring, so there’s no need for new tests.
First, I create the SaveEmployeeOperation class and copy over the code to save the employee. I have to add a constructor and a new method, Execute(), to initiate the save. Listing 37-24 shows the budding class.
Listing 37-24. SaveEmployeeOperation.cs (partial)
public class SaveEmployeeOperation
{
private readonly Employee employee;
private readonly SqlConnection connection;
private string methodCode;
private string classificationCode;
private SqlCommand insertPaymentMethodCommand;
private SqlCommand insertEmployeeCommand;
private SqlCommand insertClassificationCommand;
public SaveEmployeeOperation(
Employee employee, SqlConnection connection)
{
this.employee = employee;
this.connection = connection;
}
public void Execute()
{
/*
All the code to save an Employee
*/
}
Then I change the SqlPayrollDatabase.AddEmplyee() method to create a new instance of SaveEmployeeOperation and execute it (shown in Listing 37-25). All the tests pass, including SaveMailMethodThenHoldMethod. Once all the copied code is deleted, SqlPayrollDatabase becomes much cleaner.
Listing 37-25. SqlPayrollDatabase.AddEmployee()
public void AddEmployee(Employee employee)
{
SaveEmployeeOperation operation =
new SaveEmployeeOperation(employee, connection);
operation.Execute();
}
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.
What Remains?
The SqlPayrollDatabase class can save new Employee objects and load Employee objects. But it’s not complete. What would happen if we save an Employee object that was already saved in the database? This still needs to be handled. Also, we haven’t done anything about time cards, sales receipts, or union affiliations. Based on the work that we’ve accomplished so far, adding this functionality should be fairly straightforward and is, again, left to you.