How to Properly Test Controllers in ASP.net MVC that has database calls

Question

I am working on an ASP.net MVC 3.0 Application. I am using MSTest along with Moq for unit testing. I have written all the test methods for my controllers and ran those tests , which gave successful results.

Now, I have a doubt whether I have properly made unit testing. Because, almost most of my controller actions contains database calls.

I am not mocking them , I am mocking only Session and Request objects using Moq.

Is it really necessary to mock database calls, since unit testing means testing a single unit of code? I think unit testing controller with database calls violates above statement.

If it is so, can any one explain me how to mock database calls? I am not using any Entity Framework.

Updated2:

[httppost]
  public void AjaxSave(Model m)
{
   m.update(); // Database call
}

score 13 · Accepted Answer · edited May 23 '17 at 11:52

You should extract code which makes database calls into separate object (take a look on Single Responsibility Principle). E.g. you have controller

public class PersonController : Controller
{
     public ActionResult Index()
     { 
         var connectionString = 
             ConfigurationManager.ConnectionStrings["foo"].ConnectionString;
         using(var connection = new SqlConnection(connectionString))
         {
             string sql = "SELECT Name FROM People";
             var command = connection.CreateCommand(sql);
             var reader = command.ExecuteReader();
             List<Person> people = new List<Person>();
             while(reader.Read())
             {
                 Person p = new Person();
                 p.Name = reader["Name"].ToString();
                 people.Add(p);
             }

             return View(people);
         }
     }
}

Extract data-access code into separate class (usually such classes called repositories):

public class PersonRepository : IPersonRepository
{
     public List<Person> GetAllPeople()
     {
         var connectionString = 
             ConfigurationManager.ConnectionStrings["foo"].ConnectionString;
         using(var connection = new SqlConnection(connectionString))
         {
             string sql = "SELECT Name FROM People";
             var command = connection.CreateCommand(sql);
             var reader = command.ExecuteReader();
             List<Person> people = new List<Person>();
             while(reader.Read())
             {
                 Person p = new Person();
                 p.Name = reader["Name"].ToString();
                 people.Add(p);
             }

             return people;
         }
     }
}

As you already notices I declared abstraction which is implemented by data-access class:

public interface IPersonRepository
{
    List<Person> GetAllPeople();
    // other data access API will go here
}

Make controller depend on this abstraction (it's important - abstraction is easy to mock):

public class PersonController : Controller
{
     private IPersonRepository _personRepository;

     public PersonController(IPersonRepository personRepository)
     {
         _personRepository = personRepository;
     }

     public ActionResult Index()
     { 
         var people = _personRepository.GetAllPeople();
         return View(people);             
     }
}

Then inject repository implementation into controller (Dependency Injection in .NET) and mock it for tests:

var repositoryMock = new Mock<IPersonRepository>();
var people = new List<People>(); // provide some sample list 
repositoryMock.Setup(r => r.GetAllPeople()).Return(people);
var controller = new PersonController(repositoryMock.Object);

var result = (ViewResult)controller.Index();
// Assert here
Assert.AreEqual(result.ViewName, "Index");
Assert.AreEqual(result.Model, people);
repositoryMock.VerifyAll();

@lazyberezovsky..can you please give me a sample test method — Sai Avinash, Dec 11 '13 at 07:46
@Avinash welcome :) I also added several assertions to sample test method. Last one verifies that all dependency calls which you have set up for mocked repository occurred successfully (i.e. you can forget to get people from database) — Sergey Berezovskiy, Dec 11 '13 at 07:56
@Lazyberezovsky..can you please explain about the test method , what actually is happening — Sai Avinash, Dec 11 '13 at 14:58
@Avinash nope, you are passing mocked object. Which is looks to your controller exactly like a normal repository. This mock has setup for returning people without hitting any real database. So, if controller will ask repository for people list, it will get list which you created. And last line of test verifies that controller did ask repository for people. — Sergey Berezovskiy, Dec 11 '13 at 15:43
@Lazyberezovsky..thanks for explanation.I understood now. So, inorder to test database methods , i need to test model methods? — Sai Avinash, Dec 12 '13 at 06:09
I don't understand one thing - in the PersonController, how does it do what it's supposed to do? All it references is the interface, where does the repository come in on runtime? — Firkamon, Dec 31 '15 at 04:27
I think one thing is missing in your answer. You need to mention that the default constructor is also needed, or the controller doesn't fetch the actual context. — Firkamon, Jan 01 '16 at 12:51

score 1 · Answer 2 · answered Dec 11 '13 at 07:38

Well, I think you have some design issues here because proper testable code will never end up with database code inside an MVC Controller, you need to better implement separation of concerns so that each piece of code is unit testable, and this is achieve by using some design patterns such as Service Factory, Dependency Injection and Inversion of Control...Joel Abrahamsson explains it pretty well here in case your not aware of what I'm talking about. You can even check out Castle Windsor, a pretty good open source tool for this purpose (Inversion of Control)

You can still unit test you controllers with a bit of effort, implementing set-up functions and clean-up functions in your unit tests. But, I strongly recommend, if you have a bit of time, REFACTOR your code, you won't get very far with some many unnecessary dependencies.

Leo

@Leo..i am not directly calling database , rather i am calling using model methods. — Sai Avinash, Dec 11 '13 at 07:50

score 1 · Answer 3 · answered Dec 11 '13 at 07:58

1

A controller never should call the database directly (one - not the most important - reason for that is that this makes the controller almost impossible to test...). Instead, I strongly advise you to refactor your code to enable testability in the first place and also to have proper Separation of Concerns: Put all your data access code into Repositories which you then access in your controllers via interfaces. This way, you can easiliy mock them with Moq.

answered Dec 11 '13 at 07:58

Thomas Weller

11,631
3
26
34

@Thomas..Please find updated part of my question, This is how, i am currently doing. On Click of save , i am making an ajax call , that gets the updated model. then i am calling model.update() to save those values to database. How should i refactor above method.Please suggest. – Sai Avinash Dec 11 '13 at 08:17

How to Properly Test Controllers in ASP.net MVC that has database calls

3 Answers3

Linked