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