How to mock IDataReader to get more than 1 record in XUnit testing

Question

I developed an ASP.NET Core 8 MVC project and want to test all action methods using XUnit testing. But when I want to read data from the database using IDataReader, the code throws a NullReferenceException.

This is my XUnit testing code:

 public HomeControllerTests()
 {
     EShopUsers.Add(new EShopUser(1, "test123", "nalin", "Jagath"));
     EShopUsers.Add(new EShopUser(2, "[email protected]", "jagath", "tertert"));  
 }

 [Fact()]
 public async Task IndexTest()
 {
     Mock<IDataReader> reader = SetupAndGetMockDataReader(EShopUsers);
     Mock<IDbCommand> command = new Mock<IDbCommand>();

     Mock<IDbConnection> mockConnection = new Mock<IDbConnection>(MockBehavior.Strict);
     mockConnection.Setup(x => x.Open());
     mockConnection.Setup(x => x.Dispose());
     mockConnection.Setup(x => x.CreateCommand()).Returns(command.Object);

     command.Setup(x => x.ExecuteReader()).Returns(reader.Object);

     Repositories.EShopUserRepository eShopUserRepository = new Repositories.EShopUserRepository(() => mockConnection.Object);
     EShopUserService mockservice = new EShopUserService(eShopUserRepository);
     WebApp.Controllers.HomeController homeController = new(mockservice);

     var httpActionResult = await homeController.Index();
     var actionresult = httpActionResult as ViewResult;

     Assert.NotNull(actionresult.Model);
     Assert.Equal(((List<EShopUser>)actionresult.Model).Count,2);
 }

 private Mock<IDataReader> SetupAndGetMockDataReader(List<EShopUser> eShopUsers)
 {
     var mockreader = new Mock<IDataReader>();

     DataTable table = new DataTable();
     table.Columns.Add(new DataColumn("ID", typeof(Int32)));
     table.Columns.Add(new DataColumn("UserName", typeof(string)));
     table.Columns.Add(new DataColumn("FirstName", typeof(string)));
     table.Columns.Add(new DataColumn("LastName", typeof(string))); 

     foreach (EShopUser item in eShopUsers)
     {
         DataRow row = table.NewRow();
         row["ID"] = item.ID;
         row["UserName"] = item.UserName;
         row["FirstName"] = item.FirstName;
         row["LastName"] = item.LastName; 
         table.Rows.Add(row);
     } 

     for (int i = 0; i < table.Columns.Count; i++)
     {
         var name = table.Columns[i].ColumnName;
         mockreader.Setup(r => r.GetOrdinal(It.Is<string>(n => n == name))).Returns(i);
     }

     var rowIndex = 0;

     mockreader.Setup(r => r.Read()).Returns(() => (rowIndex < table.Rows.Count)).Callback(() => SetupNextRow(mockreader, table, ref rowIndex));

     return mockreader;
 }

 private static void SetupNextRow(Mock<IDataReader> mockDataReader, DataTable table, ref int rowIndex)
 {
     if (rowIndex >= table.Rows.Count) return;
     var row = table.Rows[rowIndex];

     for (int columnIndex = 0; columnIndex < row.ItemArray.Length; columnIndex++)
     {
         DataColumn column = (DataColumn)table.Columns[columnIndex];

         switch (column.ColumnName.ToString())
         {
             case "ID":
                 mockDataReader.Setup(r => r.GetInt32(It.Is<Int32>(x => x == columnIndex))).Returns((Int32)row[columnIndex]);
                 break;

             case "UserName":
                 mockDataReader.Setup(r => r.GetString(It.Is<Int32>(x => x == columnIndex))).Returns((string)row[columnIndex]);
                 break;

             case "FirstName":
                 mockDataReader.Setup(r => r.GetString(It.Is<Int32>(x => x == columnIndex))).Returns((string)row[columnIndex]);
                 break;

             case "LastName":
                 mockDataReader.Setup(r => r.GetString(It.Is<Int32>(x => x == columnIndex))).Returns((string)row[columnIndex]);
                 break; 

             default:
                 break;
         }
     }

     rowIndex++;
 }

HomeController.Index() method is this:

 public async Task<IActionResult> Index()
 {
     List<EShopUser> users = new();
     var listPaginated = (List<EShopUser>)await _EShopUserService.GetAllUsersAsync();

     foreach (EShopUser item in listPaginated)
     {
         users.Add(new EShopUser(item.ID, item.UserName, item.FirstName, item.LastName));
     }

     return View(users);
 }

EShopUserService.GetAllUsersAsync() is shown here:

 public async Task<object> GetAllUsersAsync()
 {
    return await _EShopUserRepository.GetAllUsersAsync();
 }

And this is the EShopUserRepository.GetAllUsersAsync() method:

private readonly IDbConnection _connection = null;

public EShopUserRepository(Func<IDbConnection> dbConnection)
{
     _connection = dbConnection.Invoke();
}

public async Task<object> GetAllUsersAsync()
{
       List<EShopUser> users = new List<EShopUser>();

       var t = await Task.Run(() =>
       {
            using (_connection)
            {
                _connection.Open();
                IDbCommand cmd = _connection.CreateCommand();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "GetAllUser";
                IDataReader userReader = cmd.ExecuteReader();
                while (userReader.Read())
                {                        
                    users.Add(new EShopUser((int)userReader["ID"], (string)userReader["UserName"], (string)userReader["FirstName"],(string)userReader["LastName"]));
                }
            }
            return Task.FromResult<object>(users);
        });

        return t;
}

When I debug the GetAllUsersAsync method, the userReader object is null, so it throws a NullReferenceException. What is wrong?

It looks like _connection is a real object, not the mock you're trying to create. — Paul Palmpje
– Paul Palmpje, Commented Jun 5 at 12:38
Why are you trying to mock them at all? The whole point of a repository class is to hide how you handle data. It's the repository that should be mocked, preferably through an interface, not ADO.NET. Otherwise you'll end up testing your mocking code, not your application — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 10 at 6:49
In any case the repository code has a lot of problems. The base ADO.NET types are the abstract DbCommand, DbConnection, DbDataReader types, not the interfaces. ADO.NET already has a factory model that provides connections by provider name. Faking asynchronous operations is a very bad idea too. ExecuteReaderAsync is already available. The method should return IEnumerable<EshopUser> — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 10 at 6:58

Nitin S · Accepted Answer · 2025-06-10 06:08:18Z

1

You can use Smart.Mock library https://github.com/usausa/Smart-Net-Mock-Data

// ExecuteReader
using (var con = new MockDbConnection())
{
    var columns = new[]
    {
        new MockColumn(typeof(int), "Id"),
        new MockColumn(typeof(string), "Name")
    };
    var rows = new List<object[]>
    {
        new object[] { 1, "Employee1" },
        new object[] { 2, "Employee2" },
        new object[] { 3, "Employee3" }
    };
    con.SetupCommand(cmd => cmd.SetupResult(new MockDataReader(columns, rows)));

    var list = (await con.QueryAsync<Employee>("SELECT COUNT(*) FROM Employee")).ToList();

    Assert.Equal(3, list.Count);
}

answered Jun 10 at 6:08

Nitin S

7,71310 gold badges64 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Panagiotis Kanavos Jun 10 at 6:53

There's a reason such libraries aren't popular - they don't help at all while introducing their own quirks. You can't just mock a database's syntax and behavior. The Repository pattern and ORMs exist to hide the existence of the database and allow testing by mocking the repository, not the connection. If you do care about SQL, SQLite in in-memory mode or a test database are far better. As for MockDataReader, FastMember's ObjectReader creates an IDbDataReader wrapper over any IEnumerable<>.

Collectives™ on Stack Overflow

How to mock IDataReader to get more than 1 record in XUnit testing

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related