CODEWITHSUNDEEP
unit-testingmockingrhino-mocks
Ivo
Ivo

Reputation: 3436

Mocking SQL output parameter

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

Upvotes: 6

Views: 3170

Answers (3)

DrGriff
DrGriff

Reputation: 4916

I stubbed this using the "WhenCalled" method.

In my code (to be tested) I first create the connection and command and add three parameters, the third of which is my output parameter. I then call "ExecuteCommand" on this....I won't go through the code for this as I think it's fairly standard (ExecuteCommand takes the command object as its parameter).

In my test, I create a stub for my sql data service and program it so that this sets the value of the parameter:

var sqlService = MockRepository.GenerateStub<ISqlDataService>();
sqlService.Stub(s => s.ExecuteCommand(null))
    .IgnoreArguments()
    .WhenCalled(s => ((SqlCommand)s.Arguments[0]).Parameters[2].Value = expectedValue)
    .Return(0);

The answer's probably a bit late for your project, but hope this helps someone...

Griff

Upvotes: 0

Wim Coenen
Wim Coenen

Reputation: 66783

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

Upvotes: 2

Grzenio
Grzenio

Reputation: 36679

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

Upvotes: 0

Related Questions