Moq - Mock Database

Introduction

Moq is a very useful framework which easily mocks service calls and methods for your unit testing. 

This article helps you to understand Moq with respect to mocking a database (i.e. writing unit test cases for your repository project). 

Here I have used Microsoft Enterprise Library objects (to make it easy to understand) you can very well extend it to any other framework, util or ADO.NET methods. I will also try to cover some advanced concepts used in Moq like anonymous methods, Callback() and Queueing.    

Background

I have been using Moq since last, almost an year and found that many people struggle or find difficult to mock databases. Many of us use Dev instance of database and make our test cases call the actual SQL Instance. 

Using the code

First things first – Your repository should have a constructor (or a public property)  through which you can pass the mocked database object from the unit test.

Below is sample of such constructor:-

public MyRepository(Databse Db)
{
  this.database = Db;
}

Below is sample of an "ExecuteScalar" method (it returns number of employees in a certain location).

using (DbCommand cmd = database.GetStoredProcCommand(SPCREATEPRODUCTLIST))           
{ 
    this.database.AddParameter(cmd, "@Location", DbType.String, 0, 
      ParameterDirection.Input, true, 0, 0, "Location", DataRowVersion.Default, location);
    object result = database.ExecuteScalar(cmd);
}

This is how you can mock a scalar method:

private static Mock<Database> MockExecuteScalar(object returnValue)
{
   Mock<DbProviderFactory> mockedDBFactory = new Mock<DbProviderFactory>();
   Mock<Database> mockedDB = new Mock<Database>("MockedDB", mockedDBFactory.Object);
   mockedDB.Setup(x => x.ExecuteScalar(It.IsAny<DbCommand>())).Returns(returnValue);
   return mockedDB;
}

(You can read more about Enterprise library and its implementations at http://msdn.microsoft.com/en-us/library/ff648951.aspx ).

This is quite straight forward, this method mocks the "ExecuteScalar" method (since this method is mentioned as virtual in Database class you are able to mock it. You can only mock Interfaces easily, while mocking a class you can only mock virtual properties and  methods) 

Below is how you will call this in your unit test case:

Database mockedDB = MockExecuteScalar("5").Object;
MyRepository target = new MyRepository(mockedDB);
var result = target.GetEmployeeCount("London");

In the same way you can mock "ExecuteNonQuery" implementations:

private static Mock<Database> MockExecuteNonQuery(object returnValue)
{
   Mock<DbProviderFactory> mockedDBFactory = new Mock<DbProviderFactory>();
   Mock<Database> mockedDB = new Mock<Database>("MockedDB", mockedDBFactory.Object);
   mockedDB.Setup(x => x.ExecuteNonQuery(It.IsAny<DbCommand>())).Returns(1);         
   return mockedDB;
}

Now, let's move on to "ExecuteReader" implementations. ExecuteReader can be a collection of rows and we loop the DataReader stream till the end of data. So here there are two functions to mock.

1. ExecuteReader() - To get the actual data
2. Read() - To return true till we get the desired data

Below is example of a typical implementation using "ExecuteReader":

using (DbCommand cmd = database.GetStoredProcCommand("GetEmployeeDetails", parameters))
{
    using (IDataReader dr = database.ExecuteReader(cmd))
    {
        while (dr.Read())
        {
           listofEmployeeDetails.Add(new Employee
           {
                EmployeeId = dr["EmpID"].ToString();
                EmployeeName = dr["EmployeeName"].toString();
                Location = dr["Location"].toString(); 
           });           
        }
    }
}

First let's see a simple example where  we will mock "ExecuteReader" to return a single row of data from our MockedDatabase:

Step 1: Mock "Read" method

Before mocking read method I would like to brief you about anonymous methods in Moq functions and Callback() method.

Callback()

We have already seen the .Returns() method which returns response for a mocked function call. If you want to execute your custom logic after the control comes back from Return() you can use Callback().   

This will look something like below:

mockedObject.Setup(x=>x.myMethod(It.IsAny<string>())).Returns("Hello").Callback(//custom logic goes here);

Anonymous Methods

Anonymous methods come handy you are calling a mocked method multiple times and want to change the return value  dynamically.

Below is an example:

string returnValue = "Hello"  
mockedObject.Setup(x=>x.myMethod(It.IsAny<string>())).Returns(()=>returnValue).Callback(()=>returnValue="World");

When we call "myMethod" for the very first time, the return value will be "Hello" from second time onward it will return "World". You can put any conditions or your custom implementation inside this anonymous method to suit your needs.

Now in this scenario we want "ExecuteReader" method to read one row of data. So in that case dataReader.Read() method should return true 1st time only.

So, we can mock .Read() method like:

var mockedDataReader = new Mock<IDataReader>();
bool readFlag = true;
mockedDataReader.Setup(x => x.Read()).Returns(() => readFlag).Callback(() => readFlag = false);

Step 2: Mock ExecuteReader

Before we mock "ExecuteReader" method we need to setup the response data. So when I call dr["EmpID"] 

I get my desired mocked value. We can achieve this like below:-

mockedDataReader.Setup(x => x["EmpID"]).Returns("43527");  
mockedDataReader.Setup(x => x["EmployeeName"]).Returns("Smith");  
mockedDataReader.Setup(x => x["Location"]).Returns("London"); 

Now we will mock the "ExecuteReader" method, which will return our mocked object.

Mock<DbProviderFactory> mockedDBFactory = new Mock<DbProviderFactory>();
Mock<Database> mockedDB = new Mock<Database>("MockedDB", mockedDBFactory.Object);
mockedDB.Setup(x => x.ExecuteReader(It.IsAny<DbCommand>())).Returns(mockedDataReader.Object);

The above way is same like "ExecuteScalar" and "ExecuteNonQuery" but here we are returning our custom DataReader object. Below is how the complete method will look like.

private static Mock<Database> MockExecuteReader(Dictionary<string, object> returnValues)
{
    var mockedDataReader = new Mock<IDataReader>();
    bool readFlag = true;
    mockedDataReader.Setup(x => x.Read()).Returns(() => readFlag).Callback(() => readFlag = false);
    foreach (KeyValuePair<string, object> keyVal in returnValues)
    {
        mockedDataReader.Setup(x => x[keyVal.Key]).Returns(keyVal.Value);
    }          
    Mock<DbProviderFactory> mockedDBFactory = new Mock<DbProviderFactory>();
    Mock<Database> mockedDB = new Mock<Database>("MockedDB", mockedDBFactory.Object);
    mockedDB.Setup(x => x.ExecuteReader(It.IsAny<DbCommand>())).Returns(mockedDataReader.Object);
    return mockedDB;
} 

There might be cases where you want to select multiple rows from database.

Before I start explaining about mocking multiple rows, let me explain one tricky thing in Return() function.

Let say I mocked a method, which I am calling multiple times in my code.

mockedService.Setup(x=>x.myMethod(It.IsAny<string>())).Returns("First");
mockedService.Setup(x=>x.myMethod(It.IsAny<string>())).Returns("Second"); 
mockedService.Setup(x=>x.myMethod(It.IsAny<string>())).Returns("Third"); 

The code above might look OK at first glance. But it will give "Third" as output every time.

Here anonymous functions come real handy but we need to ensure that we get output in certain order. We can achieve it by using Queue. The code will look something like this:-

Queue<object> responseQueue = new Queue<object>();
responseQueue.Enqueue("First");
responseQueue.Enqueue("Second");
responseQueue.Enqueue("Third");
mockedService.Setup(x=>x.myMethod(It.IsAny<string>())).Returns(()=>responseQueue.Dequeue()); 

If you observe the Returns() method will now invoke an anonymous method which will dequeue the values one by one. 

For returning multiple rows we will need something similar where we need to Dequeue() each row one by one. The completed method will look like below:- 

private static Mock<Database> MockExecuteReader(List<Dictionary<string, object>> returnValues)
{
  var mockedDataReader = new Mock<IDataReader>();
  int count = 0;
  Queue<object> responseQueue = new Queue<object>();
  mockedDataReader.Setup(x => x.Read()).Returns(() => count<returnValues.Count).Callback(() => count++);
  returnValues.ForEach(rows =>
  {
   foreach (KeyValuePair<string, object> keyVal in rows)
   {
       responseQueue.Enqueue(keyVal.Value);
       mockedDataReader.Setup(x => x[keyVal.Key]).Returns(()=>responseQueue.Dequeue());
   }
  });
  Mock<DbProviderFactory> mockedDBFactory = new Mock<DbProviderFactory>();
  Mock<Database> mockedDB = new Mock<Database>("MockedDB", mockedDBFactory.Object);
  mockedDB.Setup(x => x.ExecuteReader(It.IsAny<DbCommand>())).Returns(mockedDataReader.Object);
  return mockedDB;
}

If you observe the mocking of Read(), it is based on the length of the no. of mocked datarows you want to return (length of the List<>).

On each Callback() a local variable count is incremented so that when it exceeds the number of datarows Read() method will return false. 

You can implement the techniques of anonymous methods, Callback method and Queuing in all your unit tests. While mocking Repositories you can use these generic methods to Mock your databases.

Download Moq - (http://code.google.com/p/moq/)    

Happy mocking