Mock Testing the SqlDataReader results

Tight coupling to implementation details/concerns make this difficult to unit test in isolation.

Refactor to depend on abstractions that can be mocked.

For example

class EmployeesRepository : IEmployeeRepository {
    private readonly IDbConnectionFactory dbConnectionFactory;
    //...

    public EmployeesRepository(IDbConnectionFactory dbConnectionFactory /*,...*/) {
        this.dbConnectionFactory = dbConnectionFactory;

        //...
    }

    public IEnumerable<Employee> GetAllEmployees() {
        List<Employee> list = new List<Employee>();

        try {
            string connectionString = _secureConfig.Value.MyDbSetting;
            string sql = "select id, firstname, lastname, entrydate, email from empoyees";

            using (IDbConnection connection = dbConnectionFactory.CreateConnection(connectionString)) {
                using (IDbCommand command = connection.CreateCommand()) {
                    command.CommandText = sql;

                    connection.Open();

                    using (IDataReader reader = command.ExecuteReader()) {
                        while (reader.Read()) {
                            list.Add(new Employee {
                                Id = reader.GetString(0),
                                FirstName = reader.GetString(1),
                                LastName = reader.GetString(2),
                                EntryDate = reader.GetDateTime(3),
                                Email = (reader.IsDBNull(4) ? null : reader.GetString(4))
                            });
                        }
                    }
                }
            }
        } catch (Exception ex) {
            _log.LogError(ex, "An error is caught when getting all employees");
        }
        return list;
    }
}

Where IDbConnectionFactory is defined as

public interface IDbConnectionFactory {
    ///<summary>
    ///  Creates a connection based on the given database name or connection string.
    ///</summary>
    IDbConnection CreateConnection(string nameOrConnectionString);
}

The runtime implementation that will be registered with your DI container would look like this

class SqlConnectionFactory : IDbConnectionFactory {
    public IDbConnection CreateConnection(string nameOrConnectionString) {
        return new SqlConnection(nameOrConnectionString);
    }
}


//...

services.AddSingleton<IDbConnectionFactory, SqlConnectionFactory>();

//...

For testing the above, the abstractions can be mocked as needed

public void ShouldGetAllEmployees() {
    //Arrange

    var readerMock = new Mock<IDataReader>();
    //...setup reader members as needed
    
    var commandMock = new Mock<IDbCommand>();
    commandMock.Setup(m => m.ExecuteReader())
        .Returns(readerMock.Object);

    var connectionMock = new Mock<IDbConnection>();
    connectionMock.Setup(m => m.CreateCommand())
        .Returns(commandMock.Object);
    //..Setup...

    var connectionFactoryMock = new Mock<IDbConnectionFactory>();
    connectionFactoryMock
        .Setup(m => m.CreateConnection(It.IsAny<string>()))
        .Returns(connectionMock.Object);

    EmployeesRepository sut = new EmployeesRepository(connectionFactoryMock.Object);

    //Act
    IEnumerable<Employee> actual = sut.GetAllEmployees();

    //Assert
    //...assert desired behavior
}