How to fake SqlDataAdapter, SqlConnection and SqlCommand in unit tests

Question

I'm trying to figure out how I can run unit tests on a class I created to execute queries on a database but I keep running around in circles trying to figure out how to fake all the dependencies. My class implements IDisposable which closes the SqlConnection if open. A typical method (I have several overloaded) for selecting data looks like this:

public DataSet SelectData(string selectCommand)
    {
        if (!string.IsNullOrEmpty(selectCommand))
        {
            DataSet ds = new DataSet();
            ds.Locale = CultureInfo.InvariantCulture;
            SqlDataAdapter adapter = new SqlDataAdapter(selectCommand, Connection);
            adapter.Fill(ds);
            return ds;
        }
        throw new ArgumentException("SelectCommand was null or empty", "selectCommand");
    }

Note that the Connection parameter in the SqlCommand constructor is a property that returns the SqlConnection for this instance of my class. Obviously I need to somehow fake the SqlDataAdapter but that also means I have to fake the SqlCommand and SqlConnection used. All classes are sealed so I can't simply create a fake object that inherits from these classes. Creating a database sort of defeats the purpose of dependency injection so I'd like to avoid that. Does anyone have a suggestion on how to test this method?

Answer 1

As a general rule, to mock sealed classes (1) you need a mocking framework that can do it or (2) you need to write (unsealed) wrappers around the sealed class and use/mock those. TypeMock can mock sealed classes, but it costs money. But, beware, the ability to mock sealed classes and other typically non-mockable items, can keep you from having to refactor your code to a better design (assuming you agree that testable code is better code). Wrappers or adapters are relatively easy to write, but they are themselves not testable for precisely the same reason you write them. Because of their simplicity, though, you can often reason that they are correct by inspection.

As an aside, you might want to look at more modern data access mechanisms, using an object-relational mapper (ORM), for example. Entity framework, LINQ-to-SQL, nHibernate, Subsonic... all are better choices than writing your own data access layer at a low level in my opinion.

Answer 2

I think we have all the interfaces (IDbConnection, IDbTransaction, IDbCommand and IDbDataAdapter) to mock everything method we used, by using NSubstitute and Dependency Injection.

//using init class to inject IDbDataAdapter
IDbDataAdapter adapter = init.DbAdapter("command");
adapter.Connection = connection;

As for SqlConnection and SqlCommand

using (IDbConnection connection = init.DbConnection("connection"))
{
  using (IDbTransaction transaction = connection.BeginTransaction())
  {
    using (IDbCommand command = init.DbCommand("sproc"))
    {  
       command.Transaction = transaction;
       command.Connection = connection;
       ...
    }
  }
}

Unit test will look something like this

//arrange
var connection = Substitute.For<IDbConnection>();
var command = Substitute.For<IDbCommand>();
var transaction = Substitute.For<IDbTransaction>();
//this is the init class used before
var init = Substitute.For<ISqlInitializer>();
connection.Open();
connection.BeginTransaction(Arg.Any<IsolationLevel>()).Returns(transaction);
init.DbConnection(Arg.Any<string>()).Returns(connection);
init.DbCommand(Arg.Any<string>()).Returns(command);
var client = new SqlClient(init);

//act
var result = await client.CommandMultipleAsync(new SqlConfiguration(FakeConnection, new List<string> { "testSproc1", "testSproc2" }));

//assert
Assert.AreEqual(0, result);
command.Received(2).ExecuteNonQuery();
transaction.Received(1).Commit();

Please check the proof of concept project on GitHub for detailed usage and also any suggestion or feedback is welcome :p https://github.com/TianyuanC/dals/blob/master/DALs.Sql/SqlClient.cs