Mocking the database layer, Oracle refCursor, should I refactor this?

Question

I apologize for the verbose posting. I don't like seeing those myself, but my question is about structure and I think all the pieces are needed for asking it. The interesting part is at the bottom, though, so feel free to scroll down to the question.

Here is a Controller. I am injecting a context and a command factory. The controller returns a list of objects that are read from an Oracle database.

public class aController : ControllerBase
{
    protected readonly IDB db;

    public aController(IContext context, ICommandFactory factory)
    {
        db = IDB.dbFactory(context, factory);
    }

    [HttpGet]
    public ActionResult<s> GetS()
    {
        return Ok(db.DbGetS());
    }
}

What is currently not injected is the persistence class. There will be a manageable number of stored procedures that are mapped to a model, all hand-coded to a spec. This interface has a factory to construct an implementation (so that I can mock it should the need arise), and our data retrieval method.

public interface IDB
{
    public static IDB dbFactory(
        IContext context,
        ICommandFactory factory)
    { 
        return new DB(context, factory); 
    }

    public S DbGetS();
}

This class implements the interface. It has a constructor that passes the injected items to the base constructor and otherwise does the Oracle interaction by calling generic access methods in the base class.

public class DB: dbBase, IDB
{
    public DB(
        IContext context,
        ICommandFactory factory)
            : base(context, factory)
    { }

    public S DbGetS()
    {
        S s = new S();
        IEnumerable<S> ss = GetData("proc-name");
        return ss.SingleOrDefault();
    }
}

Then there is a base class to all the model classes that uses generics and does the heavy lifting. This is very much simplified.

public abstract class dbBase
{
    private readonly IContext _context;
    private readonly ICommandFactory _commandFactory;

    protected delegate IEnumerable<T> ParseResult<T>(IDbCommand cmd);

    protected dbBase(IContext context, ICommandFactory factory)
    {
        _context = context;
        _commandFactory = factory;
    }

    protected IEnumerable<T> GetData<T>(string sproc)
    {
        IEnumerable<T> results = null;
        var cmd = this._commandFactory.GetDbCommand(sproc, this._context);
        
        // boilerplate code omitted that sets up the command and executes the query

        results = parseResult<T>(cmd);  // this method will read from the refCursor      
        return results;
    }

    private IEnumerable<T> parseResult<T>(IDbCommand cmd) where T : ModelBase, new()
    {
       // This cast is the problem:
        OracleRefCursor rc = (OracleRefCursor)cmd.Parameters["aCursor"];
        using (OracleDataReader reader = rc.GetDataReader())
        {
            while (reader.Read())
            {
              // code omitted that reads the data and returns it

And here is the Unit Test that should test the Controller:

 public void S_ReturnsObject()
    {
        // Arrange
        var mockFactory = new Mock<ICommandFactory>();
        var mockContext = new Mock<IContext>();
        var mockCommand = new Mock<IDbCommand>();
        var mockCommandParameters = new Mock<IDataParameterCollection>();

        mockCommandParameters.SetupGet(p => p[It.IsAny<string>()]).Returns(mockParameter.Object);

        // Set up the command and parameters
        mockCommand.SetupGet(x => x.Parameters)
            .Returns(mockCommandParameters.Object);

        mockCommand.Setup(x => x.ExecuteNonQuery()).Verifiable();

        // Set up the command factory
        mockFactory.Setup(x => x.GetDbCommand(
                It.IsAny<string>(),
                mockContext.Object))
            .Returns(mockCommand.Object)
            .Verifiable();

        var controller = new aController(mockContext.Object, mockFactory.Object);

        // Act
        var result = controller.GetS();
     
        // omitted verification

All stored procedures have refCursor output parameters that contain the results. The only way to obtain an OracleDataReader for this is to cast the query output parameter to OracleRefCursor. Mocking the reader is therefore not possible, because even though I can get a mock parameter, the test will fail with a cast exception in the ParseResult method. Unless I am missing something.

I fear that I need to cut the Oracle API interactions out, even though it would be nice to at least enter parseResults() as part of the test.

I could inject IDB and replace DbGetS() with a mock version, but then there will be not much code coverage by my test and I won't be able to mock any database connection issues and the like. Also, there will be about a dozen IDB level interfaces that would all have to be injected.

How should I restructure this to be able write meaningful tests?

---

(Disclaimer: The code snippets that I pasted here are for illustration purposes and were heavily edited. The results were not tested and will not compile or run.)

Answer 1

I didn't really expect an answer, or at least, I am fine with not receiving one. As it often happens when I ask a question on SO, just laying out the problem in a way that others can understand it makes it clear enough for me to see through.

In this instance, the essence of the question boiled down to there not being a way around using GetDataReader() on the RefCursor. In other words, if you have Oracle Stored Procedures with an output cursor (i.e. not a SELECT result set), you cannot mock the database interaction unless you manage to write your own RefCursor and OracleDataReader. If you think this is wrong, please elaborate. OracleCommand, OracleParameter, and operations on the command (ExecuteNonQuery) can be substituted with System.Data equivalencies that can be mocked.

So what did I do? I reverted the substitution of the Oracle.ManageDataAccess types with System.Data stuff (because the former are less verbose) and injected IDB instead.

This is the resulting unit test:

        // Arrange
        var mockDbS = new Mock<IDB>();
        Model.S expected = new Model.S() { var1 = 1, var2 = 2, var3 = 3 };
        mockDbS.Setup(d => d.DbGetS().Returns(expected);
        var controller = new aController(mockDbS.Object);

        // Act
        ActionResult<Model.Summary> actionResult = controller.GetS();
        Model.S actual = ((ObjectResult)actionResult.Result).Value as Model.S;

        // Assert
        mockDbS.Verify();
        Assert.Equal(200, ((ObjectResult)actionResult.Result).StatusCode);
        Assert.Equal(expected, actual);

This does not give me the coverage that I wanted, but it is a basic test of the controller actions.