0

Unit test your database classes

 1 year ago
source link: https://www.codeproject.com/Articles/5332010/Unit-test-your-database-classes
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Introduction

Download DbCreation.zip

Download MoqSQlAccess.zip

In this article we explain how to create a data base access class that it be unit test friedly 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 developer that use ADO.NET, create the Data class by direct implementation on it the objects designed to manager the database access, and  normally we use the concrete implementation of the connection object (for example SqlConnection) to implement the data access class. 

Image 1

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

I found that many developer think that is not possible do a mock of the DB class bcause the lack of the interface in the concrete implementation of the ADO.NET class (such as SQLCommand, or SQLConnection),  The fact is that 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 a instance of a object derivated from IDbConnection. This ensure us that the object used in our instantiation of the class be unique for the class, avoiding concurrence problems.

Image 2

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 3 simple step.

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

Copy Code
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 will be invoke.

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

Copy Code
/// <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 last step is 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

Copy Code
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 what operation are we using in the method, but the creation of the IDbConnection implementation is the same using the instruction:

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

In resume to create our class testeable the operations are the following:

Image 3

Implement the Test Code:

Implement the test code is pretty straighforward now. We only need to change the factory implementation for a a Mock object and replace and configured  all the object based in this initial mock.

Image 4

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

Copy Code
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 create based in the IDbConnection and part of the configuration of the test. after create this base object. the creation of the rest of the test depend for what type of data access function you wants to test. Let see this in the following section. 

Using the code

Download DbCreation.zip

Download MoqSQlAccess.zip

The code present 2 examples of test class that test method that read and insert information from the database. 

Testing a read operation with Data Reader.

Shrink ▲   Copy Code
[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 a Insert operation using Mock behaviour Strict.

Shrink ▲   Copy Code
[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 necessety 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 behavoiur depend about what do you want to test in your class. 

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

Here a example a loose behaviour using the same class that the last code example:

Shrink ▲   Copy Code
[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 tendence to use in simple operations with the database,  very masive frameworks as Entity Frameworks, and the justifications are the follwoing

  • ADO.NET class can not be unit test
  • ADO.NET can not make Asynchronic operation

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

I am not opossite to EFm 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 day by day with the Db. 

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

https://youtu.be/ta4ZVLPra5E

History

First version.


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK