Tag: stored procedure

Database stored procedure calls are one of the trickiest things to unit test, and there are many different approaches that can be taken. My team has run the gamut: test DBs that rollback with each run, no testing for direct data access functions (!), virtual functions w/ partial mocks (see here).

The latest approach that I’ve been using is much more straightforward and feels like a more natural use of Rhino Mocks. Let’s look at some examples of how to test some common database stored procedure tasks. (Note that these examples assume use of the Microsoft Enterprise Library.)

Create a mockable Database

The primary challenge that I’ve found with testing database code is that Microsoft.Practices.EnterpriseLibrary.Data.Database isn’t mock-friendly. However, the other “pieces” such as DbCommand and DbCommandParameterCollection are very easy to work with. So, we can solve the Database problem by creating a simple wrapper (Important! Note that the methods have the virtual keyword, which will allow them to be overridden.):

public class DatabaseWrapper
{
    private Database _database;
    private Database Database
    {
        get { return _database = _database ?? DatabaseFactory.CreateDatabase(); }
        set { _database = value; }
    }

    public virtual DbCommand GetStoredProcCommand(string storedProcedureName)
    {
        return Database.GetStoredProcCommand(storedProcedureName);
    }

    public virtual void DiscoverParameters(DbCommand command)
    {
        Database.DiscoverParameters(command);
    }
}

Executing a stored procedure

Now that we are able to mock the database object, we can write some useful tests. Let’s say you want to execute a stored procedure named “MyStoredProcedure,” and you want to write a test to verify that your code handles an exception thrown when it’s executed. That’s very easy!

Here’s my class with the function I want to test:

public class MyDataAccess
{
    public DatabaseWrapper Database { get; set; }
    public Thingy GetThingy()
    {
        Thingy thingy = null;
        try
        {
            var dbCommand = Database.GetStoredProcCommand("MyStoredProcedure");
            Database.DiscoverParameters(dbCommand);
            var result = dbCommand.ExecuteNonQuery();
            // populate thingy
        }
        catch (Exception ex)
        {
            // handle exception
        }
        return thingy;
    }
}

And here’s my test that will throw an exception when the stored procedure is executed. I create my DatabaseWrapper as a PartialMock, allowing me to override its methods.

[TestMethod]
public void GetThingyHandlesException()
{
    // Arrange
    var target = new MyDataAccess();
    var mockDatabase = MockRepository.GeneratePartialMock<DatabaseWrapper>();
    target.Database = mockDatabase;

    // mock Database
    const string storedProc = "MyStoredProcedure";
    var mockDbCommand = MockRepository.GenerateMock<DbCommand>();
    mockDatabase.Expect(x => x.GetStoredProcCommand(storedProc))
        .Return(mockDbCommand);
    mockDatabase.Expect(x => x.DiscoverParameters(mockDbCommand));
    
    // mock DbCommand
    var ex = new Exception("Oh noes!");
    mockDbCommand.Expect(x => x.ExecuteNonQuery())
        .Throw(ex);

    // Act
    var actual = target.GetThingy();

    // Assert
    mockDatabase.VerifyAllExpectations();
    mockDbCommand.VerifyAllExpectations();
    Assert.IsNull(actual);
}

Setting input parameters

Need to set some input parameters? No problem!

dbCommand.Parameters["@id"].Value = id;

And, in your test, you add this:

var mockParams = MockRepository.GenerateMock<DbParameterCollection>();
var mockParam = MockRepository.GenerateMock<DbParameter>();

mockDbCommand.Expect(x => x.Parameters).Return(mockParams);

mockParams.Expect(x => x["@id"]).Return(mockParam);

const int id = 123;
mockParam.Expect(x => x.Value = id);

mockParams.VerifyAllExpectations();
mockParam.VerifyAllExpectations();

Reading output parameters

How about output parameters?

thingy.Value = dbCommand.Parameters["@Value"].Value as string;

Add the additional mocks and assertions:

var mockOutParam = MockRepository.GenerateMock<DbParameter>();

mockParams.Expect(x => x["@Value"]).Return(mockOutParam);

const string value = "MyValue";
mockOutParam.Expect(x => x.Value).Return(value);

mockParams.VerifyAllExpectations();
mockOutParam.VerifyAllExpectations();
Assert.AreEqual(value, actual.Value);

Working with sets of parameters

When you have more than a few parameters to work with, the unit test code can get quite lengthy. I like to keep it clean by extracting the duplicated logic into a separate function, like so:

var paramsToVerify = new List<DbParameter>();
mockParams.Expect(x => x["@whammyparammy"])
    .Return(MockParameter<int>(paramsToVerify));

My function allows you to specify and verify the type of each parameter, but you could easily modify it to expect a specific value.

private static DbParameter MockParameter<T>(List<DbParameter> paramsCollection)
{
    // set Expect with Arg<T>.Is.TypeOf to force the specific type
    var mockParam = MockRepository.GenerateMock<DbParameter>();
    mockParam.Expect(x => x.Value = Arg<T>.Is.TypeOf);

    if (paramsCollection != null)
        paramsCollection.Add(mockParam);

    return mockParam;
}

I keep the parameters in a list so I can verify them during my assertions.

paramsToVerify.ForEach(x => x.VerifyAllExpectations());