The data access layer code has couple of method for fetch and update. please find sample that has one method. I need to write unit test. The challenge I face is unable to mock SqlConnection and SqlCommand with isolation from database.

public class DataAccessLayer : IDataAccessLayer
{
    private readonly string _connectionString;
    private readonly ILogger<DataAccessLayer> _logger;
    private Settings _Settings { get; }

    TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;

    public DataAccessLayer(string connectionString, ILogger<DataAccessLayer> logger, IOptions<Settings> emailAlertSettings)
    {
        _connectionString = connectionString;
        _logger = logger;
        _Settings = emailAlertSettings.Value;
    }

    public async Task<DataTable> ExecuteNonQueryWithResultAsync(string storedProcedureName, SqlParameter[] parameters = null)
    {
        DataTable dataTable = new DataTable();
        try
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();

                using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    // Add input parameters, if any
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }

                    // Execute the stored procedure
                    using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SingleResult))
                    {
                        // Get schema information for the result set
                        dataTable.Load(reader);
                    }
                }
            }
        }
        catch (SqlException ex)
        {
            _logger.LogError(ex, "DataAccessLayer: Got exception when excecuting ExecuteNonQueryWithResultAsync method");
            // Handle SQL exceptions appropriately
            throw;  // Rethrow for further handling
        }

        return dataTable;

    }
}

Please find Xunit Test code

Writen the XUnit Test as below but getting Error in m.OpenAsync() and m.ExecuteReaderAsync IDbConnection’ does not contain a definition for ‘OpenAsync’ ‘IDbCommand’ does not contain a definition for ‘ExecuteReaderAsync’ and no accessible extension method ‘ExecuteReaderAsync’ accepting a first argument of type ‘IDbCommand’. Please advise valid unit test.

public class DataAccessLayerTests : DataAccessLayerFixture
{
    [Fact]
    public async Task ExecuteNonQueryWithResultAsync_ReturnsEmptyDataTable_Test()
    {
        // Arrange (Similar setup as previous test)
        var mockConnection = new Mock<IDbConnection>();
        var mockCommand = new Mock<IDbCommand>();
        var mockReader = new Mock<DbDataReader>();

        mockConnection.Setup(m => m.OpenAsync()).Returns(Task.CompletedTask);
        mockCommand.Setup(m => m.ExecuteReaderAsync(CommandBehavior.SingleResult))
                       .Returns(Task.FromResult(mockReader.Object));

        // Mock no data in the result set
        mockReader.Setup(r => r.HasRows).Returns(false);

        string storedProcedureName = "MyStoredProcedure";
        SqlParameter[] parameters = null; // No parameters in this example

        var dataAccessLayer = new DataAccessLayer(_connectionString, _mockLogger.Object, _mockOptions);

        // Act
        DataTable dataTable = await dataAccessLayer.ExecuteNonQueryWithResultAsync(storedProcedureName, parameters);

        // Assert
        Assert.NotNull(dataTable);
        Assert.Empty(dataTable.Columns); // No columns expected
        Assert.Empty(dataTable.Rows); // No rows expected
    }

    [Fact]
    public async Task ExecuteNonQueryWithResultAsync_ReturnsDataTable_Test()
    {
        // Arrange
        var mockConnection = new Mock<IDbConnection>();
        var mockCommand = new Mock<IDbCommand>();
        var mockReader = new Mock<DbDataReader>();

        mockConnection.Setup(m => m.OpenAsync()).Returns(Task.CompletedTask);
        mockCommand.Setup(m => m.ExecuteReaderAsync(CommandBehavior.SingleResult))
                   .Returns(Task.FromResult(mockReader.Object));

        // Mock data for the result set (replace with your expected data)
        mockReader.Setup(r => r.HasRows).Returns(true);
        mockReader.Setup(r => r.Read()).Returns(true); // Simulate one row
        mockReader.Setup(r => r["ColumnName1"]).Returns("Value1"); // Replace with actual column names and values

        string storedProcedureName = "MyStoredProcedure";
        SqlParameter[] parameters = null; // No parameters in this example

        var dataAccessLayer = new MyDataAccessLayer("connectionString");

        // Act
        DataTable dataTable = await dataAccessLayer.ExecuteNonQueryWithResultAsync(storedProcedureName, parameters);

        // Assert
        Assert.NotNull(dataTable);
        Assert.NotEmpty(dataTable.Columns);
        Assert.Single(dataTable.Rows); // Expect one row

        // You can add further assertions to verify specific column values based on your mock setup
    }