Unit Test Your Database Classes

• Download source (DbCreation) - 1.3 KB
• Download source (MoqSQlAccess) - 119.8 KB

Introduction

In this article, we explain how to create a database access class that is unit test friendly and is done with plain ADO.NET classes without the necessity of more complex framework. The tests will be implemented using XUnit and Moq. The examples are implemented using C# and NET 5, but can be implemented also in other versions of NET, for example NET Core 3.1

Background

Traditionally, developers that use ADO.NET, create the Data class by direct implementation on it the objects designed to manage the database access, and normally we use the concrete implementation of the connection object (for example, SqlConnection) to implement the data access class.

This form does not allow to create a mock of the class that depends on the existence of an interface. The interface allow us to create a fake object to implement the mock.

I found that many developers think that is not possible do a mock of the DB class because the lack of the interface in the concrete implementation of the ADO.NET class (such as SQLCommand, or SQLConnection), The fact is that there exists a generic interface that allow us to do this.

https://docs.microsoft.com/en-us/dotnet/api/system.data.idbconnection?view=netcore-3.1

IDbConnection allow us to use it to inject it in the class instead a concrete implementation of the connection, or create it with new inside the code.

In our code, because, actually using the same object injected in all instances of the database access class may create some concurrence problem, we use a delegate to pass a function to the db class instead directly an instance of an object derived from IDbConnection. This ensures that the object used in our instantiation of the class is unique for the class, avoiding concurrence problems.

Implementing the Class as Unit Testable

How we implement it, well for use in the real program to access the database we need to follow three simple steps.

First Step

Configure the function to be injected to the object in the startup.cs class.

public void ConfigureServices(IServiceCollection services)
{
    // Rest of code .....
    string connectionStr = Configuration.GetConnectionString("Wheater");
    services.AddScoped<IMoqReadyService, MoqReadyService>(  
     x => new MoqReadyService(() => new SqlConnection(connectionStr)));
}

Observe in this snip of code that we get the connection string from configuration, and the factory function is coded to create a new SqlConnection object when it will be invoked.

Second Step

Create the Data access class and inject the function as parameter in the constructor.

/// <summary>
/// Factory for IDb Connection
/// </summary>
private Func<IDbConnection> Factory { get; }

/// <summary>
/// Class Constructor
/// </summary>
/// <param name="factory">The IdbConnection compatible factory function</param>
public MoqReadyService(Func<IDbConnection> factory)
{
    this.Factory = factory;
}

As you see, we inject the function inside the class in the constructor and store it in a private variable.

Third Step

Invoke the factory and create the rest of the needed objects.

As the last step, invoke the factory inside out method to create our instance the SqlConnection (as is configured in this example) and create the rest of the ADO.NET objects:

public async Task<List<WeatherForecast>> GetForecastMoqableAsync(DateTime startDate)
{
   var t = await Task.Run(() =>
   {
       // This invoke the factory and create the SqlCommand object
       using IDbConnection connection = this.Factory.Invoke();
       
       using IDbCommand command = connection.CreateCommand();
       command.CommandType = CommandType.Text;
       command.CommandText = "SELECT * FROM WeatherInfo WHERE Date = @date";
       command.Parameters.Clear();
       command.Parameters.Add(new SqlParameter("@date", SqlDbType.DateTime) 
      { Value = startDate });
  //.... Rest of the code....  

That can be different depending on what operation we use in the method, but the creation of the IDbConnection implementation is the same using the instruction:

using IDbConnection connection = this.Factory.Invoke();

In resume to create our class testable, the operations are the following:

Implement the Test Code

Implementing the test code is pretty straightforward now. We only need to change the factory implementation for a Mock object and replace and configure all the objects based in this initial mock.

The main step in the code of the XUnit is the creation of the IdbConnection mock object as is shown in the next code segment:

public class MoqSqlTest
{
   readonly MoqReadyService service;
   readonly Mock<IDbConnection> moqConnection;
   public MoqSqlTest()
   {
       this.moqConnection = new Mock<IDbConnection>(MockBehavior.Strict);
       moqConnection.Setup(x => x.Open());
       moqConnection.Setup(x => x.Dispose());
       this.service = new MoqReadyService(() => moqConnection.Object);
   }
   // Continue the code.....

In this code segment, you can observe how the moq object is created based in the IDbConnection and part of the configuration of the test. After creating this base object, the creation of the rest of the test depends on what type of data access function you want to test. Let's see this in the following section.

Using the Code

The code presents two examples of test classes that test methods that read and insert information from the database.

Testing a read operation with Data Reader.

[Trait("DataReader", "1")]
[Fact(DisplayName = "DataReader Moq Set Strict Behaviour to Command Async")]
public async Task MoqExecuteReaderFromDatabaseAsync()
{
      // Define the data reader, that return only one record.
      var moqDataReader = new Mock<IDataReader>();
      moqDataReader.SetupSequence(x => x.Read())
          .Returns(true) // First call return a record: true
          .Returns(false); // Second call finish

      // Record to be returned
      moqDataReader.SetupGet<object>(x => x["Date"]).Returns(DateTime.Now);
      moqDataReader.SetupGet<object>(x => x["Summary"]).Returns("Sunny with Moq");
      moqDataReader.SetupGet<object>(x => x["Temperature"]).Returns(32);

      // Define the command to be mock and use the data reader
      var commandMock = new Mock<IDbCommand>();

      // Because the SQL to mock has parameter we need to mock the parameter
      commandMock.Setup(m => m.Parameters.Add
                       (It.IsAny<IDbDataParameter>())).Verifiable();
      commandMock.Setup(m => m.ExecuteReader())
      .Returns(moqDataReader.Object);

      // Now the mock if IDbConnection configure the command to be used
      this.moqConnection.Setup(m => m.CreateCommand()).Returns(commandMock.Object);

      // And we are ready to do the call.
      List<WeatherForecast> result = 
           await this.service.GetForecastMoqableAsync(DateTime.Now);
      Assert.Single(result);
      commandMock.Verify(x => x.Parameters.Add(It.IsAny<IDbDataParameter>()), 
                         Times.Exactly(1));
 }

Testing an Insert operation using Mock behaviour Strict.

[Trait("ExecuteNonQuery", "1")]
[Fact(DisplayName = "Moq Set Strict Behaviour to Command Async")]
public async Task MoqExecuteNonQueryStrictBehaviourforCommandAsync()
{
     WeatherForecast whetherForecast = new()
     {
          TemperatureC = 25,
          Date = DateTime.Now,
          Summary = "Time for today"
      };

       // Configure the mock of the command to be used
       var commandMock = new Mock<IDbCommand>(MockBehavior.Strict);
       commandMock.Setup(c => c.Dispose());
       commandMock.Setup(c => c.ExecuteNonQuery()).Returns(1);
            
       // Use sequence when several parameters are needed
       commandMock.SetupSequence(m => m.Parameters.Add(It.IsAny<IDbDataParameter>()));
            
       // You need to set this if use strict behaviour. 
       // Depend of your necessity for test
       commandMock.Setup(m => m.Parameters.Clear()).Verifiable();
       commandMock.SetupProperty<CommandType>(c => c.CommandType);
        commandMock.SetupProperty<string>(c => c.CommandText);
            
       // Setup the IdbConnection Mock with the mocked command
       this.moqConnection.Setup(m => m.CreateCommand()).Returns(commandMock.Object);

       // SUT
       var result = await service.SetForecastAsync(whetherForecast);
       Assert.Equal(1, result);
       commandMock.Verify(x => x.Parameters.Add
                   (It.IsAny<IDbDataParameter>()), Times.Exactly(3));
}

Observe that in this case, we are creating the mock objects using strict behaviour, we can also create it using Loose behaviour, the use of the behaviour depends on what you want to test in your class.

The loose behaviour allows you to create more short tests, but you can lose information about what you want to test in the class under test.

Here is an example of a loose behaviour using the same class as the last code example:

[Trait("ExecuteNonQuery", "2")]
[Fact(DisplayName = "Moq Set Loose Behaviour to Command Async")]
public async Task MoqExecuteNonQuerySetLooseBehaviourToCommandAsync()
{
      WeatherForecast whetherForecast = new()
      {
           TemperatureC = 25,
           Date = DateTime.Now,
           Summary = "Time for today"
      };

      // Configure the mock of the command to be used
      var commandMock = new Mock<IDbCommand>(MockBehavior.Loose);
       commandMock.Setup(c => c.ExecuteNonQuery()).Returns(1);

      // Use sequence when several parameters are needed
      commandMock.SetupSequence(m => m.Parameters.Add(It.IsAny<IDbDataParameter>()));

      // Setup the IdbConnection Mock with the mocked command
      this.moqConnection.Setup(m => m.CreateCommand()).Returns(commandMock.Object);

      // SUT
      var result = await service.SetForecastAsync(whetherForecast);
      Assert.Equal(1, result);
      commandMock.Verify(x => x.Parameters.Add
                        (It.IsAny<IDbDataParameter>()), Times.Exactly(3));
}

Points of Interest

I found in some developers the tendency to use in simple operations with the database, very massive frameworks as Entity Frameworks, and the justifications are the following:

• ADO.NET class cannot be unit test
• ADO.NET cannot make Asynchronous operation

The simple example code that you can download allows you to make asynchronous the call to the DB and also do unit tests over the class without the overhead of the EF.

I am not opposed to EF, it is very useful in large and complex interfaces with the DB, but I prefer the simple ADO.NET operation when all the interaction with the DB are a few requests or insert operations.

I normally working with Microservices, and that is the situation that I deal with day by day with the Db.

You can also see a version in the video of this article at:

History

• 16^th May, 2022: First version