This blog post was inspired by a question from one of our clients.
When developing software, database interactions are often a crucial part of your application. However, testing these interactions can be challenging due to their dependency on an external SQL database. This is where Typemock Isolator shines, enabling you to isolate and mock SQL connections and commands effortlessly.
In this blog post, we’ll demonstrate how to mock an SQL connection using Typemock Isolator in a unit test. To illustrate, we’ll use a simple example of a repository class that retrieves employee names from a database. Let’s dive right in!
Code Under Test
Here’s our EmployeeRepository class that interacts with an SQL database to retrieve employee names:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
public class EmployeeRepository { private readonly string _connectionString; public EmployeeRepository(string connectionString) { _connectionString = connectionString; } public List<string> GetEmployeeNames() { var employeeNames = new List<string>(); using (var connection = new SqlConnection(_connectionString)) { connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = "SELECT Name FROM Employees"; using (var reader = command.ExecuteReader()) { while (reader.Read()) { employeeNames.Add(reader["Name"].ToString()); } } } } return employeeNames; } } |
This class connects to a database, executes an SQL query, and reads the results. While this works well in production, testing it requires an actual database connection, which can be cumbersome.
Unit Test with Typemock Isolator
Let’s write a unit test that mocks the SQL connection and ensures the GetEmployeeNames method works as expected.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
[TestClass] public class Test { [TestMethod, Isolated] public void GetEmployeeNames_TwoEmployeesInDatabase_ReturnsCorrectNames() { // Arrange // Fake the future instance of the SqlConnection var fakeConnectionHandle = Isolate.Fake.NextInstance<SqlConnection>(); // Obtain the recursive fake SqlDataReader handle var fakeReaderHandle = fakeConnectionHandle.CreateCommand().ExecuteReader(); // Specify the mocking behavior Isolate.WhenCalled(() => fakeReaderHandle.Read()).WillReturn(true); Isolate.WhenCalled(() => fakeReaderHandle.Read()).WillReturn(true); Isolate.WhenCalled(() => fakeReaderHandle.Read()).WillReturn(false); Isolate.WhenCalled(() => fakeReaderHandle["Name"]).WillReturn("John Doe"); Isolate.WhenCalled(() => fakeReaderHandle["Name"]).WillReturn("Jane Smith"); // Instantiate the class under test var repository = new EmployeeRepository("FakeConnectionString"); // Act var employeeNames = repository.GetEmployeeNames(); // Assert CollectionAssert.AreEqual(new List<string> { "John Doe", "Jane Smith" }, employeeNames); } } |
Here’s what this test does:
- Faking SQL Objects: Using Typemock Isolator, we fake the future instance of SqlConnection, which is instantiated in the GetEmployeeNames method, and obtain a handle of that type. Typemock’s fake objects are, by default, recursive, meaning that all related object instances (such as SqlCommand and SqlDataReader) are automatically faked as well. This eliminates the need to fake each object individually, simplifying the process.
- Getting the Handle for the SqlConnection Future Object: When faking a future instance with NextInstance (as in our example for SqlConnection), Typemock provides a handle to the future object, not the actual instance. Since the corresponding object hasn’t been created yet, we cannot directly modify its behavior on the handle. If we want to change the behavior of a recursive fake, we can do so as described in the next two steps.
- Obtaining the Recursive Fake SqlDataReader Handle: Through method chaining, we can easily retrieve the fake SqlDataReader handle from within the GetEmployeeNames method, even though the object has not been created yet! This is the power of Typemock’s Future Fake feature, using NextInstance.
- Mocking Behavior: We specify the behavior of the mocked SqlDataReader to return pre-defined data, matching the behavior of the method under test. In our example, we define the first two calls to reader.Read() will return true, simulating two rows in the database (representing two employees). We also define that reader[“Name”] will return the names of these employees. This is all made possible by Typemock’s ability to simulate sequenced behavior.
- Testing the Class: Finally, we instantiate the EmployeeRepository and call the GetEmployeeNames method to verify that it retrieves the expected data.
Key Benefits
Using Typemock Isolator to mock SQL connections offers several advantages:
- Decouples Tests from External Systems: Your tests are independent of an actual SQL database, making them faster and more reliable.
- Simplifies Test Setup: You can focus on the logic being tested without worrying about database setup or teardown.
- Helps with Continuous Integration (CI): By decoupling your tests from an actual database, you can run your unit tests in environments like CI pipelines without needing a dedicated database setup. This reduces configuration complexity and ensures that tests run consistently across different environments.
Conclusion
With Typemock Isolator, you can write unit tests for code that interacts with a database without needing a real SQL connection. This approach saves time, increases test reliability, and allows you to focus on what matters most: your business logic.
Try Typemock Isolator today and take your unit testing to the next level!
