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.Close();
11.
}
This way, we can change our DataService code to the
following more concise implementation:
1.
public Testament
GetTestamentByName(string input) {
2.
OleDbCommand comm = CreateCommand("SELECT
[TestamentID],[Name] FROM [Testaments] WHERE [Name]=@name");
3.
comm.Parameters.Add("@name", OleDbType.VarChar).Value = input.Trim();
4.
Testament output = null;
5.
WhileInReader(comm, delegate(OleDbDataReader reader) {
6.
output = new Testament { Id = reader.GetInt32(0), Name = reader.GetString(1).Trim() };
7.
});
8.
return output;
9.
}
10.
11.
public Testament[] GetAllTestaments() {
12.
OleDbCommand comm = CreateCommand("SELECT
[TestamentID],[Name] FROM [Testaments] ORDER BY [TestamentID]");
13.
List<Testament> testaments = new List<Testament>();
14.
WhileInReader(comm, delegate(OleDbDataReader reader) {
15.
Testament t = new Testament();
16.
t.Id = reader.GetInt32(0);
17.
t.Name = reader.GetString(1).Trim();
18.
testaments.Add(t);
19.
});
20.
return testaments.ToArray();
21.
}
Now isn’t that more readable? We have abstracted the
process of maintaining connections and readers away from our code. The benefit
of the TDD approach is that while we were doing the refactoring, we can ensure
that our code is still working OK by running the tests and making sure
everything passes. We are not afraid that our change might introduce
regressions to the system. I don’t know about you but for me that is a big win
for the TDD approach. Anyway, let’s continue…
Our next test is to make sure that the GetBookByName()
method of the DataService is working. Let’s start with the test, as usual:
1.
[TestMethod]
2.
public void GetBookOfGenesisByName() {
3.
IDataService srv = new DataService(connString);
4.
Book genesis = srv.GetBookByName("Genesis");
5.
Assert.AreEqual("Genesis", genesis.Name);
6.
Assert.AreEqual(1, genesis.Id);
7.
}
The change to DataService.cs to make this test pass:
1.
public Book
GetBookByName(string input) {
2.
OleDbCommand comm = CreateCommand("SELECT
[BookID],[Name] FROM [Books] WHERE [Name]=@name");
3.
comm.Parameters.Add("@name", OleDbType.VarChar).Value = input.Trim();
4.
Book output = null;
5.
WhileInReader(comm, delegate(OleDbDataReader reader) {
6.
output = new Book { Id = reader.GetInt32(0), Name = reader.GetString(1).Trim() };
7.
});
8.
return output;
9.
}
So far so good, right? However if you cross-check with
our unit tests, you’ll notice that when we receive a Testament object from the
IDataService, it should have a Books property containing all the books
belonging to that testament! Uh-oh…looks like our tests for
GetTestamentByName() and GetAllTestaments()
are wrong. Is it time to hit the DELETE key? Or worse, time to give up? Of
course not! TDD thrives in situations like this. Let’s start by changing our
tests to reflect how we expect the returned Testament object should work:
1.
[TestMethod]
2.
public void GetOldTestament() {
3.
IDataService srv = new DataService(connString);
4.
Testament oldT = srv.GetTestamentByName("Old Testament");
5.
Assert.AreEqual(1, oldT.Id);
6.
Assert.AreEqual("Old Testament", oldT.Name);
7.
Assert.AreEqual(39, oldT.Books.Length);
8.
Assert.AreEqual("Genesis", oldT.Books[0].Name);
9.
Assert.AreEqual("Malachi", oldT.Books[38].Name);
10.
}
Let’s see here: we should be getting 39 Books in the Old
Testament, with the first being Genesis and Malachi being the 39th.
I didn’t bother to put in tests for all 39 books because I assume that the
database contains the correct data. This will compile but the tests will fail
when run. In order to get the books, we may want to execute another query to get
them right after we get the Testament object. A better approach though, in my
opinion, is to just do a join of the Testaments and Books tables and read the
Testament object and its child Books in one go. This should be marginally
faster than the other approach because it requires fewer roundtrips to the
database. The code change for GetTestamentByName() is as follows:
1.
public Testament
GetTestamentByName(string input) {
2.
string query = "SELECT
DISTINCT T.[TestamentID],T.[Name],B.[BookID],B.[Name] AS BookName" +
3.
"
FROM [Testaments] T INNER JOIN [Books] B ON
T.[TestamentID]=B.[TestamentID]" +
4.
"
WHERE T.[Name]=@name ORDER BY B.[BookID]";
5.
OleDbCommand comm = CreateCommand(query);
6.
comm.Parameters.Add("@name", OleDbType.VarChar).Value = input.Trim();
7.
Testament output = null;
8.
List<Book> books = new List<Book>();
9.
WhileInReader(comm, delegate(OleDbDataReader reader) {
10.
if (output == null) {
11.
output = new Testament();
12.
output.Id = reader.GetInt32(0);
13.
output.Name = reader.GetString(1).Trim();
14.
}
15.
Book b = new Book();
16.
b.Id = reader.GetInt32(2);
17.
b.Name = reader.GetString(3).Trim();
18.
books.Add(b);
19.
});
20.
if (output != null) {
21.
output.Books = books.ToArray();
22.
}
23.
return output;
24.
}
This should pass the test now. Similarly we need to
change the test for GetAllTestaments():
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(39, testaments[0].Books.Length);
9.
Assert.AreEqual("Genesis", testaments[0].Books[0].Name);
10.
Assert.AreEqual("Malachi", testaments[0].Books[38].Name);
11.
Assert.AreEqual(2, testaments[1].Id);
12.
Assert.AreEqual("New Testament", testaments[1].Name);
13.
Assert.AreEqual(27, testaments[1].Books.Length);
14.
Assert.AreEqual("Matthew", testaments[1].Books[0].Name);
15.
Assert.AreEqual("Revelation", testaments[1].Books[26].Name);
16.
}
The reason for the change is similar; we need to get the
array of Books when we receive a Testament object from the data access layer.
The fix to GetAllTestaments() in the DataService is as follows:
1.
public Testament[] GetAllTestaments() {
2.
string query = "SELECT
DISTINCT T.[TestamentID],T.[Name],B.[BookID],B.[Name] AS BookName" +
3.
"
FROM [Testaments] T INNER JOIN [Books] B ON
T.[TestamentID]=B.[TestamentID]" +
4.
"
ORDER BY T.[TestamentID],B.[BookID]";
5.
OleDbCommand comm = CreateCommand(query);
6.
List<Testament> testaments = new List<Testament>();
7.
List<Book> books = new List<Book>();
8.
int previousTestamentId = 0;
9.
WhileInReader(comm, delegate(OleDbDataReader reader) {
10.
int testamentId = reader.GetInt32(0);
11.
if (testamentId != previousTestamentId) {
12.
if (testaments.Count > 0) {
13.
testaments[testaments.Count - 1].Books = books.ToArray();
14.
}
15.
Testament t = new Testament();
16.
t.Id = testamentId;
17.
t.Name = reader.GetString(1).Trim();
18.
books = new List<Book>();
19.
testaments.Add(t);
20.
previousTestamentId =
testamentId;
21.
}
22.
Book b = new Book();
23.
b.Id = reader.GetInt32(2);
24.
b.Name = reader.GetString(3).Trim();
25.
books.Add(b);
26.
});
27.
if (testaments.Count > 0) {
28.
testaments[testaments.Count - 1].Books = books.ToArray();
29.
}
30.
return testaments.ToArray();
31.
}
That code made me wince a bit. It’s not the best-looking
code out there, but it works if you run the tests. We’re not done yet – when
getting a Book from the data service we want to retrieve an array of its
Chapters too. So we change the test first:
1.
[TestMethod]
2.
public void GetBookOfGenesisByName() {
3.
IDataService srv = new DataService(connString);
4.
Book genesis = srv.GetBookByName("Genesis");
5.
Assert.AreEqual("Genesis", genesis.Name);
6.
Assert.AreEqual(1, genesis.Id);
7.
Assert.AreEqual(50, genesis.Chapters.Length);
8.
}
We just added one line here, the test for the number of
Chapters found in the Book. In the case of Genesis, there are 50 chapters. This
test change will cause our code to fail the tests, hence we change our code:
1.
public Book
GetBookByName(string input) {
2.
string query = "SELECT
DISTINCT B.[BookID],B.[Name],V.[ChapterID]" +
3.
"
FROM [Books] B INNER JOIN [Verses] V ON B.[BookID]=V.[BookID]" +
4.
"
WHERE B.[Name]=@name ORDER BY V.[ChapterID]";
5.
OleDbCommand comm = CreateCommand(query);
6.
comm.Parameters.Add("@name", OleDbType.VarChar).Value = input.Trim();
7.
Book output = null;
8.
List<Chapter> chaps = new List<Chapter>();
9.
WhileInReader(comm, delegate(OleDbDataReader reader) {
10.
if (output == null) {
11.
output = new Book();
12.
output.Id = reader.GetInt32(0);
13.
output.Name = reader.GetString(1).Trim();
14.
}
15.
Chapter c = new Chapter();
16.
c.Id = reader.GetInt32(2);
17.
chaps.Add(c);
18.
});
19.
if (output != null) {
20.
output.Chapters = chaps.ToArray();
21.
}
22.
return output;
23.
}
Ah, finally all tests are passing again. Now we can stop
sweeping the floor for bugs, and we can continue development. Let’s implement
GetChapter() now, starting with this test method:
1.
[TestMethod]
2.
public void GetChapter1OfGenesis() {
3.
IDataService srv = new DataService(connString);
4.
Book genesis = srv.GetBookByName("Genesis");
5.
Chapter chap1 = srv.GetChapter(genesis, 1);
6.
Assert.AreEqual(1, chap1.Id);
7.
Assert.AreEqual(31, chap1.Verses.Length);
8.
foreach (string verse in chap1.Verses) {
9.
Assert.AreNotEqual(0, verse.Length);
10.
}
11.
}
What we’re doing here is that we’re retrieving the Book
(Genesis), and we’re retrieving its first chapter in a subsequent call to the
IDataService. Then we’re verifying if indeed we got chapter #1, and that it has
31 verses and each of those verses actually contain something. To pass the
test:
1.
public Chapter
GetChapter(Book book, int chapterNumber) {
2.
string query = "SELECT
[VerseText]" +
3.
"
FROM [Verses]" +
4.
"
WHERE [ChapterID]=@chap AND [BookID]=@book" +
5.
"
ORDER BY [VerseID]";
6.
OleDbCommand comm = CreateCommand(query);
7.
comm.Parameters.Add("@chap", OleDbType.Integer).Value = chapterNumber;
8.
comm.Parameters.Add("@book", OleDbType.Integer).Value = book.Id;
9.
Chapter chap = null;
10.
List<string> verses = new List<string>();
11.
WhileInReader(comm, delegate(OleDbDataReader reader) {
12.
if (chap == null) {
13.
chap = new Chapter { Id =
chapterNumber };
14.
}
15.
verses.Add(reader.GetString(0).Trim());
16.
});
17.
if (chap != null) {
18.
chap.Verses = verses.ToArray();
19.
}
20.
return chap;
21.
}
We’ve come to the final test – retrieving matching verses
given text. Here’s our test code:
1.
[TestMethod]
2.
public void FindVersesMatching() {
3.
IDataService srv = new DataService(connString);
4.
VerseMatch[] matches = srv.FindVersesMatching("christian");
5.
Assert.AreEqual(3, matches.Length);
6.
}
Pop quiz, hotshot – how many times is the word
“Christian” found in the Bible? Well the answer is in the test above – only 3
times! At least that’s for the particular translation Keith provided us. The
code to pass this test is here:
1.
public VerseMatch[] FindVersesMatching(string text) {
2.
string query = "SELECT
B.[Name] AS BookName, V.[ChapterID], V.[VerseID]" +
3.
"
FROM [Books] B INNER JOIN [Verses] V ON B.[BookID]=V.[BookID]" +
4.
"
WHERE V.[VerseText] LIKE @argument" +
5.
"
ORDER BY B.[BookID], V.[ChapterID], V.[VerseID]";
6.
string argument = '%' + text.Replace(' ', '%') + '%';
7.
OleDbCommand comm = CreateCommand(query);
8.
comm.Parameters.Add("@argument", OleDbType.VarChar).Value = argument;
9.
List<VerseMatch> matches = new List<VerseMatch>();
10.
WhileInReader(comm, delegate(OleDbDataReader reader) {
11.
VerseMatch v = new VerseMatch();
12.
v.BookName = reader.GetString(0).Trim();
13.
v.Chapter = reader.GetInt32(1);
14.
v.Verse = reader.GetInt32(2);
15.
matches.Add(v);
16.
});
17.
return matches.ToArray();
18.
}
And there you go, all our tests are passing now. Big
question: are these tests sufficient to demonstrate that our data access code
is actually working? I’d say no. For one, we haven’t actually done assertions
to the results in the FindVersesMatching test. And we haven’t made sure that
the search will work if you give a phrase or a combination of words. I’ll leave
the addition of those further tests as an exercise to you, dear
reader/programmer. In the meantime, let me catch some sleep first. Stay tuned
for Part 3 where we will create our application’s user interface. We will
discuss how you can unit-test your user interface logic!
Posted
11-24-2008 11:37 AM
by
cruizer