| 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 passafter 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); } ... }
|
|
