The standard .net Mock approach to database testing

Standard mock object techniques solve part of the problem of testing database driven code. You can substitute real database calls with calls to a mock object. These objects are stubs for the real things. Consider this code, abbreviated from the examples in the .Net Mock Objects code:

public class MailingList
{
	public static string SELECT_SQL = "SELECT email FROM mailing_list";
	public ArrayList _list = new ArrayList();
	public MailingList() : ArrayList
	{

	}

	public void Load(IDbConnection connection, IListAction listAction)
	{
		IDbCommand command = connection.CreateCommand();
		try 
		{
			command.CommandText = MailingList.SELECT_SQL;
			connection.Open();
			IDataReader reader = command.ExecuteReader();

			while (reader.Read()) 
			{
				_list.Add(reader.GetString(0));
			}
		} 
		finally
		{
			connection.Close();
		}
	}
}

This class is handed a connection, nicely not requiring a provider specific connection type, creates a Command from it, sets the sql and executes the command. Then it reads from the reader until all the records are loaded into the array list of addresses. Finally, it must close the connection.
How would you go about testing this? You might want to just test that all the members of the list in the test database get loaded? But what if somebody else, or even your self adds to the list? Boom, broken test. What if somebody renames an email address? Boom. Deletes one? Boom.
What you really need to check here isn't the result of the query. Testing the query is a separate concern, though one you can't ignore. But for this code, what you really need to test is that the class puts in the correct sql statement, executes it, reads from the reader and closes the reader and connection. Mock objects were designed to help you with that.

[TestFixture]
public class MailingListTests
{
	private string _email = "fred.bloggs@an.address";
	private string _name = "Fred Bloggs";

	private MailingList _list = null;
	private MockListAction _mockListAction = null;
	private MockDbConnection _mockConnection = null;
	private MockCommand _mockCommand = null;
	private MockDataReader _mockReader = null;


	[SetUp]
	public void Init()
	{
		_mockConnection = new MockDbConnection();
		_mockCommand = new MockCommand();
		_mockReader = new MockDataReader();
		_mockConnection.SetExpectedCommand(_mockCommand);
	}
	private void setGeneralExpectations()
	{
		_mockConnection.SetExpectedCreateCalls(1);
		_mockCommand.SetExpectedExecuteCalls(1);
		_mockConnection.SetExpectedCloseCalls(1);
	}
	[Test]
	public void ListMembers()
	{
	
		_mockCommand.SetExpectedReader(_mockReader);
		_mockCommand.SetExpectedCommandText(MailingList.SELECT_SQL);
		_mockConnection.SetExpectedState(ConnectionState.Closed);
		object[,] values = new object[2,2];
		values[0,0] = _email;
		values[0,1] = _name;
		values[1,0] = _email;
		values[1,1] = _name;
		_mockReader.SetRows(values);

		_mockReader.SetExpectedReadCalls(3);
		MailingList list = new MailingList();
		
		list.Load(_mockConnection);

		_mockCommand.Verify();
		_mockReader.Verify();
		_mockConnection.Verify();
	}
}

What this test does is allow the test to supply the data. That is very significant. It completely isolates the problem of using test databases away from the test. If the correct number of reads, the specific expected sql, the number of calls, the connection getting closed are wrong, the test will fail when the verify calls are made on the mock objects. All of this is completely controlled by the test data in the test instead of by externally controlled data.