Is it possible to Mock a Database Transaction parameter?

Question

I'm trying to unit-test my implementation of an interface, and I'm having a little difficulty successfully mocking out a SqlTransaction parameter to one of the interface methods.

Here's what the interface, and method under test that I'm interested in look like..

public class MyInterface 
{ 
  void MyMethod(SqlTransaction SharedTransaction, DateTime EventTime);
} 

public class MyImplementation : MyInterface
{
  public void MyMethod(SqlTransaction SharedTransaction, DateTime EventTime)
  {
    DateTime dtLastEventTime = DateTime.MinValue;
    using(SqlCommand comm = SharedTransaction.Connection.CreateCommand())
    {
      comm.CommandText = SQL_GET_LAST_EVENTTIME_DEFINED_ELSEWHERE;
      comm.Parameters.AddWithValue("ParamName", 123);
      object oResult = comm.ExecuteScalar();
      dtLastEventTime = DateTime.Parse(oResult.ToString());
    }
    //Do something with dtLastEventTime
  }
}

I've been using Moq and various syntax approaches to mock out the Database objects and not having much luck.. (I had to do some conversion to the System.Data.Common objects in order to be able to get a little further.. DbTransaction, DbConnection, DbCommand etc).

What I'd like to know is primarily whether it's possible to mock out a transaction in this way, or whether I'm barking up the wrong tree here. Luckily I may be able to get the interface converted to use a generic DbTransaction parameter rather than the provider-specific SqlTransaction, but I'm not convinced that's why I'm having a hard time with the mocking.

Here's (and this could be completely wrong, so please correct me or comment if i'm approaching this incorretly) what I've got so far for mocking code...

   var mockParams = new Mock<DbParameterCollection>();
    mockParams.Setup(p => p.Add(new SqlParameter("ParamName", 123)));
    var mockCommand = new Mock<DbCommand>();
    mockCommand.Setup(p => p.Parameters).Returns(mockParams.Object);
    var mockConnection = new Mock<DbConnection>();
    mockConnection.Setup(con => con.CreateCommand()).Returns(mockCommand.Object);
    var mockTrans = new Mock<DbTransaction>();
    mockTrans.Setup(t => t.Connection).Returns(mockConnection.Object);

However, this seems to throw an ArgumentException on the mockCommand.Setup line.. (Invalid setup on a non-overridable member)

Does anyone have any ideas or suggestions on how I might be able to correctly unit-test this method with a mocked SqlTransaction parameter?

Answer 1

Peter, I am going to assume SqlServer backend.

First off, I would refactor MyMethod() by changing the inbound parameter type to IDbTransaction on MyMethod() so you can pass in your mock. Then I would abstract away the ExecuteScalar() call so I could easily check the SqlCommand parameters in my test.

Since Moq supports recursive mocking, in the test I would mock the call on sharedTransaction like this:

var mockTrx = new Mock<IDbTransaction>();
mockTrx.Setup(txn => txn.Connection.CreateCommand()).Returns(new SqlCommand());

Create one more mock for checking the command parameters in your method, and returning a value on the ExecuteScalar() call, and your done!

Refactored MyMethod():

public void MyMethod(IDbTransaction sharedTransaction, DateTime eventTime)
{
    DateTime lastEventTime;
    using (var cmd = (SqlCommand)sharedTransaction.Connection.CreateCommand())
    {
        cmd.CommandText = "somecmdtext";
        cmd.Parameters.AddWithValue("ParamName", 123);

        object oResult = dbUtility.ExecuteScalar(cmd);

        lastEventTime = DateTime.Parse(oResult.ToString());
    }

    //Do something with dtLastEventTime
    lastEventTime += TimeSpan.FromMinutes(1);
}

The test:

[Test]
public void ShouldCallCorrectProcWithParams()
{
    var mockTrx = new Mock<IDbTransaction>();
    mockTrx.Setup(txn => txn.Connection.CreateCommand()).Returns(new SqlCommand());

    var dbUtil = new Mock<IDbUtility>();
    dbUtil.Setup(exec => exec.ExecuteScalar(
                             It.Is<SqlCommand>(
                             cmd => cmd.CommandText == "somecmdtext"
                             && cmd.Parameters.Count == 1
                             && cmd.Parameters[0].ParameterName == "ParamName")))
                 .Returns(DateTime.Now);

     var session = new Session {dbUtility = dbUtil.Object};

     session.MyMethod(mockTrx.Object, DateTime.Now);

     mockTrx.VerifyAll();
     dbUtil.VerifyAll();

}

Personally, I like to test the parameters on my SqlCommand calls, so I attacked your problem from this angle in my example.