Stubbing / mocking a database in .Net

Question

I have a webservice which basically just executes some stored procedures, transforms the data and sends it to the browser. No fancy ORM mapper or something like that involved. To be able to write test without accessing the database, I have done the following:

• I have extracted all calls to the DB into one class. The methods return just the DataSet and DataTable objects.
• Executed a sample call for each method and serialized the DataSet/DataTable to disk.
• Extracted an interface exposing all available methods.
• Implemented a fake database class which just loads the serialized data and returns it.

Now I have serialized sample results which I can check in with my project, and I can use the fake database in my tests.

This works quite well for me. Is there some framework which makes creating and loading the sample data easier? My current project is small, but I would use the same schema in larger projects.

Update:

Obviously all answers are not wrong, but miss the point. I'm aware of the basics of unit testing. But my code is working with DataTables, so I would have to somehow fake my DataTables. Building a DataTable from scratch is not an easy task, and it would bloat my tests and reduce readability. In my case, it would be quite impossible to generate useful sample data by hand.

Therefore, I executed some sample calls against a sample database to get some DataTables. I have serialized these tables to disk and use the serialized versions to create my fake DataTables when testing. That way the tests are independent of the database.

There are different options regarding how to structure the code, to make deserialization of the tables easier. But those are implementation details which don't need a discussion at this point. My problem is the following:

Managing the sample calls and (de)serializing the tables is tedious work. I was looking for some tools to make this easier.

Answer 1

From reading the other answers and various comments you've made, it seems you want an easier way to generate large populated datasets for integration testing that doesn't hit the database.

NBuilder is a great open-source library that I've successfully used to create large amounts of test data. Simply combine NBuilder, a few basic POCO object classes, and some reflection - you'll have plenty of huge datatables you can easily combine into datasets in no time:

public class Person
{
    public string First { get; set; }
    public string Last { get; set; }
    public DateTime Birthday { get; set; }
}

private DataTable GenerateDataTable<T>(int rows)
{
    var datatable = new DataTable(typeof(T).Name);
    typeof(T).GetProperties().ToList().ForEach(
        x => datatable.Columns.Add(x.Name));
    Builder<T>.CreateListOfSize(rows).Build()
        .ToList().ForEach(
            x => datatable.LoadDataRow(x.GetType().GetProperties().Select(
                y => y.GetValue(x, null)).ToArray(), true));
    return datatable;
}

var dataset = new DataSet();
dataset.Tables.AddRange(new[]{
        GenerateDataTable<Person>(50),
        GenerateDataTable<Dog>(100)});

Answer 2

To unit test the transformation you really shouldn't need to mock the database at all. I suspect that you've tightly coupled the transformations with you database calls. What you want to do here is extract all your transformation logic into a class of it's own like the following:

public static Transformations
{
    public static DataSet TransformationA(DataSet dataSet)
    {
        //transformation logic here
    }

    public static DataSet TransformationB(DataSet dataSet)
    {
        //transformation logic here
    }
}

With this you can unit test only the transformations logic by passing in a dataset and then asserting the dataset returned has the correct transformations applied to it. This will prevent you from having to implement another data store (your 'fake' database) for testing purposes only.

Hopefully this helps

Answer 3

You could mock your DataAccess class with Rhinomocks and return a fake datatable. So you can test the code that uses this DataTable.

var mockedDatatable= GetMockdt();

var mocks = new MockRepository();
var dal = mocks.StrictMock<DataAccess>();

using (mocks.Record())
{
  Expect.Call(dal.GetDataTableFromDatabase("", null)).Return(mockedDatatable).IgnoreArguments();
}

using (mocks.Playback())
{
  new SomeClass(dal);
}

UPDATE the mockdt message

private static DataTable GetMockdt()
{
  var dt = new DataTable();

  dt.Columns.Add("pageHeader");
  dt.Columns.Add("templatename");
  dt.Columns.Add("pageText");
  dt.Columns.Add("pageTitleBar");
  dt.Columns.Add("metaDescription");
  dt.Columns.Add("pageStartCode");
  dt.Columns.Add("pageEndCode");
  dt.Columns.Add("templateStartCode");
  dt.Columns.Add("templateEndCode");
  dt.Columns.Add("Author");
  dt.Columns.Add("version_date");
  dt.Columns.Add("pageurl");
  dt.Columns.Add("type");
  dt.Columns.Add("isparent");
  dt.Columns.Add("pagename");
  dt.Columns.Add("parentname");
  dt.Columns.Add("url");

  var mockRow = dt.NewRow();

  mockRow["pageHeader"] = "homepage";
  mockRow["pageText"] = "<p>home</p>";
  mockRow["templatename"] = "home";
  mockRow["pageTitleBar"] = "homepages";
  mockRow["metaDescription"] = "homepages";
  mockRow["pageStartCode"] = "homepages";
  mockRow["pageEndCode"] = "homepages";
  mockRow["templateStartCode"] = "homepages";
  mockRow["templateEndCode"] = "homepages";
  mockRow["Author"] = "someone";
  mockRow["version_date"] = "";
  mockRow["pageurl"] = "home";
  mockRow["type"] = "internal";
  mockRow["isparent"] = "true";
  mockRow["pagename"] = "homepage";
  mockRow["parentname"] = "root";
  mockRow["url"] = "homepage";

  dt.Rows.Add(mockRow);

  return dt;
}

Answer 4

In my experience, it has been fairly easy to make end-to-end testing work with Fluent NHibernate. There's no excuse not to use such a lightweight layer when it does so much for ya.

Persistence specification testing

Answer 5

There are not any tools for doing what you want because of your requirements that your data be stored as DataTables, and you need original data from the database. The manual part of tools is wiring up what points to what (i.e. your data in storage to your data representation in code). You have already done this part, and it is not what is automated.

Answer 6

Check out https://github.com/nbuilder/nbuilder

"What is it?

Through a fluent, extensible interface, NBuilder allows you to rapidly create test data, automatically assigning values to properties and public fields that are of type of the built in .NET data types (e.g. ints and strings). NBuilder allows you to override for properties you are interested in using lambda expressions."