Mocking SQL output parameter

Question

I mocked a couple of methods of my Data Access layer, but in some methods the value of an SQL output param is set. How can I mock this ?

Method:

var wrappedParameters = new SqlParameter[3];
            wrappedParameters[0] = new SqlParameter("@username",username);
            wrappedParameters[1] = new SqlParameter("@password",password);
            wrappedParameters[2] = new SqlParameter("returnValue",SqlDbType.Int) { Direction =ParameterDirection.ReturnValue };

            dal.ExecuteUsingStoredProcedure("GetUser", wrappedParameters);

Mocking (I tried using "OutRef", but that doesn't work):

using (mocks.Record())
        {
            Expect.Call(dal.ExecuteUsingStoredProcedure("",> null)).Return(true).IgnoreArguments().OutRef(1);
        }

But that didnt work. When I excute the SP GetUser the param return value is set, but I have no idea how to mock this

Answer 1

I don't think you can mock it, because it is a normal SqlParameter (concrete class), and its not an out/ref parameter in .Net sense. What I would try is to simply set the value of the parameter in the mocked call to dal (I haven't checked the syntax, but I am sure you get the idea):

using (mocks.Record())
        {
            Expect.Call(dal.ExecuteUsingStoredProcedure("",null)).Return(true).IgnoreArguments().Do(x => wrappedParameters[2].Value = 1; true);
        }

Answer 2

I think you're going about this the wrong way. Your DAL interface should look like this:

/// <summary>
/// Models a service which holds the user information.
/// </summary>
public interface IUserRepository
{
   /// <summary>
   /// Gets the user with the given name, or <c>null</c> if no user with
   /// that name and password exists.
   /// </summary>
   /// <exception cref="IOException">
   /// An I/O problem occurred while accessing the repository.
   /// </exception>
   User TryGetUser(string name, string password);
}

The DAL abstraction now hides the fact that a stored procedure is used. In fact, the DAL might not even be a database: it could be a text file on disk, a webservice, a mock or anything else.

Mocking the DAL in order to test code which uses the DAL now becomes trivial. I've chosen the view model (aka presentation model)of a login screen as the system under test in these examples:

[Test]
public void Login_sets_user_and_goes_to_main_screen_when_TryGetUser_not_null()
{
    var userRepositoryStub = MockRepository.GenerateStub<IUserRepository>();
    var user = new User(...);
    userRepositoryStub.Stub(x=>x.GetUserByName("foo","bar")).Return(user);
    var sessionStub = MockRepository.GenerateStub<ISession>();
    var loginScreenViewModel = 
        new LoginScreenViewModel(sessionStub, userRepositoryStub);

    loginScreenViewModel.UserName = "foo";
    loginScreenViewModel.Password = "bar";
    loginScreenViewModel.Login();

    userRepositoryStub.AssertWasCalled(x=>x.TryGetUser("foo","bar"));
    sessionStub.AssertWasCalled(x=>x.ShowMainScreen());
    Assert.AreEqual(user, session.User);
}

.

[Test]
public void Login_shows_error_when_TryGetUser_returns_null()
{
    var userRepositoryStub = MockRepository.GenerateStub<IUserRepository>();
    var sessionStub = MockRepository.GenerateStub<ISession>();
    var loginScreenViewModel = 
        new LoginScreenViewModel(sessionStub, userRepositoryStub);

    loginScreenViewModel.UserName = "foo";
    loginScreenViewModel.Password = "bar";
    loginScreenViewModel.Login();

    Assert.AreEqual(loginScreenViewModel.Error, 
        "User 'foo' does not exist or password is incorrect"));
    userRepositoryStub.AssertWasCalled(x=>x.TryGetUser("foo","bar"));
    sessionStub.AssertWasNotCalled(x=>x.ShowMainScreen());
    Assert.IsNull(session.User);
}