Welcome to part two of our series on building a Windows application using test-driven development (TDD). In the previous article we drove the design of our entity classes and data access layer by means of unit tests. The unit tests acted more as specifications for the system rather than tests, since we actually weren’t testing any output from our system. We simply used the tests to illustrate how we want to interact with our data access layer and what properties are exposed for each entity class.

Now we move on to the next step, which is creating the actual data access layer for the application. Keith graciously shared with us the MS Access database file containing the entire Bible (I wonder if there are any copyright issues with this?). If you would open the .mdb file you will see only three tables inside: Testaments, Books and Verses.

So what we are going to do now is to map the database to our classes by means of our IDataService implementation. Since I’m not very good when it comes to names, I’ll just call our implementation DataService. Since we are test-driven, we cannot go directly to coding without creating test code first. Strictly speaking, we will not actually be creating unit tests but integration tests because we are interacting with an external subsystem (the database). So let’s load the solution we’ve been working on, and add this first test to the BibleCodeTests project. I named the class DbIntegrationTests.cs:

1.  using Microsoft.VisualStudio.TestTools.UnitTesting;

2.  using BibleCode;

3.   

4.  namespace BibleCodeTests {

5.      [TestClass]

6.      public class DbIntegrationTests {

7.          private static string connString =

8.  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\To\BibleDB.mdb;";

9.          [TestMethod]

10.         public void GetOldTestament() {

11.             IDataService srv = new DataService(connString);

12.             Testament oldT = srv.GetTestamentByName("Old Testament");

13.             Assert.AreEqual(1, oldT.Id);

14.             Assert.AreEqual("Old Testament", oldT.Name);

15.         }

16. }

Again, notice how we are specifying our intentions using this test. We expect to have a DataService class that implements the IDataService interface we developed (in Part 1), and we pass a connection string to initialize the DataService. For this test, we will be retrieving the object corresponding to the Old Testament. If you’d notice in the Testaments table from the actual database, the record for it has a TestamentID (primary key) value of 1.

So let’s code DataService.cs to pass this test:

1.  using System;

2.  using System.Collections.Generic;

3.  using System.Data.OleDb;

4.   

5.  namespace BibleCode {

6.      public class DataService : IDataService {

7.          private string connectionString;

8.   

9.          public DataService(string connString) {

10.             connectionString = connString;

11.         }

12.  

13.         public Testament GetTestamentByName(string input) {

14.             OleDbConnection conn = new OleDbConnection(connectionString);

15.             OleDbCommand comm = new OleDbCommand("SELECT [TestamentID],[Name] FROM [Testaments] WHERE [Name]=@name", conn);

16.             comm.Parameters.Add("@name", OleDbType.VarChar).Value = input.Trim();

17.             conn.Open();

18.             Testament output = null;

19.             OleDbDataReader reader = comm.ExecuteReader();

20.             while (reader.Read()) {

21.                 output = new Testament( Id = reader.GetInt32(0), Name = reader.GetString(1).Trim() );

22.             }

23.             reader.Close();

24.             conn.Close();

25.             return output;

26.         }

27. }

We use the OleDb* classes in ADO.NET to connect to the MS Access database. Compiling the app will fail because we have not yet implemented the other methods specified by the IDataService interface. For now just create stub methods for this that return null, or you can use a tool like ReSharper to generate stub methods that throw a NotImplementedException when called. Well, whatever…just make sure it compiles. When it finally compiles and you run the tests, all the tests (including our unit tests from Part 1) should pass.

So let’s proceed to the next method we want to develop in our DataService: retrieving all Testament objects. Here’s the test:

1.         [TestMethod]

2.          public void GetAllTestaments() {

3.              IDataService srv = new DataService(connString);

4.              Testament[] testaments = srv.GetAllTestaments();

5.              Assert.AreEqual(2, testaments.Length);

6.              Assert.AreEqual(1, testaments[0].Id);

7.              Assert.AreEqual("Old Testament", testaments[0].Name);

8.              Assert.AreEqual(2, testaments[1].Id);

9.              Assert.AreEqual("New Testament", testaments[1].Name);

10.         }

And the DataService method:

1.         public Testament[] GetAllTestaments() {

2.              OleDbConnection conn = new OleDbConnection(connectionString);

3.              OleDbCommand comm = new OleDbCommand("SELECT [TestamentID],[Name] FROM [Testaments] ORDER BY [TestamentID]", conn);

4.              List<Testament> testaments = new List<Testament>();

5.              conn.Open();

6.              OleDbDataReader reader = comm.ExecuteReader();

7.              while (reader.Read()) {

8.                  Testament t = new Testament();

9.                  t.Id = reader.GetInt32(0);

10.                 t.Name = reader.GetString(1).Trim();

11.                 testaments.Add(t);

12.             }

13.             reader.Close();

14.             conn.Close();

15.             return testaments.ToArray();

16.         }

At this point you may notice some code patterns repeating here. It is a good thing to eliminate duplication (DRY principle), so let’s exercise the “yellow” or refactoring stage in our red-green-refactor cycle. This means we improve our code while ensuring that all our tests still pass. We can start off by creating a private method that creates the OleDbConnection for us:

1.         private OleDbConnection CreateConnection() {

2.              return new OleDbConnection(connectionString);

3.          }

Hmm, come to think of it, we can do the same for the creation of the OleDbCommand as well:

1.         private OleDbCommand CreateCommand(string query) {

2.              return new OleDbCommand(query, CreateConnection());

3.          }

We can still spot duplication in the code – the connection is opened, the command is executed, then a reader is created, the reader is iterated, the reader is closed then finally the connection is closed. A way to eliminate that is to create a private method that will do the dirty work for us; we will just pass a delegate block to do what we want to do within the reader iteration. For that, we define a delegate type called “ReaderDelegate” and we create another private method that uses it:

1.         public delegate void ReaderDelegate(OleDbDataReader reader);

2.   

3.          private void WhileInReader(OleDbCommand comm, ReaderDelegate block) {

4.              comm.Connection.Open();

5.              OleDbDataReader reader = comm.ExecuteReader();

6.              while (reader.Read()) {

7.                  block(reader);

8.              }

9.              reader.Close();

10.             comm.Connection.