Unit testing calling of stored procedures that return a value with Simple.Data

Question

I have the following stored procedure:

[dbo].[MyStoredProcedure] 
     @field1 int, @field2 int, @field3 nvarchar(5), @returnValue int output

I call it using Simple.Data and get the return value as follows:

 var result = db.MyStoredProcedure(intParam1, intParam2, stringParam3, new int());
 int returnValue = result.OutputValues["returnValue"];

I am trying to test the code above to ensure it gets returnValue from the OutputValues dictionary and handles the result of that correctly. To do so, I need to fake the stored procedure MyStoredProcedure.

I can see a good example of how to test stored procedures that add rows to the database in the Simple.Data documentation here: Simple.Data documentation, but I cannot see how to return values from this function.

How do I simulate a stored procedure that takes a number of arguments (in my case int, int, nvarchar, int) and add my fake returnValue key (with corresponding int value) to the OutputValues dictionary?

Answer 1

I generally try to avoid "unit testing" code that has permanent side-effects, but you can do it by setting up a transaction scope in your unit test:

    TransactionScope _trans;

    [TestInitialize()]
    public void Init()
    {
        _trans = new TransactionScope();  // start a new transaction
        base.InitializeTest();
    }

    [TestCleanup()]
    public void Cleanup()
    {
        base.CleanupTest();
        _trans.Dispose();  // rollback the transaction
    }

    [TestMethod()]
    public void TransactedTest()
    {

        // set up test parameters

        var result = db.MyStoredProcedure(intParam1, intParam2, stringParam3, new int());
        int returnValue = result.OutputValues["returnValue"];

        // validate return value

    }