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
}

Answer 1

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();