Unit testing - stubbing an SqlDataReader

We have an n-tier web application that pulls data from SQL Server. Our Data Access logic returns an SqlDataReader whose data is then used to create our Business objects (a.k.a. Data Transfer objects).

We wish to build unit tests to check our code that interprets the data returned by these SqlDataReader objects to build our Business objects.

It therefore seems necessary to build stubs to replace the SqlDataReader objects during unit testing. As is probably fairly typical, our SqlDataReader objects generally return multiple recordsets, each with multiple rows.

1. Is this a sensible endeavour?
2. How should we go about building these stub objects?

Many thanks in advance

Griff

Automated testing is basically always a sensible endeavour :)

Your first step to be able to test this is to have your Data Access logic return an IDataReader instead of an SqlDataReader - SqlDataReader implements IDataReader, so no problems there.

In your unit tests you can then manually build and populate DataTable objects, and call dataTable.CreateDataReader() to get an IDataReader to pass into the object under test.

Edit

To provide your tests with a set of sample data, I'd suggest using an ObjectMother for each data table you use, keeping creation of the data tables in one dedicated place. You can then put methods on each ObjectMethod class to update certain data in a strongly-typed way. For example:

public class PersonalDetailsBuilder
{
    private DataTable _dataTable;

    public PersonalDetailsBuilder CreateNewTable()
    {
        this._dataTable = new DataTable("CustomerPersonalDetails")
        {
            Columns = 
            {
                new DataColumn("CustomerId", typeof(int)),
                new DataColumn("CustomerName", typeof(string))
            }
        };

        return this;
    }

    public PersonalDetailsBuilder AddStandardData(int numberOfRows = 3)
    {
        foreach (int i in Enumerable.Range(1, numberOfRows + 1))
        {
            this.AddRow(i, "Customer " + i);
        }

        return this;
    }

    public PersonalDetailsBuilder AddRow(int customerId, string customerName)
    {
        this._dataTable.Rows.Add(customerId, customerName);

        return this;
    }

    public IDataReader ToDataReader()
    {
        return this._dataTable.CreateDataReader();
    }
}

...which you could then use like this to get a data reader:

IDataReader customerDetailsReader = new PersonalDetailsBuilder()
    .CreateNewTable()
    .AddStandardData()
    .AddRow(17, "Customer 17")
    .ToDataReader();