Unit Tests for DataReaderHelper Class

Here are the unit tests I wrote for the DataReaderHelper class described in yesterday’s post.  These unit tests are written using NUnit 2.5.2 and Rhino.Mocks 3.6.  I followed Roy Osherove’s naming standards for unit tests.  I found help with the Rhino.Mocks WhenCalled method from Graham Nash’s blog.

using System;
using NUnit.Framework;
using Rhino.Mocks;
using System.Data;
using DataAccessLibrary.Common;
using System.Collections.Generic;

[TestFixture]
public class DataReaderHelperTests
{
    #region Variables and Constants
    private MockRepository _mocks;
    private DataReaderHelper.CreateConnection _mockCreateConnection;
    private IDbConnection _mockConnection;
    private IDbCommand _mockCommand;
    private IDataReader _mockReader;
    private DataReaderHelper.ReadDatabaseValue<object> _mockDelegate;
    private object _obj;
    #endregion


    #region Setup

    [SetUp]
    public void InitTest()
    {
        _mocks = new MockRepository();
        _mockCreateConnection = _mocks.StrictMock<DataReaderHelper.CreateConnection>();
        _mockConnection = _mocks.StrictMock<IDbConnection>();
        _mockCommand = _mocks.StrictMock<IDbCommand>();
        _mockReader = _mocks.StrictMock<IDataReader>();
        _mockDelegate = _mocks.StrictMock<DataReaderHelper.ReadDatabaseValue<object>>();
        _obj = null;
    }

    #endregion


    #region Unit Tests

    [Test]
    public void GetData_ListInt_ReturnsFromDelegateUsingReader()
    {
        DataReaderHelper.CreateConnection stubCreateConnection = MockRepository.GenerateStub<DataReaderHelper.CreateConnection>();
        IDbConnection stubConnection = MockRepository.GenerateStub<IDbConnection>();
        IDbCommand stubCommand = MockRepository.GenerateStub<IDbCommand>();
        IDataReader stubReader = MockRepository.GenerateStub<IDataReader>();
        DataReaderHelper.ReadDatabaseValue<List<int>> stubDelegate =
            MockRepository.GenerateStub<DataReaderHelper.ReadDatabaseValue<List<int>>>();
        List<int> testList = new List<int>();

        stubCreateConnection.Stub(get => get()).Return(stubConnection);
        stubConnection.Stub(conn => conn.CreateCommand()).Return(stubCommand);
        stubCommand.Stub(comm => comm.ExecuteReader()).Return(stubReader);
        stubReader.Stub(rdr => rdr.Read()).Return(true).Repeat.Times(2);
        stubReader.Stub(rdr => rdr.Read()).Return(false).Repeat.Once();
        stubReader.Stub(rdr => rdr.GetInt32(0)).Return(2).Repeat.Once();
        stubReader.Stub(rdr => rdr.GetInt32(0)).Return(4).Repeat.Once();
        stubDelegate.Stub(del => del(stubReader, ref testList))
            .WhenCalled(invocation => testList.Add(stubReader.GetInt32(0))).Repeat.Times(2).OutRef(testList);

        DataReaderHelper.GetData<List<int>>(stubCreateConnection, "sql", stubDelegate, ref testList);

        Assert.That(testList.Count, Is.EqualTo(2));
        Assert.That(testList[0], Is.EqualTo(2));
        Assert.That(testList[1], Is.EqualTo(4));
    }

    [Test]
    public void GetData_CreateConnectionException_NoDisposals()
    {
        using (_mocks.Ordered())
        {
            Expect.Call(_mockCreateConnection()).Throw(MockRepository.GenerateStub<Exception>()).Repeat.Once();
            Expect.Call(_mockConnection.State).Repeat.Never();
            Expect.Call(() => _mockConnection.Open()).Repeat.Never();
            Expect.Call(_mockConnection.CreateCommand()).Return(_mockCommand).Repeat.Never();
            Expect.Call(_mockCommand.CommandText = "sql").Repeat.Never();
            Expect.Call(_mockCommand.ExecuteReader()).Return(_mockReader).Repeat.Never();
            Expect.Call(_mockReader.Read()).Repeat.Never();
            Expect.Call(() => _mockDelegate(_mockReader, ref _obj)).Repeat.Never();
            Expect.Call(_mockReader.GetValue(0)).Repeat.Never();
            Expect.Call(() => _mockConnection.Dispose()).Repeat.Never();
            Expect.Call(() => _mockCommand.Dispose()).Repeat.Never();
            Expect.Call(() => _mockReader.Dispose()).Repeat.Never();
        }
        _mocks.ReplayAll();
        VerifyBehavior();
    }

    [Test]
    public void GetData_ConnectionOpenException_DisposesOfConnection()
    {
        using (_mocks.Ordered())
        {
            Expect.Call(_mockCreateConnection()).Return(_mockConnection).Repeat.Once();
            Expect.Call(_mockConnection.State).Return(ConnectionState.Closed).Repeat.Once();
            Expect.Call(() => _mockConnection.Open()).Throw(MockRepository.GenerateStub<Exception>()).Repeat.Once();
            Expect.Call(_mockConnection.CreateCommand()).Return(_mockCommand).Repeat.Never();
            Expect.Call(_mockCommand.CommandText = "sql").Repeat.Never();
            Expect.Call(_mockCommand.ExecuteReader()).Return(_mockReader).Repeat.Never();
            Expect.Call(_mockReader.Read()).Repeat.Never();
            Expect.Call(() => _mockDelegate(_mockReader, ref _obj)).Repeat.Never();
            Expect.Call(_mockReader.GetValue(0)).Repeat.Never();
            Expect.Call(() => _mockConnection.Dispose()).Repeat.Once();
            Expect.Call(() => _mockCommand.Dispose()).Repeat.Never();
            Expect.Call(() => _mockReader.Dispose()).Repeat.Never();
        }
        _mocks.ReplayAll();
        VerifyBehavior();
    }

    [Test]
    public void GetData_ConnectionCreateCommandException_DisposesOfConnection()
    {
        using (_mocks.Ordered())
        {
            Expect.Call(_mockCreateConnection()).Return(_mockConnection).Repeat.Once();
            Expect.Call(_mockConnection.State).Return(ConnectionState.Closed).Repeat.Once();
            Expect.Call(() => _mockConnection.Open()).Repeat.Once();
            Expect.Call(_mockConnection.CreateCommand()).Return(_mockCommand)
                .Throw(MockRepository.GenerateStub<Exception>()).Repeat.Once();
            Expect.Call(_mockCommand.CommandText = "sql").Repeat.Never();
            Expect.Call(_mockCommand.ExecuteReader()).Return(_mockReader).Repeat.Never();
            Expect.Call(_mockReader.Read()).Repeat.Never();
            Expect.Call(() => _mockDelegate(_mockReader, ref _obj)).Repeat.Never();
            Expect.Call(_mockReader.GetValue(0)).Repeat.Never();
            Expect.Call(() => _mockConnection.Dispose()).Repeat.Once();
            Expect.Call(() => _mockCommand.Dispose()).Repeat.Never();
            Expect.Call(() => _mockReader.Dispose()).Repeat.Never();
        }
        VerifyBehavior();
    }

    [Test]
    public void GetData_CommandExecuteReaderException_DisposesOfConnectionAndCommand()
    {
        using (_mocks.Ordered())
        {
            Expect.Call(_mockCreateConnection()).Return(_mockConnection).Repeat.Once();
            Expect.Call(_mockConnection.State).Return(ConnectionState.Closed).Repeat.Once();
            Expect.Call(() => _mockConnection.Open()).Repeat.Once();
            Expect.Call(_mockConnection.CreateCommand()).Return(_mockCommand).Repeat.Once();
            Expect.Call(_mockCommand.CommandText = "sql").Repeat.Once();
            Expect.Call(_mockCommand.ExecuteReader()).Return(_mockReader)
                .Throw(MockRepository.GenerateStub<Exception>()).Repeat.Once();
            Expect.Call(_mockReader.Read()).Repeat.Never();
            Expect.Call(() => _mockDelegate(_mockReader, ref _obj)).Repeat.Never();
            Expect.Call(_mockReader.GetValue(0)).Repeat.Never();
            Expect.Call(() => _mockConnection.Dispose()).Repeat.Once();
            Expect.Call(() => _mockCommand.Dispose()).Repeat.Once();
            Expect.Call(() => _mockReader.Dispose()).Repeat.Never();
        }
        VerifyBehavior();
    }

    [Test]
    public void GetData_ReaderReadException_DisposesOfConnectionAndCommandAndReader()
    {
        using (_mocks.Ordered())
        {
            Expect.Call(_mockCreateConnection()).Return(_mockConnection).Repeat.Once();
            Expect.Call(_mockConnection.State).Return(ConnectionState.Closed).Repeat.Once();
            Expect.Call(() => _mockConnection.Open()).Repeat.Once();
            Expect.Call(_mockConnection.CreateCommand()).Return(_mockCommand).Repeat.Once();
            Expect.Call(_mockCommand.CommandText = "sql").Repeat.Once();
            Expect.Call(_mockCommand.ExecuteReader()).Return(_mockReader).Repeat.Once();
            Expect.Call(_mockReader.Read()).Throw(MockRepository.GenerateStub<Exception>()).Repeat.Once();
            Expect.Call(() => _mockDelegate(_mockReader, ref _obj)).Repeat.Never();
            Expect.Call(_mockReader.GetValue(0)).Repeat.Never();
            Expect.Call(() => _mockConnection.Dispose()).Repeat.Once();
            Expect.Call(() => _mockCommand.Dispose()).Repeat.Once();
            Expect.Call(() => _mockReader.Dispose()).Repeat.Once();
        }
        VerifyBehavior();
    }

    [Test]
    public void GetData_ReaderGetValueException_DisposesOfConnectionAndCommandAndReader()
    {
        using (_mocks.Ordered())
        {
            Expect.Call(_mockCreateConnection()).Return(_mockConnection).Repeat.Once();
            Expect.Call(_mockConnection.State).Return(ConnectionState.Closed).Repeat.Once();
            Expect.Call(() => _mockConnection.Open()).Repeat.Once();
            Expect.Call(_mockConnection.CreateCommand()).Return(_mockCommand).Repeat.Once();
            Expect.Call(_mockCommand.CommandText = "sql").Repeat.Once();
            Expect.Call(_mockCommand.ExecuteReader()).Return(_mockReader).Repeat.Once();
            Expect.Call(_mockReader.Read()).Return(true).Repeat.Once();
            Expect.Call(() => _mockDelegate(_mockReader, ref _obj))
                .WhenCalled(invocation => _obj = _mockReader.GetValue(0)).OutRef(_obj).Repeat.Once();
            Expect.Call(_mockReader.GetValue(0)).Throw(MockRepository.GenerateStub<Exception>()).Repeat.Once();
            Expect.Call(() => _mockConnection.Dispose()).Repeat.Once();
            Expect.Call(() => _mockCommand.Dispose()).Repeat.Once();
            Expect.Call(() => _mockReader.Dispose()).Repeat.Once();
        }
        VerifyBehavior();
    }

    [Test]
    public void GetData_NoExceptionsConnectionClosed_AllBehaviorIsCorrect()
    {
        using (_mocks.Ordered())
        {
            Expect.Call(_mockCreateConnection()).Return(_mockConnection).Repeat.Once();
            Expect.Call(_mockConnection.State).Return(ConnectionState.Closed).Repeat.Once();
            Expect.Call(() => _mockConnection.Open()).Repeat.Once();
            Expect.Call(_mockConnection.CreateCommand()).Return(_mockCommand).Repeat.Once();
            Expect.Call(_mockCommand.CommandText = "sql").Repeat.Once();
            Expect.Call(_mockCommand.ExecuteReader()).Return(_mockReader).Repeat.Once();
            Expect.Call(_mockReader.Read()).Return(true).Repeat.Once();
            Expect.Call(() => _mockDelegate(_mockReader, ref _obj))
                .WhenCalled(invocation => _obj = _mockReader.GetValue(0)).OutRef(_obj).Repeat.Once();
            Expect.Call(_mockReader.GetValue(0)).Return(new object()).Repeat.Once();
            Expect.Call(_mockReader.Read()).Return(false).Repeat.Once();
            Expect.Call(() => _mockConnection.Dispose()).Repeat.Once();
            Expect.Call(() => _mockCommand.Dispose()).Repeat.Once();
            Expect.Call(() => _mockReader.Dispose()).Repeat.Once();
        }
        VerifyBehavior();
    }

    [Test]
    public void GetData_NoExceptionsConnectionAlreadyOpen_AllBehaviorIsCorrect()
    {
        using (_mocks.Ordered())
        {
            Expect.Call(_mockCreateConnection()).Return(_mockConnection).Repeat.Once();
            Expect.Call(_mockConnection.State).Return(ConnectionState.Open).Repeat.Once();
            Expect.Call(_mockConnection.CreateCommand()).Return(_mockCommand).Repeat.Once();
            Expect.Call(_mockCommand.CommandText = "sql").Repeat.Once();
            Expect.Call(_mockCommand.ExecuteReader()).Return(_mockReader).Repeat.Once();
            Expect.Call(_mockReader.Read()).Return(true).Repeat.Once();
            Expect.Call(() => _mockDelegate(_mockReader, ref _obj))
                .WhenCalled(invocation => _obj = _mockReader.GetValue(0)).OutRef(_obj).Repeat.Once();
            Expect.Call(_mockReader.GetValue(0)).Return(new object()).Repeat.Once();
            Expect.Call(_mockReader.Read()).Return(false).Repeat.Once();
            Expect.Call(() => _mockConnection.Dispose()).Repeat.Once();
            Expect.Call(() => _mockCommand.Dispose()).Repeat.Once();
            Expect.Call(() => _mockReader.Dispose()).Repeat.Once();
        }
        VerifyBehavior();
    }

    #endregion


    #region Helper Methods

    // common unit test code
    private void VerifyBehavior()
    {
        _mocks.ReplayAll();
        try
        {
            DataReaderHelper.GetData<object>(_mockCreateConnection, "sql", _mockDelegate, ref _obj);
        }
        catch (Exception) { }
        finally
        {
            _mocks.VerifyAll();
        }
    }

    #endregion
}

A Generic Database Reader Class

I’ve been doing a lot of CRUD-type work recently against a reporting database.  The application does not utilize an ORM because of the complexity of selection SQL, so I’ve been designing a custom ORM framework.

One of my top priorities is to route all database access through a single class, so I developed this generic database reader.  The code is shown below.

The generic reader class is DataReaderHelper.  It has a GetData generic method that accepts a generic delegate and returns read data using a referenced generic parameter.  This combination of generics and delegates provides an exceptionally flexible class that I can use for any database reads, ensuring that my connections are centrally managed.

The code sample below also provides sample usage in the Mapper class.

// generic class for reading database data
public class DataReaderHelper
{
    // generic delegate passed into the GetData method to manipulate the IDataReader object
    public delegate void ReadDatabaseValue<T>(IDataReader reader, ref T businessObject);
    // delegate is passed into the GetData method to create a new connection
    public delegate IDbConnection CreateConnection();

    public static void GetData<T>(CreateConnection createConnection, string sql, 
        ReadDatabaseValue<T> readDelegate, ref T businessObject)
    {
        IDbConnection dbConnection = null;
        IDbCommand command = null;
        IDataReader reader = null;
        try
        {
            dbConnection = createConnection();
            if (dbConnection.State != ConnectionState.Open) { dbConnection.Open(); }
            command = dbConnection.CreateCommand();
            command.CommandText = sql;
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                readDelegate(reader, ref businessObject);
            }
        }
        catch { throw; }
        finally
        {
            if (dbConnection != null) dbConnection.Dispose();
            if (command != null) command.Dispose();
            if (reader != null) reader.Dispose();
        }
    }
}

// provides the database connection delegate
internal class ConnectionFactory
{
    internal static SqlConnection GetConnection()
    {
        try
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
        }
        catch { throw; }
    }
}

// provides delegates used to read data
internal class DataReaderFactory
{
    internal static void ReadFirstColumnIntoString(IDataReader reader, ref string value)
    {
        value = reader.GetString(0);
    }
    internal static void ReadFirstColumnIntoStringCollection(IDataReader reader,
        ref StringCollection value)
    {
        value.Add(reader.GetString(0));
    }
}

// example DataReaderHelper usage
internal class Mapper
{
    internal static string DemoGetString()
    {
        string demo = null;
        DataReaderHelper.GetData<string>(ConnectionFactory.GetConnection, "my sql",
            DataReaderFactory.ReadFirstColumnAsValue, ref demo);
        return demo;
    }

    internal static StringCollection DemoGetStringCollection()
    {
        StringCollection demo = null;
        DataReaderHelper.GetData<StringCollection>(ConnectionFactory.GetConnection, "my sql",
            DataReaderFactory.ReadFirstColumnAsInt32IdList, ref demo);
        return demo;
    }
}