Moq and SqlConnection?

Question

I'm writing unit tests for one of our products and have been used Moq to successfully mock connections to Entity Framework. However, I've come across the following method:

public static productValue findValues(string productName, string dbConnectionString)
{
    try
    {
        SqlConnection conn = new SqlConnection(dbConnectionString);
        conn.Open();
        //Do stuff 
    }
}

Which accesses our database inside that method using a passed connection string. Is it possible to setup a mock DB using Moq and create a connection string which points to the mocked DB? I've trying doing something along the lines of

var mockSqlConnnection = new Mock<SqlConnection>();

Though I'm unsure if this is the correct approach, as this would mock the connection itself rather than the DB.

Answer 1

I had a similar problem.

I introduced an SqlDataContext wrapper around the SqlConnection which inherited from and ISqlDataContext interface:

class SqlDataContext : ISqlDataContext {

    private readonly SqlConnection _connection;

    public SqlDataContext(string connectionString)
    {
        _connection = CreateConnection(connectionString);
    }

    public IDataReader ExecuteReader(string storedProcedureName, ICollection<SqlParameter> parameters)
    {
       // execute the command here using the _connection private field.
       // This is where your conn.Open() and "do stuff" happens.
    }

    private SqlConnection CreateConnection(string connectionString)
    {
        if (string.IsNullOrEmpty(connectionString))
        {
            throw new ArgumentNullException("connectionString");
        }

        return new SqlConnection(connectionString);
    }
}

interface ISqlDataContext
{
    IDataReader ExecuteReader(string storedProcedureName, ICollection<SqlParameter> parameters);
}

You can add overloads to the ISqlDataContext as you need.

What this means is that you can then mock the ISqlDataContext as requires using Moq or similar and return mock values.

Means you can then test your repository or anything else that hits the database through the SqlConnection without actually having to hit the database.

The other advantage is that you can inject ISqlContext with DI / IoC as needed.

Answer 2

late but why not with mstest:

[TestMethod]
MyTestWithInternSqlConnection()
{
   using (ShimsContext.Create())
   {
      // simulate a connection
      ShimSqlConnection.AllInstances.Open = connection => { };
      string commandText;

      // shim-Mock all called methods
      ShimSqlCommand.AllInstances.ExecuteReader = command =>
      {
         commandText = command.CommandText;
         return new ShimSqlDataReader();
      };

      int readCount = 0;
      ShimSqlDataReader.AllInstances.Read = reader => readCount == 0;
      ShimSqlDataReader.AllInstances.GetSqlStringInt32 = (reader, i) =>
      {
         readCount++;
         return "testServer";
      };

      var theReadedString = AMethodUnderTestThatReadsFromDatabaseAString();
      Assert.IsTrue(theReadedString == "testServer");
   }
}

you need to add a reference to System.Data and then add a Fake for it.

https://msdn.microsoft.com/en-us/library/hh549175.aspx Better is it, if you change the implementation and you can change the used read layer but ...

Answer 3

Have a look at the Repository Pattern, essentially you would mock the data in your consuming classes, rather than worrying about the implementation of talking to the database.

---

Essentially, you would have a repository

namespace ContosoUniversity.DAL
{
    public class StudentRepository : IStudentRepository, IDisposable
    {
        private SchoolContext context;

        public StudentRepository(SchoolContext context)
        {
            this.context = context;
        }

        public IEnumerable<Student> GetStudents()
        {
            return context.Students.ToList();
        }

        // ... more

Which is then consumed in other classes:

   public class StudentController : Controller
   {
      private IStudentRepository studentRepository;

      public StudentController(IStudentRepository studentRepository)
      {
        this.studentRepository = studentRepository;
      }

And used as:

  public ViewResult Index(string sortOrder, string currentFilter, string searchString, int? page)
  {
     var students = from s in studentRepository.GetStudents()
                    select s;

The full example is in the link at the top.

---

So then you would pass a mocked repository into your class:

// arrange
var mockedRepo = new Mock<IStudentRepository>();
// configure

// act
var controller = new StudentController(mockedRepo.Object);
// do stuff

// assert

Answer 4

How to Moq a SqlConnection: wrap at SqlConnection in a protected virtual method that has a return type of IDbConnection, so that it can be setup from its mocked parent class.

We're doing a similar thing as the accepted answer, but exclusively with Moq (and Moq.Protected).

In your data repository class break-out your connection into it's own function. In this example, it's called GetConnection. Make this a protected virtual method so we can mock it later. And add a return type of IDbConnection. This is the key.

  public Stuff FindValueMethod(string product)
  {
    ...
    try
    {
      using (var connection = GetConnection())
      {
          var result = await FindValue(connection, params);
          //Do stuff
      }
      
      return stuff;
    }
    ...
    
    protected virtual IDbConnection GetConnection()
    {
        return new SqlConnection(injectedSettings.connectionString)
    }

Later, in the data repository unit tests, do the dependency injection as you normally would, but wrap your dependencies with "Mock<>". In order to mock protected virtual classes, we need them to be extensible from the main DataRepository class. So we also have to mock the data repo. Be sure to add a mock database connection, too.

using Moq;
using Moq.Protected;
...
    private readonly Mock<DbConnection> _connection;
    private readonly Mock<ILogger<DataRepository>> _logger;
    private readonly Mock<Param> _param;
    private readonly Mock<DataRepository> _dataRepository;

    ...

    DataRepositoryTestsConstructor()
    {
        _connection = new Mock<DbConnection>();
        _logger = new Mock<ILogger<DataRepository>>();
        _param = new Mock<Param>();

        //use the Mock.Object for dependency injections into the Mock repo.
        _dataRepository = new Mock<DataRepository>(_logger.Object, _param.Object);
    }

    ...

    [Fact]
    public async Task FindValueMethod_Returns_ProductPrice()
    {
       //Arrange
       _dataRepository.Protected().Setup<IDbConnection>("GetConnection").Returns(_connection.Object);

       //Act
       var result = await _dataRepository.Object.FindValueMethod("rhubarb");

       //Assert
       Assert.NotNull(result);
    }

In the unit test above, once we have a mock data repository, then we can set up a protected method with a return type of IDbConnection. And return the Mock connection object. Now, the debugger will slide over the using (var connection = GetConnection()) line like butter.

It's a bit of work to mock the repo and the repo dependencies. And refactor your code to have a protected virtual database connection method. But that one-liner in the Arrange section is worth it.

Answer 5

If your connection string variable on your main project is calling the configuration manager you would just set the configuration manager setting on the unit test with what you have on the main project. Afterwards, reference System.Configuration and no need to create any config files.

using System.Configuration;
using Xunit;

namespace xUnitExample
{
    public class ExampleTests
    {
        [Fact]
        public void TesReturnSomething()
        {
            string value = "connection";
            ConfigurationManager.AppSettings["key"] = value;
            //Your Test
        }
    }
}