how to unit test following Method, returnig value using "ref"

Question

I have similar methods, in the Business layer. New to unit testing and sometimes get confused. For an idea, can you suggest, what will be a better approach to test this method behaviour? I am using C# NUnit and Moq

public int? AddNewCatRetID(string categoryName)
 {
   int? categoryID = 0;
        Adapter.AddNewBlogCategoryReturnID(categoryName, ref categoryID);
        if (categoryID.HasValue)
            return categoryID;
        else return 0;
 }

where

Aadpter = Visual Studio 2008, Data Set Designer generated TableAdater

AddDeveloperCategoryReturnID() = Name of a function which utilises a Stored procedure in DB

It adds a new record, "Category" and returns its auto generated ID. If it is non zero, we take that result for further processing.

I know, should not be interested in talking to Database, below is the procedure, just to give an idea about what is going on in DB

PROCEDURE [dbo].[AddDeveloperCategoryReturnID]

@NAME NVARCHAR(MAX),
@CATEGORY_ID INT OUTPUT
AS
 BEGIN
 INSERT INTO [AllTimeGreatProgrammersDateBase].dbo.CATEGORIES
 (NAME )
 VALUES (@NAME )
 SET @CATEGORY_ID = SCOPE_IDENTITY()
 SELECT @CATEGORY_ID
END

some issues

• how to check the values returned using "ref" from the method
• what will you prefer to test and not to test? will be great if can list

Answer 1

There are several options depending on the characteristics of the Adapter type. If AddDeveloperCategoryReturnID is virtual or an interface member, you can most likely use a Test Double (either a hand-rolled one or a dynamic mock) to replace its behavior with some test-specific behavior.

If is a non-virtual method, you have two options:

• Refactor the method to make it more testable.
• Write an automated test that involves a database round-trip.

Automated tests that involve the database are orders of magnitudes more difficult to write and maintain than pure unit tests, so I would tend to shoot for the refactoring option.

On the other hand, if you think that the stored procedure represents a valuable code asset that should be protected by an automated test, you have no recourse but to write the database test.

Answer 2

I'd first convert Adapter.AddNewBlogCategoryReturnID(categoryName, ref categoryID) so that instead of returning a variable by reference, it simply returned the value.

Then, I would extract that into a virtual method.

To test AddNewCatRetID, I would extend the class to make a testable version, and override that virtual method to return an int? stored in a public variable.

That way, when you test to see what happens when you call AddNewCatRetID in a situation where there's a 0 in the database, you don't need to actually put a 0 in the database - you just set that parameter on the testable version of your class, and when your test calls AddNewCatRetID, instead if hitting the database, it just returns the value you set. Your test is guaranteed to be faster if you can avoid hitting the database, and since it's MS's generated adapter, there's not really a need to test it - you only care about what your method does with what the adapter returns.